PostgreSQL权限控制

针对PostgreSQL权限的一些常见查询。

查看哪些用户对表t01有具体哪些权限。

SELECT grantee,table_schema,table_name,string_agg( privilege_type,', ' ) as privilege_type FROM information_schema.role_table_grants 
WHERE table_name='t01' group by grantee,table_schema,table_name;

devops_user 拥有哪些表的权限。

SELECT table_name, table_schema, grantee, string_agg(privilege_type, ',') FROM information_schema.table_privileges WHERE grantee='devops_user' GROUP BY table_name, table_schema, grantee;

查看某个角色对库拥有哪些权限。

SELECT a.datname, b.rolname, string_agg(DISTINCT a.pri_t, ',') 
FROM (
  SELECT datname, (aclexplode(COALESCE(datacl, acldefault('d'::"char", datdba)))).grantee as grantee, 
         (aclexplode(COALESCE(datacl, acldefault('d'::"char", datdba)))).privilege_type as pri_t
  FROM pg_database
  WHERE datname NOT LIKE 'template%'
) a, pg_roles b
WHERE (a.grantee=b.oid OR a.grantee=0) 
  AND b.rolname='devops_user'
GROUP BY a.datname, b.rolname;