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 bypostgres
- 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.
Best Answer
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 asroot
on your servers. On your servers you should be logging in as real users and be usingsudo
for the odd occasion that you need superuser privileges. And preferablysudo su - <other_user>
rather than just doing everything directly usingsudo <command>
.In PostgreSQL we can implement more fine-grained policies.
Create a role structure something like:
Then login as
<real_person>
.To your scripts to deploy into
<owner>
, add the first line: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.