PostgreSQL / 系统课时

编写跨租户隔离回归测试

编写跨租户隔离回归测试

跨租户隔离不能只验证“当前租户能查到数据”,还要主动证明错误租户读不到、写不进、关联不串、UPSERT 不越界,并在角色切换后仍保持预期边界。本课用一组可重复 SQL 把这些要求变成回归断言。

学习目标

  • 为读取、写入、关联、UPSERT 和角色切换设计正反用例
  • 用最小双租户夹具验证 RLS 的 USING 与 WITH CHECK
  • 把“零串租”转成每次策略或 Schema 变更都能执行的验收条件

先把零串租写成可判定条件

测试夹具至少准备两个租户,并让两边都存在形状相同的数据。这样每个用例都有“应该出现”和“绝不能出现”的对象,而不是把空结果误判成隔离成功。

场景 正向断言 反向断言
读取 租户 1 能看到自己的账户 结果中不存在租户 2
写入 能修改本租户可见行 不能把行改成其他租户
关联 账户与账单只在本租户内关联 JOIN 结果不出现其他租户
UPSERT 本租户冲突键可更新 显式写入其他租户被拒绝
角色切换 切到租户 2 后只看到租户 2 应用角色不能意外保持高权限身份

搭建最小 RLS 测试基线

下面脚本适合在专用测试数据库中由具备建表、建角色和 SET ROLE 能力的管理账号执行。策略用自定义会话参数保存租户上下文;NULLIF 可以让未设置参数时安全地得到 NULL,从而默认拒绝访问。

sql
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
);

预期输出

text
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 拒绝。

sql
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;

预期结果

text
 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。真正可靠的门禁同时检查返回集合、受影响行数、预期拒绝错误和当前角色身份。

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

目录与阅读设置

阅读设置

17

记录一个想法

编写跨租户隔离回归测试

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

登录哪都通