0

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.

  • If the person leading the feature is responsible for running the change scripts, why would people other than that person need to be able to unilaterally delete tables? – jjanes Jan 13 '18 at 18:49
  • If the original feature lead creates a table and a few months later we decide to move things around, perhaps a feature doesn't get used and we decide to remove it later on, the person who created it initially will need to be around to move ownership when attempting to do the deployment unless they've remembered in advance that they have object ownership. – Elliot Blackburn Jan 14 '18 at 16:30
1

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.

  • This is fantastic thank you, we were looking for basically a way to share ownership of created objects and this looks like it'll do the job. I especially like the more restricted version, we could then ensure that creation can be carried out by those with database experience and others can still call functions, inspect data, and so on. Thank you! – Elliot Blackburn Jan 14 '18 at 18:51

Your Answer

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

Not the answer you're looking for? Browse other questions tagged or ask your own question.