PostgreSQL查询对象权限

---

一、PostgreSQL查询表权限

1.1、pg_class的relacl

根据pg_class的relacl字段来查权限：

SELECT owner
      ,relname as object_name,grantor,grantee
      ,CASE
         WHEN (t.privilege = 'r'::text) THEN 'SELECT'::text
         WHEN (t.privilege = 'a'::text) THEN 'INSERT'::text
         WHEN (t.privilege = 'd'::text) THEN 'DELETE'::text
         WHEN (t.privilege = 'w'::text) THEN 'UPDATE'::text
         WHEN (t.privilege = 'D'::text) THEN 'TRUNCATE'::text
         WHEN (t.privilege = 'X'::text) THEN 'EXECUTE'::text
         WHEN (t.privilege = 'x'::text) THEN 'REFERENCES'::text
         WHEN (t.privilege = 'U'::text) THEN 'USAGE'::text
         WHEN (t.privilege = 't'::text) THEN 'TRIGGER'::text
         WHEN (t.privilege = 'C'::text) THEN 'CREATE'::text
         WHEN (t.privilege = 'c'::text) THEN 'CONNECT'::text
         WHEN (t.privilege = 'T'::text) THEN 'TEMPORARY'::text
         ELSE t.privilege
        END AS privilege
  FROM (select CASE 
                 WHEN grantee IS NOT NULL AND grantee!=''
                   THEN has_table_privilege(grantee, oid, 'TRUNCATE')
                 ELSE NULL
               END AS TRUNCATE_privs
              ,regexp_split_to_table(privs, ''::text) AS privilege
              ,t.* 
          from (select (regexp_split_to_array(unnest(relacl)::text,'=|/'))[1] as grantee
                      ,(regexp_split_to_array(unnest(relacl)::text,'=|/'))[2] as privs
                      ,(regexp_split_to_array(unnest(relacl)::text,'=|/'))[3] as grantor
                      ,relname
                      ,relacl
                      ,(select usename from pg_user where usesysid = c.relowner) as owner
                      ,(SELECT nspname FROM pg_namespace n WHERE n.oid = c.relnamespace) as nspname
                      ,c.oid
                  FROM pg_class c
                 WHERE 1=1 
                   AND relkind in ('r','p','P')
                 order by owner,relname
        ) t
        WHERE GRANTOR <> GRANTEE
          AND GRANTEE <> ''
) t;

ACL 权限详解

pg_class的relacl中的权限，详细说明如下：

1.2、information_schema.table_privileges的privilege_type

根据系统视图information_schema.table_privileges查询某个schema下的某个表的权限：

select * 
  from information_schema.table_privileges
 where table_schema = 'tzq'
   and table_name = 'tzq_bas_bank_account_pay_ti'
   and grantor <> grantee;

二、PostgreSQL查询某个角色在数据库中的权限

PostgreSQL查询某个角色在数据库中的权限，查询SQL如下：

SELECT rolname
      ,rolcreatedb
      ,rolcanlogin
      ,rolvaliduntil
  FROM pg_roles
 WHERE rolname = 'tzq' -- role_name
 ;

三、PostgreSQL查询某个角色在某个表中的权限

其中，‘table_name’ 是需要查询的表名，‘schema_name’ 是表所在的模式名，‘role_name’ 是需要查询的角色名。查询结果将返回该角色在该表中的读取权限。

SELECT relname
      ,array_to_string(relacl
                      ,E'\n') AS permissions
  FROM pg_class
 WHERE relname = 'tzq_bas_bank_account_pay_ti' -- table_name
   AND relkind in ( 'r','p')
   AND relnamespace IN
       (SELECT OID FROM pg_namespace WHERE nspname = 'tzq') -- schema_name
   AND has_table_privilege('tzq' -- role_name
                          ,OID
                          ,'SELECT');

四、PostgreSQL查询某个角色在某个架构（Schema）中的权限

其中，‘schema_name’ 是需要查询的架构名，‘role_name’ 是需要查询的角色名。查询结果将返回该角色在该架构中的创建权限。

SELECT nspname
      ,array_to_string(nspacl
                      ,E'\n') AS permissions
  FROM pg_namespace
 WHERE nspname = 'tzq' -- schema_name
   AND has_schema_privilege('tzq' -- role_name
                           ,OID
                           ,'CREATE');