permission denied for function seq_nextval (SQLSTATE 42501)" projection=projections.notifications
Hi,
My instance can't send notifications on password reset or on email verification. I have this error :
I have grant all on sequences to my zitadel user (he's also the owner of the sequences).
After some research, I saw an error in postgresql logs before the permission denied :
My instance is self-hosted on the latest version 3.2.2.
I'll appreciate if someone have an idea on how to fix this. Thanks !
9 Replies
I granted superuser access to my zitadel user so that he could use the NEXTVAL function.
I will continue to debug this, as I am not happy with the idea of an app user being a superuser on a database.
I'm sorry bacause it's most likely a postgre question than a zitadel question, and I saw in
projections.failed_events that the error occurs since our migration from cockroach.hey @Wolfbergen will look into this thanks for your question.
hey @Wolfbergen have you already checked if there are any stale projections for
notifications
it should give us an idea about the next steps to be taken(if they are stale -> terminate OR if they are corrupted -> delete)Hey @Rajat
I rollback my attribute superuser and create a new user to generate a notification. Here is what I have in the table current_states and in failed events2
And here is the content of the table events2
cc @adlerhurst here
Hi there
Can you provide some information about your setup?
You migrated from cockroach to postgres right? On which version have you been before? How did you migrate?
Also the configuration of zitadel would be interesting if you could share without secrets
Hi @adlerhurst
We have a cluster with 3 servers (Zitadel + postgreSQL) and a load balancer in front of this. PostgreSQL is replicated with EDB Postgres Distributed (https://www.enterprisedb.com/docs/pgd/4/bdr/) to have multiple masters.
We used cockroachDB until March and I have followed the documentation to migrate data with the mirror command (https://zitadel.com/docs/self-hosting/manage/cli/mirror) just before the pre-release of the v3. I just had a problem in my events2 table where most of the positions (~95%) were not in seconds but in micro (or nano) seconds so I had to divide all values to have it in seconds.
You can see my config attached, I have removed all sensible information.
ZITADEL Docs
The mirror command allows you to do database to database migrations. This functionality is useful to copy data from one database to another.
Hi @Wolfbergen
sorry for the late reply this one slipped through.
can you try to grant usage of the sequences of the notifications manually by executing the following command?
GRANT USAGE, SELECT ON ALL SEQUENCES IN SCHEMA queue TO zitadel;
additionally, do all notifications fail or do some of them work? Im asking because I want to verify that the grant got promoted to all database serversHi @adlerhurst ,
This was the first thing I've tested when I encountered this issue. It doesn't do anything.
I'll retry this when I have time to test it again