I'm having a re-look at roles and privileges in postgres to minimise the damage that a compromised account can do.

Ownership and migrations is a part I'm having trouble getting my head around.

As I understand it, a table is initially owned by the role that creates it, and a table can only be dropped by the owner. However, the owner of a table is effectively a super-user. They can grant themselves all permissions on that table, making any attempt to limit that user's access to the table fairly pointless.

I've a separate role that our application uses (application_role) to connect and that role is not the owner and does not have permission to alter the table or truncate.

Changes to the schema are automated as part of our build process with fairly high permission levels. I'm looking at a couple of alternatives to see if they offer any practical additional security. But it seems like not really?

  1. One option would be to set up the migration user as CREATE ROLE build_account NOINHERIT IN ROLE db_owner and then SET ROLE db_owner before creating/dropping a table.

  2. Have the build role do ALTER TABLE new_table TO db_owner after it creates a table. This does seem more robust than the above option, but means we'll have to intervene manually if we ever want to drop a table. That might be ok, since dropping tables is rare, but it eliminates some of the advantage of automated schema changes as it introduces more opportunity for user error.

Is there anything I've missed? Is there a best practice for setting up the role that performs Postgres schema migrations in a build process?

  • "with fairly high permission levels" not sure I understand that. Why aren't you running the migrations using the table owner? – a_horse_with_no_name Jul 29 '20 at 8:45
  • Because I didn't understand the nature of table ownership at the time the build process was set up. But I'm wondering if they should be. – ChrisJ Jul 29 '20 at 21:30

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.