数据库对象所有权信息

显示当前数据库中对象的拥有者

场景：您想列出当前数据库中所有对象的拥有者。

解决方案：使用此系统目录查询

    SELECT pg_get_userbyid(c.relowner),
           COUNT(*) AS objects_owned
      FROM pg_class c
  GROUP BY 1
  ORDER BY 2 DESC
  

显示当前数据库中具有权限的用户

场景：您想列出在当前数据库中显式授予权限的所有用户。

解决方案：使用此系统目录查询

WITH a AS (
    SELECT UNNEST(c.relacl)::TEXT AS priv
      FROM pg_catalog.pg_class c
INNER JOIN pg_catalog.pg_namespace n
        ON n.oid = c.relnamespace
     WHERE relacl IS NOT NULL
       AND n.nspname NOT LIKE 'pg_%'
       AND n.nspname != 'information_schema'
)
    SELECT SUBSTRING(priv, 1, POSITION('=' IN priv)-1),
           COUNT(*)
      FROM a
     WHERE SUBSTRING(priv, 1, POSITION('=' IN priv)-1) != ''
  GROUP BY 1
  ORDER BY 2 DESC