PostgreSQL / 系统课时
编写跨租户隔离回归测试
编写跨租户隔离回归测试
跨租户隔离不能只验证“当前租户能查到数据”,还要主动证明错误租户读不到、写不进、关联不串、UPSERT 不越界,并在角色切换后仍保持预期边界。本课用一组可重复 SQL 把这些要求变成回归断言。
学习目标
- 为读取、写入、关联、UPSERT 和角色切换设计正反用例
- 用最小双租户夹具验证 RLS 的
USING与WITH CHECK - 把“零串租”转成每次策略或 Schema 变更都能执行的验收条件
先把零串租写成可判定条件
测试夹具至少准备两个租户,并让两边都存在形状相同的数据。这样每个用例都有“应该出现”和“绝不能出现”的对象,而不是把空结果误判成隔离成功。
| 场景 | 正向断言 | 反向断言 |
|---|---|---|
| 读取 | 租户 1 能看到自己的账户 | 结果中不存在租户 2 |
| 写入 | 能修改本租户可见行 | 不能把行改成其他租户 |
| 关联 | 账户与账单只在本租户内关联 | JOIN 结果不出现其他租户 |
| UPSERT | 本租户冲突键可更新 | 显式写入其他租户被拒绝 |
| 角色切换 | 切到租户 2 后只看到租户 2 | 应用角色不能意外保持高权限身份 |
搭建最小 RLS 测试基线
下面脚本适合在专用测试数据库中由具备建表、建角色和 SET ROLE 能力的管理账号执行。策略用自定义会话参数保存租户上下文;NULLIF 可以让未设置参数时安全地得到 NULL,从而默认拒绝访问。
DROP SCHEMA IF EXISTS rls_regress CASCADE;
CREATE SCHEMA rls_regress;
CREATE TABLE rls_regress.account (
tenant_id bigint NOT NULL,
account_id bigint NOT NULL,
name text NOT NULL,
PRIMARY KEY (tenant_id, account_id)
);
CREATE TABLE rls_regress.invoice (
tenant_id bigint NOT NULL,
invoice_id bigint NOT NULL,
account_id bigint NOT NULL,
external_key text NOT NULL,
amount integer NOT NULL,
PRIMARY KEY (tenant_id, invoice_id),
UNIQUE (tenant_id, external_key),
FOREIGN KEY (tenant_id, account_id)
REFERENCES rls_regress.account (tenant_id, account_id)
);
INSERT INTO rls_regress.account VALUES
(1, 11, 'tenant-1-a'), (1, 12, 'tenant-1-b'),
(2, 21, 'tenant-2-a'), (2, 22, 'tenant-2-b');
INSERT INTO rls_regress.invoice VALUES
(1, 101, 11, 'inv-a', 100),
(2, 201, 21, 'inv-a', 200);
DO $$
BEGIN
IF NOT EXISTS (SELECT 1 FROM pg_roles WHERE rolname = 'rls_test_app') THEN
CREATE ROLE rls_test_app NOLOGIN;
END IF;
END
$$;
GRANT USAGE ON SCHEMA rls_regress TO rls_test_app;
GRANT SELECT, INSERT, UPDATE ON ALL TABLES IN SCHEMA rls_regress TO rls_test_app;
ALTER TABLE rls_regress.account ENABLE ROW LEVEL SECURITY;
ALTER TABLE rls_regress.account FORCE ROW LEVEL SECURITY;
ALTER TABLE rls_regress.invoice ENABLE ROW LEVEL SECURITY;
ALTER TABLE rls_regress.invoice FORCE ROW LEVEL SECURITY;
CREATE POLICY account_tenant ON rls_regress.account
FOR ALL TO rls_test_app
USING (
tenant_id = NULLIF(current_setting('app.tenant_id', true), '')::bigint
)
WITH CHECK (
tenant_id = NULLIF(current_setting('app.tenant_id', true), '')::bigint
);
CREATE POLICY invoice_tenant ON rls_regress.invoice
FOR ALL TO rls_test_app
USING (
tenant_id = NULLIF(current_setting('app.tenant_id', true), '')::bigint
)
WITH CHECK (
tenant_id = NULLIF(current_setting('app.tenant_id', true), '')::bigint
);预期输出
DROP SCHEMA
CREATE SCHEMA
CREATE TABLE
CREATE TABLE
INSERT 0 4
INSERT 0 2
DO
GRANT
GRANT
ALTER TABLE
ALTER TABLE
ALTER TABLE
ALTER TABLE
CREATE POLICY
CREATE POLICY若 Schema 原先不存在,DROP SCHEMA 前可能额外出现 NOTICE;这不影响测试基线。
执行正反用例
先切到受 RLS 约束的应用角色,再设置租户 1。读取和 JOIN 应只返回租户 1;更新不可见的租户 2 行应影响 0 行,而把租户 1 的可见行改成租户 2 会被 WITH CHECK 拒绝。
SET ROLE rls_test_app;
SET app.tenant_id = '1';
SELECT tenant_id, account_id
FROM rls_regress.account
ORDER BY account_id;
SELECT i.tenant_id, i.invoice_id, a.account_id
FROM rls_regress.invoice AS i
JOIN rls_regress.account AS a
ON a.tenant_id = i.tenant_id
AND a.account_id = i.account_id
ORDER BY i.invoice_id;
UPDATE rls_regress.account
SET name = 'should-not-change'
WHERE tenant_id = 2;
UPDATE rls_regress.account
SET tenant_id = 2
WHERE tenant_id = 1 AND account_id = 12;
INSERT INTO rls_regress.invoice
(tenant_id, invoice_id, account_id, external_key, amount)
VALUES
(1, 999, 11, 'inv-a', 150)
ON CONFLICT (tenant_id, external_key)
DO UPDATE SET amount = EXCLUDED.amount
RETURNING tenant_id, invoice_id, amount;
INSERT INTO rls_regress.invoice
(tenant_id, invoice_id, account_id, external_key, amount)
VALUES
(2, 299, 21, 'inv-b', 300)
ON CONFLICT (tenant_id, external_key)
DO UPDATE SET amount = EXCLUDED.amount;
SET app.tenant_id = '2';
SELECT tenant_id, count(*) AS visible_accounts
FROM rls_regress.account
GROUP BY tenant_id;
RESET ROLE;
SELECT count(*) AS privileged_control
FROM rls_regress.account;预期结果
tenant_id | account_id
-----------+------------
1 | 11
1 | 12
tenant_id | invoice_id | account_id
-----------+------------+-----------
1 | 101 | 11
UPDATE 0
ERROR: new row violates row-level security policy for table "account"
tenant_id | invoice_id | amount
-----------+------------+--------
1 | 101 | 150
ERROR: new row violates row-level security policy for table "invoice"
tenant_id | visible_accounts
-----------+-----------------
2 | 2
privileged_control
--------------------
4最后一条控制查询应由测试管理账号执行;超级用户以及带 BYPASSRLS 的角色仍会绕过 RLS,FORCE ROW LEVEL SECURITY 也不能约束它们。因此回归测试既要证明应用角色被正确限制,也要检查连接池或测试框架没有在用高权限角色“假装通过”。
把回归测试接入变更门禁
策略、表结构、唯一约束、外键和连接角色任何一项变化,都可能改变隔离结果。CI 中应使用固定夹具重新执行同一组正反断言,并把“出现其他租户行”“跨租户写入成功”“应用会话处于超级用户或 BYPASSRLS 角色”视为立即失败。
不要只统计查询是否报错:RLS 的读取隔离通常表现为静默过滤,对不可见行的 UPDATE 也可能只是 UPDATE 0。真正可靠的门禁同时检查返回集合、受影响行数、预期拒绝错误和当前角色身份。