2003 Commits

Author SHA1 Message Date
Livio Spring
0df399ac1f fix(login): handle requests without auth request correctly (#9713)
# Which Problems Are Solved

We found some paths in the login UI, where requests without any
`AuthRequest` were not handled correctly and could potentially panic.
This also includes providing the `AuthRequest` as part of `ctx` object
in actions V1.

# How the Problems Are Solved

- Check for the existance of an `AuthRequest` were needed and return an
error otherwise.
- Provide correct state of the `AuthRequest` for actions V1

# Additional Changes

None

# Additional Context

- Noticed as part of a support request
- requires backport to at least 2.70.x

Co-authored-by: Stefan Benz <46600784+stebenz@users.noreply.github.com>
2025-04-09 13:34:03 +02:00
Livio Spring
79a5585f91 fix(login): handle requests without auth request correctly (#9713)
# Which Problems Are Solved

We found some paths in the login UI, where requests without any
`AuthRequest` were not handled correctly and could potentially panic.
This also includes providing the `AuthRequest` as part of `ctx` object
in actions V1.

# How the Problems Are Solved

- Check for the existance of an `AuthRequest` were needed and return an
error otherwise.
- Provide correct state of the `AuthRequest` for actions V1

# Additional Changes

None

# Additional Context

- Noticed as part of a support request
- requires backport to at least 2.70.x

Co-authored-by: Stefan Benz <46600784+stebenz@users.noreply.github.com>
2025-04-07 17:40:42 +02:00
Fabienne Bühler
07ce3b6905 chore!: Introduce ZITADEL v3 (#9645)
This PR summarizes multiple changes specifically only available with
ZITADEL v3:

- feat: Web Keys management
(https://github.com/zitadel/zitadel/pull/9526)
- fix(cmd): ensure proper working of mirror
(https://github.com/zitadel/zitadel/pull/9509)
- feat(Authz): system user support for permission check v2
(https://github.com/zitadel/zitadel/pull/9640)
- chore(license): change from Apache to AGPL
(https://github.com/zitadel/zitadel/pull/9597)
- feat(console): list v2 sessions
(https://github.com/zitadel/zitadel/pull/9539)
- fix(console): add loginV2 feature flag
(https://github.com/zitadel/zitadel/pull/9682)
- fix(feature flags): allow reading "own" flags
(https://github.com/zitadel/zitadel/pull/9649)
- feat(console): add Actions V2 UI
(https://github.com/zitadel/zitadel/pull/9591)

BREAKING CHANGE
- feat(webkey): migrate to v2beta API
(https://github.com/zitadel/zitadel/pull/9445)
- chore!: remove CockroachDB Support
(https://github.com/zitadel/zitadel/pull/9444)
- feat(actions): migrate to v2beta API
(https://github.com/zitadel/zitadel/pull/9489)

---------

Co-authored-by: Livio Spring <livio.a@gmail.com>
Co-authored-by: Stefan Benz <46600784+stebenz@users.noreply.github.com>
Co-authored-by: Silvan <27845747+adlerhurst@users.noreply.github.com>
Co-authored-by: Ramon <mail@conblem.me>
Co-authored-by: Elio Bischof <elio@zitadel.com>
Co-authored-by: Kenta Yamaguchi <56732734+KEY60228@users.noreply.github.com>
Co-authored-by: Harsha Reddy <harsha.reddy@klaviyo.com>
Co-authored-by: Livio Spring <livio@zitadel.com>
Co-authored-by: Max Peintner <max@caos.ch>
Co-authored-by: Iraq <66622793+kkrime@users.noreply.github.com>
Co-authored-by: Florian Forster <florian@zitadel.com>
Co-authored-by: Tim Möhlmann <tim+github@zitadel.com>
Co-authored-by: Copilot <175728472+Copilot@users.noreply.github.com>
Co-authored-by: Max Peintner <peintnerm@gmail.com>
2025-04-02 16:53:06 +02:00
Livio Spring
4fde7822d8 fix(oauth): check key expiry on JWT Profile Grant
# Which Problems Are Solved

ZITADEL allows the use of JSON Web Token (JWT) Profile OAuth 2.0 for Authorization Grants in machine-to-machine (M2M) authentication. Multiple keys can be managed for a single machine account (service user), each with an individual expiry.

A vulnerability existed where expired keys can be used to retrieve tokens. Specifically, ZITADEL fails to properly check the expiration date of the JWT key when used for Authorization Grants. This allows an attacker with an expired key to obtain valid access tokens.

This vulnerability does not affect the use of JWT Profile for OAuth 2.0 Client Authentication on the Token and Introspection endpoints, which correctly reject expired keys.

# How the Problems Are Solved

Added proper validation of the expiry of the stored public key.

# Additional Changes

None

# Additional Context

None

(cherry picked from commit 315503beab)
2025-03-31 12:49:56 +02:00
Livio Spring
ae1e86ed9a fix(login): remove normalization to prevent username enumeration
# Which Problems Are Solved

The username entered by the user was resp. replaced by the stored user's username. This provided a possibility to enumerate usernames as unknown usernames were not normalized.

# How the Problems Are Solved

- Store and display the username as entered by the user.
- Removed the part where the loginname was always set to the user's loginname when retrieving the `nextSteps`

# Additional Changes

None

# Additional Context

None

(cherry picked from commit 14de8ecac2)
2025-03-31 12:49:55 +02:00
Livio Spring
315503beab Merge commit from fork 2025-03-31 12:45:11 +02:00
Livio Spring
14de8ecac2 Merge commit from fork 2025-03-31 12:44:49 +02:00
Silvan
817670f1f7 perf: improve scalability of session api (#9635)
This pull request improves the scalability of the session API by
enhancing middleware tracing and refining SQL query behavior for user
authentication methods.

# Which Problems Are Solved

- Eventstore subscriptions locked each other during they wrote the
events to the event channels of the subscribers in push.
- `ListUserAuthMethodTypesRequired` query used `Bitmap heap scan` to
join the tables needed.
- The auth and oidc package triggered projections often when data were
read.
- The session API triggered the user projection each time a user was
searched to write the user check command.

# How the Problems Are Solved

- the `sync.Mutex` was replaced with `sync.RWMutex` to allow parallel
read of the map
- The query was refactored to use index scans only
- if the data should already be up-to-date `shouldTriggerBulk` is set to
false
- as the user should already exist for some time the trigger was
removed.

# Additional Changes

- refactoring of `tracing#Span.End` calls

# Additional Context

- part of https://github.com/zitadel/zitadel/issues/9239

---------

Co-authored-by: Tim Möhlmann <tim+github@zitadel.com>
2025-03-28 12:36:05 +00:00
Zach Hirschtritt
61ddecee31 fix: add prometheus metrics on projection handlers (#9561)
# 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

![image](https://github.com/user-attachments/assets/94ba5c2b-9c62-44cd-83ee-4db4a8859073)

![image](https://github.com/user-attachments/assets/60a1b406-a8c6-48dc-a925-575359f97e1e)

---------

Co-authored-by: Silvan <27845747+adlerhurst@users.noreply.github.com>
Co-authored-by: Livio Spring <livio.a@gmail.com>
(cherry picked from commit c1535b7b49)
2025-03-28 07:58:47 +01:00
Livio Spring
611e2f30db fix(actions): correctly handle api.v1.appendUserGrant (#9598)
# Which Problems Are Solved

It was not possible to use the `api.v1.appendUserGrant` function in the
`postCreation` trigger action as documented.

# How the Problems Are Solved

- Correctly initialize the javascript / Goja function
- Added `projectID` and `projectGrantID` (as documented), but kept
`projectId` and `projectGrantId` (for backwards compatibility) when
mapping the object in the `appendUserGrant` function

# Additional Changes

None

# Additional Context

- A customer reached out to support regarding this issue.
- requires back port to 2.70.x

(cherry picked from commit 57bfecf7f7)
2025-03-28 07:40:35 +01:00
Iraq
14754e8edc chore: updating go to 1.24 (#9507)
# Which Problems Are Solved

Updating go to 1.24

---------

Co-authored-by: Iraq Jaber <IraqJaber@gmail.com>
(cherry picked from commit 596970cc7e)
2025-03-28 07:40:30 +01:00
Livio Spring
c6e27183c1 fix: correct event type for permission removed (#9626)
# Which Problems Are Solved

When we recently changed some permission for the `SYSTEM_OWNER` role on
QA, we noticed that there we multiple `permission.added` even when we
removed specific permissions.

# How the Problems Are Solved

Fixed the event type when removing permissions.

# Additional Changes

None

# Additional Context

Noticed when rolling out some changes on QA

(cherry picked from commit e7c7f9b800)
2025-03-28 07:40:19 +01:00
Harsha Reddy
113a4ed817 fix: Make service name configurable for Metrics and Tracing (#9563)
# Which Problems Are Solved

The service name is hardcoded in the metrics code. Making the service
name to be configurable helps when running multiple instances of
Zitadel.

The defaults remain unchanged, the service name will be defaulted to
ZITADEL.

# How the Problems Are Solved

Add a config option to override the name in defaults.yaml and pass it
down to the corresponding metrics or tracing module (google or otel)

# Additional Changes
NA

# Additional Context
NA

(cherry picked from commit dc64e35128)
2025-03-28 07:37:19 +01:00
Stefan Benz
3f329e8459 fix: milestone multiple results per instance domain instead of primary instance domain (#9564)
# Which Problems Are Solved

The milestones query returns multiple results for every milestone for
every instance domain.

# How the Problems Are Solved

Corrected where condition on milestone query.

# Additional Changes

None

# Additional Context

None

Co-authored-by: Livio Spring <livio.a@gmail.com>
(cherry picked from commit 352fa6aa6f)
2025-03-28 07:36:31 +01:00
Stefan Benz
0337a861ba test: correct notifications integration test with eventual consistency (#9569)
# Which Problems Are Solved

Quota notification integration test failed sometimes due to eventual
consistency issues, which resulted in calls which should have been
counted to the quota not being added. This resulted in flaky integration
tests as the expected API calls to be limited were executed normally.

# How the Problems Are Solved

As there is no API call to query the currently applied Quota, there was
a sleep added as a last effort, to give some time that the event gets
processed into the projection.

# Additional Changes

None

# Additional Context

Related to
https://github.com/zitadel/zitadel/actions/runs/13922326003/job/38959595055

Co-authored-by: Livio Spring <livio.a@gmail.com>
(cherry picked from commit 5ca76af779)
2025-03-28 07:36:17 +01:00
Zach Hirschtritt
c1535b7b49 feat: add prometheus metrics on projection handlers (#9561)
# 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


![image](https://github.com/user-attachments/assets/94ba5c2b-9c62-44cd-83ee-4db4a8859073)

![image](https://github.com/user-attachments/assets/60a1b406-a8c6-48dc-a925-575359f97e1e)

---------

Co-authored-by: Silvan <27845747+adlerhurst@users.noreply.github.com>
Co-authored-by: Livio Spring <livio.a@gmail.com>
2025-03-27 07:40:27 +01:00
Stefan Benz
0e10ed0e0b fix: SAML and OIDC issuer (in proxied use cases) (#9638)
# Which Problems Are Solved

When using implicit flow through the session API and a login UI on a
custom domain (proxy), the tokens were signed by the API domain of the
instance, rather than the public (proxy) domain.
The SAML response had the same issue. Additionally, the saml library had
an issue and lost the issuer context. This prevented also a successful
login through the hosted login UI.

# How the Problems Are Solved

- The issuer of the SAML and Auth request is persisted to provide the
information when signing the responses and tokens.
- The SAML library is updated to the latest version.

# Additional Changes

None

# Additional Context

None
2025-03-26 17:08:13 +00:00
Livio Spring
57bfecf7f7 fix(actions): correctly handle api.v1.appendUserGrant (#9598)
# Which Problems Are Solved

It was not possible to use the `api.v1.appendUserGrant` function in the
`postCreation` trigger action as documented.

# How the Problems Are Solved

- Correctly initialize the javascript / Goja function
- Added `projectID` and `projectGrantID` (as documented), but kept
`projectId` and `projectGrantId` (for backwards compatibility) when
mapping the object in the `appendUserGrant` function

# Additional Changes

None

# Additional Context

- A customer reached out to support regarding this issue.
- requires back port to 2.70.x
2025-03-25 11:07:54 +00:00
Iraq
596970cc7e chore: updating go to 1.24 (#9507)
# Which Problems Are Solved

Updating go to 1.24

---------

Co-authored-by: Iraq Jaber <IraqJaber@gmail.com>
2025-03-25 07:01:29 +00:00
Livio Spring
e7c7f9b800 fix: correct event type for permission removed (#9626)
# Which Problems Are Solved

When we recently changed some permission for the `SYSTEM_OWNER` role on
QA, we noticed that there we multiple `permission.added` even when we
removed specific permissions.

# How the Problems Are Solved

Fixed the event type when removing permissions.

# Additional Changes

None

# Additional Context

Noticed when rolling out some changes on QA
2025-03-24 17:00:44 +00:00
Tim Möhlmann
c3258ecf78 chore(deps): upgrade go mods from zitadel organization (#9601)
# Which Problems Are Solved

Outdated dependencies foir packages developed by Zitadel.
Some of them included important security updates from sub-dependencies.

# How the Problems Are Solved

Upgrade all packages under `github.com/zitadel/*` to the latest released
version.

# Additional Changes

- The `github.com/superseriousbusiness/exifremove` was removed from
Github. We copied the cached go mod code to
https://github.com/zitadel/exifremove and use this module now.

# Additional Context

- Related to https://github.com/zitadel/zitadel/issues/9422
- Closes https://github.com/zitadel/zitadel/issues/9443

---------

Co-authored-by: Livio Spring <livio.a@gmail.com>
2025-03-24 06:03:24 +00:00
Miro Trisc
e4c12864e5 feat(crypto): support md5 salted for imported password hashes (#9596)
# Which Problems Are Solved

Allow verification of imported salted passwords hashed with plain md5.

# How the Problems Are Solved

- Upgrade passwap to
[v0.7.0](https://github.com/zitadel/passwap/releases/tag/v0.7.0)
- Add md5salted as a new verifier option in `defaults.yaml`

# Additional Changes

- go version and libraries updated  (required by passkey v0.7.0)
- secrets.md verifiers updated  
- configuration verifiers updated 
- added MD5salted and missing MD5Plain to test cases
2025-03-21 12:25:52 +00:00
Harsha Reddy
dc64e35128 feat: Make service name configurable for Metrics and Tracing (#9563)
# Which Problems Are Solved

The service name is hardcoded in the metrics code. Making the service
name to be configurable helps when running multiple instances of
Zitadel.

The defaults remain unchanged, the service name will be defaulted to
ZITADEL.

# How the Problems Are Solved

Add a config option to override the name in defaults.yaml and pass it
down to the corresponding metrics or tracing module (google or otel)

# Additional Changes
NA

# Additional Context
NA
2025-03-20 09:35:54 +00:00
Faey
833e654a07 feat(actions): Add refresh token to post authentication action context (#9493)
# Which Problems Are Solved

- Refresh Tokens issued by third party authentication providers are lost

# How the Problems Are Solved

- Allows the existing post authentication action to capture the refresh
token

# Additional Changes

- Docs updated to reflect the new property

# Additional Context

- Partially addresses #7851 by allowing the refresh token to be
captured.

Co-authored-by: Stefan Benz <46600784+stebenz@users.noreply.github.com>
2025-03-20 09:00:36 +00:00
Stefan Benz
352fa6aa6f fix: milestone multiple results per instance domain instead of primary instance domain (#9564)
# Which Problems Are Solved

The milestones query returns multiple results for every milestone for
every instance domain.

# How the Problems Are Solved

Corrected where condition on milestone query.

# Additional Changes

None

# Additional Context

None

Co-authored-by: Livio Spring <livio.a@gmail.com>
2025-03-20 07:57:36 +00:00
Stefan Benz
5486ef2627 test: correct sorting of scim list users integration tests (#9568)
# Which Problems Are Solved

SCIM integration test failed sometimes, as ListUsers with usernames-sort
was not reliable if the asserted list is not sorted as well.

# How the Problems Are Solved

Sort the list of results in the sorted integration tests.

# Additional Changes

None

# Additional Context

Relates to
https://github.com/zitadel/zitadel/actions/runs/13922326003/job/38960759621
2025-03-20 07:05:01 +00:00
Stefan Benz
5ca76af779 test: correct notifications integration test with eventual consistency (#9569)
# Which Problems Are Solved

Quota notification integration test failed sometimes due to eventual
consistency issues, which resulted in calls which should have been
counted to the quota not being added. This resulted in flaky integration
tests as the expected API calls to be limited were executed normally.

# How the Problems Are Solved

As there is no API call to query the currently applied Quota, there was
a sleep added as a last effort, to give some time that the event gets
processed into the projection.

# Additional Changes

None

# Additional Context

Related to
https://github.com/zitadel/zitadel/actions/runs/13922326003/job/38959595055

Co-authored-by: Livio Spring <livio.a@gmail.com>
2025-03-20 07:47:19 +01:00
Iraq
9f0da00cd5 fix: manage root CA for LDAP IdPs correctly (#9517)
# Which Problems Are Solved

#9292 did not correctly change the projection table to list IdPs for existing ZITADEL setups.

# How the Problems Are Solved

Fixed the projection table by an explicit setup.

# Additional Changes

To prevent user facing error when using the LDAP with a custom root CA as much as possible, the certificate is parsed when passing it to the API.

# Additional Context

- Closes https://github.com/zitadel/zitadel/issues/9514

---------

Co-authored-by: Iraq Jaber <IraqJaber@gmail.com>
(cherry picked from commit 11c9be3b8d)
2025-03-18 16:38:22 +01:00
Iraq
11c9be3b8d chore: updating projections.idp_templates6 to projections.idp_templates7 (#9517)
# Which Problems Are Solved

This was left out as part of
https://github.com/zitadel/zitadel/pull/9292

- Closes https://github.com/zitadel/zitadel/issues/9514

---------

Co-authored-by: Iraq Jaber <IraqJaber@gmail.com>
2025-03-18 16:23:12 +01:00
Stefan Benz
6cd5e717a4 chore: correct integration tests for query endpoints eventual consistent (#9554)
# Which Problems Are Solved

Integration tests for OIDC service failed irregularly.

# How the Problems Are Solved

Add eventual consistent checks for querying endpoints to the integration
tests for the OIDC service.

# Additional Changes

None

# Additional Context

None
2025-03-18 08:16:48 +01:00
Harsha Reddy
7c1e211a3c fix: reduce cardinality in metrics and tracing for unknown paths (#9523)
# Which Problems Are Solved
Zitadel should not record 404 response counts of unknown paths (check
`/debug/metrics`).
This can lead to high cardinality on metrics endpoint and in traces.

```
GOOD http_server_return_code_counter_total{method="GET",otel_scope_name="",otel_scope_version="",return_code="200",uri="/.well-known/openid-configuration"} 2
GOOD http_server_return_code_counter_total{method="GET",otel_scope_name="",otel_scope_version="",return_code="200",uri="/oauth/v2/keys"} 2
BAD http_server_return_code_counter_total{method="GET",otel_scope_name="",otel_scope_version="",return_code="404",uri="/junk"} 2000
```

After
```
GOOD http_server_return_code_counter_total{method="GET",otel_scope_name="",otel_scope_version="",return_code="200",uri="/.well-known/openid-configuration"} 2
GOOD http_server_return_code_counter_total{method="GET",otel_scope_name="",otel_scope_version="",return_code="200",uri="/oauth/v2/keys"} 2
```

# How the Problems Are Solved

This PR makes sure, that any unknown path is recorded as `UNKNOWN_PATH`
instead of the actual path.

# Additional Changes

N/A

# Additional Context

On our production instance, when a penetration test was run, it caused
our metric count to blow up to many thousands due to Zitadel recording
404 response counts.

Next nice to have steps, remove 404 timer recordings which serve no
purpose

---------

Co-authored-by: Livio Spring <livio.a@gmail.com>
Co-authored-by: Silvan <27845747+adlerhurst@users.noreply.github.com>
Co-authored-by: Livio Spring <livio@zitadel.com>
(cherry picked from commit 599850e7e8)
2025-03-17 17:39:46 +01:00
Livio Spring
5aaaaa2398 fix(login): cache scripts and other assets correctly (#9551)
# Which Problems Are Solved

Scripts and other assets for the hosted login UI are served with a
public cache with `max-age` and `s-maxage`. After changing scripts or
assets, old versions might be still used as they might be cached locally
or in a shared cache (CDN, proxy, ...). This can lead to unwanted
behaviour or even errors.

# How the Problems Are Solved

To ensure the correct file is served a query parameter with the build
time is added to the assets filename dynamically. (`?v=2025-03-17...`)

# Additional Changes

None

# Additional Context

- relates to #9485
- requires backport to at least 2.70.x

(cherry picked from commit 19f022e1cf)
2025-03-17 17:39:43 +01:00
Harsha Reddy
599850e7e8 fix: reduce cardinality in metrics and tracing for unknown paths (#9523)
# Which Problems Are Solved
Zitadel should not record 404 response counts of unknown paths (check
`/debug/metrics`).
This can lead to high cardinality on metrics endpoint and in traces.

```
GOOD http_server_return_code_counter_total{method="GET",otel_scope_name="",otel_scope_version="",return_code="200",uri="/.well-known/openid-configuration"} 2
GOOD http_server_return_code_counter_total{method="GET",otel_scope_name="",otel_scope_version="",return_code="200",uri="/oauth/v2/keys"} 2
BAD http_server_return_code_counter_total{method="GET",otel_scope_name="",otel_scope_version="",return_code="404",uri="/junk"} 2000
```

After
```
GOOD http_server_return_code_counter_total{method="GET",otel_scope_name="",otel_scope_version="",return_code="200",uri="/.well-known/openid-configuration"} 2
GOOD http_server_return_code_counter_total{method="GET",otel_scope_name="",otel_scope_version="",return_code="200",uri="/oauth/v2/keys"} 2
```

# How the Problems Are Solved

This PR makes sure, that any unknown path is recorded as `UNKNOWN_PATH`
instead of the actual path.

# Additional Changes

N/A

# Additional Context

On our production instance, when a penetration test was run, it caused
our metric count to blow up to many thousands due to Zitadel recording
404 response counts.

Next nice to have steps, remove 404 timer recordings which serve no
purpose

---------

Co-authored-by: Livio Spring <livio.a@gmail.com>
Co-authored-by: Silvan <27845747+adlerhurst@users.noreply.github.com>
Co-authored-by: Livio Spring <livio@zitadel.com>
2025-03-17 17:37:59 +01:00
Kenta Yamaguchi
d57fa819cb chore: Replace deprecated io/ioutil functions with recommended alternatives (#9542)
# Which Problems Are Solved

- The `io/ioutil` package was deprecated in Go 1.16.  
  - Reference: https://go.dev/doc/go1.16#ioutil  

# How the Problems Are Solved

- Replaced deprecated functions with their recommended alternatives:  
  - `ioutil.ReadFile` → `os.ReadFile`  
  - `ioutil.ReadAll` → `io.ReadAll`  
  - `ioutil.NopCloser` → `io.NopCloser`
2025-03-17 13:17:14 +00:00
Livio Spring
19f022e1cf fix(login): cache scripts and other assets correctly (#9551)
# Which Problems Are Solved

Scripts and other assets for the hosted login UI are served with a
public cache with `max-age` and `s-maxage`. After changing scripts or
assets, old versions might be still used as they might be cached locally
or in a shared cache (CDN, proxy, ...). This can lead to unwanted
behaviour or even errors.

# How the Problems Are Solved

To ensure the correct file is served a query parameter with the build
time is added to the assets filename dynamically. (`?v=2025-03-17...`)

# Additional Changes

None

# Additional Context

- relates to #9485
- requires backport to at least 2.70.x
2025-03-17 11:13:24 +00:00
Silvan
205229f0d1 fix(perf): simplify eventstore queries by removing or in projection handlers (#9530)
# Which Problems Are Solved

[A recent performance
enhancement]((https://github.com/zitadel/zitadel/pull/9497)) aimed at
optimizing event store queries, specifically those involving multiple
aggregate type filters, has successfully improved index utilization.
While the query planner now correctly selects relevant indexes, it
employs [bitmap index
scans](https://www.postgresql.org/docs/current/indexes-bitmap-scans.html)
to retrieve data.

This approach, while beneficial in many scenarios, introduces a
potential I/O bottleneck. The bitmap index scan first identifies the
required database blocks and then utilizes a bitmap to access the
corresponding rows from the table's heap. This subsequent "bitmap heap
scan" can result in significant I/O overhead, particularly when queries
return a substantial number of rows across numerous data pages.

## Impact:

Under heavy load or with queries filtering for a wide range of events
across multiple aggregate types, this increased I/O activity may lead
to:

- Increased query latency.
- Elevated disk utilization.
- Potential performance degradation of the event store and dependent
systems.

# How the Problems Are Solved

To address this I/O bottleneck and further optimize query performance,
the projection handler has been modified. Instead of employing multiple
OR clauses for each aggregate type, the aggregate and event type filters
are now combined using IN ARRAY filters.

Technical Details:

This change allows the PostgreSQL query planner to leverage [index-only
scans](https://www.postgresql.org/docs/current/indexes-index-only-scans.html).
By utilizing IN ARRAY filters, the database can efficiently retrieve the
necessary data directly from the index, eliminating the need to access
the table's heap. This results in:

* Reduced I/O: Index-only scans significantly minimize disk I/O
operations, as the database avoids reading data pages from the main
table.
* Improved Query Performance: By reducing I/O, query execution times are
substantially improved, leading to lower latency.

# Additional Changes

- rollback of https://github.com/zitadel/zitadel/pull/9497

# Additional Information

## Query Plan of previous query

```sql
SELECT
    created_at, event_type, "sequence", "position", payload, creator, "owner", instance_id, aggregate_type, aggregate_id, revision
FROM
    eventstore.events2
WHERE
    instance_id = '<INSTANCE_ID>'
    AND (
        (
            instance_id = '<INSTANCE_ID>'
            AND "position" > <POSITION>
            AND aggregate_type = 'project'
            AND event_type = ANY(ARRAY[
                                'project.application.added'
                                ,'project.application.changed'
                                ,'project.application.deactivated'
                                ,'project.application.reactivated'
                                ,'project.application.removed'
                                ,'project.removed'
                                ,'project.application.config.api.added'
                                ,'project.application.config.api.changed'
                                ,'project.application.config.api.secret.changed'
                ,'project.application.config.api.secret.updated'
                                ,'project.application.config.oidc.added'
                                ,'project.application.config.oidc.changed'
                                ,'project.application.config.oidc.secret.changed'
                ,'project.application.config.oidc.secret.updated'
                                ,'project.application.config.saml.added'
                                ,'project.application.config.saml.changed'
                        ])
        ) OR (
            instance_id = '<INSTANCE_ID>'
            AND "position" > <POSITION>
            AND aggregate_type = 'org'
            AND event_type = 'org.removed'
        ) OR (
            instance_id = '<INSTANCE_ID>'
            AND "position" > <POSITION>
            AND aggregate_type = 'instance'
            AND event_type = 'instance.removed'
        )
    )
    AND "position" > 1741600905.3495
    AND "position" < (
        SELECT
            COALESCE(EXTRACT(EPOCH FROM min(xact_start)), EXTRACT(EPOCH FROM now()))
        FROM
            pg_stat_activity
        WHERE
            datname = current_database()
            AND application_name = ANY(ARRAY['zitadel_es_pusher_', 'zitadel_es_pusher', 'zitadel_es_pusher_<INSTANCE_ID>'])
            AND state <> 'idle'
    )
ORDER BY "position", in_tx_order LIMIT 200 OFFSET 1;
```

```
Limit  (cost=120.08..120.09 rows=7 width=361) (actual time=2.167..2.172 rows=0 loops=1)
   Output: events2.created_at, events2.event_type, events2.sequence, events2."position", events2.payload, events2.creator, events2.owner, events2.instance_id, events2.aggregate_type, events2.aggregate_id, events2.revision, events2.in_tx_order
   InitPlan 1
     ->  Aggregate  (cost=2.74..2.76 rows=1 width=32) (actual time=1.813..1.815 rows=1 loops=1)
           Output: COALESCE(EXTRACT(epoch FROM min(s.xact_start)), EXTRACT(epoch FROM now()))
           ->  Nested Loop  (cost=0.00..2.74 rows=1 width=8) (actual time=1.803..1.805 rows=0 loops=1)
                 Output: s.xact_start
                 Join Filter: (d.oid = s.datid)
                 ->  Seq Scan on pg_catalog.pg_database d  (cost=0.00..1.07 rows=1 width=4) (actual time=0.016..0.021 rows=1 loops=1)
                       Output: d.oid, d.datname, d.datdba, d.encoding, d.datlocprovider, d.datistemplate, d.datallowconn, d.dathasloginevt, d.datconnlimit, d.datfrozenxid, d.datminmxid, d.dattablespace, d.datcollate, d.datctype, d.datlocale, d.daticurules, d.datcollversion, d.datacl
                       Filter: (d.datname = current_database())
                       Rows Removed by Filter: 4
                 ->  Function Scan on pg_catalog.pg_stat_get_activity s  (cost=0.00..1.63 rows=3 width=16) (actual time=1.781..1.781 rows=0 loops=1)
                       Output: s.datid, s.pid, s.usesysid, s.application_name, s.state, s.query, s.wait_event_type, s.wait_event, s.xact_start, s.query_start, s.backend_start, s.state_change, s.client_addr, s.client_hostname, s.client_port, s.backend_xid, s.backend_xmin, s.backend_type, s.ssl, s.sslversion, s.sslcipher, s.sslbits, s.ssl_client_dn, s.ssl_client_serial, s.ssl_issuer_dn, s.gss_auth, s.gss_princ, s.gss_enc, s.gss_delegation, s.leader_pid, s.query_id
                       Function Call: pg_stat_get_activity(NULL::integer)
                       Filter: ((s.state <> 'idle'::text) AND (s.application_name = ANY ('{zitadel_es_pusher_,zitadel_es_pusher,zitadel_es_pusher_<INSTANCE_ID>}'::text[])))
                       Rows Removed by Filter: 49
   ->  Sort  (cost=117.31..117.33 rows=8 width=361) (actual time=2.167..2.168 rows=0 loops=1)
         Output: events2.created_at, events2.event_type, events2.sequence, events2."position", events2.payload, events2.creator, events2.owner, events2.instance_id, events2.aggregate_type, events2.aggregate_id, events2.revision, events2.in_tx_order
         Sort Key: events2."position", events2.in_tx_order
         Sort Method: quicksort  Memory: 25kB
         ->  Bitmap Heap Scan on eventstore.events2  (cost=84.92..117.19 rows=8 width=361) (actual time=2.088..2.089 rows=0 loops=1)
               Output: events2.created_at, events2.event_type, events2.sequence, events2."position", events2.payload, events2.creator, events2.owner, events2.instance_id, events2.aggregate_type, events2.aggregate_id, events2.revision, events2.in_tx_order
               Recheck Cond: (((events2.instance_id = '<INSTANCE_ID>'::text) AND (events2.aggregate_type = 'project'::text) AND (events2.event_type = ANY ('{project.application.added,project.application.changed,project.application.deactivated,project.application.reactivated,project.application.removed,project.removed,project.application.config.api.added,project.application.config.api.changed,project.application.config.api.secret.changed,project.application.config.api.secret.updated,project.application.config.oidc.added,project.application.config.oidc.changed,project.application.config.oidc.secret.changed,project.application.config.oidc.secret.updated,project.application.config.saml.added,project.application.config.saml.changed}'::text[])) AND (events2."position" > <POSITION>) AND (events2."position" > 1741600905.3495) AND (events2."position" < (InitPlan 1).col1)) OR ((events2.instance_id = '<INSTANCE_ID>'::text) AND (events2.aggregate_type = 'org'::text) AND (events2.event_type = 'org.removed'::text) AND (events2."position" > <POSITION>) AND (events2."position" > 1741600905.3495) AND (events2."position" < (InitPlan 1).col1)) OR ((events2.instance_id = '<INSTANCE_ID>'::text) AND (events2.aggregate_type = 'instance'::text) AND (events2.event_type = 'instance.removed'::text) AND (events2."position" > <POSITION>) AND (events2."position" > 1741600905.3495) AND (events2."position" < (InitPlan 1).col1)))
               ->  BitmapOr  (cost=84.88..84.88 rows=8 width=0) (actual time=2.080..2.081 rows=0 loops=1)
                     ->  Bitmap Index Scan on es_projection  (cost=0.00..75.44 rows=8 width=0) (actual time=2.016..2.017 rows=0 loops=1)
                           Index Cond: ((events2.instance_id = '<INSTANCE_ID>'::text) AND (events2.aggregate_type = 'project'::text) AND (events2.event_type = ANY ('{project.application.added,project.application.changed,project.application.deactivated,project.application.reactivated,project.application.removed,project.removed,project.application.config.api.added,project.application.config.api.changed,project.application.config.api.secret.changed,project.application.config.api.secret.updated,project.application.config.oidc.added,project.application.config.oidc.changed,project.application.config.oidc.secret.changed,project.application.config.oidc.secret.updated,project.application.config.saml.added,project.application.config.saml.changed}'::text[])) AND (events2."position" > <POSITION>) AND (events2."position" > 1741600905.3495) AND (events2."position" < (InitPlan 1).col1))
                     ->  Bitmap Index Scan on es_projection  (cost=0.00..4.71 rows=1 width=0) (actual time=0.016..0.016 rows=0 loops=1)
                           Index Cond: ((events2.instance_id = '<INSTANCE_ID>'::text) AND (events2.aggregate_type = 'org'::text) AND (events2.event_type = 'org.removed'::text) AND (events2."position" > <POSITION>) AND (events2."position" > 1741600905.3495) AND (events2."position" < (InitPlan 1).col1))
                     ->  Bitmap Index Scan on es_projection  (cost=0.00..4.71 rows=1 width=0) (actual time=0.045..0.045 rows=0 loops=1)
                           Index Cond: ((events2.instance_id = '<INSTANCE_ID>'::text) AND (events2.aggregate_type = 'instance'::text) AND (events2.event_type = 'instance.removed'::text) AND (events2."position" > <POSITION>) AND (events2."position" > 1741600905.3495) AND (events2."position" < (InitPlan 1).col1))
 Query Identifier: 3194938266011254479
 Planning Time: 1.295 ms
 Execution Time: 2.832 ms
```

## Query Plan of new query

```sql
SELECT
    created_at, event_type, "sequence", "position", payload, creator, "owner", instance_id, aggregate_type, aggregate_id, revision
FROM
    eventstore.events2
WHERE
    instance_id = '<INSTANCE_ID>'
    AND "position" > <POSITION>
    AND aggregate_type = ANY(ARRAY['project', 'instance', 'org'])
    AND event_type = ANY(ARRAY[
        'project.application.added'
        ,'project.application.changed'
        ,'project.application.deactivated'
        ,'project.application.reactivated'
        ,'project.application.removed'
        ,'project.removed'
        ,'project.application.config.api.added'
        ,'project.application.config.api.changed'
        ,'project.application.config.api.secret.changed'
        ,'project.application.config.api.secret.updated'
        ,'project.application.config.oidc.added'
        ,'project.application.config.oidc.changed'
        ,'project.application.config.oidc.secret.changed'
        ,'project.application.config.oidc.secret.updated'
        ,'project.application.config.saml.added'
        ,'project.application.config.saml.changed'
        ,'org.removed'
        ,'instance.removed'
    ])
    AND "position" < (
        SELECT
            COALESCE(EXTRACT(EPOCH FROM min(xact_start)), EXTRACT(EPOCH FROM now()))
        FROM
            pg_stat_activity
        WHERE
            datname = current_database()
            AND application_name = ANY(ARRAY['zitadel_es_pusher_', 'zitadel_es_pusher', 'zitadel_es_pusher_<INSTANCE_ID>'])
            AND state <> 'idle'
    )
ORDER BY "position", in_tx_order LIMIT 200 OFFSET 1;
```

```
Limit  (cost=293.34..293.36 rows=8 width=361) (actual time=4.686..4.689 rows=0 loops=1)
   Output: events2.created_at, events2.event_type, events2.sequence, events2."position", events2.payload, events2.creator, events2.owner, events2.instance_id, events2.aggregate_type, events2.aggregate_id, events2.revision, events2.in_tx_order
   InitPlan 1
     ->  Aggregate  (cost=2.74..2.76 rows=1 width=32) (actual time=1.717..1.719 rows=1 loops=1)
           Output: COALESCE(EXTRACT(epoch FROM min(s.xact_start)), EXTRACT(epoch FROM now()))
           ->  Nested Loop  (cost=0.00..2.74 rows=1 width=8) (actual time=1.658..1.659 rows=0 loops=1)
                 Output: s.xact_start
                 Join Filter: (d.oid = s.datid)
                 ->  Seq Scan on pg_catalog.pg_database d  (cost=0.00..1.07 rows=1 width=4) (actual time=0.026..0.028 rows=1 loops=1)
                       Output: d.oid, d.datname, d.datdba, d.encoding, d.datlocprovider, d.datistemplate, d.datallowconn, d.dathasloginevt, d.datconnlimit, d.datfrozenxid, d.datminmxid, d.dattablespace, d.datcollate, d.datctype, d.datlocale, d.daticurules, d.datcollversion, d.datacl
                       Filter: (d.datname = current_database())
                       Rows Removed by Filter: 4
                 ->  Function Scan on pg_catalog.pg_stat_get_activity s  (cost=0.00..1.63 rows=3 width=16) (actual time=1.628..1.628 rows=0 loops=1)
                       Output: s.datid, s.pid, s.usesysid, s.application_name, s.state, s.query, s.wait_event_type, s.wait_event, s.xact_start, s.query_start, s.backend_start, s.state_change, s.client_addr, s.client_hostname, s.client_port, s.backend_xid, s.backend_xmin, s.backend_type, s.ssl, s.sslversion, s.sslcipher, s.sslbits, s.ssl_client_dn, s.ssl_client_serial, s.ssl_issuer_dn, s.gss_auth, s.gss_princ, s.gss_enc, s.gss_delegation, s.leader_pid, s.query_id
                       Function Call: pg_stat_get_activity(NULL::integer)
                       Filter: ((s.state <> 'idle'::text) AND (s.application_name = ANY ('{zitadel_es_pusher_,zitadel_es_pusher,zitadel_es_pusher_<INSTANCE_ID>}'::text[])))
                       Rows Removed by Filter: 42
   ->  Sort  (cost=290.58..290.60 rows=9 width=361) (actual time=4.685..4.685 rows=0 loops=1)
         Output: events2.created_at, events2.event_type, events2.sequence, events2."position", events2.payload, events2.creator, events2.owner, events2.instance_id, events2.aggregate_type, events2.aggregate_id, events2.revision, events2.in_tx_order
         Sort Key: events2."position", events2.in_tx_order
         Sort Method: quicksort  Memory: 25kB
         ->  Index Scan using es_projection on eventstore.events2  (cost=0.70..290.43 rows=9 width=361) (actual time=4.616..4.617 rows=0 loops=1)
               Output: events2.created_at, events2.event_type, events2.sequence, events2."position", events2.payload, events2.creator, events2.owner, events2.instance_id, events2.aggregate_type, events2.aggregate_id, events2.revision, events2.in_tx_order
               Index Cond: ((events2.instance_id = '<INSTANCE_ID>'::text) AND (events2.aggregate_type = ANY ('{project,instance,org}'::text[])) AND (events2.event_type = ANY ('{project.application.added,project.application.changed,project.application.deactivated,project.application.reactivated,project.application.removed,project.removed,project.application.config.api.added,project.application.config.api.changed,project.application.config.api.secret.changed,project.application.config.api.secret.updated,project.application.config.oidc.added,project.application.config.oidc.changed,project.application.config.oidc.secret.changed,project.application.config.oidc.secret.updated,project.application.config.saml.added,project.application.config.saml.changed,org.removed,instance.removed}'::text[])) AND (events2."position" > <POSITION>) AND (events2."position" < (InitPlan 1).col1))
 Query Identifier: -8254550537132386499
 Planning Time: 2.864 ms
 Execution Time: 5.414 ms
 ```

(cherry picked from commit e36f402e09)
2025-03-13 16:51:46 +01:00
Silvan
e36f402e09 fix(perf): simplify eventstore queries by removing or in projection handlers (#9530)
# Which Problems Are Solved

[A recent performance
enhancement]((https://github.com/zitadel/zitadel/pull/9497)) aimed at
optimizing event store queries, specifically those involving multiple
aggregate type filters, has successfully improved index utilization.
While the query planner now correctly selects relevant indexes, it
employs [bitmap index
scans](https://www.postgresql.org/docs/current/indexes-bitmap-scans.html)
to retrieve data.

This approach, while beneficial in many scenarios, introduces a
potential I/O bottleneck. The bitmap index scan first identifies the
required database blocks and then utilizes a bitmap to access the
corresponding rows from the table's heap. This subsequent "bitmap heap
scan" can result in significant I/O overhead, particularly when queries
return a substantial number of rows across numerous data pages.

## Impact:

Under heavy load or with queries filtering for a wide range of events
across multiple aggregate types, this increased I/O activity may lead
to:

- Increased query latency.
- Elevated disk utilization.
- Potential performance degradation of the event store and dependent
systems.

# How the Problems Are Solved

To address this I/O bottleneck and further optimize query performance,
the projection handler has been modified. Instead of employing multiple
OR clauses for each aggregate type, the aggregate and event type filters
are now combined using IN ARRAY filters.

Technical Details:

This change allows the PostgreSQL query planner to leverage [index-only
scans](https://www.postgresql.org/docs/current/indexes-index-only-scans.html).
By utilizing IN ARRAY filters, the database can efficiently retrieve the
necessary data directly from the index, eliminating the need to access
the table's heap. This results in:

* Reduced I/O: Index-only scans significantly minimize disk I/O
operations, as the database avoids reading data pages from the main
table.
* Improved Query Performance: By reducing I/O, query execution times are
substantially improved, leading to lower latency.

# Additional Changes

- rollback of https://github.com/zitadel/zitadel/pull/9497

# Additional Information

## Query Plan of previous query

```sql
SELECT 
    created_at, event_type, "sequence", "position", payload, creator, "owner", instance_id, aggregate_type, aggregate_id, revision 
FROM 
    eventstore.events2 
WHERE 
    instance_id = '<INSTANCE_ID>'
    AND (
        (
            instance_id = '<INSTANCE_ID>'
            AND "position" > <POSITION>
            AND aggregate_type = 'project'
            AND event_type = ANY(ARRAY[
                                'project.application.added'
                                ,'project.application.changed'
                                ,'project.application.deactivated'
                                ,'project.application.reactivated'
                                ,'project.application.removed'
                                ,'project.removed'
                                ,'project.application.config.api.added'
                                ,'project.application.config.api.changed'
                                ,'project.application.config.api.secret.changed'
                ,'project.application.config.api.secret.updated'
                                ,'project.application.config.oidc.added'
                                ,'project.application.config.oidc.changed'
                                ,'project.application.config.oidc.secret.changed'
                ,'project.application.config.oidc.secret.updated'
                                ,'project.application.config.saml.added'
                                ,'project.application.config.saml.changed'
                        ])
        ) OR (
            instance_id = '<INSTANCE_ID>'
            AND "position" > <POSITION>
            AND aggregate_type = 'org'
            AND event_type = 'org.removed'
        ) OR (
            instance_id = '<INSTANCE_ID>'
            AND "position" > <POSITION>
            AND aggregate_type = 'instance'
            AND event_type = 'instance.removed'
        )
    ) 
    AND "position" > 1741600905.3495
    AND "position" < (
        SELECT 
            COALESCE(EXTRACT(EPOCH FROM min(xact_start)), EXTRACT(EPOCH FROM now())) 
        FROM 
            pg_stat_activity
        WHERE 
            datname = current_database() 
            AND application_name = ANY(ARRAY['zitadel_es_pusher_', 'zitadel_es_pusher', 'zitadel_es_pusher_<INSTANCE_ID>']) 
            AND state <> 'idle'
    ) 
ORDER BY "position", in_tx_order LIMIT 200 OFFSET 1;
```

```
Limit  (cost=120.08..120.09 rows=7 width=361) (actual time=2.167..2.172 rows=0 loops=1)
   Output: events2.created_at, events2.event_type, events2.sequence, events2."position", events2.payload, events2.creator, events2.owner, events2.instance_id, events2.aggregate_type, events2.aggregate_id, events2.revision, events2.in_tx_order
   InitPlan 1
     ->  Aggregate  (cost=2.74..2.76 rows=1 width=32) (actual time=1.813..1.815 rows=1 loops=1)
           Output: COALESCE(EXTRACT(epoch FROM min(s.xact_start)), EXTRACT(epoch FROM now()))
           ->  Nested Loop  (cost=0.00..2.74 rows=1 width=8) (actual time=1.803..1.805 rows=0 loops=1)
                 Output: s.xact_start
                 Join Filter: (d.oid = s.datid)
                 ->  Seq Scan on pg_catalog.pg_database d  (cost=0.00..1.07 rows=1 width=4) (actual time=0.016..0.021 rows=1 loops=1)
                       Output: d.oid, d.datname, d.datdba, d.encoding, d.datlocprovider, d.datistemplate, d.datallowconn, d.dathasloginevt, d.datconnlimit, d.datfrozenxid, d.datminmxid, d.dattablespace, d.datcollate, d.datctype, d.datlocale, d.daticurules, d.datcollversion, d.datacl
                       Filter: (d.datname = current_database())
                       Rows Removed by Filter: 4
                 ->  Function Scan on pg_catalog.pg_stat_get_activity s  (cost=0.00..1.63 rows=3 width=16) (actual time=1.781..1.781 rows=0 loops=1)
                       Output: s.datid, s.pid, s.usesysid, s.application_name, s.state, s.query, s.wait_event_type, s.wait_event, s.xact_start, s.query_start, s.backend_start, s.state_change, s.client_addr, s.client_hostname, s.client_port, s.backend_xid, s.backend_xmin, s.backend_type, s.ssl, s.sslversion, s.sslcipher, s.sslbits, s.ssl_client_dn, s.ssl_client_serial, s.ssl_issuer_dn, s.gss_auth, s.gss_princ, s.gss_enc, s.gss_delegation, s.leader_pid, s.query_id
                       Function Call: pg_stat_get_activity(NULL::integer)
                       Filter: ((s.state <> 'idle'::text) AND (s.application_name = ANY ('{zitadel_es_pusher_,zitadel_es_pusher,zitadel_es_pusher_<INSTANCE_ID>}'::text[])))
                       Rows Removed by Filter: 49
   ->  Sort  (cost=117.31..117.33 rows=8 width=361) (actual time=2.167..2.168 rows=0 loops=1)
         Output: events2.created_at, events2.event_type, events2.sequence, events2."position", events2.payload, events2.creator, events2.owner, events2.instance_id, events2.aggregate_type, events2.aggregate_id, events2.revision, events2.in_tx_order
         Sort Key: events2."position", events2.in_tx_order
         Sort Method: quicksort  Memory: 25kB
         ->  Bitmap Heap Scan on eventstore.events2  (cost=84.92..117.19 rows=8 width=361) (actual time=2.088..2.089 rows=0 loops=1)
               Output: events2.created_at, events2.event_type, events2.sequence, events2."position", events2.payload, events2.creator, events2.owner, events2.instance_id, events2.aggregate_type, events2.aggregate_id, events2.revision, events2.in_tx_order
               Recheck Cond: (((events2.instance_id = '<INSTANCE_ID>'::text) AND (events2.aggregate_type = 'project'::text) AND (events2.event_type = ANY ('{project.application.added,project.application.changed,project.application.deactivated,project.application.reactivated,project.application.removed,project.removed,project.application.config.api.added,project.application.config.api.changed,project.application.config.api.secret.changed,project.application.config.api.secret.updated,project.application.config.oidc.added,project.application.config.oidc.changed,project.application.config.oidc.secret.changed,project.application.config.oidc.secret.updated,project.application.config.saml.added,project.application.config.saml.changed}'::text[])) AND (events2."position" > <POSITION>) AND (events2."position" > 1741600905.3495) AND (events2."position" < (InitPlan 1).col1)) OR ((events2.instance_id = '<INSTANCE_ID>'::text) AND (events2.aggregate_type = 'org'::text) AND (events2.event_type = 'org.removed'::text) AND (events2."position" > <POSITION>) AND (events2."position" > 1741600905.3495) AND (events2."position" < (InitPlan 1).col1)) OR ((events2.instance_id = '<INSTANCE_ID>'::text) AND (events2.aggregate_type = 'instance'::text) AND (events2.event_type = 'instance.removed'::text) AND (events2."position" > <POSITION>) AND (events2."position" > 1741600905.3495) AND (events2."position" < (InitPlan 1).col1)))
               ->  BitmapOr  (cost=84.88..84.88 rows=8 width=0) (actual time=2.080..2.081 rows=0 loops=1)
                     ->  Bitmap Index Scan on es_projection  (cost=0.00..75.44 rows=8 width=0) (actual time=2.016..2.017 rows=0 loops=1)
                           Index Cond: ((events2.instance_id = '<INSTANCE_ID>'::text) AND (events2.aggregate_type = 'project'::text) AND (events2.event_type = ANY ('{project.application.added,project.application.changed,project.application.deactivated,project.application.reactivated,project.application.removed,project.removed,project.application.config.api.added,project.application.config.api.changed,project.application.config.api.secret.changed,project.application.config.api.secret.updated,project.application.config.oidc.added,project.application.config.oidc.changed,project.application.config.oidc.secret.changed,project.application.config.oidc.secret.updated,project.application.config.saml.added,project.application.config.saml.changed}'::text[])) AND (events2."position" > <POSITION>) AND (events2."position" > 1741600905.3495) AND (events2."position" < (InitPlan 1).col1))
                     ->  Bitmap Index Scan on es_projection  (cost=0.00..4.71 rows=1 width=0) (actual time=0.016..0.016 rows=0 loops=1)
                           Index Cond: ((events2.instance_id = '<INSTANCE_ID>'::text) AND (events2.aggregate_type = 'org'::text) AND (events2.event_type = 'org.removed'::text) AND (events2."position" > <POSITION>) AND (events2."position" > 1741600905.3495) AND (events2."position" < (InitPlan 1).col1))
                     ->  Bitmap Index Scan on es_projection  (cost=0.00..4.71 rows=1 width=0) (actual time=0.045..0.045 rows=0 loops=1)
                           Index Cond: ((events2.instance_id = '<INSTANCE_ID>'::text) AND (events2.aggregate_type = 'instance'::text) AND (events2.event_type = 'instance.removed'::text) AND (events2."position" > <POSITION>) AND (events2."position" > 1741600905.3495) AND (events2."position" < (InitPlan 1).col1))
 Query Identifier: 3194938266011254479
 Planning Time: 1.295 ms
 Execution Time: 2.832 ms
```

## Query Plan of new query

```sql
SELECT 
    created_at, event_type, "sequence", "position", payload, creator, "owner", instance_id, aggregate_type, aggregate_id, revision 
FROM 
    eventstore.events2 
WHERE 
    instance_id = '<INSTANCE_ID>'
    AND "position" > <POSITION>
    AND aggregate_type = ANY(ARRAY['project', 'instance', 'org'])
    AND event_type = ANY(ARRAY[
        'project.application.added'
        ,'project.application.changed'
        ,'project.application.deactivated'
        ,'project.application.reactivated'
        ,'project.application.removed'
        ,'project.removed'
        ,'project.application.config.api.added'
        ,'project.application.config.api.changed'
        ,'project.application.config.api.secret.changed'
        ,'project.application.config.api.secret.updated'
        ,'project.application.config.oidc.added'
        ,'project.application.config.oidc.changed'
        ,'project.application.config.oidc.secret.changed'
        ,'project.application.config.oidc.secret.updated'
        ,'project.application.config.saml.added'
        ,'project.application.config.saml.changed'
        ,'org.removed'
        ,'instance.removed'
    ])
    AND "position" < (
        SELECT 
            COALESCE(EXTRACT(EPOCH FROM min(xact_start)), EXTRACT(EPOCH FROM now())) 
        FROM 
            pg_stat_activity
        WHERE 
            datname = current_database() 
            AND application_name = ANY(ARRAY['zitadel_es_pusher_', 'zitadel_es_pusher', 'zitadel_es_pusher_<INSTANCE_ID>']) 
            AND state <> 'idle'
    ) 
ORDER BY "position", in_tx_order LIMIT 200 OFFSET 1;
```

```
Limit  (cost=293.34..293.36 rows=8 width=361) (actual time=4.686..4.689 rows=0 loops=1)
   Output: events2.created_at, events2.event_type, events2.sequence, events2."position", events2.payload, events2.creator, events2.owner, events2.instance_id, events2.aggregate_type, events2.aggregate_id, events2.revision, events2.in_tx_order
   InitPlan 1
     ->  Aggregate  (cost=2.74..2.76 rows=1 width=32) (actual time=1.717..1.719 rows=1 loops=1)
           Output: COALESCE(EXTRACT(epoch FROM min(s.xact_start)), EXTRACT(epoch FROM now()))
           ->  Nested Loop  (cost=0.00..2.74 rows=1 width=8) (actual time=1.658..1.659 rows=0 loops=1)
                 Output: s.xact_start
                 Join Filter: (d.oid = s.datid)
                 ->  Seq Scan on pg_catalog.pg_database d  (cost=0.00..1.07 rows=1 width=4) (actual time=0.026..0.028 rows=1 loops=1)
                       Output: d.oid, d.datname, d.datdba, d.encoding, d.datlocprovider, d.datistemplate, d.datallowconn, d.dathasloginevt, d.datconnlimit, d.datfrozenxid, d.datminmxid, d.dattablespace, d.datcollate, d.datctype, d.datlocale, d.daticurules, d.datcollversion, d.datacl
                       Filter: (d.datname = current_database())
                       Rows Removed by Filter: 4
                 ->  Function Scan on pg_catalog.pg_stat_get_activity s  (cost=0.00..1.63 rows=3 width=16) (actual time=1.628..1.628 rows=0 loops=1)
                       Output: s.datid, s.pid, s.usesysid, s.application_name, s.state, s.query, s.wait_event_type, s.wait_event, s.xact_start, s.query_start, s.backend_start, s.state_change, s.client_addr, s.client_hostname, s.client_port, s.backend_xid, s.backend_xmin, s.backend_type, s.ssl, s.sslversion, s.sslcipher, s.sslbits, s.ssl_client_dn, s.ssl_client_serial, s.ssl_issuer_dn, s.gss_auth, s.gss_princ, s.gss_enc, s.gss_delegation, s.leader_pid, s.query_id
                       Function Call: pg_stat_get_activity(NULL::integer)
                       Filter: ((s.state <> 'idle'::text) AND (s.application_name = ANY ('{zitadel_es_pusher_,zitadel_es_pusher,zitadel_es_pusher_<INSTANCE_ID>}'::text[])))
                       Rows Removed by Filter: 42
   ->  Sort  (cost=290.58..290.60 rows=9 width=361) (actual time=4.685..4.685 rows=0 loops=1)
         Output: events2.created_at, events2.event_type, events2.sequence, events2."position", events2.payload, events2.creator, events2.owner, events2.instance_id, events2.aggregate_type, events2.aggregate_id, events2.revision, events2.in_tx_order
         Sort Key: events2."position", events2.in_tx_order
         Sort Method: quicksort  Memory: 25kB
         ->  Index Scan using es_projection on eventstore.events2  (cost=0.70..290.43 rows=9 width=361) (actual time=4.616..4.617 rows=0 loops=1)
               Output: events2.created_at, events2.event_type, events2.sequence, events2."position", events2.payload, events2.creator, events2.owner, events2.instance_id, events2.aggregate_type, events2.aggregate_id, events2.revision, events2.in_tx_order
               Index Cond: ((events2.instance_id = '<INSTANCE_ID>'::text) AND (events2.aggregate_type = ANY ('{project,instance,org}'::text[])) AND (events2.event_type = ANY ('{project.application.added,project.application.changed,project.application.deactivated,project.application.reactivated,project.application.removed,project.removed,project.application.config.api.added,project.application.config.api.changed,project.application.config.api.secret.changed,project.application.config.api.secret.updated,project.application.config.oidc.added,project.application.config.oidc.changed,project.application.config.oidc.secret.changed,project.application.config.oidc.secret.updated,project.application.config.saml.added,project.application.config.saml.changed,org.removed,instance.removed}'::text[])) AND (events2."position" > <POSITION>) AND (events2."position" < (InitPlan 1).col1))
 Query Identifier: -8254550537132386499
 Planning Time: 2.864 ms
 Execution Time: 5.414 ms
 ```
2025-03-13 16:50:23 +01:00
Stefan Benz
5eb3a543e8 fix: add permission check for saml request query (#9520) 2025-03-12 21:53:16 +01:00
Silvan
e770569413 fix(eventstore): optimise query hints for event filters (#9497)
(cherry picked from commit b578137139)
2025-03-12 16:05:03 +01:00
Livio Spring
a47f4a30fa fix(OIDC): back channel logout work for custom UI (#9487)
# Which Problems Are Solved

When using a custom / new login UI and an OIDC application with
registered BackChannelLogoutUI, no logout requests were sent to the URI
when the user signed out.
Additionally, as described in #9427, an error was logged:
`level=error msg="event of type *session.TerminateEvent doesn't
implement OriginEvent"
caller="/home/runner/work/zitadel/zitadel/internal/notification/handlers/origin.go:24"`

# How the Problems Are Solved

- Properly pass `TriggerOrigin` information to session.TerminateEvent
creation and implement `OriginEvent` interface.
- Implemented `RegisterLogout` in `CreateOIDCSessionFromAuthRequest` and
`CreateOIDCSessionFromDeviceAuth`, both used when interacting with the
OIDC v2 API.
- Both functions now receive the `BackChannelLogoutURI` of the client
from the OIDC layer.

# Additional Changes

None

# Additional Context

- closes #9427

(cherry picked from commit ed697bbd69)
2025-03-12 16:05:00 +01:00
Livio Spring
5ad33e717b fix(token exchange): properly return an error if membership is missing (#9468)
# Which Problems Are Solved

When requesting a JWT (`urn:ietf:params:oauth:token-type:jwt`) to be
returned in a Token Exchange request, ZITADEL would panic if the `actor`
was not granted the necessary permission.

# How the Problems Are Solved

Properly check the error and return it.

# Additional Changes

None

# Additional Context

- closes #9436

(cherry picked from commit e6ce1af003)
2025-03-12 16:04:57 +01:00
Max Peintner
6256908181 fix(login): passkey setup when pressing "Enter" key on login form (#9485)
# Which Problems Are Solved

When registering passkeys or u2f methods as second factor, some users
pressed the "Enter" key, rather than clicking the submit button. This
method has bypassed the execution of the device registration and
encoding scripts, resulting in the form being submitted without the
necessary encoded values.

# How the Problems Are Solved

This PR ensures that device registration is always executed and the
required information are submitted in the form regardless of pressing
"Enter" or clicking the button.

# Additional Changes

None

# Additional Context

- closes #6592
- closes #2910

(cherry picked from commit 27b319bd98)
2025-03-12 16:04:54 +01:00
Silvan
b578137139 fix(eventstore): optimise query hints for event filters (#9497) 2025-03-12 09:44:06 +00:00
Livio Spring
ed697bbd69 fix(OIDC): back channel logout work for custom UI (#9487)
# Which Problems Are Solved

When using a custom / new login UI and an OIDC application with
registered BackChannelLogoutUI, no logout requests were sent to the URI
when the user signed out.
Additionally, as described in #9427, an error was logged:
`level=error msg="event of type *session.TerminateEvent doesn't
implement OriginEvent"
caller="/home/runner/work/zitadel/zitadel/internal/notification/handlers/origin.go:24"`

# How the Problems Are Solved

- Properly pass `TriggerOrigin` information to session.TerminateEvent
creation and implement `OriginEvent` interface.
- Implemented `RegisterLogout` in `CreateOIDCSessionFromAuthRequest` and
`CreateOIDCSessionFromDeviceAuth`, both used when interacting with the
OIDC v2 API.
- Both functions now receive the `BackChannelLogoutURI` of the client
from the OIDC layer.

# Additional Changes

None

# Additional Context

- closes #9427
2025-03-11 14:19:09 +00:00
Livio Spring
e6ce1af003 fix(token exchange): properly return an error if membership is missing (#9468)
# Which Problems Are Solved

When requesting a JWT (`urn:ietf:params:oauth:token-type:jwt`) to be
returned in a Token Exchange request, ZITADEL would panic if the `actor`
was not granted the necessary permission.

# How the Problems Are Solved

Properly check the error and return it.

# Additional Changes

None

# Additional Context

- closes #9436
2025-03-11 11:14:18 +00:00
Max Peintner
27b319bd98 fix(login): passkey setup when pressing "Enter" key on login form (#9485)
# Which Problems Are Solved

When registering passkeys or u2f methods as second factor, some users
pressed the "Enter" key, rather than clicking the submit button. This
method has bypassed the execution of the device registration and
encoding scripts, resulting in the form being submitted without the
necessary encoded values.

# How the Problems Are Solved

This PR ensures that device registration is always executed and the
required information are submitted in the form regardless of pressing
"Enter" or clicking the button.

# Additional Changes

None

# Additional Context

- closes #6592
- closes #2910
2025-03-07 09:51:39 +00:00
Silvan
e82e53bd45 fix(cmd): clarify notification config handling (#9459)
# 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

(cherry picked from commit 92f0cf018f)
2025-03-06 07:41:17 +01:00
Max Peintner
52bb9ca3a5 fix(login): improve webauthn error handling (#9474)
This PR improves error handling around webauthn functions in the login.

(cherry picked from commit a82f5805b6)
2025-03-06 07:41:17 +01:00
Iraq
122b5f3e0e fix(actions): Linking external account doesn't trigger flow "External Authentication" on "Post Authentication" on first login (#9397)
# Which Problems Are Solved

When logging in using exeternal idp to Zitadel using SAML with action
setup to override existing Zitadel account attributes (first name/last
name/display name ect) with that of external linked idp account as
described here:
https://zitadel.com/docs/guides/integrate/identity-providers/azure-ad-saml#add-action-to-map-user-attributes,
does not happen until the next time the user logs in using the external
idp

# Additional Context

- Closes https://github.com/zitadel/zitadel/issues/9133

---------

Co-authored-by: Iraq Jaber <IraqJaber@gmail.com>
Co-authored-by: Livio Spring <livio.a@gmail.com>
(cherry picked from commit b0fa974419)
2025-03-06 07:41:16 +01:00
Silvan
92f0cf018f fix(cmd): clarify notification config handling (#9459)
# 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
2025-03-06 06:26:33 +00:00