How to modify owner of all tables in PostgreSQL?


For PostgreSQL from the version 8.2, you can use REASSIGN OWNED to transfer the ownership of any database objects owned by a database role to a new role.

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

2. Using psql command

For older versions, you can use these command to change to ownership of tables, sequences, and views.


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


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


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

Need a good GUI tool for PostgreSQL? Check out TablePlus. It’s native, beautiful, and available for free.

Download TablePlus here.

Not on Mac? Download TablePlus for Windows.

Need a quick edit on the go? Download TablePlus for iOS

TablePlus GUI Tool PostgreSQL