`chown -R` equivalent for PostgreSQL

Question

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.

Answer 1

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.