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 模拟应用传入的租户上下文,并由数据库超级用户完成建角色和切换角色。它适合演示策略语义;生产环境必须保证这个值由可信认证链路设置,不能让终端用户任意指定其他租户编号。
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);预期结果
CREATE ROLE
CREATE TABLE
INSERT 0 4
GRANT
ALTER TABLE
CREATE POLICY
CREATE POLICY
CREATE POLICYcurrent_setting(..., true) 在参数不存在时返回 NULL,NULLIF 也会把空字符串变成 NULL。因此租户上下文没有被正确设置时,比较表达式不会变成 true,不会意外放行全部行。
验证 SELECT、UPDATE 与 DELETE
把当前会话设为租户 1,然后故意让 UPDATE 和 DELETE 的业务条件同时包含租户 1 与租户 2 的行。事务最后回滚,便于重复执行实验。
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;预期输出
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
RESETid=3 和 id=4 虽然满足业务语句里的 WHERE,但它们的 tenant_id=2 不满足当前策略,所以不会成为可操作目标。普通 UPDATE、DELETE 对这些不可见行通常表现为“没有命中”,而不是泄露一条“该行属于别的租户”的错误信息。
USING 与新行检查不是同一个问题
USING 的主要职责是检查现有行。UPDATE 还会产生修改后的新行,因此工程上还要关注 WITH CHECK:它决定更新后的记录是否允许被写回。对于只定义 USING 的 UPDATE 策略,PostgreSQL 会用同一表达式承担新行检查,但生产策略仍建议显式写出 WITH CHECK,把“能修改哪些旧行”和“修改后允许成为什么行”表达清楚。
因此,多租户策略至少要分别回答两个问题:当前租户能不能碰到这条现有记录,以及写入后的记录是否仍属于允许的租户边界。USING 解决的是前一个问题,也是避免跨租户读取、更新和删除的第一道数据库防线。