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?
One option would be to set up the migration user as
CREATE ROLE build_account NOINHERIT IN ROLE db_ownerand then
SET ROLE db_ownerbefore creating/dropping a table.
Have the build role do
ALTER TABLE new_table TO db_ownerafter 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?