1. 数据库系统角色

1） 系统角色

select * from pg_roles where rolname='pguser';

2） 自定义角色

select t2.rolname as group_name,t3.rolname as member_name,t4.rolname as grantor_name,t1.admin_option

from pg_auth_members t1 

 left join pg_authid t2 on t2.oid = t1.roleid

 left join pg_authid t3 on t3.oid = t1.member

 left join pg_authid t4 on t4.oid = t1.grantor

where t3.rolname='pguser';

2. 数据库对象权限（需要加上PUBLIC角色的权限）

1) 根据用户名查询database权限

select a.datname,b.rolname,string_agg(a.pri_t,',') from (select datname,(aclexplode(COALESCE(datacl, acldefault('d'::"char",datdba)))).grantee as grantee,(aclexplode(COALESCE(datacl, acldefault('d'::"char", datdba)))).privilege_type as pri_t 

from pg_database where datname not like 'template%') a,pg_roles b 

where (a.grantee=b.oid or a.grantee=0) and b.rolname='pguser' group by a.datname,b.rolname;

2) 根据用户名查询schema权限

select a.nspname,b.rolname,string_agg(a.pri_t,',') 

from (select nspname,(aclexplode(COALESCE(nspacl, acldefault('n'::"char",nspowner)))).grantee as grantee,(aclexplode(COALESCE(nspacl, acldefault('n'::"char",nspowner)))).privilege_type as pri_t from pg_namespace where nspname not like 'pg%' and nspname <> 'information_schema') a,pg_authid b 

where (a.grantee=b.oid or a.grantee=0) and b.rolname='pguser' group by a.nspname,b.rolname;

3) 根据用户名查询对象权限

-> 表对象(包含表、视图、触发器)：

select table_name,table_schema,grantee,string_agg(privilege_type,',') from information_schema.table_privileges where grantee='pguser' group by table_name,table_schema,grantee;

select * from information_schema.table_privileges where grantee='pguser';

-> 表的列对象

select * from information_schema.column_privileges where grantee='pguser';

 

select table_name,table_schema,column_name,grantee,string_agg(privilege_type,',') 

from information_schema.column_privileges 

where grantee='pguser' group by table_name,table_schema,column_name,grantee order by table_name,table_schema,column_name,grantee ; 

-> usage权限(包含序列、SCHEMA、外部对象等)：

select * from information_schema.usage_privileges where grantee='pguser';

-> 查看用户自定义类型上授予的USAGE权限：

select * from information_schema.udt_privileges where grantee='pguser';

-> 存储过程函数对象：

select * from information_schema.routine_privileges where grantee='pguser';

4） 默认权限影响后期新增的对象

SELECT pg_catalog.pg_get_userbyid(d.defaclrole) AS "Granter",

  n.nspname AS "Schema",

  CASE d.defaclobjtype WHEN 'r' THEN 'table' WHEN 'S' THEN 'sequence' WHEN 'f' THEN 'function' WHEN 'T' THEN 'type' END AS "Type",

  pg_catalog.array_to_string(d.defaclacl, E', ') AS "Access privileges"

FROM pg_catalog.pg_default_acl d

LEFT JOIN pg_catalog.pg_namespace n ON n.oid = d.defaclnamespace

ORDER BY 1, 2, 3;

https://www.cndba.cn/dunluozhong/article/131585