使用psql列出数据库权限的命令是什么？

Question

我正在进行数据库服务器迁移的过程中，但是我无法弄清楚如何使用psql命令行工具列出PostgreSQL上的数据库权限（或者服务器上的所有权限），尽管我已经在Google和这里搜索过了。

我使用的是Ubuntu 11.04操作系统，PostgreSQL版本为8.2.x。

- pedrosanta

Answer 1

postgres=> \l
                                  List of databases
   Name    |  Owner   | Encoding |   Collate   |    Ctype    |   Access privileges   
-----------+----------+----------+-------------+-------------+-----------------------
 postgres  | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | 
 template0 | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =c/postgres          +
           |          |          |             |             | postgres=CTc/postgres
 template1 | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =c/postgres          +
           |          |          |             |             | postgres=CTc/postgres

特权文档解释了如何解读输出结果。要查看当前数据库表的具体权限，请使用\z myTable命令。

- DrColossos

---

\z myTable非常适合确保您已成功授予某人访问权限，并避免在说“好了，现在可以工作了吗？它不行？？？”时显得像个白痴。 - ijoseph

第一列（标题为“姓名”）是否指的是数据库的名称？ - Flimm

Answer 2

你可以通过以下方式来做到：

SELECT grantee, privilege_type 
FROM information_schema.role_table_grants 
WHERE table_name='mytable'

这将会给你这样的输出结果：

mail=# select grantee, privilege_type from information_schema.role_table_grants where table_name='aliases';
   grantee    |  privilege_type
--------------+-----------------
 mailreader   |  INSERT
 mailreader   |  SELECT
 mailreader   |  UPDATE
 mailreader   |  DELETE
 mailreader   |  TRUNCATE
 mailreader   |  REFERENCES
 mailreader   |  TRIGGER
(7 rows)

mail=#

- Himanshu Chauhan

---

请注意，上述方法（至少在Postgres 9.4版本中）对于物化视图是不起作用的。 - Seldom 'Where's Monica' Needy

请注意，您可能需要以postgres用户身份运行这些命令，否则某些授权可能不会显示出来（例如，\dp 命令可以告诉您特定用户对表的访问权限，但此查询不会选择相关的授权）。 - Jānis Elmeris

Answer 3

也许你是指列出数据库中的用户及其权限 - 从问题中我无法确定。

postgres=> \du
                             List of roles
    Role name    |  Attributes  |                    Member of
-----------------+--------------+------------------------------------------------
 dba             | Create role  | {util_user,helpdesk_user,helpdesk_admin}
 helpdesk_admin  | Cannot login | {helpdesk_user}
 helpdesk_user   | Cannot login | {helpdesk_reader}
 jack            |              | {helpdesk_admin}
 postgres        | Superuser    | {}
                 : Create role
                 : Create DB

- Jack Douglas

---

不，我想要一种列出特定数据库权限的方法，但是我已经弄清楚了。数据库的所有者总是拥有所有权限，对吗？然后我们可以在数据库上为其他用户/组添加更多权限。这些权限可以使用\l命令列出。无论如何，非常感谢。 - pedrosanta

Answer 4

在执行\du命令时，Undercovers psql使用以下查询。

SELECT r.rolname, r.rolsuper, r.rolinherit,
  r.rolcreaterole, r.rolcreatedb, r.rolcanlogin,
  r.rolconnlimit, r.rolvaliduntil,
  ARRAY(SELECT b.rolname
        FROM pg_catalog.pg_auth_members m
        JOIN pg_catalog.pg_roles b ON (m.roleid = b.oid)
        WHERE m.member = r.oid) as memberof
, r.rolreplication
, r.rolbypassrls
FROM pg_catalog.pg_roles r
WHERE r.rolname !~ '^pg_'
ORDER BY 1;

- Denys

---

抱歉，无法正常工作。出现错误：列 r.rolbypassrls 不存在。在第9行出现问题：, r.rolbypassrls。 - ribamar

来源是什么？\l的查询是什么？ - milahu

Answer 5

使用 psql 元命令：

https://www.postgresql.org/docs/current/static/app-psql.html

使用Ctrl+F在页面上查找：

\ddp [ 模式 ] 列出默认访问权限设置。

\dp [ 模式 ] 列出表、视图和序列及其关联的访问权限。

\l[+] [ 模式 ] 列出服务器中的数据库并显示...访问权限。

如上所述，但在手册页面中未找到包含单词“权限”的内容：

\du+ 用于具有登录的角色，\dg+ 用于没有登录的角色-将在“成员”字段中列出授予该角色的角色。

我故意忽略了函数和语言权限，在 psql 手册中只作为基本操作（如果您使用这些权限，就不会来这里寻求建议）。同样适用于用户定义类型、域等-在元命令后加上“+”将显示适用的权限。

---

一种稍微极端的检查权限的方法是在事务中删除用户，例如：

s=# begin; drop user x;
BEGIN
Time: 0.124 ms
ERROR:  role "x" cannot be dropped because some objects depend on it
DETAIL:  privileges for type "SO dT"
privileges for sequence so
privileges for schema bin
privileges for table xx
privileges for table "csTest"
privileges for table tmp_x
privileges for table s1
privileges for table test
Time: 0.211 ms
s=# rollback;
ROLLBACK
Time: 0.150 ms

当列表长度超过N时（至少在9.3版本中），包含特权列表的警告会被折叠，但你仍然可以在日志中找到完整的内容...

- Vao Tsun

---

在v12版本中，\du+和\dg+似乎返回相同的结果。我有两个带有NOLOGIN选项的角色，这两个命令都列出了这两个角色。通过使用\?进行检查，它为这两个命令提供了相同的描述，所以我猜登录角色与用户之间的区别可能不再可用。 - EAmez

Answer 6

这是如何列出角色（受让方）的所有权限的方法：

SELECT grantor, grantee, table_schema, table_name, privilege_type
FROM information_schema.table_privileges
WHERE grantee = 'myuser'

将产生以下结果：

 grantor  | grantee  | table_schema | table_name | privilege_type
----------+----------+--------------+------------+----------------
 postgres | myuser   | myapp        | employees  | INSERT
 postgres | myuser   | myapp        | employees  | SELECT
 postgres | myuser   | myapp        | employees  | UPDATE
 postgres | myuser   | myapp        | employees  | DELETE

适用于PG 10

- Markofo

---

第12页也适用。 - RicHincapie

An error occurred:

marked(): input parameter is undefined or null
Please report this to https://github.com/markedjs/marked.

- undefined

An error occurred:

marked(): input parameter is undefined or null
Please report this to https://github.com/markedjs/marked.

- undefined

Answer 7

这是我对这个问题的多个答案组成的查询：

SELECT grantee AS user, CONCAT(table_schema, '.', table_name) AS table, 
    CASE 
        WHEN COUNT(privilege_type) = 7 THEN 'ALL'
        ELSE ARRAY_TO_STRING(ARRAY_AGG(privilege_type), ', ')
    END AS grants
FROM information_schema.role_table_grants
GROUP BY table_name, table_schema, grantee;

这将导致类似于这样的结果：

+------+--------------+----------------+
| user |    table     |     grants     |
+------+--------------+----------------+
| foo  | schema.table | ALL            |
| bar  | schema.table | SELECT, INSERT |
+------+--------------+----------------+

- stefannienhuis

---

在pg 9.4中出现了以下错误： ERROR: 找不到数据类型information_schema.character_data的数组类型。 - Adam Mulla

聚合做得很棒。 - samshers

Answer 8

一个（可能显而易见的）额外步骤是切换到postgres用户，否则你可能会遇到关于角色不存在的错误。

sudo su - postgres
psql -l

或者

psql
postgres=> \l

- Adam Shostack

Answer 9

你可以按照以下方式输入：

SELECT * FROM pg_roles;

你将会得到

rolname | rolsuper | rolinherit | rolcreaterole | rolcreatedb | rolcanlogin | rolreplication | rolconnlimit | rolpassword | rolvaliduntil | rolbypassrls | rolconfig | oid

也许在这里你会觉得有太多的角色，这时候，你可以使用WHERE来选择你想要看到的角色。

SELECT * FROM pg_roles WHERE rolname='your role name';

实际上，你也可以只输入

\du

你将看到你创建的所有角色，而不是默认角色

- Ricky Xu

---

这与之前发布的现有答案有何不同？ - mustaccio

作为对之前的补充，我认为之前的回答缺少了这部分内容或者不够简洁。 - Ricky Xu

Answer 10

列出数据库所有者

select d.datname, r.rolname
from pg_catalog.pg_database d, pg_catalog.pg_roles r
where d.datdba = r.oid;

文档：pg_database 和 pg_roles

相关：测试特定权限：

select has_database_privilege('dbname', 'CREATE');

文档：具有数据库权限（通过）

- milahu