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 OWNEDapparently is per-cluster, not per-database (too broad)
REASSIGN OWNEDdoes not work for objects owned by
- 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.