PostgreSQL: Modify OWNER on all tables simultaneously in PostgreSQL
postgresql
Solution
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.
Problem
How do I modify the owner of all tables in a PostgreSQL database? I tried `ALTER TABLE * OWNER TO new_owner` but it doesn't support the asterisk syntax.