在postgresql数据库中,有information_schema.table_privileges这个view,可以直接查看某个用户对于某张表有哪些权限。现在有个需求 ,希望对于database/schema也能这么方便的查询。
database_privileges
SELECT pd.datname AS database_name, COALESCE(NULLIF(role.name, ''::name), 'PUBLIC'::name) AS grantee,
"substring"((
CASE
WHEN "position"(split_part(split_part(','::text || array_to_string(pd.datacl, ','::text), (','::text || role.name::text) || '='::text, 2), '/'::text, 1), 'C'::text) > 0 THEN ',CREATE'::text
ELSE ''::text
END ||
CASE
WHEN "position"(split_part(split_part