Change Table Ownership in PostgreSQL

I wanted to fix a problem with all the tables that were owned by a specific user. I looked up the user and found out it was a user with the ID (OID) of 16387.

Then I could use the following command to generate sql to fix the ownership in my database.

select 'ALTER TABLE ' || relname || ' OWNER TO web_user;' from pg_class where relkind = 'r' and relowner = 16387 order by relname;

This generated the following SQL for me (some people might recognize this as a way to generate dynamic SQL):

ALTER TABLE access OWNER TO web_user;
ALTER TABLE accesslog OWNER TO web_user;
ALTER TABLE actions OWNER TO web_user;
...
ALTER TABLE vocabulary OWNER TO web_user;
ALTER TABLE vocabulary_node_types OWNER TO web_user;
ALTER TABLE watchdog OWNER TO web_user;
ALTER TABLE webfm_attach OWNER TO web_user;
ALTER TABLE webfm_file OWNER TO web_user;
ALTER TABLE wysiwyg OWNER TO web_user;
ALTER TABLE wysiwyg_web_user OWNER TO web_user;

I could then run this output in my Postgresql client (psql as one example) to apply these ownership changes to all the tables (notice that my list above is NOT complete). (Just don't forget to be connected to the correct database or it will fail with a message like: ERROR:  relation "url_alias" does not exist.)