如何检查所有角色/用户/ group_role在postgres数据库中具有哪些特权？ [英] How to check all roles/user/group_role have what privileges in postgres database?

问题描述

我有postgres数据库。
我想要被分配了具有访问权限的用户列表。

I have postgres database. I want the list of users with access privileges they are being assigned.

我试图查找查询，并查看了psql命令行帮助。 （\nu和所有），但我还没有找到任何有用的信息。

I tried to find query and also looked in to psql command line help. (\nu and all) but I haven't found any usefull information.

有人知道这可以帮助我吗？

Is anyone knows about that can help me out.

谢谢。

推荐答案

几乎没有像\du和\l这样的基本命令可以提供一般信息。

There are few basic command like \du and \l that will provide the general information.

要获取详细信息，可以使用以下功能。

For getting the detailed information you may use the below function.

CREATE OR REPLACE FUNCTION database_privs(text) RETURNS table(username text,dbname name,privileges  text[])
AS
$$
SELECT $1, datname, array(select privs from unnest(ARRAY[
( CASE WHEN has_database_privilege($1,c.oid,'CONNECT') THEN 'CONNECT' ELSE NULL END),
(CASE WHEN has_database_privilege($1,c.oid,'CREATE') THEN 'CREATE' ELSE NULL END),
(CASE WHEN has_database_privilege($1,c.oid,'TEMPORARY') THEN 'TEMPORARY' ELSE NULL END),
(CASE WHEN has_database_privilege($1,c.oid,'TEMP') THEN 'CONNECT' ELSE NULL END)])foo(privs) WHERE privs IS NOT NULL) FROM pg_database c WHERE 
has_database_privilege($1,c.oid,'CONNECT,CREATE,TEMPORARY,TEMP') AND datname not in ('template0');
$$ language sql;

，然后通过提供从\du获取的用户名/角色来调用相同的功能

and then call the same function by providing the username/role that you get from \du

postgres=# \du
                                   List of roles
 Role name |                         Attributes                         | Member of 
-----------+------------------------------------------------------------+-----------
 postgres  | Superuser, Create role, Create DB, Replication, Bypass RLS | {}
 test      |                                                            | {}
 test2     |                                                            | {}
 test3     |                                                            | {}

postgres=# select * from database_privs('test');
 username |  dbname   |         privileges          
----------+-----------+-----------------------------
 test     | postgres  | {CONNECT,TEMPORARY,CONNECT}
 test     | template1 | {CONNECT}
 test     | test      | {CONNECT,TEMPORARY,CONNECT}
(3 rows)

披露：我为< a href = https://www.enterprisedb.com rel = nofollow noreferrer> EnterpriseDB（EDB）

这篇关于如何检查所有角色/用户/ group_role在postgres数据库中具有哪些特权？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！