如何在PostgreSQL中查询PUBLIC角色的已授予权限列表？

1. 获取PUBLIC的SCHEMA权限列表 #

要查询PUBLIC拥有的模式权限，可以直接使用information_schema.schema_privileges视图——这是PostgreSQL专门存储模式权限信息的系统视图，对应的查询语句如下：

-- 获取PUBLIC的模式权限
SELECT schema_name, string_agg(privilege_type, ',') AS privileges
FROM information_schema.schema_privileges
WHERE grantee = 'PUBLIC' AND schema_name NOT LIKE 'pg_%' AND schema_name != 'information_schema'
GROUP BY schema_name;

2. 一次性查询所有PUBLIC已授予权限 #

如果需要统一获取表/视图、函数、模式的全部PUBLIC权限，可以通过UNION ALL将三个查询整合，输出结构化的结果：

-- 整合查询所有PUBLIC权限（表/视图、函数、模式）
SELECT 'table/view' AS object_type, table_schema AS schema_name, table_name AS object_name, privileges
FROM (
    SELECT table_schema, table_name, string_agg(privilege_type, ',') AS privileges
    FROM information_schema.table_privileges
    WHERE grantee='PUBLIC' AND table_schema NOT LIKE 'pg_%' AND table_schema != 'information_schema'
    GROUP BY table_schema, table_name
) t
UNION ALL
SELECT 'function' AS object_type, routine_schema AS schema_name, routine_name AS object_name, privileges
FROM (
    SELECT routine_schema, routine_name, string_agg(privilege_type, ',') AS privileges
    FROM information_schema.routine_privileges
    WHERE grantee='PUBLIC' AND routine_schema NOT LIKE 'pg_%' AND routine_schema != 'information_schema'
    GROUP BY routine_schema, routine_name
) f
UNION ALL
SELECT 'schema' AS object_type, schema_name AS schema_name, NULL AS object_name, privileges
FROM (
    SELECT schema_name, string_agg(privilege_type, ',') AS privileges
    FROM information_schema.schema_privileges
    WHERE grantee='PUBLIC' AND schema_name NOT LIKE 'pg_%' AND schema_name != 'information_schema'
    GROUP BY schema_name
) s
ORDER BY object_type, schema_name, object_name;

3. 用系统表查询更全面的权限 #

如果需要覆盖更多对象类型（比如序列、自定义类型），可以直接查询PostgreSQL底层系统表，这种方式信息更完整，但需要解析权限位：

模式的PUBLIC权限查询 #

SELECT n.nspname AS schema_name,
       array_to_string(array_agg(DISTINCT priv), ',') AS privileges
FROM pg_namespace n
JOIN (
    VALUES
        ('USAGE', nspacl ?~ '.*=U/.*'),
        ('CREATE', nspacl ?~ '.*=C/.*')
) p(priv, cond) ON cond
WHERE n.nspname NOT LIKE 'pg_%' AND n.nspname != 'information_schema'
GROUP BY n.nspname;

整合所有对象的PUBLIC权限 #

这个查询可涵盖表、视图、序列、函数、模式的PUBLIC权限：

-- 表/视图/序列的PUBLIC权限
SELECT 'table/view/sequence' AS object_type,
       n.nspname AS schema_name,
       c.relname AS object_name,
       array_to_string(array_agg(DISTINCT priv), ',') AS privileges
FROM pg_class c
JOIN pg_namespace n ON c.relnamespace = n.oid
JOIN (
    VALUES
        ('SELECT', relacl ?~ '.*=r/.*'),
        ('INSERT', relacl ?~ '.*=a/.*'),
        ('UPDATE', relacl ?~ '.*=w/.*'),
        ('DELETE', relacl ?~ '.*=d/.*'),
        ('TRUNCATE', relacl ?~ '.*=D/.*'),
        ('REFERENCES', relacl ?~ '.*=x/.*'),
        ('TRIGGER', relacl ?~ '.*=t/.*')
) p(priv, cond) ON cond
WHERE c.relkind IN ('r', 'v', 'S')
  AND n.nspname NOT LIKE 'pg_%' AND n.nspname != 'information_schema'
GROUP BY n.nspname, c.relname

UNION ALL

-- 函数的PUBLIC权限
SELECT 'function' AS object_type,
       n.nspname AS schema_name,
       p.proname || '(' || pg_get_function_identity_arguments(p.oid) || ')' AS object_name,
       'EXECUTE' AS privileges
FROM pg_proc p
JOIN pg_namespace n ON p.pronamespace = n.oid
WHERE proacl ?~ '.*=X/.*'
  AND n.nspname NOT LIKE 'pg_%' AND n.nspname != 'information_schema'

UNION ALL

-- 模式的PUBLIC权限
SELECT 'schema' AS object_type,
       n.nspname AS schema_name,
       NULL AS object_name,
       array_to_string(array_agg(DISTINCT priv), ',') AS privileges
FROM pg_namespace n
JOIN (
    VALUES
        ('USAGE', nspacl ?~ '.*=U/.*'),
        ('CREATE', nspacl ?~ '.*=C/.*')
) p(priv, cond) ON cond
WHERE n.nspname NOT LIKE 'pg_%' AND n.nspname != 'information_schema'
GROUP BY n.nspname

ORDER BY object_type, schema_name, object_name;

内容的提问来源于stack exchange，提问作者sasakiyori