Modify OWNER on all tables simultaneously in Postgresql

Question

How do I modify all tables in a Postgres database to change the owner?

I tried ALTER TABLE * OWNER TO new_owner but it doesn't like the star.

Answer 1

There is no such command in PostgreSQL. But you can work around it using method I described some time ago for GRANTs.

Answer 2

Since you're changing the owner ship 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 table $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 table $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.

Answer 3

I recently had to change the ownership of all objects in a database. Although tables, views, triggers and sequences were somewhat easily changed the above approach failed for functions as the signature is part of the function name. Granted, I have a MySQL background and am not that familiar with Postgres.

However, pg_dump allows you to dump just the schema and this contains the ALTER xxx OWNER TO yyy; statements you need. Here is my bit of shell magic on the topic

pg_dump -s YOUR_DB | grep -i 'owner to' | sed -e 's/OWNER TO .*;/OWNER TO NEW_OWNER;/i' | psqL YOUR_DB