PostgreSQL – Automatically Assign Object Owner to a Role on Creation

postgresql

Background:

I'll start by saying I'm fairly new to postgres, I generally work with web apps and I know my way around SQL but I'm new to both postgres and database administration. I have however read through the postgres docs for database roles.

My team at work is rather small and we're responsible for the building and deployment of a web application which uses postgres as our main database. There are about 3 of us who actively work on the database. This includes creating, altering (and occasionally dropping) tables, and doing the same to functions and types.

We've recently run into the problem of object ownership. It seems only the owner of an object can drop it from the database, be that any database object. We want reasonable freedom to be able to manipulate the database as required without messing around too much with object ownership all the time.

At the moment we haven't had the chance to automate the deployment of our database changes via migration scripts and the responsibility of running these falls to the individual leading a feature development and deployment.

Question:

We'd like a way to assign object ownership to a group role (which we would all have access to) without having to do it manually after every object creation. Is there a way to set this up globally for a schema or database, or even for a group role? If in the (likely) event that this is a really silly thing to do, does anyone have any suggestions of how we should be working in this scenario?

Things we've explored:

I have already gone through the alter default privileges but it seems this must be set for every login role (user), which feels like there's some friction meaning we might be going in the wrong direction. Past that, google and stack exchange have turned up little, though I may not be using the correct terminology.

Best Answer

You can't use alter default privileges because it can't reassign ownership.

What you could do instead is run for each dev:

alter user john_q_developer set role = feature_role;

This way all of the developers will by default create all their objects with feature_role as the owner. This is pretty much the same thing as all the developers just logging on to the database as the same user, except this way they all get their own password (or their own authentication method of choice) rather than sharing one.

This has the same "friction" that alter default privileges does, in that you have to run the alter user ... one time for each create user ..., but that level of friction seems pretty small to me.

The objects won't be tagged inside the database with who created them, but you can set up the server to log that information if you want (include "%u" in the log_line_prefix, and set log_statement = 'ddl' in the configuration file postgresql.conf.)

A slightly more restricted approach would be to create a feature_owner role that can create objects, and a feature_dev noinherit in role feaure_owner role which has all rights other than to create objects, and grant all devs the role feature_dev.

That way attempts to create objects will fail by default, but anyone can just execute set role = feature_owner and then repeat the creation in order to create objects under that ownership. It would just be common knowledge that only the feature lead is supposed to make use of this power under normal circumstances.

Related Question