主页 / dba / 问题 / 4286
Accepted
pedrosanta
Asked: 2011-08-04 11:01:21 +0800 CST

使用 psql 列出数据库权限

  • 772

我正在进行数据库服务器迁移，但我不知道（在此处进行谷歌搜索和搜索之后）如何使用psql命令行工具列出 PostgreSQL 上的数据库权限（或服务器上的所有权限）？

我在 Ubuntu 11.04 上，我的 PostgreSQL 版本是 8.2.x。

postgresql ubuntu

10 个回答

  1. Best Answer
    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

    Privileges 上的文档解释了如何解释输出。对于当前数据库表的特定权限，请使用\z myTable.

    • 200

  2. 也许您的意思是列出用户及其对数据库的权限-我无法从问题中完全看出：

    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
    • 176

  3. 您可以通过以下方式做到这一点：

    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=#
    • 162

  4. 使用psql元命令：

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

    使用 Ctrl+F 浏览页面给出：

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

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

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

    上面也提到过，但在手册页上找不到“特权”一词：

    \du+对于具有登录名\dg+的角色和没有登录的角色 - 将有一个字段"Member of"，您可以在其中找到授予角色的角色。

    我故意在这里跳过功能和语言特权，在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 中），带有权限列表的警告被折叠，但您仍然可以在日志中找到它...

    • 36

  5. Undercovers psql 在您发出\du命令时使用波纹管查询。

    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;
    • 31

  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 中工作

    • 22

  7. 一个（可能很明显）附加步骤是成为 postgres 用户，否则您可能会收到有关角色不存在的错误。

    sudo su - postgres
psql -l

    或者

    psql
postgres=> \l
    • 13

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

    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 |
+------+--------------+----------------+
    • 12

  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

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

    • 2

  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');

    文档：has_database_privilege（通过）

    • 2

相关问题