PostgreSQL有无RLS下的三角关系验证方法问询

问题1：未启用RLS时的实现方案 #

完全可行，有两种高效的原生实现方式：

方式1：复合外键约束（推荐） #

通过给boxes表设置复合主键，再让things表的(box_id, owner_id)字段关联这个复合主键，直接通过外键强制保证箱子归属关系：

-- 修改boxes表的主键为(id, owner_id)复合键
ALTER TABLE boxes
DROP CONSTRAINT IF EXISTS boxes_pkey,
ADD PRIMARY KEY (id, owner_id);

-- 给things表添加复合外键约束
ALTER TABLE things
ADD CONSTRAINT fk_things_box_owner
FOREIGN KEY (box_id, owner_id)
REFERENCES boxes(id, owner_id);

这种方式依赖PostgreSQL原生外键检查，性能最优，且能完全避免数据不一致。

方式2：检查约束+自定义函数 #

如果不想修改boxes表的主键结构，可以用自定义函数配合检查约束实现：

-- 创建验证箱子归属的稳定函数
CREATE OR REPLACE FUNCTION is_box_owned_by(p_box_id INT, p_owner_id INT)
RETURNS BOOLEAN AS $$
SELECT EXISTS(
    SELECT 1 FROM boxes b
    WHERE b.id = p_box_id AND b.owner_id = p_owner_id
);
$$ LANGUAGE sql STABLE;

-- 给things表添加检查约束
ALTER TABLE things
ADD CONSTRAINT check_box_belongs_to_owner
CHECK (is_box_owned_by(box_id, owner_id));

注意：函数必须标记为STABLE才能用于检查约束，PostgreSQL不允许直接在CHECK中写子查询。

问题2：启用RLS后的实现方案 #

由于PostgreSQL的引用完整性检查（包括外键）会绕过RLS，所以仅靠外键无法限制用户的操作，必须结合RLS策略来实现业务规则：

核心实现步骤 #

1. 启用目标表的RLS：

ALTER TABLE things ENABLE ROW LEVEL SECURITY;
-- 如果boxes表也需要限制访问，同样启用RLS
ALTER TABLE boxes ENABLE ROW LEVEL SECURITY;

2. 创建boxes表的RLS策略（仅允许查看自己的箱子）：

CREATE POLICY view_own_boxes ON boxes
FOR SELECT USING (owner_id = current_setting('app.user_id')::INT);

这里假设用current_setting('app.user_id')获取当前会话的用户ID，实际场景可根据认证方式调整（比如current_user映射到成员ID）。

3. 创建things表的INSERT策略（强制验证归属）：

CREATE POLICY insert_own_things ON things
FOR INSERT WITH CHECK (
    -- 确保物品属于当前用户
    owner_id = current_setting('app.user_id')::INT
    -- 借助boxes表的RLS策略，仅能查询到自己的箱子，自动验证归属
    AND EXISTS(SELECT 1 FROM boxes b WHERE b.id = box_id)
);

如果boxes表未启用RLS，需明确验证箱子归属：

CREATE POLICY insert_own_things ON things
FOR INSERT WITH CHECK (
    owner_id = current_setting('app.user_id')::INT
    AND EXISTS(
        SELECT 1 FROM boxes b
        WHERE b.id = box_id AND b.owner_id = owner_id
    )
);

关于RLS策略覆盖REFERENCES操作的说明 #

FOR [SELECT|UPDATE] USING (...)这类RLS语法无法直接覆盖外键的REFERENCES检查，因为外键检查是数据库级的完整性验证，会绕过所有RLS规则。但可以通过RLS的INSERT/UPDATE WITH CHECK策略，从业务层面限制用户能插入的合法数据组合，间接实现和外键一致的归属验证效果。

内容的提问来源于stack exchange，提问作者yen