PostgreSQL的权限查询

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

sns_log=> \z sns_log_member_b_aciton
                                        Access privileges
 Schema  |           Name            | Type  |    Access privileges    | Column access privileges 
---------+---------------------------+-------+-------------------------+--------------------------
 sns_log | sns_log_member_b_aciton   | table | dwetl=r/sns_log        +| 
         |                           |       | sns_select=r/sns_log   +| 
         |                           |       | sns_log=arwdDxt/sns_log | 
(1 row)

sns_log=> \dp sns_log_member_b_aciton
                                        Access privileges
 Schema  |           Name            | Type  |    Access privileges    | Column access privileges 
---------+---------------------------+-------+-------------------------+--------------------------
 sns_log | sns_log_member_b_aciton   | table | dwetl=r/sns_log        +| 
         |                           |       | sns_select=r/sns_log   +| 
         |                           |       | sns_log=arwdDxt/sns_log | 
(1 row)

可以看出有三个用户sns_log、sns_select和dwetl，sns_log用arwdDxt权限，sns_select和dwetl用户有r权限。权限后的sns_log名称是schema名称。

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

            r -- SELECT ("read")
            w -- UPDATE ("write")
            a -- INSERT ("append")
            d -- DELETE
            D -- TRUNCATE
            x -- REFERENCES
            t -- TRIGGER
            X -- EXECUTE
            U -- USAGE
            C -- CREATE
            c -- CONNECT
            T -- TEMPORARY
      arwdDxt -- ALL PRIVILEGES (for tables, varies for other objects)
            * -- grant option for preceding privilege

也可以查询系统视图pg_class;

sns_log=> select relname,relacl from pg_class where relname='sns_log_member_b_aciton';
          relname          |                             relacl                             
---------------------------+----------------------------------------------------------------
 sns_log_member_b_aciton   | {dwetl=r/sns_log,sns_select=r/sns_log,sns_log=arwdDxt/sns_log}
(1 row)

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

sns_log=> select * from INFORMATION_SCHEMA.role_table_grants where grantee='dwetl';
 grantor | grantee | table_catalog | table_schema |              table_name               | privilege_type | is_grantable | with_hierarchy 
---------+---------+---------------+--------------+---------------------------------------+----------------+--------------+----------------
 sns_log | dwetl   | sns_log       | sns_log      | mkt_sns_gation_log                | SELECT         | NO           | YES

 

参考：

http://www.postgresql.org/docs/9.2/static/sql-grant.html

http://blog.chinaunix.net/uid-15145533-id-2775889.html