Closed pdarcos closed 5 months ago
From your logs Postgres says: permission denied for schema public
Thanks @aeneasr
I saw that too but it makes no sense since the kratos user already has full privileges on the database kratos as well as schema public:
postgres@auth:~$ psql
psql (15.6 (Debian 15.6-0+deb12u1))
Type "help" for help.
postgres=# GRANT ALL ON SCHEMA public TO kratos;
GRANT
and
postgres@auth:~$ psql
psql (15.6 (Debian 15.6-0+deb12u1))
Type "help" for help.
postgres=# GRANT ALL ON ALL TABLES IN SCHEMA public TO kratos ;
GRANT
yet I still get the same error message...
DEBU[2024-03-26T15:21:12Z] 0.0033 seconds audience=application service_name=Ory Kratos service_version=v1.1.0 Error: migrator: problem creating schema migrations: unable to execute statement: CREATE TABLE schema_migration (version VARCHAR (48) NOT NULL, version_self INT NOT NULL DEFAULT 0): ERROR: permission denied for schema public (SQLSTATE 42501)
I believe your documentation is outdated (it still tells us to install node v16 for example) and must be missing something to work with newer versions of postgresql.
Any idea?
Thanks
EDIT: Finally figured out the problem. It seems my suspicions are confirmed. In newer versions of postgresql one must explicitly set the kratos user as the owner of the kratos database or else it won't work regardless if the kratos user already was given full privileges on the database.
ie. the documentation is incomplete. Besides running the command "GRANT CONNECT ON DATABASE kratos to kratos;" from the documentation, one must also run the following command with newer versions of postgresql
postgres=# ALTER DATABASE kratos owner to kratos;
ALTER DATABASE
You might want to update the documentation so that other new users don't spend time figuring this out on their own
Preflight checklist
Ory Network Project
No response
Describe the bug
Hi, I am trying to folow the docs "deploy to production" from here But when I try to run the migrations "/opt/kratos/bin/kratos -c /opt/kratos/config/kratos.yml migrate sql -y postgres://kratos:CHANGE-ME-INSECURE-PASSWORD@127.0.0.1:5432/kratos?sslmode=disable" I get the following error:
An error occurred while checking for the legacy migration table, maybe it does not exist yet? Trying to create. audience=application error=map[message:ERROR: relation "schema_migration" does not exist (SQLSTATE 42P01) stack_trace:stack trace could not be recovered from error type *pgconn.PgError] migration_table=schema_migration service_name=Ory Kratos service_version=v1.1.0
I checked out the identity.schema.json that's being called and I see there https://schemas.ory.sh/presets/kratos/quickstart/email-password/identity.schema.json
But when I test the schemas.ory.sh domian it seems it cannot be resolved?
Reproducing the bug
Just follow the instructions from https://www.ory.sh/docs/kratos/guides/deploy-kratos-example
Relevant log output
An error occurred while checking for the legacy migration table, maybe it does not exist yet? Trying to create. audience=application error=map[message:ERROR: relation "schema_migration" does not exist (SQLSTATE 42P01) stack_trace:stack trace could not be recovered from error type *pgconn.PgError] migration_table=schema_migration service_name=Ory Kratos service_version=v1.1.0
and
Relevant configuration
No response
Version
v1.1.0
On which operating system are you observing this issue?
Linux
In which environment are you deploying?
Docker Compose
Additional Context
No response