如何查询PostgreSQL数据库中拥有提升权限的用户/角色？

1. 查询所有普通表的所有者 #

表所有者默认拥有该表的ALTER、DROP、CREATE（关联模式权限）等完整管理权限，可通过以下语句获取：

SELECT
    n.nspname AS schema_name,
    c.relname AS table_name,
    u.rolname AS owner_name
FROM
    pg_catalog.pg_class c
JOIN
    pg_catalog.pg_authid u ON c.relowner = u.oid
JOIN
    pg_catalog.pg_namespace n ON c.relnamespace = n.oid
WHERE
    c.relkind = 'r' -- 仅查询普通表，需视图可改为'v'
    AND n.nspname NOT IN ('pg_catalog', 'information_schema') -- 排除系统模式
ORDER BY
    schema_name, table_name;

2. 查询拥有表ALTER/DROP权限的角色 #

通过has_table_privilege函数直接校验角色对表的ALTER、DROP权限，包含继承自其他角色的权限：

-- 查询拥有ALTER权限的角色
SELECT
    n.nspname AS schema_name,
    c.relname AS table_name,
    u.rolname AS role_name,
    'ALTER' AS permission_type
FROM
    pg_catalog.pg_class c
JOIN
    pg_catalog.pg_namespace n ON c.relnamespace = n.oid
CROSS JOIN
    pg_catalog.pg_authid u
WHERE
    c.relkind = 'r'
    AND n.nspname NOT IN ('pg_catalog', 'information_schema')
    AND has_table_privilege(u.rolname, c.oid, 'ALTER')

UNION ALL

-- 查询拥有DROP权限的角色
SELECT
    n.nspname AS schema_name,
    c.relname AS table_name,
    u.rolname AS role_name,
    'DROP' AS permission_type
FROM
    pg_catalog.pg_class c
JOIN
    pg_catalog.pg_namespace n ON c.relnamespace = n.oid
CROSS JOIN
    pg_catalog.pg_authid u
WHERE
    c.relkind = 'r'
    AND n.nspname NOT IN ('pg_catalog', 'information_schema')
    AND has_table_privilege(u.rolname, c.oid, 'DROP')

ORDER BY
    schema_name, table_name, role_name, permission_type;

3. 查询拥有模式级CREATE权限的角色 #

创建表需要对应模式的CREATE权限，可通过以下语句获取：

SELECT
    n.nspname AS schema_name,
    u.rolname AS role_name,
    'CREATE TABLE' AS permission_type
FROM
    pg_catalog.pg_namespace n
JOIN
    pg_catalog.pg_authid u ON has_schema_privilege(u.rolname, n.nspname, 'CREATE')
WHERE
    n.nspname NOT IN ('pg_catalog', 'information_schema')
ORDER BY
    schema_name, role_name;

4. 查询超级用户角色 #

超级用户拥有所有数据库对象的权限，可单独排查：

SELECT rolname AS superuser_role FROM pg_catalog.pg_authid WHERE rolsuper = true;

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