23.3 角色与最小权限

认证确认的是 login identity，授权判断的却是当前 effective role。把应用密码直接挂在对象 owner 身上，看起来省去了一层 SET ROLE，实际上把“能够连接” 和“能够改变安全边界”绑在了同一个身份上。

本节的目标是建立一条清晰的权限链：

human/workload identity
  -> LOGIN role
      -> explicitly SET approved NOLOGIN role
          -> object ACL
              -> row policy

每一条边都要有理由，每一个高权角色都应尽量不可登录。

23.3.1 login、group、owner 与 runtime role

PostgreSQL 只有 role 这一种主体

PostgreSQL 的 user 和 group 都建立在 role 上：

CREATE ROLE app_login LOGIN;
CREATE ROLE app_runtime NOLOGIN;

CREATE USER 只是默认带 LOGIN 的语法别名。所谓 group role 通常只是 NOLOGIN role，用 membership 聚合权限。

关键属性包括：

LOGIN
SUPERUSER
CREATEDB
CREATEROLE
REPLICATION
BYPASSRLS
CONNECTION LIMIT
VALID UNTIL

正常应用身份通常全部关闭高权属性：

CREATE ROLE app_login
  LOGIN
  NOSUPERUSER NOCREATEDB NOCREATEROLE
  NOINHERIT NOREPLICATION NOBYPASSRLS;

VALID UNTIL 只约束密码认证的有效期，不会让既有 session 自动断开，也不约束所有外部认证方式。它是凭据控制的一部分，不是完整账户生命周期。

四类角色不要合并

本章使用：

类型	LOGIN	用途	为什么拆开
workload login	是	认证应用实例	可单独轮换、禁用和归因
runtime	否	正常 DML	不拥有对象，不做 DDL
readonly	否	受控查询	与写路径独立授权、撤销
migrate	否或临时身份切入	发布窗口	不进入日常流量
owner	否	拥有 schema、table、policy	隔离隐式 owner 权力
break-glass	独立	限时应急	不属于应用 role graph

推荐图：

app_login
  ├─ SET TRUE / INHERIT FALSE -> app_runtime
  └─ SET TRUE / INHERIT FALSE -> app_readonly

release identity
  -> app_migrate
      -> SET TRUE / INHERIT FALSE -> app_owner

不推荐：

app_login LOGIN
  -> owns schema
  -> owns tables
  -> can ALTER/DROP policies
  -> credential copied to every app instance

owner 的能力来自所有权，不完全来自 ACL。撤销 table 上的 ALL 不能撤销 owner 的 ALTER、DROP、授权和 policy 管理能力。要收回这些能力，必须改变 owner 或改变运行身份。

`session_user` 与 `current_user`

连接建立后：

SELECT session_user, current_user, current_role;

初始通常相同。执行：

SET ROLE app_runtime;

之后：

session_user   仍是完成认证的 login
current_user   变为权限检查使用的 effective role
current_role   与 current_user 对应

审计时应保留两者。只记录 current_user=app_runtime 会丢失是哪个 workload login 使用了该能力；只记录 login 又可能误判 SQL 实际以何权限执行。

membership 的三个开关

PostgreSQL 16 起，一条 role membership 有三个独立选项：

GRANT app_runtime TO app_login
WITH ADMIN FALSE, INHERIT FALSE, SET TRUE;

语义：

选项	问题	本章默认
`ADMIN`	member 能否继续授予/撤销该 membership	`FALSE`
`INHERIT`	member 是否自动使用目标角色权限	`FALSE`
`SET`	member 能否 `SET ROLE` 到目标角色	`TRUE`

这种组合要求应用显式进入受控事务：

BEGIN;
SET LOCAL ROLE app_runtime;
-- business statements
COMMIT;

如果 INHERIT TRUE，login 在没有 SET ROLE 时就可能使用 runtime ACL，破坏 “没有声明上下文就失败”的设计。如果 SET FALSE，即便是 member 也不能切换到该 role。完整语义见 PostgreSQL：角色成员关系和 SET ROLE。

检查 membership 不应只看成员名称：

SELECT
    parent.rolname AS granted_role,
    member.rolname AS member_role,
    m.admin_option,
    m.inherit_option,
    m.set_option
FROM pg_auth_members AS m
JOIN pg_roles AS parent ON parent.oid = m.roleid
JOIN pg_roles AS member ON member.oid = m.member
ORDER BY 1, 2;

还要检查 role 自身属性：

SELECT
    rolname, rolcanlogin, rolsuper, rolcreatedb, rolcreaterole,
    rolreplication, rolbypassrls, rolconnlimit, rolvaliduntil
FROM pg_roles
WHERE rolname LIKE 'app_%'
ORDER BY rolname;

`SET LOCAL ROLE` 的边界

SET LOCAL 只在事务中有局部效果：

BEGIN;
SET LOCAL ROLE app_runtime;
SELECT current_user;
COMMIT;
SELECT current_user;  -- 回到原 login

它特别适合 transaction pooling，因为角色状态在事务结束时回收。应用不能把切换角色与业务 SQL 分在两个独立事务里：

transaction A: SET LOCAL ROLE app_runtime; COMMIT
transaction B: business query

第二个事务可能落在不同 backend，且局部角色早已消失。23.4 会把 role 与 tenant context 放进同一个事务合同。

23.3.2 schema、table、sequence、function 权限

权限是一组相互独立的门

一条：

SELECT id FROM app.account;

至少受这些条件影响：

database CONNECT
schema USAGE
table SELECT
column privilege, if table privilege is absent
RLS policy
role membership / ownership / bypass attributes

因此“给了表权限却仍报 permission denied”并不奇怪。应从外到内定位，而不是直接 GRANT ALL。

database 与 schema

database 常见权限：

CONNECT
CREATE
TEMPORARY

schema 常见权限：

USAGE   可以按名称访问 schema 中已获授权对象
CREATE  可以在 schema 中创建对象

runtime 通常只需：

GRANT CONNECT ON DATABASE appdb TO app_runtime;
GRANT USAGE ON SCHEMA app TO app_runtime;
REVOKE CREATE ON SCHEMA app FROM app_runtime;

USAGE 不会自动授予表权限，CREATE 却是一条重要越权路径：若可写 schema 出现在高权函数的 search_path 前部，攻击者可能创建同名函数、operator 或对象劫持解析。

安全基线通常包括：

REVOKE CREATE ON SCHEMA public FROM PUBLIC;

但执行前要盘点依赖。已有应用可能把 public 当作共享可写工作区，直接撤销会暴露历史设计问题。先发现、迁移，再收紧。

table 与 column

table 权限主要有：

SELECT INSERT UPDATE DELETE
TRUNCATE REFERENCES TRIGGER
MAINTAIN

不要把 TRUNCATE 当成普通 DELETE。它绕过逐行语义，不触发 ON DELETE trigger，且不受 RLS policy 逐行过滤。runtime 通常不应拥有它。

REFERENCES 允许创建引用约束，TRIGGER 允许在表上创建 trigger，二者也不属于日常 DML。最小 runtime grant 示例：

GRANT SELECT, INSERT, UPDATE ON TABLE app.account TO app_runtime;
REVOKE DELETE, TRUNCATE, REFERENCES, TRIGGER
ON TABLE app.account FROM app_runtime;

如果只授权部分列：

GRANT SELECT (id, display_name) ON app.account TO reporting_role;

要同时检查 view、function、COPY、returning expression 和新增列的暴露方式。列级 grant 不是数据脱敏系统。

sequence 不随 table 自动授权

使用 identity/serial 的 INSERT 可能还要访问 sequence：

GRANT USAGE, SELECT ON SEQUENCE app.account_id_seq TO app_runtime;

table 上的权限不会自动扩展到 sequence。常见症状是：

INSERT permission okay
nextval(...) -> permission denied for sequence

USAGE 允许 currval/nextval；SELECT 涉及 currval；UPDATE 可影响 setval。runtime 通常不应随意 setval。

function 默认可执行

新 function/procedure 通常会把 EXECUTE 授给 PUBLIC，除非创建者通过默认权限改变。对安全敏感函数，应在同一事务内创建和撤销：

BEGIN;

CREATE FUNCTION app.rotate_secret(...)
RETURNS void
LANGUAGE plpgsql
SECURITY DEFINER
SET search_path = pg_catalog, app_private
AS $function$
...
$function$;

REVOKE ALL ON FUNCTION app.rotate_secret(...) FROM PUBLIC;
GRANT EXECUTE ON FUNCTION app.rotate_secret(...) TO security_operator;

COMMIT;

不要让函数在“已创建但仍对 PUBLIC 开放”的窗口被调用。

SECURITY INVOKER 使用调用者权限，是默认和首选。SECURITY DEFINER 使用函数 owner 权限，必须：

owner 不可登录且不是不必要的 superuser；
固定安全 search_path，把 pg_catalog 和受控 schema 放入；
避免引用可被调用者替换的对象；
撤销 PUBLIC EXECUTE；
验证参数、tenant identity 和动态 SQL；
对返回错误与日志进行脱敏；
定期审计 owner 和函数定义。

官方安全写法见 PostgreSQL：CREATE FUNCTION。

`PUBLIC` 是隐式全体角色

每个角色都隐式属于 PUBLIC。审计 ACL 时不能只搜索显式 app_runtime：

effective privilege =
  PUBLIC
  + direct grant
  + inherited membership
  + owner rights
  + special attributes / predefined roles

这解释了为什么“ACL 里没有这个用户”不等于没有权限。

用权限函数做行为验收

ACL 文本适合审计来源，has_*_privilege 适合回答结果：

SELECT
    has_database_privilege('app_runtime', 'appdb', 'CONNECT') AS db_connect,
    has_schema_privilege('app_runtime', 'app', 'USAGE') AS schema_usage,
    has_schema_privilege('app_runtime', 'app', 'CREATE') AS schema_create,
    has_table_privilege('app_runtime', 'app.account', 'SELECT') AS can_select,
    has_table_privilege('app_runtime', 'app.account', 'TRUNCATE') AS can_truncate;

二者都不能替代实际负例。例如 has_table_privilege(..., 'SELECT')=true 不会告诉你 RLS 最终能看到哪些行。

23.3.3 默认权限、所有权迁移与越权路径

default privilege 只影响未来对象

下面语句不是给现有表授权：

ALTER DEFAULT PRIVILEGES
FOR ROLE app_owner
IN SCHEMA app
GRANT SELECT, INSERT, UPDATE ON TABLES TO app_runtime;

它表示：

以后由 app_owner 在 app schema 创建的 table
  -> 自动给 app_runtime 指定权限

三个限定都很重要：

future objects，不追溯现有对象；
creating role 是 app_owner；
schema scope 是 app。

如果迁移工具实际以 release_login 创建对象，而没有先 SET LOCAL ROLE app_owner，owner 和 default privilege 都可能偏离设计。角色 membership 的权限不会自动替创建者的默认权限生效。官方细节见 PostgreSQL：ALTER DEFAULT PRIVILEGES。

一个完整初始化事务通常同时处理当前和未来对象：

BEGIN;
SET LOCAL ROLE app_owner;

REVOKE CREATE ON SCHEMA public FROM PUBLIC;

GRANT USAGE ON SCHEMA app TO app_runtime, app_readonly;
GRANT SELECT, INSERT, UPDATE
  ON ALL TABLES IN SCHEMA app TO app_runtime;
GRANT SELECT
  ON ALL TABLES IN SCHEMA app TO app_readonly;
GRANT USAGE, SELECT
  ON ALL SEQUENCES IN SCHEMA app TO app_runtime;

ALTER DEFAULT PRIVILEGES IN SCHEMA app
  GRANT SELECT, INSERT, UPDATE ON TABLES TO app_runtime;
ALTER DEFAULT PRIVILEGES IN SCHEMA app
  GRANT SELECT ON TABLES TO app_readonly;
ALTER DEFAULT PRIVILEGES IN SCHEMA app
  GRANT USAGE, SELECT ON SEQUENCES TO app_runtime;
ALTER DEFAULT PRIVILEGES IN SCHEMA app
  REVOKE EXECUTE ON FUNCTIONS FROM PUBLIC;

COMMIT;

实际语句需按应用操作矩阵裁剪，不能机械复制。

所有权迁移是安全迁移

把一个旧 login 改为 NOLOGIN 之前，要盘点它拥有的对象：

SELECT
    n.nspname,
    c.relname,
    c.relkind
FROM pg_class AS c
JOIN pg_namespace AS n ON n.oid = c.relnamespace
JOIN pg_roles AS r ON r.oid = c.relowner
WHERE r.rolname = 'legacy_app'
ORDER BY 1, 2;

还要覆盖：

database, schema
table, sequence, view, materialized view
function, procedure
type, domain
publication/subscription
large object
default privileges
extension-owned dependencies

REASSIGN OWNED BY legacy_app TO app_owner 只作用于当前 database 中的对象，其他数据库要分别执行。DROP OWNED 会撤销 grant、并可能删除对象，是破坏性动作，不能拿来“顺手清理”生产账号。

推荐迁移：

inventory all databases
  -> create NOLOGIN owner
      -> transfer ownership in a reviewed change
          -> recreate/verify default privileges
              -> run positive and negative tests
                  -> stop old workload
                      -> NOLOGIN + PASSWORD NULL
                          -> terminate old sessions if required

常见越权路径

最小权限评审至少检查：

路径	风险
`SUPERUSER` / `BYPASSRLS`	绕过大多数数据库内控制
`CREATEROLE` / membership `ADMIN`	扩展角色图
owner login	日常凭据可改变对象和 policy
`INHERIT TRUE`	未显式进入业务角色也能使用其 ACL
writable `search_path` schema	对象名称劫持
`SECURITY DEFINER` + `PUBLIC EXECUTE`	以 owner 权限执行攻击输入
table owner without `FORCE RLS`	owner 默认绕过 RLS
`pg_read_all_data` / `pg_write_all_data`	跨 schema 广泛读写
`pg_read_server_files`	读取数据库服务器可见文件
`pg_write_server_files`	写入服务器文件
`pg_execute_server_program`	执行服务器程序
extension install/control	引入高权代码
untrusted procedural language	数据库进程内执行不受信代码

预定义角色是方便的能力包，不是低风险标签。它们随版本演进，升级评审必须重新阅读目标版本的预定义角色说明。

本章权限矩阵

正式实验收敛到：

行为	raw login	runtime	readonly	owner	break-glass
schema `USAGE`	否	是	是	owner	是
schema `CREATE`	否	否	否	是	是
table `SELECT`	否	是	是	owner	是
`INSERT/UPDATE`	否	是	否	owner	是
`DELETE/TRUNCATE`	否	否	否	owner 能力	是
管理 RLS policy	否	否	否	是	是
绕过 RLS	否	否	否	FORCE 后否	是

五个 synthetic role 在演练结束时全部：

NOLOGIN
NOSUPERUSER
NOCREATEDB
NOCREATEROLE
NOREPLICATION
NOBYPASSRLS

负例实际得到：

raw login SELECT table       SQLSTATE 42501
runtime CREATE               SQLSTATE 42501
runtime TRUNCATE             SQLSTATE 42501
readonly INSERT              SQLSTATE 42501

这比一张手工填写的权限表更强，因为它同时证明“应该成功的能成功”和“不该成功的确实失败”。下一节再把 table ACL 与 RLS 行边界组合起来。

上一节：认证与连接准入 · 返回本章目录 · 下一节：行级安全与连接池上下文 · 查看全书目录 · 查看索引中心

最后更新于 2026-07-30

23.2 认证与连接准入 23.4 行级安全与连接池上下文