PHP小码农

Appearance

组角色与会话角色 ​

前一页解决了「成员如何使用组的权限」，这一页解决「组应该怎么设计、会话身份如何流动」。组角色（group role）是 PostgreSQL 权限治理的核心模式：权限挂在组上，人挂在组下，授权矩阵从「人 × 对象」缩成「组 × 对象」。而会话角色（session role）的概念——session_user 与 current_user 的分野——则是理解登录、切换、审计的钥匙。

这一页还是一套可以直接落地的工程模板：三层基本组（只读 / 读写 / 管理）、成员的增删与嵌套、递归审计查询，以及「应用连接专用角色、人用成员角色」的分离原则。它与连接池、备份恢复等场景都有衔接。

前置知识

建议先阅读：角色继承（INHERIT / NOINHERIT）

基础概念 ​

组角色：一个「不登录的权限容器」 ​

组角色没有任何特殊类型标记，它就是一个普通的角色，只是不带 LOGIN 属性——不能出现在连接串里，只能被别人 GRANT 成员关系。除此之外它与普通角色别无二致：可以拥有对象、接受对象授权、成为别的组的成员、携带角色级参数。

这个设计带来一个重要推论：组角色可以拥有对象，而且这往往是最佳实践。让 app_owner（NOLOGIN 组）拥有业务表，应用与人的角色都只是成员——人员流动（离职、换岗）永远不会导致「对象无主」，SET ROLE app_owner 后创建的对象也天然归组所有。

会话角色：session_user 与 current_user ​

一次连接里存在两个「身份」，权限体系对它们的使用完全不同：

标识含义何时改变
session_user最初登录（认证）的角色，连接建立后固定仅超级用户可用 SET SESSION AUTHORIZATION 替换
current_user当前执行语句的有效角色，权限检查依据SET ROLE、SECURITY DEFINER 函数执行期
current_rolecurrent_user 的同义词（SQL 标准兼容保留）同 current_user
system_user认证方法与外部身份（auth_method:identity），如 scram-sha-256:app_user连接认证后固定

用官方文档的类比：session_user 是「真实用户」（real user），current_user 是「有效用户」（effective user）。权限审计时两者都要看——current_user 告诉你「这条语句以谁的身份跑」，session_user 告诉你「这个连接最初是谁」。

三层基本组：只读 / 读写 / 管理 ​

绝大多数业务库的组设计可以收敛为三层，再按需派生：

text
                ┌────────────┐
                │ app_owner  │   NOLOGIN：拥有全部对象（表、序列、模式）
                └─────┬──────┘
        ┌─────────────┼─────────────┐
┌───────┴──────┐ ┌────┴───────┐ ┌────┴──────┐
│ app_readonly │ │app_readwrite│ │ app_admin │
│   SELECT     │ │ +I/U/D/序列 │ │ +DDL 所需  │
└───────┬──────┘ └────┬───────┘ └────┬──────┘
        │             │              │
     人/报表        应用连接       迁移工具/DBA

原则只有一条：权限挂在 NOLOGIN 组上，LOGIN 角色只承载「谁能连、怎么连」。组的数量要克制——三层之外每多一个组，都要有明确的职责命名理由（如 app_batch_write 专供批处理写入）。

语法与用法 ​

建组与挂成员 ​

sql
-- 1. 建组（NOLOGIN 是默认值，显式写出更清晰）
CREATE ROLE app_readonly  NOLOGIN;
CREATE ROLE app_readwrite NOLOGIN;
CREATE ROLE app_admin     NOLOGIN;

-- 2. 授权给组（对象权限部分，详见 GRANT 页）
GRANT SELECT ON ALL TABLES IN SCHEMA app TO app_readonly;
GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA app TO app_readwrite;
GRANT USAGE, SELECT ON ALL SEQUENCES IN SCHEMA app TO app_readwrite;

-- 3. 人挂进组（成员关系）
CREATE ROLE alice LOGIN PASSWORD '...';
GRANT app_readonly  TO alice;                          -- 全默认选项
GRANT app_readwrite TO alice WITH INHERIT TRUE, SET TRUE;  -- 显式写选项（推荐）

-- 4. 摘除成员
REVOKE app_readonly FROM alice;

创建时直接挂组 ​

sql
-- 新角色创建后立即成为 app_readwrite 的成员
CREATE ROLE bob LOGIN PASSWORD '...' IN ROLE app_readwrite;

-- IN GROUP 是同一件事的旧写法（PG18 文档标记为弃用别名）
CREATE ROLE bob LOGIN IN GROUP app_readwrite;

-- 反向写法：把已有角色挂为「新角色（作为组）」的成员
CREATE ROLE grp_new NOLOGIN ROLE alice, bob;
-- 带 ADMIN 选项的版本
CREATE ROLE grp_new2 NOLOGIN ADMIN alice;

成员关系的常规增删首选 GRANT / REVOKE；CREATE ROLE 的这些子句适合一次性初始化脚本。

会话内切换与还原 ​

sql
SET ROLE app_readwrite;   -- current_user 变为 app_readwrite（需 SET 选项链为真）
RESET ROLE;               -- 回到连接时的有效角色
SET ROLE NONE;            -- 等价写法：回到 session_user

组级与成员级参数 ​

sql
-- 挂在 LOGIN 角色上的参数：登录时生效
ALTER ROLE alice SET search_path = app, public;
ALTER ROLE alice IN DATABASE appdb SET statement_timeout = '30s';
ALTER ROLE alice SET default_transaction_read_only = on;

注意：角色级参数（ALTER ROLE ... SET）只在登录时应用，SET ROLE 切换到组时不会应用组自己的角色级参数——详见下文「组角色与会话参数」。

详细说明 ​

组角色设计模式：命名与职责 ​

组角色的价值 90% 来自命名纪律。推荐的命名约定：

前缀 / 模式职责典型成员
<app>_readonly只读：CONNECT + USAGE + SELECT报表、分析、只读副本消费方
<app>_readwrite读写：只读能力 + INSERT/UPDATE/DELETE + 序列 USAGE应用连接、常规服务
<app>_admin管理：读写 + DDL 所需能力 + 转授权迁移工具、发布流水线
<app>_owner属主：拥有全部对象，原则上无人日常使用SET ROLE 后建对象、迁移对象
<app>_<domain>_<verb>按领域细分（如 billing_readonly）多模块大库

组与组之间用嵌套表达「包含」关系（读写组可以包含于…）。

组角色与预设角色的关系 ​

PostgreSQL 自带一组预设角色（pg_monitor、pg_read_all_data、pg_signal_backend 等，完整清单见权限系统速查），它们与自建组角色的关系要摆正：

  • 预设角色是「权限包」，不是你的组。它们的属性与权限是系统固化的：不能改属性、不能改名、不能删除，权限内容也无法枚举或裁剪。把它当作「官方打包好的一组能力」按需取用即可。
  • 正确的接法是「我的组挂到预设角色下」：GRANT pg_monitor TO app_admin; 让管理组整体获得监控能力，成员继承后无需逐人授予。反方向（把自建角色挂为预设角色的成员）没有意义也不被支持语义。
  • 预设角色不接受定制：想裁剪能力（比如只要 pg_stat_activity 不要文件读取），就自建组并精确授予，而不是把 pg_read_server_files 这种过宽的角色接进来。
  • 命名空间避让：自建角色不要以 pg_ 开头——那是系统预设角色的保留前缀。
sql
-- 标准接法：自建组 → 预设角色 → 人
CREATE ROLE ops_monitor NOLOGIN;
GRANT pg_monitor TO ops_monitor;
GRANT ops_monitor TO alice WITH INHERIT TRUE, SET TRUE;

对象属主模式：app_owner 与发布流程 ​

「让组拥有对象」值得单独展开，因为它是组角色体系里收益最大的单项实践：

text
app_owner（NOLOGIN）           ← 拥有 schema、表、序列、函数
   ↑ SET ROLE（发布时）          ← 迁移工具/发布流水线的登录角色是它的成员
   ↑ 继承（日常）               ← readonly/readwrite 组经授权获得使用权限

落地为三条流程规则：

  1. DDL 一律以 app_owner 身份执行。迁移工具的登录角色挂为 app_admin 组成员，迁移脚本开头 SET ROLE app_owner;（或连接初始化时执行），之后建出来的对象属主统一是组，永不出现「张三建的表张三离职后没人敢动」。
  2. 应用连接永不拥有对象。app_conn 这类应用角色只经 app_readwrite 组获得 DML 权限；应用账号被攻破时，攻击面被限制在数据操纵，拿不到 DDL。
  3. 属主不参与日常查询。app_owner 平时谁也不切换成它，权限收得很紧（甚至可以 REVOKE 它自己的部分权限），降低误用面。
sql
-- 发布流水线的标准姿势
SET ROLE app_owner;
CREATE TABLE app.refunds (...);
ALTER DEFAULT PRIVILEGES IN SCHEMA app
    GRANT SELECT ON TABLES TO app_readonly;   -- 新表自动对只读组可见
RESET ROLE;

成员管理：GRANT / REVOKE 与成员关系选项 ​

成员关系的完整选项在《角色继承》一页已经详述，这里给出组管理视角的速查：

sql
-- 增加/调整成员（重发 GRANT 可修改选项，未提及的保持原值）
GRANT app_readwrite TO bob WITH INHERIT TRUE, SET TRUE;
GRANT app_readwrite TO bob WITH ADMIN TRUE;        -- 之后 bob 可自行拉人进组

-- 只回收「转授权资格」，保留成员身份
REVOKE ADMIN OPTION FOR app_readwrite FROM bob;

-- 完全摘除
REVOKE app_readwrite FROM bob;

两个硬性限制：成员关系不能授予 PUBLIC；成员关系图不允许成环（数据库会拒绝循环授予）。

成员变更的标准操作流 ​

人事动作映射到成员关系操作，建议固化为三套标准脚本：

sql
-- ── 入职：建角色 → 挂组 → 挂参数 → 留痕 ─────────────────
CREATE ROLE carol LOGIN PASSWORD '<初始密码>' VALID UNTIL '2026-09-30';
COMMENT ON ROLE carol IS '客服组，工单 #1234，2026-09-30 复审';
GRANT app_readonly TO carol WITH INHERIT TRUE, SET TRUE;
ALTER ROLE carol IN DATABASE appdb SET statement_timeout = '30s';

-- ── 转岗：换组而不是加组 ────────────────────────────────
REVOKE app_readonly FROM carol;                    -- 摘掉旧岗组
GRANT app_readwrite TO carol WITH INHERIT TRUE, SET TRUE;
COMMENT ON ROLE carol IS '转运营组，工单 #2345';   -- 更新户口

-- ── 离职：断会话 → 清资产 → 摘组 → 删人 ─────────────────
SELECT pg_terminate_backend(pid) FROM pg_stat_activity WHERE usename = 'carol';
REASSIGN OWNED BY carol TO app_owner;              -- 逐库执行（若她建过对象）
DROP OWNED BY carol;                               -- 清残留授权
REVOKE app_readwrite FROM carol;                   -- 摘组（防御性，DROP 也会做）
DROP ROLE carol;

三个纪律点：VALID UNTIL + COMMENT ON ROLE 让每个账号自带「复审日期」与「来龙去脉」；转岗时摘旧组是必须动作，只加不减会让权限单调递增；离职流程里「断会话」要放在最前——摘了成员关系后，已切换成组的存量会话在下次权限检查才会失效，主动断连才是即时生效。

SET ROLE 临时切换与对象属主归属 ​

成员通过 SET ROLE 切换进组后（需成员关系 SET TRUE），会话行为发生三个变化：

  1. 权限边界替换：此后只有组角色（及其继承链）的权限参与检查，登录角色自己的权限暂时失效。
  2. 创建的对象归组所有：CREATE TABLE、CREATE FUNCTION 的属主是组角色而非个人。这是规避「个人离职导致对象无主」的标准手法——把发布流程规定为「先 SET ROLE app_owner 再跑 DDL」。
  3. session_user 不变：审计视角下，pg_stat_activity.usename 显示的仍是登录角色，而 current_user 侧的组身份体现在语句的权限检查中。
sql
-- 验证身份流动
SELECT session_user, current_user;
SET ROLE app_owner;
SELECT session_user, current_user;   -- session_user 不变，current_user 变成组
CREATE TABLE app.demo (id int);      -- 属主是 app_owner
RESET ROLE;

会话角色三剑客的日常用法 ​

sql
-- 我是谁、我以谁的身份在跑、我是通过什么方式认证的
SELECT session_user, current_user, system_user;

典型输出：

text
 session_user | current_user |               system_user
--------------+--------------+-------------------------------------------
 alice        | alice        | scram-sha-256:alice

排查「权限异常」时，这三个值是第一现场：current_user 不是预期值，多半是连接池残留的 SET ROLE 没被重置；system_user 则能识别「同一个人从不同认证通道进来」。

会话身份在监控视图中的体现 ​

监控与审计视图里看到的「用户」列，取的是哪个身份需要心里有数：

sql
SELECT pid, usename, application_name, client_addr, state, query
FROM pg_stat_activity
WHERE datname = 'appdb';
  • pg_stat_activity.usename 显示的是登录角色（session_user 一侧）。成员 SET ROLE app_owner 之后，这个列不会变成 app_owner——想从监控里看出「这条连接正在以组身份干活」，要靠日志里的 SET ROLE 语句（配合 log_statement = 'ddl' 或 'all' 的采样）或应用侧埋点。
  • 连接池场景下 usename 是池账号。经过 PgBouncer 后，数百个真实用户共享 app_conn 一个名字，pg_stat_activity 无法区分自然人——这正是「人用角色与池账号分离、审计走应用层关联」的原因。
  • application_name 是关联业务的锚点。池化环境里约定每个服务连接串带上 application_name（如 order-service），usename × application_name 的组合才能把慢查询定位到系统。
  • 服务器日志同样记录登录身份。log_connections 输出的 user=xxx 是认证角色；log_min_duration_statement 慢查询行的用户也是登录角色，审计时若需要「有效角色」，必须在会话里自行记录。
text
-- log_connections 的典型输出（身份即登录角色）
2026-10-07 10:00:00 CST [pid] LOG:  connection authorized: user=app_conn database=appdb

组角色的角色属性与 CONNECTION LIMIT ​

组角色自己不登录，因此：

  • CONNECTION LIMIT 挂在组上没有意义。连接数限制按「发起登录的角色」逐连接检查，组成员各自用 session_user 登录，各自受限；组角色永远不发起登录，给它设 0 或任何值都不会约束成员。想限制某类人，把限制设在对应的 LOGIN 角色上。
  • 角色属性同理：组的 SUPERUSER / CREATEDB / CREATEROLE 属性不随成员关系传播，成员只能 SET ROLE 进组后使用（需 SET TRUE）。这恰恰是推荐做法——高特权属性放在 NOLOGIN 组上，使用必然留下「切换」这个显式动作。
  • 组的 VALID UNTIL / 密码：对不登录的组没有意义，保持 PASSWORD NULL（默认）即可，还顺带杜绝了「组密码外泄」这类问题。

组角色与会话参数：ALTER ROLE ... SET 的适用边界 ​

ALTER ROLE ... SET 建立的角色级默认参数，官方语义非常明确：只在登录时应用；SET ROLE 切换角色时不会应用目标角色的参数设置。由此得出两条实践规则：

  • 参数挂在 LOGIN 角色上，不要指望组。ALTER ROLE app_readwrite SET default_transaction_read_only = on; 对普通成员不起作用（组不登录、SET ROLE 也不应用它），属于「看起来生效了」的假配置。
  • 想让「只读组」真正只读，靠对象权限：不给 INSERT/UPDATE/DELETE/TRUNCATE 权限，继承后成员自然写不了；default_transaction_read_only 只是锦上添花，应设在成员的 LOGIN 角色上。
sql
-- 正确姿势：权限管能力，参数管习惯
GRANT SELECT ON ALL TABLES IN SCHEMA app TO app_readonly;      -- 能力：只读
ALTER ROLE reporter SET default_transaction_read_only = on;    -- 习惯：登录即只读
ALTER ROLE reporter SET statement_timeout = '2min';            -- 习惯：报表超时

多级组嵌套 ​

组可以互相成为成员，形成树形结构。继承与切换逐跳判定（详见角色继承）：权限沿 INHERIT TRUE 的链自动可用，SET ROLE 沿 SET TRUE 的链一步直达。

sql
CREATE ROLE grp_base  NOLOGIN;                      -- 基础：SELECT
CREATE ROLE grp_power NOLOGIN;                      -- 高权：继承基础组 + 自己的写权限
GRANT grp_base TO grp_power WITH INHERIT TRUE, SET TRUE;

GRANT grp_base  TO alice WITH INHERIT TRUE;         -- alice 只挂基础组
GRANT grp_power TO bob   WITH INHERIT TRUE;         -- bob 自动获得两级权限

嵌套深度的建议：不超过 3 层。更深的树会让「这个权限到底从哪来」的审计变成侦探工作，也让 REVOKE ... CASCADE 的波及范围难以预判。

多模块 / 多租户的组细分策略 ​

三层模型放到多模块大库或多租户场景时，有两种主流扩展方向，选型取决于「隔离要求 + 运维成本」：

方案结构适用代价
模块化组每模块一套 bill_readonly / bill_readwrite + 各自模式模块间弱隔离，共享数据库连接池组数量随模块线性增长，命名纪律要求高
租户 + RLS单套组 + 行级安全按 tenant_id 过滤租户间强数据隔离、共享表结构策略设计复杂，需理解 RLS

模块化组的要点：组与模式一一对应，模式与业务域一一对应，search_path 按登录角色固化；跨模块的共享访问通过「把模块读组挂进跨域组」表达，避免把对象权限直接授给人。

sql
-- 模块化：组 × 模式 对齐
CREATE ROLE billing_readwrite NOLOGIN;
GRANT USAGE ON SCHEMA billing TO billing_readwrite;
GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA billing TO billing_readwrite;
GRANT USAGE, SELECT ON ALL SEQUENCES IN SCHEMA billing TO billing_readwrite;

-- 财务审计组需要跨模块读：组嵌套表达，而不是对人授权
CREATE ROLE audit_cross NOLOGIN;
GRANT billing_readonly TO audit_cross WITH INHERIT TRUE, SET TRUE;
GRANT orders_readonly  TO audit_cross WITH INHERIT TRUE, SET TRUE;

租户方向的要点：组体系保持三层不变，租户隔离交给 RLS 策略与（可选的）租户级 SET app.tenant_id 自定义参数；不要为每个租户建一套角色——几十个租户尚可管理，上百个之后成员关系矩阵会失控。

成员关系的查询与递归展开 ​

直接成员关系查 pg_auth_members 即可；多级嵌套需要 WITH RECURSIVE：

sql
-- 向下：某个组的全部（直接 + 间接）成员
WITH RECURSIVE tree AS (
    SELECT am.member, 1 AS depth, ARRAY[am.roleid] AS path
    FROM pg_auth_members am
    WHERE am.roleid = 'app_admin'::regrole
  UNION ALL
    SELECT am.member, t.depth + 1, t.path || am.roleid
    FROM pg_auth_members am
    JOIN tree t ON am.roleid = t.member
)
SELECT m.rolname AS member, t.depth
FROM tree t
JOIN pg_roles m ON m.oid = t.member
ORDER BY t.depth, 1;

-- 向上：某个角色（直接 + 间接）所属的全部组
WITH RECURSIVE up AS (
    SELECT am.roleid, 1 AS depth
    FROM pg_auth_members am
    WHERE am.member = 'alice'::regrole
  UNION ALL
    SELECT am.roleid, u.depth + 1
    FROM pg_auth_members am
    JOIN up u ON am.member = u.roleid
)
SELECT r.rolname AS group_role, up.depth
FROM up
JOIN pg_roles r ON r.oid = up.roleid
ORDER BY up.depth, 1;

顺带可以验证某人的三态权限（是否成员 / 是否继承可用 / 能否切换）：

sql
SELECT pg_has_role('alice', 'app_readwrite', 'MEMBER'),   -- 是否（间接）成员
       pg_has_role('alice', 'app_readwrite', 'USAGE'),    -- 权限是否自动可用
       pg_has_role('alice', 'app_readwrite', 'SET');      -- 能否 SET ROLE

应用连接与人用角色分离 ​

把「应用连接」与「人」拆成两组 LOGIN 角色，是组模式落地后的自然结论：

维度应用连接角色人用角色
归属组app_readwrite（或按模块细分）app_readonly / app_admin 按岗位职责
认证密码/证书，进密钥管理；CONNECTION LIMIT 兜底密码 + VALID UNTIL，入职发放离职回收
search_path 等参数ALTER ROLE 固化可选固化
成员变更频率极低（跟随应用生命周期）随人事变动
审计关注点current_user 漂移、连接池残留成员关系变更、异常时段登录

混用两者是常见事故源：把应用密码发给开发「临时用一下」，离职时回收的是人的账号，应用密码却还在配置里活着。规则要写成铁律：一个 LOGIN 角色只有一个用途。

与连接池的配合

池化后所有请求可能共用少数几条物理连接，SET ROLE 的状态必须靠 server_reset_query 清理；成员权限变更后，池内旧连接上的权限立即生效（权限是执行时检查的），但「角色级参数」类变更要等新连接。详见PgBouncer一章。

实战示例 ​

示例：完整组角色体系搭建 ​

从零搭建一个业务库的标准组体系，可直接作为脚手架：

bash
psql -U postgres -d appdb <<'SQL'
-- ========== 0. 基线收紧 ==========
REVOKE CONNECT ON DATABASE appdb FROM PUBLIC;
REVOKE CREATE ON SCHEMA public FROM PUBLIC;

-- ========== 1. 组角色 ==========
CREATE ROLE app_owner     NOLOGIN;   -- 对象属主
CREATE ROLE app_readonly  NOLOGIN;
CREATE ROLE app_readwrite NOLOGIN;
CREATE ROLE app_admin     NOLOGIN;

-- 组间嵌套：admin 继承 readwrite 的全部能力
GRANT app_readwrite TO app_admin WITH INHERIT TRUE, SET TRUE;

-- ========== 2. 对象与授权 ==========
CREATE SCHEMA app AUTHORIZATION app_owner;
GRANT USAGE ON SCHEMA app TO app_readonly, app_readwrite;
GRANT USAGE, CREATE ON SCHEMA app TO app_admin;

GRANT SELECT ON ALL TABLES IN SCHEMA app TO app_readonly;
GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA app TO app_readwrite;
GRANT USAGE, SELECT ON ALL SEQUENCES IN SCHEMA app TO app_readwrite;
GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA app     TO app_owner;
GRANT ALL PRIVILEGES ON ALL SEQUENCES IN SCHEMA app  TO app_owner;

-- 未来对象自动跟上
ALTER DEFAULT PRIVILEGES FOR ROLE app_owner IN SCHEMA app
    GRANT SELECT ON TABLES TO app_readonly;
ALTER DEFAULT PRIVILEGES FOR ROLE app_owner IN SCHEMA app
    GRANT SELECT, INSERT, UPDATE, DELETE ON TABLES TO app_readwrite;
ALTER DEFAULT PRIVILEGES FOR ROLE app_owner IN SCHEMA app
    GRANT USAGE, SELECT ON SEQUENCES TO app_readwrite;

-- ========== 3. 应用连接角色（NOINHERIT：写操作显式切换） ==========
CREATE ROLE app_conn LOGIN
    PASSWORD 'App_P@ssw0rd'
    CONNECTION LIMIT 30
    NOINHERIT;
GRANT app_readonly  TO app_conn WITH INHERIT TRUE,  SET TRUE;
GRANT app_readwrite TO app_conn WITH INHERIT FALSE, SET TRUE;
ALTER ROLE app_conn SET search_path = app, public;

-- ========== 4. 人用角色 ==========
CREATE ROLE alice LOGIN PASSWORD 'A1ice_P@ss' VALID UNTIL '2026-12-31';
CREATE ROLE bob   LOGIN PASSWORD 'B0b_P@sswd' VALID UNTIL '2026-12-31';
GRANT app_readonly  TO alice;
GRANT app_readwrite TO bob WITH INHERIT TRUE, SET TRUE;
GRANT app_admin     TO alice WITH ADMIN OPTION;
SQL

验证体系是否按预期工作：

bash
psql "dbname=appdb user=app_conn password=App_P@ssw0rd"
sql
SELECT session_user, current_user;                       -- app_conn | app_conn
SELECT * FROM app.orders LIMIT 1;                        -- 读：OK（继承只读组）
INSERT INTO app.orders(id) VALUES (9001);                -- 写：DENIED（NOINHERIT）
SET ROLE app_readwrite;
INSERT INTO app.orders(id) VALUES (9001);                -- 写：OK（切换生效）
RESET ROLE;

用递归查询输出整个体系的成员树，作为交付物归档：

sql
WITH RECURSIVE tree AS (
    SELECT am.roleid, am.member, 1 AS depth
    FROM pg_auth_members am
    JOIN pg_roles g ON g.oid = am.roleid AND g.rolname LIKE 'app_%'
  UNION ALL
    SELECT am.roleid, am.member, t.depth + 1
    FROM pg_auth_members am
    JOIN tree t ON am.roleid = t.member
)
SELECT g.rolname AS "group", m.rolname AS member, t.depth
FROM tree t
JOIN pg_roles g ON g.oid = t.roleid
JOIN pg_roles m ON m.oid = t.member
ORDER BY 1, 3;

示例二：成员的全生命周期（入职 → 转岗 → 离职） ​

接「成员变更的标准操作流」小节，跑一遍带验证的完整闭环：

bash
psql -U postgres -d appdb <<'SQL'
-- ── Day 1：入职 ──
CREATE ROLE dave LOGIN PASSWORD 'D@ve_2026' VALID UNTIL '2027-03-31';
COMMENT ON ROLE dave IS '数据分析，工单 #3456';
GRANT app_readonly TO dave WITH INHERIT TRUE, SET TRUE;

-- ── Day 180：转岗为读写 ──
REVOKE app_readonly FROM dave;
GRANT app_readwrite TO dave WITH INHERIT TRUE, SET TRUE;
COMMENT ON ROLE dave IS '转业务开发，工单 #3456';

-- ── Day 400：离职 ──
SELECT pg_terminate_backend(pid) FROM pg_stat_activity WHERE usename = 'dave';
REASSIGN OWNED BY dave TO app_owner;
DROP OWNED BY dave;
REVOKE app_readwrite FROM dave;
DROP ROLE dave;
SQL

每一步后都可以用同一句验证兜底：

sql
SELECT g.rolname AS group_role
FROM pg_auth_members am
JOIN pg_roles g ON g.oid = am.roleid
JOIN pg_roles m ON m.oid = am.member
WHERE m.rolname = 'dave';   -- 预期：转岗后只剩 app_readwrite；离职后 0 行

示例三：排查「current_user 漂移」事故 ​

现象：报表系统偶发 permission denied for schema app，重连后自愈。怀疑连接池上的会话状态残留。

sql
-- 1. 出错时先抓第一现场：登录身份与有效角色是否一致
SELECT pid, usename, application_name, backend_start, state, left(query, 60)
FROM pg_stat_activity
WHERE datname = 'appdb' AND application_name = 'report-service';

-- 2. 检查池的 server_reset_query 是否清理了 SET ROLE（以 PgBouncer 为例）
--    pgbouncer 管理库：
SHOW server_reset_query;   -- 期望包含 DISCARD ALL 或至少 RESET ALL

-- 3. 复现：手动模拟漂移会话
SET ROLE app_readwrite;    -- 模拟上一个租用者忘记 RESET
SELECT current_user;       -- app_readwrite（残留状态）
RESET ROLE;

定位结论的三种可能：池配置缺少 server_reset_query（最常见）；应用代码在初始化/收尾时执行了不对称的 SET ROLE；某个框架的连接装饰器在错误处理分支里漏了 RESET ROLE。修复优先级永远是「池兜底重置 > 应用成对书写」，因为池兜底不依赖每个调用方都正确。详细配置见PgBouncer一章。

常见问题速答 ​

  • 组角色能设密码吗？ 技术上可以，但毫无意义：组不登录，密码永远不会被认证流程用到。保持 PASSWORD NULL，杜绝「组密码」这个概念外泄。
  • 能把人直接挂进 app_owner 吗？ 技术可行，等于把 DDL 能力发给个人。只应把发布工具的登录角色挂进去（且建议 INHERIT FALSE，逼它显式 SET ROLE）。
  • 组里能再套预设角色吗？ 方向反过来：GRANT pg_monitor TO 你的组; 是正解；预设角色不是可以往里挂人的组。
  • 成员离职但 DROP ROLE 报「cannot be dropped because some objects depend on it」？ 说明还有对象/授权依赖：先逐库 REASSIGN OWNED / DROP OWNED，报错里的依赖对象清单会指明漏了哪个库。
  • 同一个组能给一个人挂两次吗？ 不能也没有意义：成员关系以（组， 成员）二元组唯一，重复 GRANT 只是修改选项。
  • 组能挂在登录角色「头上」吗？ 可以——成员关系是有向边，GRANT 普通组 TO 某登录角色 与反向挂载都合法；但让组成为人的成员几乎总是设计混乱的信号，评审时优先质疑。
  • 组的数量有没有上限？ 没有技术上限，但有审计上限：\dp 输出与成员树的复杂度随组数增长，超过「一屏能看完」就该考虑合并同职责的组。

注意事项 ​

组角色不是类型，是用法。 一个 NOLOGIN 角色一旦被误设 LOGIN（哪怕手滑），就从「权限容器」变成了「可登录实体」；如果它还持有大量权限，等于凭空多了一个超级业务账号。巡检时应检查「LOGIN 且持有大量成员的组」这类异常形态。

成员关系的增删要留痕。 GRANT group TO user 是权限体系里最敏感的变更之一，却往往只有一行 SQL 的成本。务必纳入工单/迁移脚本，配合 pg_auth_members 快照做周期对比。

SET ROLE 后建对象，属主是组不是人。 这通常是期望行为，但如果不希望（比如想保留个人署名），就不要在切换状态下建对象。反过来，发布流程应当强制切换到 app_owner 再跑 DDL。

组级参数是假配置。 ALTER ROLE <nogroup LOGIN 组> SET ... 永远不会被应用：组不登录，SET ROLE 也不应用角色级参数。参数必须挂在 LOGIN 角色上。

嵌套要克制。 每一层嵌套都是一条继承链与一条 SET ROLE 链，超过三层的组树会让排错与审计成本陡增；能用扁平多组表达的，不要用深树。

成员关系不能给 PUBLIC，不能成环。 这是数据库层面的硬限制；企图「让所有人自动获得某组」只能通过把权限授给 PUBLIC 实现——而那是另一类更宽的决策，需要单独评审。

应用角色的 NOINHERIT 要配套使用规范。 登录角色 NOINHERIT 后，写操作必须显式 SET ROLE；如果应用框架不支持在连接初始化时执行 SET ROLE（或事务开头执行），就不要用这个模式，改为直接继承读写组并把只读需求放另一组。

删组前先摘成员或直接 DROP。 DROP ROLE 组角色会自动撤销所有成员关系，成员角色不受影响；但组拥有的对象仍然存在，需要先 REASSIGN OWNED 转交。

自建角色不要占用 pg_ 前缀。 预设角色与系统角色都生活在这个命名空间里，业务角色用了 pg_ 开头不仅命名混乱，还可能在版本升级时与新增的系统角色撞名。

system_user 才是「认证通道」的证据。 同一账号可以从密码、证书、GSSAPI 等不同通道进入，审计怀疑凭证复用时，system_user 的 auth_method:identity 格式能直接回答「这条连接是怎么进来的」。

最佳实践 ​

  1. 三层起步：readonly / readwrite / admin（外加 owner 作属主），组数量与业务模块对齐，禁止「一人一组、一组一权」的碎片化。
  2. 权限只授给 NOLOGIN 组，LOGIN 角色零直接授权，让 \dp 输出里的被授权方永远是组，权限矩阵一目了然。
  3. 成员关系一律显式写选项（WITH INHERIT ..., SET ..., ADMIN ...），不依赖默认值，规避 PG16 前后语义差异并让脚本自文档化。
  4. 对象属主统一给 app_owner 组：发布流程用 SET ROLE app_owner 执行 DDL，人员流动不影响对象归属。
  5. 组间嵌套表达能力包含关系（如 admin ⊃ readwrite），深度控制在三层以内，并保持方向单一（高层组挂低层组）。
  6. 连接数与参数配在 LOGIN 角色上：CONNECTION LIMIT、search_path、statement_timeout 都按登录角色设置，组上的角色级参数是无效配置。
  7. 「只读」用权限表达，不只用参数表达：不给写权限是硬约束；default_transaction_read_only 作为登录习惯的补充。
  8. 人用角色强制 VALID UNTIL 与最小组归属：入职发放、转岗换组、离职 REVOKE + DROP ROLE（先走 REASSIGN/DROP OWNED 流程）。
  9. 审计三件套进巡检脚本：递归成员树、pg_has_role() 三态断言、ADMIN 选项持有者清单，任何漂移即告警。
  10. 应用与人严格分角色：应用角色永不给人用，人的角色永不进应用配置；两者的生命周期、认证方式、审计重点分开管理。
  11. 配合连接池管理会话状态：server_reset_query 清理 SET ROLE 残留，变更成员关系后评估是否需要重建池内连接。

下一节

继续学习：默认权限（ALTER DEFAULT PRIVILEGES）

参考链接 ​