PostgreSQL / 系统课时

用 WITH CHECK 约束写入结果

用 WITH CHECK 约束写入结果

RLS 不只决定“哪些旧行能被看见”,还必须决定“写完后的新行是否合法”。本课用可运行的租户表演示 WITH CHECK:允许租户修改自己的行,但阻止 INSERT 或 UPDATE 把数据写到另一个 tenant_id。

学习目标

  • 区分 USING 对旧行的筛选与 WITH CHECK 对新行的校验
  • 为 INSERT、UPDATE 正确配置租户写入边界
  • 验证跨租户插入和修改 tenant_id 都会被 RLS 拒绝

先区分旧行和新行

USING 解决的是“这条现有记录能否成为本次操作的目标”。对 SELECT、UPDATE、DELETE,不满足条件的旧行通常像不存在一样被过滤。

WITH CHECK 解决的是“准备写入的行能否进入表中”。它检查 INSERT 产生的新行,也检查 UPDATE 修改后的新版本;结果为 false 或 NULL 时,语句报错,而不是静默跳过。

WITH CHECK 校验 INSERT 与 UPDATE 新行状态

配置租户写入策略

下面以 app.tenant_id 作为连接级租户上下文。使用 current_setting(..., true) 可以在上下文未设置时得到 NULL,从而让策略拒绝访问,而不是把缺失上下文误当成某个租户。

sql
CREATE TABLE projects (
    id bigint PRIMARY KEY,
    tenant_id bigint NOT NULL,
    name text NOT NULL
);

CREATE ROLE tenant_app NOLOGIN;
GRANT tenant_app TO CURRENT_USER;
GRANT SELECT, INSERT, UPDATE ON projects TO tenant_app;

ALTER TABLE projects ENABLE ROW LEVEL SECURITY;

CREATE POLICY projects_select ON projects
    FOR SELECT TO tenant_app
    USING (tenant_id = current_setting('app.tenant_id', true)::bigint);

CREATE POLICY projects_insert ON projects
    FOR INSERT TO tenant_app
    WITH CHECK (tenant_id = current_setting('app.tenant_id', true)::bigint);

CREATE POLICY projects_update ON projects
    FOR UPDATE TO tenant_app
    USING (tenant_id = current_setting('app.tenant_id', true)::bigint)
    WITH CHECK (tenant_id = current_setting('app.tenant_id', true)::bigint);

预期输出

text
CREATE TABLE
CREATE ROLE
GRANT ROLE
GRANT
ALTER TABLE
CREATE POLICY
CREATE POLICY
CREATE POLICY

UPDATE 同时需要两道门:旧行先通过 USING,修改后的新行再通过 WITH CHECK。这正是防止“先合法命中自己的行,再把 tenant_id 改成别人”的关键。

验证越界写入被拒绝

下面切换到应用角色,并把当前租户设为 10。第一条插入合法;第二条插入写入租户 20;最后一条更新试图把已属于租户 10 的行迁到租户 20。

sql
SET ROLE tenant_app;
SET app.tenant_id = '10';

INSERT INTO projects (id, tenant_id, name)
VALUES (1, 10, 'tenant-10-project');

INSERT INTO projects (id, tenant_id, name)
VALUES (2, 20, 'cross-tenant-project');

UPDATE projects
SET tenant_id = 20
WHERE id = 1;

SELECT id, tenant_id, name
FROM projects
ORDER BY id;

预期输出

text
SET
SET
INSERT 0 1
ERROR:  new row violates row-level security policy for table "projects"
ERROR:  new row violates row-level security policy for table "projects"
 id | tenant_id |       name
----+-----------+-------------------
  1 |        10 | tenant-10-project
(1 row)

两个错误都来自候选新行没有通过 WITH CHECK。其中 UPDATE 的旧行本来属于租户 10,所以能通过 USING;真正阻止越界的是修改后 tenant_id=20 的新行校验。

注意策略之外的边界

WITH CHECK 不是租户身份来源。app.tenant_id 必须由可信的认证与连接管理代码设置,不能把“客户端自己声明租户”当作安全边界。生产验收也应使用真实应用角色测试;表 owner、超级用户以及具有 BYPASSRLS 属性的角色可能绕过普通 RLS。

还有一个容易忽略的执行顺序:行级 BEFORE 触发器可以先修改候选新行,之后 WITH CHECK 才校验最终值。因此如果触发器会改写 tenant_id,测试应覆盖触发后的结果,而不是只检查客户端提交的原始值。

按自己的节奏,确认真正理解。

目录与阅读设置

阅读设置

17

记录一个想法

用 WITH CHECK 约束写入结果

0 / 10000 · 笔记保存到账号。未提交的修改会在关闭、离开或刷新后丢失。

登录哪都通