Bug: resources can't be created when Zitadel is self-hosted with PostgreSQL about terraform-provider-zitadel

[stebenz]

@stebenz @livio-a could this have something todo with https://github.com/zitadel/zitadel/releases/tag/v2.22.1 ?

@fforootd yes this error should be fixed.
@s0l4r I think as there should be a fix on ZITADEL for your error, @eliobischof still needs to have a look at this in combination with Postgres, if I can help in any way just ping me

[s0l4r]

I'm not sure if the Message=Errors.Project.NotExisting Parent=(sql: no rows in result set)"} and Message=Errors.Project.NotExisting Parent=(sql: no rows in result set)"} are still valid using the latest 2.22.2 release.
The new error message is "Error: error while listing orgs: rpc error: code = PermissionDenied desc = No matching permissions found (AUTH-5mWD2)", which seems correct.

However the permission fix for me was adding the Iam Role Manager to the service account used by Terraform. See related discussion at https://discord.com/channels/927474939156643850/1086376250899189791

The "sequence do not match" errors are still there, but the resources are now created in Zitadel without errors after adding the service account role.

[TakumiHaruta]

I dug into the structure and data model of event, but in conclusion, using -parallelism=1 option when executingterraform apply is one of the workarounds for this bug.

When I forcely dropped previous_sequence_unique and prev_agg_type_seq_unique constraints from eventstore.events table, there are some duplicate numbers in previous_aggregate_type_sequence column which were inserted by two different requests (e.g. project.role.added and project.added) at the same time. I think a client that is able to request in parallel such as Terraform is likely to encounter this error.

I'm not sure how critical this bug is in terms of Zitadel's overall architecture, but at least it needs to be fixed before the PostgreSQL integration is released as a major version.

Reference

• https://github.com/zitadel/zitadel/blob/main/docs/docs/concepts/eventstore/implementation.md
• https://github.com/zitadel/zitadel/blob/main/cmd/initialise/sql/postgres/08_events_table.sql
• https://github.com/zitadel/zitadel/blob/d721f725fd9e59b3cf99978bf499e562d5dc2869/internal/eventstore/repository/sql/crdb.go

[hifabienne]

Hei @TakumiHaruta Thanks for your bug report.
@eliobischof @stebenz can one of you have a look at this?

[eliobischof]

@adlerhurst do you by chance have something on top of your mind about a problem with sequence consistency with PostgreSQL?

[TakumiHaruta]

@eliobischof Sorry I don't have any idea about this

[s0l4r]

I opened issue zitadel/zitadel#5389 on a fresh install with Zitadel and PostgreSQL as well. When I try terraform apply to add a new organization (or user), I get these sequence errors in the server logs.

terraform apply
zitadel_org.org: Creating...
╷
│ Error: http status not ok: 400 Bad Request {"error":"server_error","error_description":"ID=QUERY-aKcc2 Message=Errors.Project.NotExisting Parent=(sql: no rows in result set)"}
│ 
│ 
│   with zitadel_org.org,
│   on resources.tf line 94, in resource "zitadel_org" "org":
│   94: resource "zitadel_org" "org" {
│ 
╵

zitadel_human_user.human_user: Creating...
╷
│ Error: failed to create human user: http status not ok: 400 Bad Request {"error":"server_error","error_description":"ID=QUERY-aKcc2 Message=Errors.Project.NotExisting Parent=(sql: no rows in result set)"}
│ 
│ 
│   with zitadel_human_user.human_user,
│   on resources.tf line 98, in resource "zitadel_human_user" "human_user":
│   98: resource zitadel_human_user human_user {
│ 
╵

Server logs:

2023-03-21T10:05:41+01:00 time="2023-03-21T09:05:41Z" level=warning msg="sequences do not match" aggregateType=user caller="/home/runner/work/zitadel/zitadel/internal/eventstore/handler/crdb/handler_stmt.go:235" currentSeq=125 prevSeq=138 projection=projections.notifications sequence=139
2023-03-21T10:05:42+01:00 time="2023-03-21T09:05:42Z" level=warning msg="sequences do not match" aggregateType=user caller="/home/runner/work/zitadel/zitadel/internal/eventstore/handler/crdb/handler_stmt.go:235" currentSeq=125 prevSeq=138 projection=projections.notifications sequence=139
2023-03-21T10:05:43+01:00 time="2023-03-21T09:05:43Z" level=warning msg="sequences do not match" aggregateType=user caller="/home/runner/work/zitadel/zitadel/internal/eventstore/handler/crdb/handler_stmt.go:235" currentSeq=125 prevSeq=138 projection=projections.notifications sequence=139
2023-03-21T10:05:44+01:00 time="2023-03-21T09:05:44Z" level=warning msg="sequences do not match" aggregateType=user caller="/home/runner/work/zitadel/zitadel/internal/eventstore/handler/crdb/handler_stmt.go:235" currentSeq=125 prevSeq=138 projection=projections.notifications sequence=139
2023-03-21T10:05:45+01:00 time="2023-03-21T09:05:45Z" level=warning msg="sequences do not match" aggregateType=user caller="/home/runner/work/zitadel/zitadel/internal/eventstore/handler/crdb/handler_stmt.go:235" currentSeq=125 prevSeq=138 projection=projections.notifications sequence=139
2023-03-21T10:05:46+01:00 time="2023-03-21T09:05:46Z" level=warning msg="sequences do not match" aggregateType=user caller="/home/runner/work/zitadel/zitadel/internal/eventstore/handler/crdb/handler_stmt.go:235" currentSeq=125 prevSeq=138 projection=projections.notifications sequence=139
2023-03-21T10:05:47+01:00 time="2023-03-21T09:05:47Z" level=warning msg="unable to process all events from subscription" caller="/home/runner/work/zitadel/zitadel/internal/eventstore/handler/handler_projection.go:179" error="some statements failed" projection=projections.notifications

[fforootd]

@stebenz @livio-a could this have something todo with https://github.com/zitadel/zitadel/releases/tag/v2.22.1 ?

[s0l4r]

I now tested the initial bulk operation with Terraform using multiple organizations, and this error occurs:

╷
│ Error: rpc error: code = Internal desc = unable to create event (SQL-SBP37)
│ 
│   with zitadel_org.tenant_ids["org1"],
│   on resources.tf line 103, in resource "zitadel_org" "tenant_ids":
│  103: resource "zitadel_org" "tenant_ids" {
│ 
╵
╷
│ Error: http status not ok: 400 Bad Request {"error":"server_error","error_description":"ID=SQL-SBP37 Message=unable to create event Parent=(ERROR: duplicate key value violates unique constraint \"previous_sequence_unique\" (SQLSTATE 23505))"}
│ 
│ 
│   with zitadel_org.tenant_ids["org5"],
│   on resources.tf line 103, in resource "zitadel_org" "tenant_ids":
│  103: resource "zitadel_org" "tenant_ids" {
│ 

2023-03-22T15:07:52+01:00 time="2023-03-22T14:07:52Z" level=debug msg="locker changed" caller="/home/runner/work/zitadel/zitadel/internal/eventstore/v1/locker/lock.go:44" locker=206351316132298890--0 logID=LOCKE-lOgbg view=auth.tokens
2023-03-22T15:10:52+01:00 time="2023-03-22T14:10:52Z" level=debug msg="locker changed" caller="/home/runner/work/zitadel/zitadel/internal/eventstore/v1/locker/lock.go:44" locker=206351316132364426--0 logID=LOCKE-lOgbg view=adminapi.styling2
2023-03-22T15:10:52+01:00 time="2023-03-22T14:10:52Z" level=debug msg="locker changed" caller="/home/runner/work/zitadel/zitadel/internal/eventstore/v1/locker/lock.go:44" locker=206351316132298890--0 logID=LOCKE-lOgbg view=auth.org_project_mapping2
2023-03-22T15:10:52+01:00 time="2023-03-22T14:10:52Z" level=debug msg="locker changed" caller="/home/runner/work/zitadel/zitadel/internal/eventstore/v1/locker/lock.go:44" locker=206351316132298890--0 logID=LOCKE-lOgbg view=auth.users2
2023-03-22T15:10:52+01:00 time="2023-03-22T14:10:52Z" level=debug msg="locker changed" caller="/home/runner/work/zitadel/zitadel/internal/eventstore/v1/locker/lock.go:44" locker=206351316132298890--0 logID=LOCKE-lOgbg view=auth.refresh_tokens
2023-03-22T15:10:52+01:00 time="2023-03-22T14:10:52Z" level=debug msg="locker changed" caller="/home/runner/work/zitadel/zitadel/internal/eventstore/v1/locker/lock.go:44" locker=206351316132298890--0 logID=LOCKE-lOgbg view=auth.user_sessions
2023-03-22T15:10:52+01:00 time="2023-03-22T14:10:52Z" level=debug msg="locker changed" caller="/home/runner/work/zitadel/zitadel/internal/eventstore/v1/locker/lock.go:44" locker=206351316132298890--0 logID=LOCKE-lOgbg view=auth.tokens
2023-03-22T15:10:54+01:00 time="2023-03-22T14:10:54Z" level=info msg="query failed" aggregate=user aggregateId=206351669175255178 aggregateType=user caller="/home/runner/work/zitadel/zitadel/internal/eventstore/repository/sql/crdb.go:142" error="ERROR: duplicate key value violates unique constraint \"previous_sequence_unique\" (SQLSTATE 23505)" eventType=user.token.added instanceID=206351300495933577
2023-03-22T15:10:54+01:00 time="2023-03-22T14:10:54Z" level=warning msg="sequences do not match" aggregateType=user caller="/home/runner/work/zitadel/zitadel/internal/eventstore/handler/crdb/handler_stmt.go:235" currentSeq=126 prevSeq=147 projection=projections.notifications sequence=148
2023-03-22T15:10:54+01:00 time="2023-03-22T14:10:54Z" level=debug msg="statement dropped" caller="/home/runner/work/zitadel/zitadel/internal/eventstore/handler/crdb/handler_stmt.go:226" currentSequence="&{206351300495933577 152}" statement="&{user 150 148 206351300495933577 <nil>}"
2023-03-22T15:10:54+01:00 time="2023-03-22T14:10:54Z" level=debug msg="statement dropped" caller="/home/runner/work/zitadel/zitadel/internal/eventstore/handler/crdb/handler_stmt.go:226" currentSequence="&{206351300495933577 152}" statement="&{user 151 150 206351300495933577 <nil>}"
2023-03-22T15:10:54+01:00 time="2023-03-22T14:10:54Z" level=debug msg="statement dropped" caller="/home/runner/work/zitadel/zitadel/internal/eventstore/handler/crdb/handler_stmt.go:226" currentSequence="&{206351300495933577 152}" statement="&{user 152 151 206351300495933577 <nil>}"
2023-03-22T15:10:55+01:00 time="2023-03-22T14:10:55Z" level=debug msg="statement dropped" caller="/home/runner/work/zitadel/zitadel/internal/eventstore/handler/crdb/handler_stmt.go:226" currentSequence="&{206351300495933577 157}" statement="&{org 153 137 206351300495933577 <nil>}"
2023-03-22T15:10:55+01:00 time="2023-03-22T14:10:55Z" level=debug msg="statement dropped" caller="/home/runner/work/zitadel/zitadel/internal/eventstore/handler/crdb/handler_stmt.go:226" currentSequence="&{206351300495933577 157}" statement="&{org 154 153 206351300495933577 <nil>}"
2023-03-22T15:10:55+01:00 time="2023-03-22T14:10:55Z" level=debug msg="statement dropped" caller="/home/runner/work/zitadel/zitadel/internal/eventstore/handler/crdb/handler_stmt.go:226" currentSequence="&{206351300495933577 157}" statement="&{org 155 154 206351300495933577 <nil>}"
2023-03-22T15:10:55+01:00 time="2023-03-22T14:10:55Z" level=debug msg="statement dropped" caller="/home/runner/work/zitadel/zitadel/internal/eventstore/handler/crdb/handler_stmt.go:226" currentSequence="&{206351300495933577 157}" statement="&{org 156 155 206351300495933577 <nil>}"
2023-03-22T15:10:55+01:00 time="2023-03-22T14:10:55Z" level=debug msg="statement dropped" caller="/home/runner/work/zitadel/zitadel/internal/eventstore/handler/crdb/handler_stmt.go:226" currentSequence="&{206351300495933577 157}" statement="&{org 157 156 206351300495933577 <nil>}"
2023-03-22T15:10:55+01:00 time="2023-03-22T14:10:55Z" level=debug msg="statement dropped" caller="/home/runner/work/zitadel/zitadel/internal/eventstore/handler/crdb/handler_stmt.go:226" currentSequence="&{206351300495933577 157}" statement="&{org 153 137 206351300495933577 <nil>}"
2023-03-22T15:10:55+01:00 time="2023-03-22T14:10:55Z" level=debug msg="statement dropped" caller="/home/runner/work/zitadel/zitadel/internal/eventstore/handler/crdb/handler_stmt.go:226" currentSequence="&{206351300495933577 157}" statement="&{org 154 153 206351300495933577 <nil>}"
2023-03-22T15:10:55+01:00 time="2023-03-22T14:10:55Z" level=debug msg="statement dropped" caller="/home/runner/work/zitadel/zitadel/internal/eventstore/handler/crdb/handler_stmt.go:226" currentSequence="&{206351300495933577 157}" statement="&{org 155 154 206351300495933577 0xfeca20}"
2023-03-22T15:10:55+01:00 time="2023-03-22T14:10:55Z" level=debug msg="statement dropped" caller="/home/runner/work/zitadel/zitadel/internal/eventstore/handler/crdb/handler_stmt.go:226" currentSequence="&{206351300495933577 157}" statement="&{org 156 155 206351300495933577 0xfecd20}"
2023-03-22T15:10:55+01:00 time="2023-03-22T14:10:55Z" level=debug msg="statement dropped" caller="/home/runner/work/zitadel/zitadel/internal/eventstore/handler/crdb/handler_stmt.go:226" currentSequence="&{206351300495933577 157}" statement="&{org 157 156 206351300495933577 <nil>}"
2023-03-22T15:10:55+01:00 time="2023-03-22T14:10:55Z" level=info msg="query failed" aggregate=org aggregateId=206356829226139786 aggregateType=org caller="/home/runner/work/zitadel/zitadel/internal/eventstore/repository/sql/crdb.go:142" error="ERROR: duplicate key value violates unique constraint \"prev_agg_type_seq_unique\" (SQLSTATE 23505)" eventType=org.added instanceID=206351300495933577
2023-03-22T15:10:55+01:00 time="2023-03-22T14:10:55Z" level=debug msg="statement dropped" caller="/home/runner/work/zitadel/zitadel/internal/eventstore/handler/crdb/handler_stmt.go:226" currentSequence="&{206351300495933577 157}" statement="&{org 153 137 206351300495933577 <nil>}"
2023-03-22T15:10:55+01:00 time="2023-03-22T14:10:55Z" level=debug msg="statement dropped" caller="/home/runner/work/zitadel/zitadel/internal/eventstore/handler/crdb/handler_stmt.go:226" currentSequence="&{206351300495933577 157}" statement="&{org 154 153 206351300495933577 <nil>}"
2023-03-22T15:10:55+01:00 time="2023-03-22T14:10:55Z" level=debug msg="statement dropped" caller="/home/runner/work/zitadel/zitadel/internal/eventstore/handler/crdb/handler_stmt.go:226" currentSequence="&{206351300495933577 157}" statement="&{org 155 154 206351300495933577 <nil>}"
2023-03-22T15:10:55+01:00 time="2023-03-22T14:10:55Z" level=debug msg="statement dropped" caller="/home/runner/work/zitadel/zitadel/internal/eventstore/handler/crdb/handler_stmt.go:226" currentSequence="&{206351300495933577 157}" statement="&{org 156 155 206351300495933577 <nil>}"

The workaround is to use terraform apply -parallelism=1 as suggested.

[hifabienne]

From the messages above I get that the issue was solved, so I will close it.
Feel free to reopen if thats not the case