postgres查看用户的权限

最新推荐文章于 2025-09-10 17:24:45 发布

原创最新推荐文章于 2025-09-10 17:24:45 发布 · 1k 阅读

2 ·

CC 4.0 BY-SA版权

文章标签：

#postgresql #数据库 #linux

本文介绍了如何在PostgreSQL数据库中查询test用户对不同级别的权限，包括table、schema和database的详细权限列表。

查询test用户table权限

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

查询test用户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='test' group by a.nspname,b.rolname;

查询test用户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='test' group by a.datname,b.rolname;