postgresql - 权限查询

权限查询

https://blog.csdn.net/opendba/article/details/5860002

• \z test

postgres=# \z test
                                       Access privileges
 Schema | Name | Type  | Access privileges | Column privileges |           Policies            
--------+------+-------+-------------------+-------------------+-------------------------------
 public | test | table | sa=arwdDxt/sa    +|                   | p4 (w):                      +
        |      |       | =arwdDxt/sa       |                   |   (u): (r = "current_user"())+
        |      |       |                   |                   |   to: r3                     +
        |      |       |                   |                   | p1 (r):                      +
        |      |       |                   |                   |   (u): (r = "current_user"())+
        |      |       |                   |                   |   to: r3
(1 row)

• \dp test

postgres=# \dp test
                                       Access privileges
 Schema | Name | Type  | Access privileges | Column privileges |           Policies            
--------+------+-------+-------------------+-------------------+-------------------------------
 public | test | table | sa=arwdDxt/sa    +|                   | p4 (w):                      +
        |      |       | =arwdDxt/sa       |                   |   (u): (r = "current_user"())+
        |      |       |                   |                   |   to: r3                     +
        |      |       |                   |                   | p1 (r):                      +
        |      |       |                   |                   |   (u): (r = "current_user"())+
        |      |       |                   |                   |   to: r3
(1 row)

• 查看用户有哪些权限：INFORMATION_SCHEMA.role_table_grants或者table_privileges

postgres=# select * from INFORMATION_SCHEMA.role_table_grants where grantee = 'lisi';
 grantor | grantee | table_catalog | table_schema |     table_name     | privilege_type | is_grantable | with_hierarchy 
---------+---------+---------------+--------------+--------------------+----------------+--------------+----------------
 sa      | lisi    | postgres      | public       | t_ssl              | SELECT         | NO           | YES
、、、
(20 rows)

postgres=# select * from information_schema.table_privileges where grantee='lisi';
 grantor | grantee | table_catalog | table_schema |     table_name     | privilege_type | is_grantable | with_hierarchy 
---------+---------+---------------+--------------+--------------------+----------------+--------------+----------------
 sa      | lisi    | postgres      | public       | t_ssl              | SELECT         | NO           | YES
、、、
(20 rows)

• 查看usage权限表

select * from information_schema.usage_privileges where grantee='user_name';

• 查看存储过程函数相关权限表

select * from information_schema.routine_privileges where grantee='user_name';