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:
- 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.
-
You could follow the guidance listed here: Sidekiq worker insufficient Postgres privileges - #2 by shaun
-
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?