如何查询PostgreSQL用户、其角色及角色对应的权限？

我需要查询PostgreSQL中的用户、用户拥有的角色，以及这些角色具备的权限。目前已通过以下语句成功查询到用户及其关联角色：

SELECT pg_user.usename, pg_roles.rolname
FROM pg_user
JOIN pg_auth_members ON pg_user.usesysid = pg_auth_members.member
JOIN pg_roles ON pg_roles.oid = pg_auth_members.roleid;

但我仍无法查询到这些角色的权限。我尝试关联information_schema.role_table_grants表并查询privilege_type字段，但返回结果为0行，所用语句如下：

SELECT pg_user.usename, pg_roles.rolname, role_table_grants.privilege_type
FROM pg_user
JOIN pg_auth_members ON pg_user.usesysid = pg_auth_members.member
JOIN pg_roles ON pg_roles.oid = pg_auth_members.roleid
JOIN information_schema.role_table_grants ON role_table_grants.grantee = pg_roles.rolname;

此外，我发现两个查询返回的角色存在差异：执行上述获取用户与角色的语句能得到部分用户及角色，但执行SELECT grantee, privilege_type FROM information_schema.role_table_grants;时，返回的角色与前者完全不同。请问如何正确实现用户、角色及角色权限的查询？

1. 先明确权限的两类核心来源 #

PostgreSQL的权限分为两种核心类型，你之前的查询只覆盖了其中一种：

• 表/对象级权限：针对特定表、列、函数等对象的授权，存储在information_schema相关表中，但仅包含直接授权记录。
• 系统级权限（角色属性）：比如超级用户、创建数据库、登录权限等，直接存储在pg_roles表的字段中。

之前查询返回0行，大概率是因为关联的角色没有被直接授予表级权限，或者权限是通过继承其他角色间接获得的。

2. 查询用户、关联角色及系统级权限 #

直接从pg_roles提取角色的系统属性，结合用户-角色关联关系：

SELECT
  pu.usename AS "用户名",
  pr.rolname AS "关联角色",
  pr.rolsuper AS "超级用户权限",
  pr.rolcreaterole AS "可创建角色",
  pr.rolcreatedb AS "可创建数据库",
  pr.rolcanlogin AS "允许登录",
  pr.rolreplication AS "复制权限",
  pr.rolbypassrls AS "绕过行级安全"
FROM pg_user pu
JOIN pg_auth_members pam ON pu.usesysid = pam.member
JOIN pg_roles pr ON pr.oid = pam.roleid;

3. 查询用户、关联角色及全量表级权限（含继承） #

由于PostgreSQL角色默认继承父角色的权限，需要用递归查询覆盖所有层级的继承关系，再关联表权限：

WITH RECURSIVE role_hierarchy AS (
  -- 基础层：用户直接关联的角色
  SELECT
    pu.usename AS "用户名",
    pr.rolname AS "角色名",
    pr.oid AS role_oid
  FROM pg_user pu
  JOIN pg_auth_members pam ON pu.usesysid = pam.member
  JOIN pg_roles pr ON pr.oid = pam.roleid
  UNION ALL
  -- 递归层：角色继承的父角色
  SELECT
    rh."用户名",
    pr.rolname AS "角色名",
    pr.oid AS role_oid
  FROM role_hierarchy rh
  JOIN pg_auth_members pam ON rh.role_oid = pam.member
  JOIN pg_roles pr ON pr.oid = pam.roleid
)
SELECT DISTINCT
  rh."用户名",
  rh."角色名",
  rtg.table_catalog AS "数据库",
  rtg.table_schema AS "模式",
  rtg.table_name AS "表名",
  rtg.privilege_type AS "权限类型"
FROM role_hierarchy rh
LEFT JOIN information_schema.role_table_grants rtg 
  ON rtg.grantee = rh."角色名"
ORDER BY rh."用户名", rh."角色名", rtg.table_name;

用LEFT JOIN替代INNER JOIN，可以保留没有表级权限的角色记录，避免丢失数据。

4. 解释两次查询角色差异的原因 #

• pg_user仅包含允许登录的角色（即rolcanlogin = true的角色），也就是通常意义上的"用户"。
• information_schema.role_table_grants中的grantee可以是任何角色，包括不可登录的组角色、系统角色等，这就是两者返回角色不同的核心原因。

如果要查看所有角色（包括不可登录的组角色）的关联关系，可以修改基础查询：

SELECT
  pr_member.rolname AS "用户名/角色",
  pr_role.rolname AS "关联角色"
FROM pg_roles pr_member
JOIN pg_auth_members pam ON pr_member.oid = pam.member
JOIN pg_roles pr_role ON pr_role.oid = pam.roleid
WHERE pr_member.rolcanlogin = true; -- 过滤出可登录的用户，去掉则显示所有角色

内容的提问来源于stack exchange，提问作者Diogo dos Santos