postgresql - 如何查看某个角色的所有数据库和对象授权？

Question

我正在尝试在发布前审核应用程序的所有权限，并且我想确保没有任何角色拥有超过其需要的访问权限。我查看了不同的函数和系统表，但一切都非常零碎。

是否有好的查询或方法能够转储特定角色拥有的每项授权？

我正在使用 pg 9.5。

Answer 1

系统目录pg_class的relacl列包含所有权限信息。

模式 public 中的示例数据由 postgres 拥有并授予 newuser:

create table test(id int);
create view test_view as select * from test;

grant select, insert, update on test to newuser;
grant select on test_view to newuser;

查询pg_class:

select 
    relname, 
    relkind, 
    coalesce(nullif(s[1], ''), 'public') as grantee, 
    s[2] as privileges
from 
    pg_class c
    join pg_namespace n on n.oid = relnamespace
    join pg_roles r on r.oid = relowner,
    unnest(coalesce(relacl::text[], format('{%s=arwdDxt/%s}', rolname, rolname)::text[])) acl, 
    regexp_split_to_array(acl, '=|/') s
where nspname = 'public'
and relname like 'test%';

  relname  | relkind | grantee  | privileges 
-----------+---------+----------+------------
 test      | r       | postgres | arwdDxt      <- owner postgres has all privileges on the table
 test      | r       | newuser  | arw          <- newuser has append/read/write privileges
 test_view | v       | postgres | arwdDxt      <- owner postgres has all privileges on the view
 test_view | v       | newuser  | r            <- newuser has read privilege
(4 rows)

评论:

• coalesce(relacl::text[], format('{%s=arwdDxt/%s}', rolname, rolname)) - relacl 中的 Null 表示所有者拥有所有特权；
• unnest(...) acl - relacl 是一个aclitem 数组，一个数组元素代表一个用户；
• regexp_split_to_array(acl, '=|/') s - 将 aclitem 拆分为:s[1] 用户名，s[2] 权限；
• coalesce(nullif(s[1], ''), 'public') as grantee - 空用户名表示 public。

修改查询以选择单个用户或特定类型的关系或其他模式等...

阅读文档:

• The catalog pg_class ,
• GRANT带有 acl 系统的描述。

以类似的方式，您可以获得有关模式(nspacl in pg_namespace 列)和数据库(datacl in pg_database)授予的特权的信息