Postgres Object ownership

Sometimes I have to fix some object ownership such as tables and views. Let's figure out if there is such object in your database:

--
-- list tables, views, foreign tables and sequences not owned by role postgres
--
SELECT n.nspname AS SCHEMA,
       c.relname AS relation,
       pg_get_userbyid(c.relowner) AS ROLE,
       'ALTER TABLE ' || quote_ident(nspname) || '.' || quote_ident(relname) || ' OWNER TO postgres;' AS command
FROM pg_class c
INNER JOIN pg_namespace n ON (c.relnamespace = n.oid)
WHERE nspname !~ '^pg_'
  AND nspname <> 'information_schema'
  AND relkind = 'r'
  AND pg_get_userbyid(c.relowner) <> 'postgres'
UNION ALL
SELECT n.nspname AS SCHEMA,
       c.relname AS relation,
       pg_get_userbyid(c.relowner) AS ROLE,
       'ALTER VIEW ' || quote_ident(nspname) || '.' || quote_ident(relname) || ' OWNER TO postgres;' AS command
FROM pg_class c
INNER JOIN pg_namespace n ON (c.relnamespace = n.oid)
WHERE nspname !~ '^pg_'
  AND nspname <> 'information_schema'
  AND relkind = 'v'
  AND pg_get_userbyid(c.relowner) <> 'postgres'
UNION ALL
SELECT n.nspname AS SCHEMA,
       c.relname AS relation,
       pg_get_userbyid(c.relowner) AS ROLE,
       'ALTER FOREIGN TABLE ' || quote_ident(nspname) || '.' || quote_ident(relname) || ' OWNER TO postgres;' AS command
FROM pg_class c
INNER JOIN pg_namespace n ON (c.relnamespace = n.oid)
WHERE nspname !~ '^pg_'
  AND nspname <> 'information_schema'
  AND relkind = 'f'
  AND pg_get_userbyid(c.relowner) <> 'postgres'
UNION ALL
SELECT n.nspname AS SCHEMA,
       c.relname AS relation,
       pg_get_userbyid(c.relowner) AS ROLE,
       'ALTER SEQUENCE ' || quote_ident(nspname) || '.' || quote_ident(relname) || ' OWNER TO postgres;' AS command
FROM pg_class c
INNER JOIN pg_namespace n ON (c.relnamespace = n.oid)
WHERE nspname !~ '^pg_'
  AND nspname <> 'information_schema'
  AND relkind = 'S'
  AND pg_get_userbyid(c.relowner) <> 'postgres';

This UNION ALL query list tables, views, foreign tables and sequences whose owner is not role postgres. They should be candidates for a new owner (mainly because you are adjusting ownership and permission on testing and/or staging environment). Let's say you want to apply such changes after check that some ownerships are wrong. The following query will output SQL command(s) to change ownership.

--
-- change owner of tables, views, foreign tables and sequences not owned by role postgres
--
SELECT 'ALTER TABLE ' || quote_ident(nspname) || '.' || quote_ident(relname) || ' OWNER TO postgres;' AS command
FROM pg_class c
INNER JOIN pg_namespace n ON (c.relnamespace = n.oid)
WHERE nspname !~ '^pg_'
  AND nspname <> 'information_schema'
  AND relkind = 'r'
  AND pg_get_userbyid(c.relowner) <> 'postgres'
UNION ALL
SELECT 'ALTER VIEW ' || quote_ident(nspname) || '.' || quote_ident(relname) || ' OWNER TO postgres;' AS command
FROM pg_class c
INNER JOIN pg_namespace n ON (c.relnamespace = n.oid)
WHERE nspname !~ '^pg_'
  AND nspname <> 'information_schema'
  AND relkind = 'v'
  AND pg_get_userbyid(c.relowner) <> 'postgres'
UNION ALL
SELECT 'ALTER FOREIGN TABLE ' || quote_ident(nspname) || '.' || quote_ident(relname) || ' OWNER TO postgres;' AS command
FROM pg_class c
INNER JOIN pg_namespace n ON (c.relnamespace = n.oid)
WHERE nspname !~ '^pg_'
  AND nspname <> 'information_schema'
  AND relkind = 'f'
  AND pg_get_userbyid(c.relowner) <> 'postgres'
UNION ALL
SELECT 'ALTER SEQUENCE ' || quote_ident(nspname) || '.' || quote_ident(relname) || ' OWNER TO postgres;' AS command
FROM pg_class c
INNER JOIN pg_namespace n ON (c.relnamespace = n.oid)
WHERE nspname !~ '^pg_'
  AND nspname <> 'information_schema'
  AND relkind = 'S'
  AND pg_get_userbyid(c.relowner) <> 'postgres';

And if you are using psql (9.6 and later), you should replace last character (semicolon) with \gexec. In this case, instead of print SQL commands, they will be executed. Voila, all tables, views, foreign tables and sequences that were not owned by postgres will switch to a new owner: postgres.