–列出所有非postgres用户的表、视图、对象，并修改对象所属用户

--
-- 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';