如何查看角色的所有数据库和对象授予？ [英] How can I review all database and object grants for a role?

问题描述

我试图在发布之前审核应用程序的所有权限，并且我想确保没有角色具有超出其所需权限的访问权限。我看过不同的功能和系统表，但是一切都是零碎的。

I am trying to audit all of the permissions for an application before release and I want to ensure no role has more access than it needs. I have looked at the different functions and system tables, but everything is very piecemeal.

是否有一个很好的查询或方法可以转储每个授予的特定角色

Is there a good query or method to be able to dump out every grant a particular role has?

我正在使用pg 9.5。

I am using pg 9.5.

推荐答案

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

The column relacl of the system catalog pg_class contains all informations on privileges.

postgres 拥有的架构 public 中的示例数据，并授予 newuser ：

Example data in schema public owned by postgres with grants to 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'）作为受让人-空用户名表示 public 。

• coalesce(relacl::text[], format('{%s=arwdDxt/%s}', rolname, rolname)) - Null in relacl means that the owner has all privileges;
• unnest(...) acl - relacl is an array of aclitem, one array element for a user;
• regexp_split_to_array(acl, '=|/') s - split aclitem into: s[1] username, s[2] privileges;
• coalesce(nullif(s[1], ''), 'public') as grantee - empty username means public.

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

Modify the query to select individual user or specific kind of relation or another schemas, etc...

阅读文档：

• 目录 pg_class ，


• GRANT 以及acl系统的说明。

• The catalog pg_class,
• GRANT with the description of acl system.

您可以通过类似的方式获得有关在模式上授予的特权的信息（列 pg_namespace 中的nspacl ）和数据库（ pg_database 中的 datacl ）

In a similar way you can get information about privileges granted on schemas (the column nspacl in pg_namespace) and databases (datacl in pg_database)

这篇关于如何查看角色的所有数据库和对象授予？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！