PostgreSQL查询用户数据库权限的脚本逻辑问题排查

你的脚本存在三个核心问题，导致输出结果与实际不符：

1. 权限判断逻辑错误 #

has_database_privilege函数传入多个权限（如'connect, create, temp'）时，作用是检查用户是否同时拥有所有指定权限，而非拥有其中任意一项。你的case语句按组合权限顺序判断，不仅无法准确收集用户实际拥有的权限，还可能因public角色的默认权限（connect、temp）导致误判。

2. 循环变量引用错误 #

循环内的RAISE NOTICE语句使用了db.datname，但原查询中的db表别名在循环块中已失效，必须改用循环变量db_r.datname，否则会触发语法错误。

3. 未区分直接权限与继承权限（可选） #

默认情况下，has_database_privilege会包含用户从public等角色继承的权限。如果只想查看用户直接拥有的权限，需添加NO INHERIT选项。

修正后的脚本 #

以下脚本通过逐个检查单权限并拼接结果，能准确输出用户实际拥有的数据库权限：

DO
$$
DECLARE
    db_r record;
BEGIN
    FOR db_r IN
        SELECT 
            db.datname,
            rl.rolname,
            STRING_AGG(priv, ', ') AS privileges
        FROM pg_roles rl
        CROSS JOIN pg_database db
        -- 逐个检查需要验证的权限
        CROSS JOIN UNNEST(ARRAY['connect', 'create', 'temp']) AS priv
        WHERE 
            rl.rolcanlogin
            AND db.datallowconn
            AND db.datname NOT IN ('postgres', 'template0', 'template1')
            -- 检查当前用户是否拥有该权限
            AND has_database_privilege(rl.rolname, db.datname, priv)
            -- 如果只想看直接权限，替换上面的条件为：
            -- AND has_database_privilege(rl.rolname, db.datname, priv, 'NO INHERIT')
        GROUP BY db.datname, rl.rolname
        ORDER BY db.datname, rl.rolname
    LOOP
        RAISE NOTICE '% has % privilege(s) on database %;',
            db_r.rolname, db_r.privileges, db_r.datname;
    END LOOP;
END
$$;

内容的提问来源于stack exchange，提问作者Diogo dos Santos