3

We occasionally have the problem that, when upgrading some software and database, inexperienced (or overworked, not excluding myself) sysadmins (we don’t have separate DBAs except for Oracle) run the DB upgrade scripts as user postgres instead of the correct application user.

Update: emphasis added. Our normal mode of operation, as documented, involves running those scripts as regular user. This question is asked to clean up for those cases when that procedure is accidentally not followed.

How can I change the ownership of all objects in a (one) database, no matter whom they belong to, to a specific user?

The methods described here don’t work for me, because:

  • REASSIGN OWNED apparently is per-cluster, not per-database (too broad)
  • REASSIGN OWNED does not work for objects owned by postgres
  • the manual enumeration from @AlexSoto’s answer requires one to know the types of the objects involved beforehand

I really want basically the equivalent of the Unix chown -R newowner /path command, just for a whole PostgreSQL database (all schemata, tables, indicēs, etc. in it).

Minimum PostgreSQL version involved is 9.1 (Debian wheezy), maximum whatever Debian unstable has at any given time.

  • 1
    IMHO you should fix this by creating personal logins for your DBAs instead of using postgres and using set role in your scripts to become the correct owner of the objects. – Colin 't Hart Jun 25 '15 at 12:12
  • @Colin'tHart we do that. See above. This question is for when that is accidentally not followed, to clean it up. – mirabilos Jun 25 '15 at 13:26
  • 2
    REASSIGN OWNED apparently is per-cluster: where does that come from? The manual says REASSIGN OWNED does not affect objects within other databases – Daniel Vérité Jun 25 '15 at 14:16
  • ah ok, I apparently misread it then; this comes from the linked SO thread – mirabilos Jun 25 '15 at 19:41
3

My answer doesn't answer your question directly, but addresses your method of operation.

You shouldn't be logging in as postgres, just like you shouldn't be logging in as root on your servers. On your servers you should be logging in as real users and be using sudo for the odd occasion that you need superuser privileges. And preferably sudo su - <other_user> rather than just doing everything directly using sudo <command>.

In PostgreSQL we can implement more fine-grained policies.

Create a role structure something like:

create role admin noinherit;
grant <owner> to admin;
grant admin to <real_person>;

Then login as <real_person>.

To your scripts to deploy into <owner>, add the first line:

set role <owner>;

Then you should never have the problem again that you describe in your question.

And you never need to login as a superuser -- unless you need to create new schemas or users.

  • We have documentation and infrastructure to do precisely that, but it can be forgotten in the heat of the moment. It’s a rare situation, but it does happen. This SO question is for cleaning up precisely the situation when this procedure is not followed. – mirabilos Jun 25 '15 at 13:24

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.