如何检查postgres数据库中所有角色/用户/组角色拥有哪些权限？

Question

我有一个Postgres数据库。 我想获取用户访问权限的清单并查看他们被分配了哪些权限。

我尝试查找查询语句，并查看psql命令行帮助（\nu等），但没有找到任何有用的信息。

是否有人知道这方面的内容可以帮助我。

谢谢。

- Jayesh

---

没有现成的功能可以实现这个，因为权限可能存在于各种对象、默认权限，甚至不同的数据库中。 - Laurenz Albe

@LaurenzAlbe：你有其他解决方案吗？ - Jayesh

Answer 1

以下是对之前内容的补充。

按照以下步骤检查用户权限：

SELECT * FROM pg_user;

同样的方法，检查角色：

SELECT * FROM pg_roles;

- Ricky Xu

Answer 2

有一些基本命令，例如 \du 和 \l 可以提供一般信息。

如果想要获取详细信息，您可以使用以下函数。

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中获取的用户名/角色调用相同的函数。

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)

- Amjad Shahzad

---

如果我想在用户列表中列出模式，查询是什么？谢谢Amjad。 - Jayesh

\dn+ 将列出数据库中的所有模式以及访问权限。 - Amjad Shahzad

我需要一个用户列表，其中包含授予他们模式的权限。查询语句是什么？ - Jayesh

你对此有任何想法吗？这是我的第二条评论。 - Jayesh

尝试这个： 选择 schema_name，schema_owner 从 information_schema.schemata;同时，在 information_schema 中，有多个表包含元数据，您可以通过在选择查询中使用一些 Join 来使用它们。 - Amjad Shahzad