PostgreSQL / 系统课时

验证写入与 UPSERT 路径

验证写入与 UPSERT 路径

RLS 的写入问题不能只用 SELECT 验证:INSERT 检查新行,UPDATE 同时涉及旧行和新行,DELETE 只会处理可见旧行,而 ON CONFLICT DO UPDATE 还会叠加插入与更新两套规则。本篇用一组可回滚 SQL 把这些边界一次测清。

学习目标

  • 分清 USING 与 WITH CHECK 在四种写入路径中的职责。
  • 能为同租户成功、跨租户拒绝和不可见行设计可重复测试。
  • 理解唯一约束为什么应尽量包含 tenant_id,以及 UPSERT 冲突可能带来的隔离风险。

先建立一个可回滚的测试表

这个实验使用独立 schema 和非绕过 RLS 的测试角色,不依赖本章其他课时。表的主键使用 (tenant_id, external_key),表示业务键只要求“租户内唯一”,这样两个租户可以合法使用相同的外部编号。

sql
CREATE SCHEMA IF NOT EXISTS rls_lab;

DO $$
BEGIN
  CREATE ROLE rls_tenant_writer NOLOGIN;
EXCEPTION
  WHEN duplicate_object THEN NULL;
END
$$;

DROP TABLE IF EXISTS rls_lab.rls_write_demo;

CREATE TABLE rls_lab.rls_write_demo (
  tenant_id bigint NOT NULL,
  external_key text NOT NULL,
  payload text NOT NULL,
  PRIMARY KEY (tenant_id, external_key)
);

INSERT INTO rls_lab.rls_write_demo (tenant_id, external_key, payload)
VALUES (202, 'seed-202', 'existing row');

ALTER TABLE rls_lab.rls_write_demo ENABLE ROW LEVEL SECURITY;
ALTER TABLE rls_lab.rls_write_demo FORCE ROW LEVEL SECURITY;

GRANT USAGE ON SCHEMA rls_lab TO rls_tenant_writer;
GRANT SELECT, INSERT, UPDATE, DELETE
ON rls_lab.rls_write_demo TO rls_tenant_writer;

CREATE POLICY demo_tenant_rw ON rls_lab.rls_write_demo
FOR ALL TO rls_tenant_writer
USING (
  tenant_id = NULLIF(current_setting('app.tenant_id', true), '')::bigint
)
WITH CHECK (
  tenant_id = NULLIF(current_setting('app.tenant_id', true), '')::bigint
);

预期结果:schema、角色、表和策略创建成功;种子数据只有租户 202 的一行。若角色已存在,DO 块会跳过重复创建,不影响后续测试。

INSERT、UPDATE、DELETE 与 UPSERT 的 RLS 判定路径

按真实判定顺序测试 DML

下面的主示例在一个事务内执行,并最终 ROLLBACK,不会留下测试数据。管理员切换成 rls_tenant_writer 后,把当前租户设为 101。失败的跨租户插入用保存点隔离,否则 PostgreSQL 会把整个事务标记为失败。

sql
BEGIN;
SET LOCAL ROLE rls_tenant_writer;
SET LOCAL app.tenant_id = '101';

INSERT INTO rls_lab.rls_write_demo (tenant_id, external_key, payload)
VALUES (101, 'customer-a', 'created by tenant 101');

SAVEPOINT cross_tenant_insert;
INSERT INTO rls_lab.rls_write_demo (tenant_id, external_key, payload)
VALUES (202, 'forbidden', 'must not be written');
ROLLBACK TO SAVEPOINT cross_tenant_insert;

UPDATE rls_lab.rls_write_demo
SET payload = 'should stay unchanged'
WHERE tenant_id = 202 AND external_key = 'seed-202';

DELETE FROM rls_lab.rls_write_demo
WHERE tenant_id = 202 AND external_key = 'seed-202';

INSERT INTO rls_lab.rls_write_demo (tenant_id, external_key, payload)
VALUES (101, 'customer-a', 'updated by upsert')
ON CONFLICT (tenant_id, external_key)
DO UPDATE SET payload = EXCLUDED.payload;

SELECT tenant_id, external_key, payload
FROM rls_lab.rls_write_demo
ORDER BY tenant_id, external_key;

ROLLBACK;

预期输出:第一次插入成功;跨租户 INSERT 报 RLS 错误;针对租户 202 的 UPDATE 和 DELETE 都影响 0 行;同租户 UPSERT 更新成功;最终查询只能看到租户 101 的行。

text
INSERT 0 1
SAVEPOINT
ERROR:  new row violates row-level security policy for table "rls_write_demo"
ROLLBACK
UPDATE 0
DELETE 0
INSERT 0 1
 tenant_id | external_key |       payload
-----------+--------------+----------------------
       101 | customer-a   | updated by upsert
(1 row)
ROLLBACK

ERROR 的具体前缀格式可能随客户端不同,但核心结果应一致:错误不能让事务继续停留在失败状态,因此测试脚本必须回滚到保存点或结束事务。

看懂 UPSERT 为什么更容易踩边界

INSERT ... ON CONFLICT 不是先找到冲突行再决定要不要做 RLS。拟插入的新行仍要先满足插入策略的 WITH CHECK;如果进入 DO UPDATE,还要对冲突旧行执行更新侧的可见性检查,并对更新后的新行执行 WITH CHECK。语句读取冲突键、表达式或 RETURNING 列时,还可能需要相应 SELECT 权限和策略。

因此不要只测“同租户 UPSERT 能成功”。至少要覆盖以下行为:

场景 期望
当前租户插入自己的新行 成功写入 1 行
当前租户伪造其他 tenant_id RLS 拒绝,整条写入失败
更新或删除其他租户已有行 看不到目标,影响 0 行
同租户命中唯一键执行 UPSERT 只更新当前租户行
两个租户使用相同业务键 若业务键是租户内唯一,应各自成功

唯一约束要和隔离边界一致

如果业务编号只在租户内唯一,唯一约束应包含 tenant_id,例如 (tenant_id, external_key)。若只对 external_key 建全局唯一索引,租户 A 的数据可能影响租户 B 的冲突结果;DO NOTHING、唯一冲突错误或 DO UPDATE 的行为差异都可能成为跨租户信息侧信道。

确实需要全局唯一值时,不要假装它是租户私有字段。应用层应把“已存在但不可透露归属”处理成统一失败语义,测试也不能断言返回冲突行的详细信息。尤其不要为了让 UPSERT 成功而给应用角色增加跨租户 SELECT 或 UPDATE 策略,这会用更大的权限掩盖数据模型问题。

最终验收应同时看“成功结果”和“失败形态”:自己的行能写、换租户 ID 会被拒、不可见旧行不会被误改、UPSERT 的唯一键与租户边界一致。只有四条路径都验证过,才能说明写入侧 RLS 真正闭合。

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

目录与阅读设置

阅读设置

17

记录一个想法

验证写入与 UPSERT 路径

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

登录哪都通