PostgreSQL的权限查询

 

查看哪些用户对表t1有哪些权限：

part00=# /z t1

                           Access privileges

 Schema | Name | Type  |  Access privileges  | Column access privileges 

--------+------+-------+---------------------+--------------------------

 public | t1   | table | osdba=arwdDxt/osdba+| 

        |      |       | scott=D/osdba       | 

part00=# /dp t1

                           Access privileges

 Schema | Name | Type  |  Access privileges  | Column access privileges 

--------+------+-------+---------------------+--------------------------

 public | t1   | table | osdba=arwdDxt/osdba+| 

        |      |       | scott=D/osdba       | 

(1 row)

可以看出有两个用户osdba和scott，osdba用arwdDxt权限，scott用户有D权限。

字母代表的权限的意思如下：

a: insert

r: select

w: update

d: delete

x: references

t: trigger

D: truncate

也可以查询系统视图pg_class

part00=# select relname,relacl from pg_class where relname='t1';

 relname |               relacl                

---------+-------------------------------------

 t1      | {osdba=arwdDxt/osdba,scott=D/osdba}

(1 row)

如果想查询用户scott有哪些权限，可以查询系统表information_schema.role_table_grants:

part00=# select * from INFORMATION_SCHEMA.role_table_grants where grantee='scott';

 grantor | grantee | table_catalog | table_schema | table_name | privilege_type | is_grantable | with_hierarchy 

---------+---------+---------------+--------------+------------+----------------+--------------+----------------

 osdba   | scott   | part00        | public       | t4         | SELECT         | NO           | NO

 osdba   | scott   | part00        | public       | t1         | TRUNCATE       | NO           | NO