PostgreSQL: Modify OWNER on all tables simultaneously in PostgreSQL

Solution 1:

You can use the REASSIGN OWNED command.

Synopsis:

REASSIGN OWNED BY old_role [, ...] TO new_role

This changes all objects owned by old_role to the new role. You don't have to think about what kind of objects that the user has, they will all be changed. Note that it only applies to objects inside a single database. It does not alter the owner of the database itself either.

It is available back to at least 8.2. Their online documentation only goes that far back.

Solution 2:

See REASSIGN OWNED command

Note: As @trygvis mentions in the answer below, the REASSIGN OWNED command is available since at least version 8.2, and is a much easier method.

Since you're changing the ownership for all tables, you likely want views and sequences too. Here's what I did:

Tables:

for tbl in `psql -qAt -c "select tablename from pg_tables where schemaname = 'public';" YOUR_DB` ; do  psql -c "alter table \"$tbl\" owner to NEW_OWNER" YOUR_DB ; done

Sequences:

for tbl in `psql -qAt -c "select sequence_name from information_schema.sequences where sequence_schema = 'public';" YOUR_DB` ; do  psql -c "alter sequence \"$tbl\" owner to NEW_OWNER" YOUR_DB ; done

Views:

for tbl in `psql -qAt -c "select table_name from information_schema.views where table_schema = 'public';" YOUR_DB` ; do  psql -c "alter view \"$tbl\" owner to NEW_OWNER" YOUR_DB ; done

You could probably DRY that up a bit since the alter statements are identical for all three.

Solution 3:

This: http://archives.postgresql.org/pgsql-bugs/2007-10/msg00234.php is also a nice and fast solution, and works for multiple schemas in one database:

Tables

SELECT 'ALTER TABLE '|| schemaname || '."' || tablename ||'" OWNER TO my_new_owner;'
FROM pg_tables WHERE NOT schemaname IN ('pg_catalog', 'information_schema')
ORDER BY schemaname, tablename;

Sequences

SELECT 'ALTER SEQUENCE '|| sequence_schema || '."' || sequence_name ||'" OWNER TO my_new_owner;'
FROM information_schema.sequences WHERE NOT sequence_schema IN ('pg_catalog', 'information_schema')
ORDER BY sequence_schema, sequence_name;

Views

SELECT 'ALTER VIEW '|| table_schema || '."' || table_name ||'" OWNER TO my_new_owner;'
FROM information_schema.views WHERE NOT table_schema IN ('pg_catalog', 'information_schema')
ORDER BY table_schema, table_name;

Materialized Views

Based on this answer

SELECT 'ALTER TABLE '|| oid::regclass::text ||' OWNER TO my_new_owner;'
FROM pg_class WHERE relkind = 'm'
ORDER BY oid;

This generates all the required ALTER TABLE / ALTER SEQUENCE / ALTER VIEW statements, copy these and paste them back into plsql to run them.

Check your work in psql by doing:

\dt *.*
\ds *.*
\dv *.*