Redshift中information_schema.table_privileges不支持TRUNCATE权限的原因及替代方案

需要查询用户对表拥有的SELECT、INSERT、UPDATE、DELETE及TRUNCATE权限，但information_schema.table_privileges视图无法显示TRUNCATE权限。尝试在makeaclitem()函数中加入TRUNCATE类型时出现错误，求替代方法。

原查询代码：

SELECT u_grantor.usename::information_schema.sql_identifier AS grantor, 
        grantee.name::information_schema.sql_identifier AS grantee, 
        current_database()::information_schema.sql_identifier AS table_catalog, 
        nc.nspname::information_schema.sql_identifier AS table_schema, 
        c.relname::information_schema.sql_identifier AS table_name, 
        pr."type"::information_schema.character_data AS privilege_type 
FROM pg_class c, pg_namespace nc, pg_user u_grantor,
(SELECT pg_user.usesysid, 0, pg_user.usename FROM pg_user ) grantee(usesysid, grosysid, name), 
(((((( SELECT 'SELECT'::character varying
        UNION ALL 
        SELECT 'DELETE'::character varying)
        UNION ALL 
        SELECT 'INSERT'::character varying)
        UNION ALL 
        SELECT 'UPDATE'::character varying)
        UNION ALL 
        SELECT 'REFERENCES'::character varying)
        UNION ALL 
        SELECT 'TRUNCATE'::character varying)
        UNION ALL 
        SELECT 'TRIGGER'::character varying) pr("type")
        WHERE c.relnamespace = nc.oid 
AND c.relkind = 'r'::"char"
AND aclcontains(c.relacl, makeaclitem(grantee.usesysid, grantee.grosysid, u_grantor.usesysid, pr."type"::text, false))

错误信息：

SQL Error [22023]: ERROR: unrecognized privilege type: "TRUNCATE"

PostgreSQL中makeaclitem()函数不接受'TRUNCATE'作为权限类型参数，因为TRUNCATE权限的内部标识是缩写代码而非完整字符串。以下是两种可行的解决方法：

方法1：拆分查询，用权限缩写单独处理TRUNCATE #

将原查询拆分为常规权限查询和TRUNCATE权限查询两部分，用UNION ALL合并结果。TRUNCATE权限对应的内部缩写是'T'，可以直接传入makeaclitem函数：

-- 查询常规表权限（SELECT/INSERT/UPDATE/DELETE等）
SELECT u_grantor.usename::information_schema.sql_identifier AS grantor, 
        grantee.name::information_schema.sql_identifier AS grantee, 
        current_database()::information_schema.sql_identifier AS table_catalog, 
        nc.nspname::information_schema.sql_identifier AS table_schema, 
        c.relname::information_schema.sql_identifier AS table_name, 
        pr."type"::information_schema.character_data AS privilege_type 
FROM pg_class c, pg_namespace nc, pg_user u_grantor,
(SELECT pg_user.usesysid, 0, pg_user.usename FROM pg_user ) grantee(usesysid, grosysid, name), 
((((( SELECT 'SELECT'::character varying
        UNION ALL 
        SELECT 'DELETE'::character varying)
        UNION ALL 
        SELECT 'INSERT'::character varying)
        UNION ALL 
        SELECT 'UPDATE'::character varying)
        UNION ALL 
        SELECT 'REFERENCES'::character varying)
        UNION ALL 
        SELECT 'TRIGGER'::character varying) pr("type")
WHERE c.relnamespace = nc.oid 
AND c.relkind = 'r'::"char"
AND aclcontains(c.relacl, makeaclitem(grantee.usesysid, grantee.grosysid, u_grantor.usesysid, pr."type"::text, false))

UNION ALL

-- 单独查询TRUNCATE权限
SELECT 
    u_grantor.usename::information_schema.sql_identifier AS grantor,
    grantee.name::information_schema.sql_identifier AS grantee,
    current_database()::information_schema.sql_identifier AS table_catalog,
    nc.nspname::information_schema.sql_identifier AS table_schema,
    c.relname::information_schema.sql_identifier AS table_name,
    'TRUNCATE'::information_schema.character_data AS privilege_type
FROM pg_class c
JOIN pg_namespace nc ON c.relnamespace = nc.oid
JOIN pg_user grantee ON TRUE
JOIN pg_user u_grantor ON TRUE
WHERE c.relkind = 'r'::"char"
AND aclcontains(c.relacl, makeaclitem(grantee.usesysid, 0, u_grantor.usesysid, 'T', false));

方法2：直接解析relacl字段提取权限 #

pg_class.relacl字段存储了表的所有权限项，每个项是aclitem类型，可以通过拆解其文本格式来提取TRUNCATE及其他权限：

SELECT
    (split_part(aclitem::text, '=', 1))::information_schema.sql_identifier AS grantee,
    (split_part(aclitem::text, '=', 2))::information_schema.sql_identifier AS grantor,
    current_database()::information_schema.sql_identifier AS table_catalog,
    nc.nspname::information_schema.sql_identifier AS table_schema,
    c.relname::information_schema.sql_identifier AS table_name,
    CASE
        WHEN position('r' in split_part(aclitem::text, '/', 1)) > 0 THEN 'SELECT'
        WHEN position('w' in split_part(aclitem::text, '/', 1)) > 0 THEN 'UPDATE'
        WHEN position('a' in split_part(aclitem::text, '/', 1)) > 0 THEN 'INSERT'
        WHEN position('d' in split_part(aclitem::text, '/', 1)) > 0 THEN 'DELETE'
        WHEN position('T' in split_part(aclitem::text, '/', 1)) > 0 THEN 'TRUNCATE'
        WHEN position('x' in split_part(aclitem::text, '/', 1)) > 0 THEN 'REFERENCES'
        WHEN position('t' in split_part(aclitem::text, '/', 1)) > 0 THEN 'TRIGGER'
    END::information_schema.character_data AS privilege_type
FROM pg_class c
JOIN pg_namespace nc ON c.relnamespace = nc.oid
LEFT JOIN unnest(c.relacl) AS aclitem ON TRUE
WHERE c.relkind = 'r'::"char"
AND aclitem IS NOT NULL;

这种方法无需依赖makeaclitem函数，直接解析原始权限数据，兼容性更强。

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