[pg]指定 schema 下所有用户或角色对 sequence

在 PostgreSQL 中可以使用以下查询来获取指定 schema 下所有用户或角色对 sequence 的权限详细信息:

查询指定 schema 下所有用户对 sequence 的权限:

SELECT 
    pg_catalog.pg_get_userbyid(g.grantee) AS grantee,
    c.relname AS sequence_name,
    array_to_string(array_agg(privilege_type), ', ') AS privileges
FROM 
    pg_catalog.information_schema.role_table_grants g
    JOIN pg_catalog.pg_class c ON g.classid = c.oid
WHERE 
    g.table_schema = 'your_schema_name'
    AND c.relkind = 'S'
GROUP BY 
    g.grantee, c.relname;

查询指定 schema 下所有角色对 sequence 的权限:

SELECT 
    r.rolname AS grantee,
    c.relname AS sequence_name,
    array_to_string(array_agg(privilege_type), ', ') AS privileges
FROM 
    pg_catalog.information_schema.role_table_grants g
    JOIN pg_catalog.pg_class c ON g.classid = c.oid
    JOIN pg_catalog.pg_roles r ON g.grantee = r.oid
WHERE 
    g.table_schema = 'your_schema_name'
    AND c.relkind = 'S'
GROUP BY 
    r.rolname, c.relname;

这两个查询将返回指定 schema 下所有用户或角色对 sequence 的权限细节,包括用户或角色名称、sequence 名称以及权限类型。

请注意将 'your_schema_name' 替换为您想要查询的 schema 名称。