用户和数据库权限知识总结

1、 查看用户user1和数据库的相关权限，要求显示数据库名、用户名、数据库的权限

select datname,(aclexplode(datacl)).grantee as grantee,(aclexplode(datacl)).privilege_type as pri_t from pg_database where datname not like 'template%';

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='shenglu' group by a.datname,b.rolname;

--aclexplode(datacl) 一共有四个属性，分别是grantor（授权人）、grantee（被授权人）、privilege_type（权限类型）、is_grantable（是否拥有该权限）