permission denied for database

[Tycholiz]

I'm attempting to run migrations on my production database, which I'm doing through my Github Actions pipeline.

However, since I'm encountering this issue locally as well, I'll take the pipeline out of the equation.

Here is how I'm defining DATABASE_URL: DATABASE_URL="postgresql://$POSTGRES_OWNER_USER:$POSTGRES_OWNER_PASSWORD@$POSTGRES_HOST:$POSTGRES_PORT/$POSTGRES_DB?ssl=true&sslrootcert=./ca-certificate.crt"

I am able to connect via psql to my remote database with this connection string.

However, when I attempt to run npx graphile-migrate migrate with that DATABASE_URL, I get:

error: permission denied for database mydb

I'm a little lost here, since I can connect to the database using this exact connection string, yet I cannot run the migrations. Is there something obvious that I may be missing here?

[benjie]

Hard to know without knowing what your migrations contain, more of the log output, etc. Is this owner user a super user? If not, do they have full permissions on the database, including the ability to create schemas?

[Tycholiz]

This is not a superuser, just a mini-superuser (I think you used that term), meant to to be superuser for my app's database alone.

However, my superuser is currently the owner of my app's main database. Maybe it should be my owner user instead?

By any means, I granted some permissions to my owner user:

    grant create on database neverforget to neverforget_owner;

This allowed me to get past the issue I was having, but now I get this:

error: permission denied for schema graphile_migrate

I've tried granting various permissions to the graphile_migrate schema:

GRANT USAGE ON SCHEMA graphile_migrate TO neverforget_owner;
GRANT SELECT ON ALL TABLES IN SCHEMA graphile_migrate TO neverforget_owner;
GRANT ALL ON ALL TABLES IN SCHEMA graphile_migrate TO neverforget_owner ;
GRANT ALL ON ALL SEQUENCES IN SCHEMA graphile_migrate TO neverforget_owner ;
GRANT ALL ON ALL FUNCTIONS IN SCHEMA graphile_migrate TO neverforget_owner ;

Even after all of this, I'm still getting the same permissions issue on the graphile_migrate schema. Precisely which permissions are needed? I couldn't find graphile-migrate docs for what permissions we should have, which makes me think that maybe I made a mistake in setting up. The superuser (not mini-superuser) is the owner of the graphile_migrate schema. Maybe again, the owner should be the mini-superuser?

Curious to hear your thoughts

[Tycholiz]

I destroyed all my infra and started from scratch, and now my owner user is the owner of the graphile_migrate schema. The migrate command works now. Not sure why that was happening, but I suppose we got our answer.

Thanks for your help