PostgreSQL / 系统课时

用 USING 控制可见与可操作行

用 USING 控制可见与可操作行

本课聚焦 USING 的核心语义:它决定一条现有记录是否进入当前命令可见的候选集合。我们会用会话中的 tenant_id 上下文验证,同一条租户条件如何过滤 SELECT,并限制 UPDATE、DELETE 实际能够命中的目标行。

学习目标

  • 解释 USING 对现有行的过滤作用,而不是把它误解为普通 WHERE 的替代品。
  • 用 tenant_id 条件让不同租户只看到自己的记录。
  • 验证 UPDATE、DELETE 对不可见租户行只会得到零命中。

USING 决定现有行是否进入候选集合

RLS 开启后,策略中的 USING 表达式会针对现有行求值。结果为 true 的行可以继续参与命令;结果为 false 或 NULL 的行被过滤。对 SELECT 来说,这决定返回哪些行;对 UPDATE 和 DELETE 来说,这决定哪些旧行有资格成为修改目标。

这和在应用 SQL 中手写 WHERE tenant_id = ... 的区别很重要:业务 WHERE 是查询逻辑,USING 是数据库侧的行级安全边界。即使应用忘记租户条件,策略仍会参与过滤。

建立基于 tenant_id 的策略

下面用自定义会话参数 app.tenant_id 模拟应用传入的租户上下文,并由数据库超级用户完成建角色和切换角色。它适合演示策略语义;生产环境必须保证这个值由可信认证链路设置,不能让终端用户任意指定其他租户编号。

sql
CREATE ROLE app_client;

CREATE TABLE tenant_orders (
    id integer PRIMARY KEY,
    tenant_id integer NOT NULL,
    status text NOT NULL
);

INSERT INTO tenant_orders (id, tenant_id, status) VALUES
    (1, 1, 'new'),
    (2, 1, 'new'),
    (3, 2, 'new'),
    (4, 2, 'new');

GRANT SELECT, UPDATE, DELETE ON tenant_orders TO app_client;
ALTER TABLE tenant_orders ENABLE ROW LEVEL SECURITY;

CREATE POLICY tenant_select ON tenant_orders
    FOR SELECT TO app_client
    USING (tenant_id = NULLIF(current_setting('app.tenant_id', true), '')::integer);

CREATE POLICY tenant_update ON tenant_orders
    FOR UPDATE TO app_client
    USING (tenant_id = NULLIF(current_setting('app.tenant_id', true), '')::integer);

CREATE POLICY tenant_delete ON tenant_orders
    FOR DELETE TO app_client
    USING (tenant_id = NULLIF(current_setting('app.tenant_id', true), '')::integer);

预期结果

text
CREATE ROLE
CREATE TABLE
INSERT 0 4
GRANT
ALTER TABLE
CREATE POLICY
CREATE POLICY
CREATE POLICY

current_setting(..., true) 在参数不存在时返回 NULL,NULLIF 也会把空字符串变成 NULL。因此租户上下文没有被正确设置时,比较表达式不会变成 true,不会意外放行全部行。

验证 SELECT、UPDATE 与 DELETE

把当前会话设为租户 1,然后故意让 UPDATE 和 DELETE 的业务条件同时包含租户 1 与租户 2 的行。事务最后回滚,便于重复执行实验。

sql
SET ROLE app_client;

BEGIN;
SET LOCAL app.tenant_id = '1';

SELECT id, tenant_id, status
FROM tenant_orders
ORDER BY id;

UPDATE tenant_orders
SET status = 'paid'
WHERE id IN (1, 3);

DELETE FROM tenant_orders
WHERE id IN (2, 4);

SELECT id, tenant_id, status
FROM tenant_orders
ORDER BY id;

ROLLBACK;
RESET ROLE;

预期输出

text
SET
BEGIN
SET
 id | tenant_id | status
----+-----------+--------
  1 |         1 | new
  2 |         1 | new
(2 rows)

UPDATE 1
DELETE 1
 id | tenant_id | status
----+-----------+--------
  1 |         1 | paid
(1 row)

ROLLBACK
RESET

id=3 和 id=4 虽然满足业务语句里的 WHERE,但它们的 tenant_id=2 不满足当前策略,所以不会成为可操作目标。普通 UPDATE、DELETE 对这些不可见行通常表现为“没有命中”,而不是泄露一条“该行属于别的租户”的错误信息。

USING 与新行检查不是同一个问题

USING 的主要职责是检查现有行。UPDATE 还会产生修改后的新行,因此工程上还要关注 WITH CHECK:它决定更新后的记录是否允许被写回。对于只定义 USING 的 UPDATE 策略,PostgreSQL 会用同一表达式承担新行检查,但生产策略仍建议显式写出 WITH CHECK,把“能修改哪些旧行”和“修改后允许成为什么行”表达清楚。

因此,多租户策略至少要分别回答两个问题:当前租户能不能碰到这条现有记录,以及写入后的记录是否仍属于允许的租户边界。USING 解决的是前一个问题,也是避免跨租户读取、更新和删除的第一道数据库防线。

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

目录与阅读设置

阅读设置

17

记录一个想法

用 USING 控制可见与可操作行

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

登录哪都通