Role permissions for postgres?

Hi,

We need to run some postgres commands making a table and inserting rows locally, ideally before the first deployment. It seems like connecting as the postgres user with the password printed when setting up postgres means the app’s postgres role cannot access that table. The workaround I can find is running fly console -a appname-db -C env | grep DATABASE_URL, grab the app role’s password, and connect with that to do the initial migration. Is there a better way?

We need to run some postgres commands making a table and inserting rows locally, ideally before the first deployment.

You could leverage the release_command configuration option within your fly.toml file.

If you have already attached the PG to your app, you should be able to call a script via the release_command to handle the migration/seed process. As long as the script leverages the DATABASE_URL envvar to establish the connection you shouldn’t run into any permissions issues.

With regards to the permission issues, this may help provide some context with whats going on there.

Let me know if you have any questions on this!

Specifically, we need to register an account locally with a human solving a captcha and getting a verification code, and then insert the contents of some files into the database. The release_command would need to read local files.

Also, we’d like multiple apps to use the same database. By default it seems like each role creates tables in separate table_catalogs (separate databases in the same postgres cluster?), and even the postgres role can’t seem to see tables created by an app role. What’s the right way to do this?

Also, we’d like multiple apps to use the same database. By default it seems like each role creates tables in separate table_catalogs (separate databases in the same postgres cluster?), and even the postgres role can’t seem to see tables created by an app role. What’s the right way to do this?

Got it, so it will depend on what kind of permission scheme you’d like to implement. Seems like you may have a couple of options:

  1. If you’re running PG14, you could consider granting some pre-defined roles to the users that require read and or write access to the same tables.
    PostgreSQL: Documentation: 16: 22.5. Predefined Roles

For audibility purposes, it’s generally a good idea to have each app connect with its own specific user.

  1. You could follow the guidance listed here: Sidekiq worker insufficient Postgres privileges - #2 by shaun

  2. If user level audibility is not a big deal for you, you could manually create a new user with super user privileges or leverage the Postgres user which has super user privileges by default.

Even the postgres role can’t seem to see tables created by an app role

How are you currently trying to list your tables?