This PR is still WIP and needs changes to at least the tests.
# Which Problems Are Solved
To be able to report analytical / telemetry data from deployed Zitadel
systems back to a central endpoint, we designed a "service ping"
functionality. See also https://github.com/zitadel/zitadel/issues/9706.
This PR adds the first implementation to allow collection base data as
well as report amount of resources such as organizations, users per
organization and more.
# How the Problems Are Solved
- Added a worker to handle the different `ReportType` variations.
- Schedule a periodic job to start a `ServicePingReport`
- Configuration added to allow customization of what data will be
reported
- Setup step to generate and store a `systemID`
# Additional Changes
None
# Additional Context
relates to #9869
# Which Problems Are Solved
We provide a seamless way to initialize Zitadel and the login together.
# How the Problems Are Solved
Additionally to the `IAM_OWNER` role, a set up admin user also gets the
`IAM_LOGIN_CLIENT` role if it is a machine user with a PAT.
# Additional Changes
- Simplifies the load balancing example, as the intermediate
configuration step is not needed anymore.
# Additional Context
- Depends on #10116
- Contributes to https://github.com/zitadel/zitadel-charts/issues/332
- Contributes to https://github.com/zitadel/zitadel/issues/10016
---------
Co-authored-by: Stefan Benz <46600784+stebenz@users.noreply.github.com>
# Which Problems Are Solved
Remove the feature flag that allowed triggers in introspection. This
option was a fallback in case introspection would not function properly
without triggers. The API documentation asked for anyone using this flag
to raise an issue. No such issue was received, hence we concluded it is
safe to remove it.
# How the Problems Are Solved
- Remove flags from the system and instance level feature APIs.
- Remove trigger functions that are no longer used
- Adjust tests that used the flag.
# Additional Changes
- none
# Additional Context
- Closes#10026
- Flag was introduced in #7356
---------
Co-authored-by: Silvan <27845747+adlerhurst@users.noreply.github.com>
# Which Problems Are Solved
Stabilize the optimized introspection code and cleanup unused code.
# How the Problems Are Solved
- `oidc_legacy_introspection` feature flag is removed and reserved.
- `OPStorage` which are no longer needed have their bodies removed.
- The method definitions need to remain in place so the interface
remains implemented.
- A panic is thrown in case any such method is still called
# Additional Changes
- A number of `OPStorage` methods related to token creation were already
unused. These are also cleaned up.
# Additional Context
- Closes#10027
- #7822
---------
Co-authored-by: Livio Spring <livio.a@gmail.com>
# Which Problems Are Solved
We are preparing to roll-out and stabilize webkeys in the next version
of Zitadel. Before removing legacy signing-key code, we must ensure all
existing instances have their webkeys generated.
# How the Problems Are Solved
Add a setup step which generate 2 webkeys for each existing instance
that didn't have webkeys yet.
# Additional Changes
Return an error from the config type-switch, when the type is unknown.
# Additional Context
- Part 1/2 of https://github.com/zitadel/zitadel/issues/10029
- Should be back-ported to v3
<!--
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
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
1. there was no embedded database to run tests against
2. there were no tests for postgres/migrate
3. there was no test setup for repository which starts a client for the
embedded database
# How the Problems Are Solved
1. postgres/embedded package was added
2. tests were added
3. TestMain was added incl. an example test
# Additional Changes
none
# Additional Context
closes#9934
---------
Co-authored-by: Copilot <175728472+Copilot@users.noreply.github.com>
# Which Problems Are Solved
To start with transactional tables we need to setup the new `zitadel`
schema in a way that does not rely on the event store later.
# How the Problems Are Solved
Setup step added which calls the function which executes the migrations.
# Additional Changes
none
# Additional Context
- closes#9933
# 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
We saw high CPU usage if many events were created on the database. This
was caused by the new actions which query for all event types and
aggregate types.
# How the Problems Are Solved
- the handler of action execution does not filter for aggregate and
event types.
- the index for `instance_id` and `position` is reenabled.
# Additional Changes
none
# Additional Context
none
# Which Problems Are Solved
#9837 added a new index `es_instance_position` on the events table with
the idea to improve performance for some projections. Unfortunately, it
makes it worse for almost all projections and would only improve the
situation for the events handler of the actions V2 subscriptions.
# How the Problems Are Solved
Remove the index again.
# Additional Changes
None
# Additional Context
relates to #9837
relates to #9863
# Which Problems Are Solved
The execution handler projection handles all events to check if an
execution has to be provided to the worker to execute.
In this logic all events would be processed from the beginning which is
not necessary.
# How the Problems Are Solved
Add the current state to the execution handler projection, to avoid
processing all existing events.
# Additional Changes
Add custom configuration to the default, so that the transactions are
limited to some events.
# Additional Context
None
# Which Problems Are Solved
Step 54 was not executed during setup.
# How the Problems Are Solved
Added the step to setup jobs
# Additional Changes
none
# Additional Context
- the step was added in https://github.com/zitadel/zitadel/pull/9837
- thanks to @zhirschtritt for raising this.
# Which Problems Are Solved
Some projection queries took a long time to run. It seems that 1 or more
queries couldn't make proper use of the `es_projection` index. This
might be because of a specific complexity aggregate_type and event_type
arguments, making the index unfeasible for postgres.
# How the Problems Are Solved
Following the index recommendation, add and index that covers just
instance_id and position.
# Additional Changes
- none
# Additional Context
- Related to https://github.com/zitadel/zitadel/issues/9832
# Which Problems Are Solved
When running a long-running Zitadel Setup, Kubernetes might decide to
move a pod to a new node automatically. Currently, this puts any
migrations into a broken state that an operator needs to manually run
the "cleanup" command on - assuming they catch the error.
The only super long running commands are typically projection pre-fill
operations, which depending on the size of the event table for that
projection, can take many hours - plenty of time for Kubernetes to make
unexpected decisions, especially in a busy cluster.
# How the Problems Are Solved
This change listens on `os.Interrupt` and `syscall.SIGTERM`, cancels the
current Setup context, and runs the `Cleanup` command. The logs then
look something like this:
```shell
...
INFO[0000] verify migration caller="/Users/zach/src/zitadel/internal/migration/migration.go:43" name=repeatable_delete_stale_org_fields
INFO[0000] starting migration caller="/Users/zach/src/zitadel/internal/migration/migration.go:66" name=repeatable_delete_stale_org_fields
INFO[0000] execute delete query caller="/Users/zach/src/zitadel/cmd/setup/39.go:37" instance_id=281297936179003398 migration=repeatable_delete_stale_org_fields progress=1/1
INFO[0000] verify migration caller="/Users/zach/src/zitadel/internal/migration/migration.go:43" name=repeatable_fill_fields_for_instance_domains
INFO[0000] starting migration caller="/Users/zach/src/zitadel/internal/migration/migration.go:66" name=repeatable_fill_fields_for_instance_domains
----- SIGTERM signal issued -----
INFO[0000] received interrupt signal, shutting down: interrupt caller="/Users/zach/src/zitadel/cmd/setup/setup.go:121"
INFO[0000] query failed caller="/Users/zach/src/zitadel/internal/eventstore/repository/sql/query.go:135" error="timeout: context already done: context canceled"
DEBU[0000] filter eventstore failed caller="/Users/zach/src/zitadel/internal/eventstore/handler/v2/field_handler.go:155" error="ID=SQL-KyeAx Message=unable to filter events Parent=(timeout: context already done: context canceled)" projection=instance_domain_fields
DEBU[0000] unable to rollback tx caller="/Users/zach/src/zitadel/internal/eventstore/handler/v2/field_handler.go:110" error="sql: transaction has already been committed or rolled back" projection=instance_domain_fields
INFO[0000] process events failed caller="/Users/zach/src/zitadel/internal/eventstore/handler/v2/field_handler.go:72" error="ID=SQL-KyeAx Message=unable to filter events Parent=(timeout: context already done: context canceled)" projection=instance_domain_fields
DEBU[0000] trigger iteration caller="/Users/zach/src/zitadel/internal/eventstore/handler/v2/field_handler.go:73" iteration=0 projection=instance_domain_fields
ERRO[0000] migration failed caller="/Users/zach/src/zitadel/internal/migration/migration.go:68" error="ID=SQL-KyeAx Message=unable to filter events Parent=(timeout: context already done: context canceled)" name=repeatable_fill_fields_for_instance_domains
ERRO[0000] migration finish failed caller="/Users/zach/src/zitadel/internal/migration/migration.go:71" error="context canceled" name=repeatable_fill_fields_for_instance_domains
----- Cleanup before exiting -----
INFO[0000] cleanup started caller="/Users/zach/src/zitadel/cmd/setup/cleanup.go:30"
INFO[0000] cleanup migration caller="/Users/zach/src/zitadel/cmd/setup/cleanup.go:47" name=repeatable_fill_fields_for_instance_domains
```
# Additional Changes
* `mustExecuteMigration` -> `executeMigration`: **must**Execute logged a
Fatal error previously which calls os.Exit so no cleanup was possible.
Instead, this PR returns an error and assigns it to a shared error in
the Setup closure that defer can check.
* `initProjections` now returns an error instead of exiting
# Additional Context
This behavior might be unwelcome or at least unexpected in some cases.
Putting it behind a feature flag or config setting is likely a good
followup.
---------
Co-authored-by: Silvan <27845747+adlerhurst@users.noreply.github.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
With v2.71.0 the `idp_templates6_ldap3` projection was created but never
filled, as it was a subtable. To fix this we altered the
`idp_templates6_ldap3` to `idp_templates6_ldap2` with v2.71.5.
This was unfortunately without a check that the `idp_templates_ldap2`was
already existing, which resulted in an error in the setup step.
# How the Problems Are Solved
Add check if `idp_templates6_ldap2` is already existing, before renaming
`idp_templates6_ldap3` -> `idp_templates6_ldap2`.
# Additional Changes
None
# Additional Context
Closes#9669
# Which Problems Are Solved
With current provided telemetry it's difficult to predict when a
projection handler is under increased load until it's too late and
causes downstream issues. Importantly, projection updating is in the
critical path for many login flows and increased latency there can
result in system downtime for users.
# How the Problems Are Solved
This PR adds three new prometheus-style metrics:
1. **projection_events_processed** (_labels: projection, success_) -
This metric gives us a counter of the number of events processed per
projection update run and whether they we're processed without error. A
high number of events being processed can let us know how busy a
particular projection handler is.
2. **projection_handle_timer** _(labels: projection)_ - This is the time
it takes to process a projection update given a batch of events - time
to take the current_states lock, query for new events, reduce,
update_the projection, and update current_states.
3. **projection_state_latency** _(labels: projection)_ - This is the
time from the last event processed in the current_states table for a
given projection. It tells us how old was the last event you processed?
Or, how far behind are you running for this projection? Higher latencies
could mean high load or stalled projection handling.
# Additional Changes
I also had to initialize the global otel metrics provider (`metrics.M`)
in the `setup` step additionally to `start` since projection handlers
are initialized at setup. The initialization checks if a metrics
provider is already set (in case of `start-from-setup` or
`start-from-init` to prevent overwriting, which causes the otel metrics
provider to stop working.
# Additional Context
## Example Dashboards
---------
Co-authored-by: Silvan <27845747+adlerhurst@users.noreply.github.com>
Co-authored-by: Livio Spring <livio.a@gmail.com>
# Which Problems Are Solved
Zitadel setup with v2.71.0 could result in errors regarding the
idp_templates6_ldap3 subtable.
# How the Problems Are Solved
Rename the subtable idp_templates6_ldap3 to idp_templates6_ldap2 if no
idp_templates6_ldap2 is existing and rename column `rootCA` to
`root_ca`.
# Additional Changes
None
# Additional Context
Related PR #9292
---------
Co-authored-by: Silvan <27845747+adlerhurst@users.noreply.github.com>
# Which Problems Are Solved
If configuration `notifications.LegacyEnabled` is set to false when
using cockroachdb as a database Zitadel start does not work and prints
the following error: `level=fatal msg="unable to start zitadel"
caller="github.com/zitadel/zitadel/cmd/start/start_from_init.go:44"
error="can't scan into dest[0]: cannot scan NULL into *string"`
# How the Problems Are Solved
The combination of the setting and cockraochdb are checked and a better
error is provided to the user.
# Additional Context
- introduced with https://github.com/zitadel/zitadel/pull/9321
# Which Problems Are Solved
SQL error in `cmd/setup/49/01-permitted_orgs_function.sql`
# How the Problems Are Solved
Updating `cmd/setup/49/01-permitted_orgs_function.sql`
# Additional Context
- Closes https://github.com/zitadel/zitadel/issues/9461
Co-authored-by: Iraq Jaber <IraqJaber@gmail.com>
# Which Problems Are Solved
The recently introduced notification queue have potential race conditions.
# How the Problems Are Solved
Current code is refactored to use the queue package, which is safe in
regards of concurrency.
# Additional Changes
- the queue is included in startup
- improved code quality of queue
# Additional Context
- closes https://github.com/zitadel/zitadel/issues/9278
# Which Problems Are Solved
Setup fails to push all role permission events when running Zitadel with
CockroachDB. `TransactionRetryError`s were visible in logs which finally
times out the setup job with `timeout: context deadline exceeded`
# How the Problems Are Solved
As suggested in the [Cockroach documentation](timeout: context deadline
exceeded), _"break down larger transactions"_. The commands to be pushed
for the role permissions are chunked in 50 events per push. This
chunking is only done with CockroachDB.
# Additional Changes
- gci run fixed some unrelated imports
- access to `command.Commands` for the setup job, so we can reuse the
sync logic.
# Additional Context
Closes#9293
---------
Co-authored-by: Silvan <27845747+adlerhurst@users.noreply.github.com>
# Which Problems Are Solved
Some OAuth2 and OIDC providers require the use of PKCE for all their
clients. While ZITADEL already recommended the same for its clients, it
did not yet support the option on the IdP configuration.
# How the Problems Are Solved
- A new boolean `use_pkce` is added to the add/update generic OAuth/OIDC
endpoints.
- A new checkbox is added to the generic OAuth and OIDC provider
templates.
- The `rp.WithPKCE` option is added to the provider if the use of PKCE
has been set.
- The `rp.WithCodeChallenge` and `rp.WithCodeVerifier` options are added
to the OIDC/Auth BeginAuth and CodeExchange function.
- Store verifier or any other persistent argument in the intent or auth
request.
- Create corresponding session object before creating the intent, to be
able to store the information.
- (refactored session structs to use a constructor for unified creation
and better overview of actual usage)
Here's a screenshot showing the URI including the PKCE params:
# Additional Changes
None.
# Additional Context
- Closes#6449
- This PR replaces the existing PR (#8228) of @doncicuto. The base he
did was cherry picked. Thank you very much for that!
---------
Co-authored-by: Miguel Cabrerizo <doncicuto@gmail.com>
Co-authored-by: Stefan Benz <46600784+stebenz@users.noreply.github.com>
# Which Problems Are Solved
Performance issue for GRPC call `zitadel.user.v2.UserService.ListUsers`
due to lack of org filtering on `ListUsers`
# 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/9191
---------
Co-authored-by: Iraq Jaber <IraqJaber@gmail.com>
Co-authored-by: Tim Möhlmann <tim+github@zitadel.com>
# Which Problems Are Solved
OIDC applications can configure the used login version, which is
currently not possible for SAML applications.
# How the Problems Are Solved
Add the same functionality dependent on the feature-flag for SAML
applications.
# Additional Changes
None
# Additional Context
Closes#9267
Follow up issue for frontend changes #9354
---------
Co-authored-by: Livio Spring <livio.a@gmail.com>
# Which Problems Are Solved
To integrate river as a task queue we need to ensure the migrations of
river are executed.
# How the Problems Are Solved
- A new schema was added to the Zitadel database called "queue"
- Added a repeatable setup step to Zitadel which executes the
[migrations of
river](https://riverqueue.com/docs/migrations#go-migration-api).
# Additional Changes
- Added more hooks to the databases to properly set the schema for the
task queue
# Additional Context
- Closes https://github.com/zitadel/zitadel/issues/9280
# Which Problems Are Solved
Systems running with PostgreSQL before Zitadel v2.39 are likely to have
a wrong type for the `in_tx_order` column in the `eventstore.event2`
table. The migration at the time used the `event_sequence` as default
value without typecast, which results in a `bigint` type for that
column. However, when creating the table from scratch, we explicitly
specify the type to be `integer`.
Starting from Zitadel v2.67 we use a Pl/PgSQL function to push events.
The function requires the types from `eventstore.events2` to the same as
the `select` destinations used in the function. In the function
`in_tx_order` is also expected to by of `integer` type.
CochroachDB systems are not affected because `bigint` is an alias to the
`int` type. In other words, CockroachDB uses `int8` when specifying type
`int`. Therefore the types already match.
# How the Problems Are Solved
Retrieve the actual column type currently in use. A template is used to
assign the type to the `ordinality` column returned as `in_tx_order`.
# Additional Changes
- Detailed logging on migration failure
# Additional Context
- Closes#9180
---------
Co-authored-by: Silvan <27845747+adlerhurst@users.noreply.github.com>
# Which Problems Are Solved
The login by email was not possible anymore. This was due to a newly
generated user projection because of #9255 .
Internal logs showed that the computed lower case column for verified
email was missing.
# How the Problems Are Solved
Update name of setup step 25 to rerun the step, since the underlying sql
changed.
# Additional Changes
None
# Additional Context
- relates to #9255
# Which Problems Are Solved
After updating to version 2.69.0, my zitadel instance refuse to start
with this error log :
```
time="2025-02-03T19:46:47Z" level=info msg="starting migration" caller="/home/runner/work/zitadel/zitadel/internal/migration/migration.go:66" name=46_init_permission_functions
time="2025-02-03T19:46:47Z" level=info msg="execute statement" caller="/home/runner/work/zitadel/zitadel/cmd/setup/46.go:29" file=01-role_permissions_view.sql migration=46_init_permission_functions
time="2025-02-03T19:46:47Z" level=info msg="execute statement" caller="/home/runner/work/zitadel/zitadel/cmd/setup/46.go:29" file=02-instance_orgs_view.sql migration=46_init_permission_functions
time="2025-02-03T19:46:47Z" level=info msg="execute statement" caller="/home/runner/work/zitadel/zitadel/cmd/setup/46.go:29" file=03-instance_members_view.sql migration=46_init_permission_functions
time="2025-02-03T19:46:47Z" level=info msg="execute statement" caller="/home/runner/work/zitadel/zitadel/cmd/setup/46.go:29" file=04-org_members_view.sql migration=46_init_permission_functions
time="2025-02-03T19:46:47Z" level=info msg="execute statement" caller="/home/runner/work/zitadel/zitadel/cmd/setup/46.go:29" file=05-project_members_view.sql migration=46_init_permission_functions
time="2025-02-03T19:46:47Z" level=info msg="execute statement" caller="/home/runner/work/zitadel/zitadel/cmd/setup/46.go:29" file=06-permitted_orgs_function.sql migration=46_init_permission_functions
time="2025-02-03T19:46:47Z" level=error msg="migration failed" caller="/home/runner/work/zitadel/zitadel/internal/migration/migration.go:68" error="46_init_permission_functions 06-permitted_orgs_function.sql: ERROR: subquery in FROM must have an alias (SQLSTATE 42601)" name=46_init_permission_functions
time="2025-02-03T19:46:47Z" level=fatal msg="migration failed" caller="/home/runner/work/zitadel/zitadel/cmd/setup/setup.go:274" error="46_init_permission_functions 06-permitted_orgs_function.sql: ERROR: subquery in FROM must have an alias (SQLSTATE 42601)" name=46_init_permission_functions
```
# How the Problems Are Solved
I used the original sql script on my database which gave me the same
error.
So i added an alias for the subquery and the error cas gone
# Additional Context
I was migrating from version 2.58.3
Closes https://github.com/zitadel/zitadel/issues/9300
Co-authored-by: Tim Möhlmann <tim+github@zitadel.com>
# Which Problems Are Solved
Add the ability to update the timestamp when MFA initialization was last
skipped.
Get User By ID now also returns the timestamps when MFA setup was last
skipped.
# How the Problems Are Solved
- Add a `HumanMFAInitSkipped` method to the `users/v2` API.
- MFA skipped was already projected in the `auth.users3` table. In this
PR the same column is added to the users projection. Event handling is
kept the same as in the `UserView`:
<details>
62804ca45f/internal/user/repository/view/model/user.go (L243-L377)
</details>
# Additional Changes
- none
# Additional Context
- Closes https://github.com/zitadel/zitadel/issues/9197
# Which Problems Are Solved
Paths for setup steps are joined with "\" when binary is started under
Windows, which results in wrongly joined paths.
# How the Problems Are Solved
Replace the usage of "filepath" with "path" package, which does only
join with "/" and nothing OS specific.
# Additional Changes
None
# Additional Context
Closes#9227
# Which Problems Are Solved
The membership fields migration timed out in certain cases. It also
tried to migrate instances which were already removed.
# How the Problems Are Solved
Revert the previous fix that combined the repeatable step for multiple
fill triggers. The membeship migration is now single-run as it might
take a lot of time. It is not worth making it repeatable. Instance IDs
of removed instances are skipped.
# Additional Changes
None
# Additional Context
Introduced in https://github.com/zitadel/zitadel/pull/9199
# Which Problems Are Solved
Memberships did not have a fields table fill migration.
# How the Problems Are Solved
Add filling of membership fields to the repeatable steps.
# Additional Changes
- Use the same repeatable step for multiple fill fields handlers.
- Fix an error for PostgreSQL 15 where a subquery in a `FROM` clause
needs an alias ing the `permitted_orgs` function.
# Additional Context
- Part of https://github.com/zitadel/zitadel/issues/9188
- Introduced in https://github.com/zitadel/zitadel/pull/9152
# Which Problems Are Solved
https://github.com/zitadel/zitadel/pull/9186 introduced the new `push`
sql function for cockroachdb. The function used the wrong database
function to generate the position of the event and would therefore
insert events at a position before events created with an old Zitadel
version.
# How the Problems Are Solved
Instead of `EXTRACT(EPOCH FROM NOW())`, `cluster_logical_timestamp()` is
used to calculate the position of an event.
# Additional Context
- Introduced in https://github.com/zitadel/zitadel/pull/9186
- Affected versions:
https://github.com/zitadel/zitadel/releases/tag/v2.67.3
# Which Problems Are Solved
Zitadel currently uses 3 database pool, 1 for queries, 1 for pushing
events and 1 for scheduled projection updates. This defeats the purpose
of a connection pool which already handles multiple connections.
During load tests we found that the current structure of connection
pools consumes a lot of database resources. The resource usage dropped
after we reduced the amount of database pools to 1 because existing
connections can be used more efficiently.
# How the Problems Are Solved
Removed logic to handle multiple connection pools and use a single one.
# Additional Changes
none
# Additional Context
part of https://github.com/zitadel/zitadel/issues/8352
# Which Problems Are Solved
Currently ZITADEL defines organization and instance member roles and
permissions in defaults.yaml. The permission check is done on API call
level. For example: "is this user allowed to make this call on this
org". This makes sense on the V1 API where the API is permission-level
shaped. For example, a search for users always happens in the context of
the organization. (Either the organization the calling user belongs to,
or through member ship and the x-zitadel-orgid header.
However, for resource based APIs we must be able to resolve permissions
by object. For example, an IAM_OWNER listing users should be able to get
all users in an instance based on the query filters. Alternatively a
user may have user.read permissions on one or more orgs. They should be
able to read just those users.
# How the Problems Are Solved
## Role permission mapping
The role permission mappings defined from `defaults.yaml` or local
config override are synchronized to the database on every run of
`zitadel setup`:
- A single query per **aggregate** builds a list of `add` and `remove`
actions needed to reach the desired state or role permission mappings
from the config.
- The required events based on the actions are pushed to the event
store.
- Events define search fields so that permission checking can use the
indices and is strongly consistent for both query and command sides.
The migration is split in the following aggregates:
- System aggregate for for roles prefixed with `SYSTEM`
- Each instance for roles not prefixed with `SYSTEM`. This is in
anticipation of instance level management over the API.
## Membership
Current instance / org / project membership events now have field table
definitions. Like the role permissions this ensures strong consistency
while still being able to use the indices of the fields table. A
migration is provided to fill the membership fields.
## Permission check
I aimed keeping the mental overhead to the developer to a minimal. The
provided implementation only provides a permission check for list
queries for org level resources, for example users. In the `query`
package there is a simple helper function `wherePermittedOrgs` which
makes sure the underlying database function is called as part of the
`SELECT` query and the permitted organizations are part of the `WHERE`
clause. This makes sure results from non-permitted organizations are
omitted. Under the hood:
- A Pg/PlSQL function searches for a list of organization IDs the passed
user has the passed permission.
- When the user has the permission on instance level, it returns early
with all organizations.
- The functions uses a number of views. The views help mapping the
fields entries into relational data and simplify the code use for the
function. The views provide some pre-filters which allow proper index
usage once the final `WHERE` clauses are set by the function.
# Additional Changes
# Additional Context
Closes#9032
Closes https://github.com/zitadel/zitadel/issues/9014https://github.com/zitadel/zitadel/issues/9188 defines follow-ups for
the new permission framework based on this concept.
# Which Problems Are Solved
The performance of the initial push function can further be increased
# How the Problems Are Solved
`eventstore.push`- and `eventstore.commands_to_events`-functions were
rewritten
# Additional Changes
none
# Additional Context
same optimizations as for postgres:
https://github.com/zitadel/zitadel/pull/9092
# Which Problems Are Solved
In versions previous to v2.66 it was possible to set a different
resource owner on project grants. This was introduced with the new
resource based API. The resource owner was possible to overwrite using
the x-zitadel-org header.
Because of this issue project grants got the wrong resource owner,
instead of the owner of the project it got the granted org which is
wrong because a resource owner of an aggregate is not allowed to change.
# How the Problems Are Solved
- The wrong owners of the events are set to the original owner of the
project.
- A new event is pushed to these aggregates `project.owner.corrected`
- The projection updates the owners of the user grants if that event was
written
# Additional Changes
The eventstore push function (replaced in version 2.66) writes the
correct resource owner.
# Additional Context
closes https://github.com/zitadel/zitadel/issues/9072
# Which Problems Are Solved
On Zitadel cloud we found changing the order of columns in the
`eventstore.events2_current_sequence` index improved CPU usage for the
`SELECT ... FOR UPDATE` query the pusher executes.
# How the Problems Are Solved
`eventstore.events2_current_sequence`-index got replaced
# Additional Context
closes https://github.com/zitadel/zitadel/issues/9082
# Which Problems Are Solved
We were seeing high query costs in a the lateral join executed in the
commands_to_events procedural function in the database. The high cost
resulted in incremental CPU usage as a load test continued and less
req/sec handled, sarting at 836 and ending at 130 req/sec.
# How the Problems Are Solved
1. Set `PARALLEL SAFE`. I noticed that this option defaults to `UNSAFE`.
But it's actually safe if the function doesn't `INSERT`
2. Set the returned `ROWS 10` parameter.
3. Function is re-written in Pl/PgSQL so that we eliminate expensive
joins.
4. Introduced an intermediate state that does `SELECT DISTINCT` for the
aggregate so that we don't have to do an expensive lateral join.
# Additional Changes
Use a `COALESCE` to get the owner from the last event, instead of a
`CASE` switch.
# Additional Context
- Function was introduced in
https://github.com/zitadel/zitadel/pull/8816
- Closes https://github.com/zitadel/zitadel/issues/8352
---------
Co-authored-by: Silvan <27845747+adlerhurst@users.noreply.github.com>
# Which Problems Are Solved
get instance by domain cannot provide an instance id because it is not
known at that time. This causes a full table scan on the fields table
because current indexes always include the `instance_id` column.
# How the Problems Are Solved
Added a specific index for this query.
# Additional Context
If a system has many fields and there is no cache hit for the given
domain this query can heaviuly influence database CPU usage, the newly
added resolves this problem.
# Which Problems Are Solved
To be able to migrate or test the new login UI, admins might want to
(temporarily) switch individual apps.
At a later point admin might want to make sure all applications use the
new login UI.
# How the Problems Are Solved
- Added a feature flag `` on instance level to require all apps to use
the new login and provide an optional base url.
- if the flag is enabled, all (OIDC) applications will automatically use
the v2 login.
- if disabled, applications can decide based on their configuration
- Added an option on OIDC apps to use the new login UI and an optional
base url.
- Removed the requirement to use `x-zitadel-login-client` to be
redirected to the login V2 and retrieve created authrequest and link
them to SSO sessions.
- Added a new "IAM_LOGIN_CLIENT" role to allow management of users,
sessions, grants and more without `x-zitadel-login-client`.
# Additional Changes
None
# Additional Context
closes https://github.com/zitadel/zitadel/issues/8702
