# Which Problems Are Solved
As an outcome of [this
issue](https://github.com/zitadel/zitadel/issues/9599) we want to
implement relational tables in Zitadel. For that we use new tables as a
successor of the current tables used by Zitadel in `projections`, `auth`
and `admin` schemas. The new logic is based on [this
proposal](https://github.com/zitadel/zitadel/pull/9870). This issue does
not contain the switch from CQRS to the new tables. This is change will
be implemented in a later stage.
We focus on the most critical tables which is user authentication.
We need a table to manage organizations.
### organization fields
The following fields must be managed in this table:
- `id`
- `instance_id`
- `name`
- `state` enum (active, inactive)
- `created_at`
- `updated_at`
- `deleted_at`
DISCUSS: should we add a `primary_domain` to this table so that we do
not have to join on domains to return a simple org?
We must ensure the unique constraints for this table matches the current
commands.
### organization repository
The repository must provide the following functions:
Manipulations:
- create
- `instance_id`
- `name`
- update
- `name`
- delete
Queries:
- get returns single organization matching the criteria and pagination,
should return error if multiple were found
- list returns list of organizations matching the criteria, pagination
Criteria are the following:
- by id
- by name
pagination:
- by created_at
- by updated_at
- by name
### organization events
The following events must be applied on the table using a projection
(`internal/query/projection`)
- `org.added` results in create
- `org.changed` sets the `name` field
- `org.deactivated` sets the `state` field
- `org.reactivated` sets the `state` field
- `org.removed` sets the `deleted_at` field
- if answer is yes to discussion: `org.domain.primary.set` sets the
`primary_domain` field
- `instance.removed` sets the the `deleted_at` field if not already set
### acceptance criteria
- [x] migration is implemented and gets executed
- [x] domain interfaces are implemented and documented for service layer
- [x] repository is implemented and implements domain interface
- [x] testing
- [x] the repository methods
- [x] events get reduced correctly
- [x] unique constraints
# Additional Context
Replace this example with links to related issues, discussions, discord
threads, or other sources with more context.
Use the Closing #issue syntax for issues that are resolved with this PR.
- Closes #https://github.com/zitadel/zitadel/issues/9936
---------
Co-authored-by: adlerhurst <27845747+adlerhurst@users.noreply.github.com>
<!--
Please inform yourself about the contribution guidelines on submitting a
PR here:
https://github.com/zitadel/zitadel/blob/main/CONTRIBUTING.md#submit-a-pull-request-pr.
Take note of how PR/commit titles should be written and replace the
template texts in the sections below. Don't remove any of the sections.
It is important that the commit history clearly shows what is changed
and why.
Important: By submitting a contribution you agree to the terms from our
Licensing Policy as described here:
https://github.com/zitadel/zitadel/blob/main/LICENSING.md#community-contributions.
-->
# Which Problems Are Solved
Implementing Instance table to new relational database schema
# How the Problems Are Solved
The following fields must be managed in this table:
- `id`
- `name`
- `default_org_id`
- `zitadel_project_id`
- `console_client_id`
- `console_app_id`
- `default_language`
- `created_at`
- `updated_at`
- `deleted_at`
The repository must provide the following functions:
Manipulations:
- create
- `name`
- `default_org_id`
- `zitadel_project_id`
- `console_client_id`
- `console_app_id`
- `default_language`
- update
- `name`
- `default_language`
- delete
Queries:
- get returns single instance matching the criteria and pagination,
should return error if multiple instances were found
- list returns list of instances matching the criteria, pagination
Criteria are the following:
- by id
pagination:
- by created_at
- by updated_at
- by name
### instance events
The following events must be applied on the table using a projection
(`internal/query/projection`)
- `instance.added` results in create
- `instance.changed` changes the `name` field
- `instance.removed` sets the the `deleted_at` field
- `instance.default.org.set` sets the `default_org_id` field
- `instance.iam.project.set` sets the `zitadel_project_id` field
- `instance.iam.console.set` sets the `console_client_id` and
`console_app_id` fields
- `instance.default.language.set` sets the `default_language` field
- if answer is yes to discussion: `instance.domain.primary.set` sets the
`primary_domain` field
### acceptance criteria
- [x] migration is implemented and gets executed
- [x] domain interfaces are implemented and documented for service layer
- [x] repository is implemented and implements domain interface
- [x] testing
- [x] the repository methods
- [x] events get reduced correctly
- [x] unique constraints
# Additional Context
- Closes https://github.com/zitadel/zitadel/issues/9935
# Which Problems Are Solved
The resource usage to query user(s) on the database was high and
therefore could have performance impact.
# How the Problems Are Solved
Database queries involving the users and loginnames table were improved
and an index was added for user by email query.
# Additional Changes
- spellchecks
- updated apis on load tests
# additional info
needs cherry pick to v3
# Which Problems Are Solved
Permission checks in project v2beta API did not cover projects and
granted projects correctly.
# How the Problems Are Solved
Add permission checks v1 correctly to the list queries, add correct
permission checks v2 for projects.
# Additional Changes
Correct Pre-Checks for project grants that the right resource owner is
used.
# Additional Context
Permission checks v2 for project grants is still outstanding under
#9972.
# Which Problems Are Solved
This pull request addresses a significant gap in the user service v2
API, which currently lacks methods for managing machine users.
# How the Problems Are Solved
This PR adds new API endpoints to the user service v2 to manage machine
users including their secret, keys and personal access tokens.
Additionally, there's now a CreateUser and UpdateUser endpoints which
allow to create either a human or machine user and update them. The
existing `CreateHumanUser` endpoint has been deprecated along the
corresponding management service endpoints. For details check the
additional context section.
# Additional Context
- Closes https://github.com/zitadel/zitadel/issues/9349
## More details
- API changes: https://github.com/zitadel/zitadel/pull/9680
- Implementation: https://github.com/zitadel/zitadel/pull/9763
- Tests: https://github.com/zitadel/zitadel/pull/9771
## Follow-ups
- Metadata: support managing user metadata using resource API
https://github.com/zitadel/zitadel/pull/10005
- Machine token type: support managing the machine token type (migrate
to new enum with zero value unspecified?)
---------
Co-authored-by: Copilot <175728472+Copilot@users.noreply.github.com>
Co-authored-by: Livio Spring <livio.a@gmail.com>
# Which Problems Are Solved
Add the ability to keep track of the current counts of projection
resources. We want to prevent calling `SELECT COUNT(*)` on tables, as
that forces a full scan and sudden spikes of DB resource uses.
# How the Problems Are Solved
- A resource_counts table is added
- Triggers that increment and decrement the counted values on inserts
and deletes
- Triggers that delete all counts of a table when the source table is
TRUNCATEd. This is not in the business logic, but prevents wrong counts
in case someone want to force a re-projection.
- Triggers that delete all counts if the parent resource is deleted
- Script to pre-populate the resource_counts table when a new source
table is added.
The triggers are reusable for any type of resource, in case we choose to
add more in the future.
Counts are aggregated by a given parent. Currently only `instance` and
`organization` are defined as possible parent. This can later be
extended to other types, such as `project`, should the need arise.
I deliberately chose to use `parent_id` to distinguish from the
de-factor `resource_owner` which is usually an organization ID. For
example:
- For users the parent is an organization and the `parent_id` matches
`resource_owner`.
- For organizations the parent is an instance, but the `resource_owner`
is the `org_id`. In this case the `parent_id` is the `instance_id`.
- Applications would have a similar problem, where the parent is a
project, but the `resource_owner` is the `org_id`
# Additional Context
Closes https://github.com/zitadel/zitadel/issues/9957
# Which Problems Are Solved
A customer reported that randomly certain login flows, such as automatic
redirect to the only configured IdP would not work. During the
investigation it was discovered that they used that same primary domain
on two different instances. As they used the domain for preselecting the
organization, one would always overwrite the other in the cache. Since
The organization and especially it's policies could not be retrieved on
the other instance, it would fallback to the default organization
settings, where the external login and the corresponding IdP were not
configured.
# How the Problems Are Solved
Include the instance id in the cache key for organizations to prevent
overwrites.
# Additional Changes
None
# Additional Context
- found because of a support request
- requires backport to 2.70.x, 2.71.x and 3.x
# Eventstore fixes
- `event.Position` used float64 before which can lead to [precision
loss](https://github.com/golang/go/issues/47300). The type got replaced
by [a type without precision
loss](https://github.com/jackc/pgx-shopspring-decimal)
- the handler reported the wrong error if the current state was updated
and therefore took longer to retry failed events.
# Mirror fixes
- max age of auth requests can be configured to speed up copying data
from `auth.auth_requests` table. Auth requests last updated before the
set age will be ignored. Default is 1 month
- notification projections are skipped because notifications should be
sent by the source system. The projections are set to the latest
position
- ensure that mirror can be executed multiple times
---------
Co-authored-by: Livio Spring <livio.a@gmail.com>
# Which Problems Are Solved
the mapping of `ListUsers` was wrong for user states.
# How the Problems Are Solved
mapping of user state introduced to correctly map it
# Additional Changes
mapping of user type introduced to prevent same issue
# Additional Context
Requires backport to 2.x and 3.x
Co-authored-by: Livio Spring <livio.a@gmail.com>
# Which Problems Are Solved
Currently if a user signs in using an IdP, once they sign out of
Zitadel, the corresponding IdP session is not terminated. This can be
the desired behavior. In some cases, e.g. when using a shared computer
it results in a potential security risk, since a follower user might be
able to sign in as the previous using the still open IdP session.
# How the Problems Are Solved
- Admins can enabled a federated logout option on SAML IdPs through the
Admin and Management APIs.
- During the termination of a login V1 session using OIDC end_session
endpoint, Zitadel will check if an IdP was used to authenticate that
session.
- In case there was a SAML IdP used with Federated Logout enabled, it
will intercept the logout process, store the information into the shared
cache and redirect to the federated logout endpoint in the V1 login.
- The V1 login federated logout endpoint checks every request on an
existing cache entry. On success it will create a SAML logout request
for the used IdP and either redirect or POST to the configured SLO
endpoint. The cache entry is updated with a `redirected` state.
- A SLO endpoint is added to the `/idp` handlers, which will handle the
SAML logout responses. At the moment it will check again for an existing
federated logout entry (with state `redirected`) in the cache. On
success, the user is redirected to the initially provided
`post_logout_redirect_uri` from the end_session request.
# Additional Changes
None
# Additional Context
- This PR merges the https://github.com/zitadel/zitadel/pull/9841 and
https://github.com/zitadel/zitadel/pull/9854 to main, additionally
updating the docs on Entra ID SAML.
- closes#9228
- backport to 3.x
---------
Co-authored-by: Silvan <27845747+adlerhurst@users.noreply.github.com>
Co-authored-by: Zach Hirschtritt <zachary.hirschtritt@klaviyo.com>
# Which Problems Are Solved
Resource management of projects and sub-resources was before limited by
the context provided by the management API, which would mean you could
only manage resources belonging to a specific organization.
# How the Problems Are Solved
With the addition of a resource-based API, it is now possible to manage
projects and sub-resources on the basis of the resources themselves,
which means that as long as you have the permission for the resource,
you can create, read, update and delete it.
- CreateProject to create a project under an organization
- UpdateProject to update an existing project
- DeleteProject to delete an existing project
- DeactivateProject and ActivateProject to change the status of a
project
- GetProject to query for a specific project with an identifier
- ListProject to query for projects and granted projects
- CreateProjectGrant to create a project grant with project and granted
organization
- UpdateProjectGrant to update the roles of a project grant
- DeactivateProjectGrant and ActivateProjectGrant to change the status
of a project grant
- DeleteProjectGrant to delete an existing project grant
- ListProjectGrants to query for project grants
- AddProjectRole to add a role to an existing project
- UpdateProjectRole to change texts of an existing role
- RemoveProjectRole to remove an existing role
- ListProjectRoles to query for project roles
# Additional Changes
- Changes to ListProjects, which now contains granted projects as well
- Changes to messages as defined in the
[API_DESIGN](https://github.com/zitadel/zitadel/blob/main/API_DESIGN.md)
- Permission checks for project functionality on query and command side
- Added testing to unit tests on command side
- Change update endpoints to no error returns if nothing changes in the
resource
- Changed all integration test utility to the new service
- ListProjects now also correctly lists `granted projects`
- Permission checks for project grant and project role functionality on
query and command side
- Change existing pre checks so that they also work resource specific
without resourceowner
- Added the resourceowner to the grant and role if no resourceowner is
provided
- Corrected import tests with project grants and roles
- Added testing to unit tests on command side
- Change update endpoints to no error returns if nothing changes in the
resource
- Changed all integration test utility to the new service
- Corrected some naming in the proto files to adhere to the API_DESIGN
# Additional Context
Closes#9177
---------
Co-authored-by: Livio Spring <livio.a@gmail.com>
<!--
Please inform yourself about the contribution guidelines on submitting a
PR here:
https://github.com/zitadel/zitadel/blob/main/CONTRIBUTING.md#submit-a-pull-request-pr.
Take note of how PR/commit titles should be written and replace the
template texts in the sections below. Don't remove any of the sections.
It is important that the commit history clearly shows what is changed
and why.
Important: By submitting a contribution you agree to the terms from our
Licensing Policy as described here:
https://github.com/zitadel/zitadel/blob/main/LICENSING.md#community-contributions.
-->
# Which Problems Are Solved
These changes introduce resource-based API endpoints for managing
instances and custom domains.
There are 4 types of changes:
- Endpoint implementation: consisting of the protobuf interface and the
implementation of the endpoint. E.g:
606439a172
- (Integration) Tests: testing the implemented endpoint. E.g:
cdfe1f0372
- Fixes: Bugs found during development that are being fixed. E.g:
acbbeedd32
- Miscellaneous: code needed to put everything together or that doesn't
fit any of the above categories. E.g:
529df92abc or
6802cb5468
# How the Problems Are Solved
_Ticked checkboxes indicate that the functionality is complete_
- [x] Instance
- [x] Create endpoint
- [x] Create endpoint tests
- [x] Update endpoint
- [x] Update endpoint tests
- [x] Get endpoint
- [x] Get endpoint tests
- [x] Delete endpoint
- [x] Delete endpoint tests
- [x] Custom Domains
- [x] Add custom domain
- [x] Add custom domain tests
- [x] Remove custom domain
- [x] Remove custom domain tests
- [x] List custom domains
- [x] List custom domains tests
- [x] Trusted Domains
- [x] Add trusted domain
- [x] Add trusted domain tests
- [x] Remove trusted domain
- [x] Remove trusted domain tests
- [x] List trusted domains
- [x] List trusted domains tests
# Additional Changes
When looking for instances (through the `ListInstances` endpoint)
matching a given query, if you ask for the results to be order by a
specific column, the query will fail due to a syntax error. This is
fixed in acbbeedd32 . Further explanation
can be found in the commit message
# Additional Context
- Relates to #9452
- CreateInstance has been excluded:
https://github.com/zitadel/zitadel/issues/9930
- Permission checks / instance retrieval (middleware) needs to be
changed to allow context based permission checks
(https://github.com/zitadel/zitadel/issues/9929), required for
ListInstances
---------
Co-authored-by: Livio Spring <livio.a@gmail.com>
# Which Problems Are Solved
When users are removed, their auth factors stay in the projection. This
data inconsistency is visible if a removed user is recreated with the
same ID. In such a case, the login UI and the query API methods show the
removed users auth methods. This is unexpected behavior.
The old users auth methods are not usable to log in and they are not
found by the command side. This is expected behavior.
# How the Problems Are Solved
The auth factors projection reduces the user removed event by deleting
all factors.
# Additional Context
- Reported by support request
- requires backport to 2.x and 3.x
# Which Problems Are Solved
In rare cases there was a possibility that multiple users were found by
a loginname. This prevented the corresponding user to sign in.
# How the Problems Are Solved
Fixed the corresponding query (to correctly respect the org domain
policy).
# Additional Changes
None
# Additional Context
Found during the investigation of a support request
# Which Problems Are Solved
When a user changes their password, Zitadel needs to terminate all of
that user's active sessions. This query can take many seconds on
deployments with large session and user tables. This happens as part of
session projection handling, so doesn't directly impact user experience,
but potentially bogs down the projection handler which isn't great. In
the future, this index could be used to power a "see all of my current
sessions" feature in Zitadel.
# How the Problems Are Solved
Adds new index on `user_id` column on `projections.sessions8` table.
Alternatively, we can index on `(instance_id, user_id)` instead but
opted for keeping the index smaller as we already index on `instance_id`
separately.
# Additional Changes
None
# Additional Context
None
---------
Co-authored-by: Silvan <27845747+adlerhurst@users.noreply.github.com>
# Which Problems Are Solved
In Actions v2, if a target is removed, which is still used in an
execution, the target is still listed when list executions.
# How the Problems Are Solved
Removed targets are now also removed from the executions.
# Additional Changes
To be sure the list executions include a check if the target is still
existing.
# Additional Context
None
Co-authored-by: Livio Spring <livio.a@gmail.com>
# Which Problems Are Solved
The `auth.auth_requests` table is not cleaned up so long running Zitadel
installations can contain many rows.
The mirror command can take long because a the data are first copied
into memory (or disk) on cockroach and users do not get any output from
mirror. This is unfortunate because people don't know if Zitadel got
stuck.
# How the Problems Are Solved
Enhance logging throughout the projection processes and introduce a
configuration option for the maximum age of authentication requests.
# Additional Changes
None
# Additional Context
closes https://github.com/zitadel/zitadel/issues/9764
---------
Co-authored-by: Livio Spring <livio.a@gmail.com>
# Which Problems Are Solved
Actions v2 is not a feature flag anymore, include functionality on
executions is not used and json tags of proto messages are handled
incorrectly.
# How the Problems Are Solved
- Remove actions from the feature flags on system and instance level
- Remove include type on executions, only in the API, later maybe in the
handling logic as well
- Use protojson in request and response handling of actions v2
# Additional Changes
- Correct integration tests for request and response handling
- Use json.RawMessage for events, so that the event payload is not
base64 encoded
- Added separate context for async webhook calls, that executions are
not cancelled when called async
# Additional Context
Related to #9759Closes#9710
---------
Co-authored-by: Livio Spring <livio.a@gmail.com>
# Which Problems Are Solved
Add the possibility to filter project resources based on project member
roles.
# How the Problems Are Solved
Extend and refactor existing Pl/PgSQL functions to implement the
following:
- Solve O(n) complexity in returned resources IDs by returning a boolean
filter for instance level permissions.
- Individually permitted orgs are returned only if there was no instance
permission
- Individually permitted projects are returned only if there was no
instance permission
- Because of the multiple filter terms, use `INNER JOIN`s instead of
`WHERE` clauses.
# Additional Changes
- system permission function no longer query the organization view and
therefore can be `immutable`, giving big performance benefits for
frequently reused system users. (like our hosted login in Zitadel cloud)
- The permitted org and project functions are now defined as `stable`
because the don't modify on-disk data. This might give a small
performance gain
- The Pl/PgSQL functions are now tested using Go unit tests.
# Additional Context
- Depends on https://github.com/zitadel/zitadel/pull/9677
- Part of https://github.com/zitadel/zitadel/issues/9188
- Closes https://github.com/zitadel/zitadel/issues/9190
# Which Problems Are Solved
Classic permission checks execute for every returned row on resource
based search APIs. Complete background and problem definition can be
found here: https://github.com/zitadel/zitadel/issues/9188
# How the Problems Are Solved
- PermissionClause function now support dynamic query building, so it
supports multiple cases.
- PermissionClause is applied to all list resources which support org
level permissions.
- Wrap permission logic into wrapper functions so we keep the business
logic clean.
# Additional Changes
- Handle org ID optimization in the query package, so it is reusable for
all resources, instead of extracting the filter in the API.
- Cleanup and test system user conversion in the authz package. (context
middleware)
- Fix: `core_integration_db_up` make recipe was missing the postgres
service.
# Additional Context
- Related to https://github.com/zitadel/zitadel/issues/9190
