多对多关系下PostgreSQL RLS策略适配INSERT...RETURNING的实现方法

现有如下数据库Schema，calendar与member为多对多关联：

CREATE TABLE IF NOT EXISTS event_manager.calendar (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    created_at TIMESTAMPTZ NOT NULL DEFAULT now()
);

CREATE TABLE IF NOT EXISTS event_manager.calendar_member (
    id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    calendar_id UUID NOT NULL REFERENCES event_manager.calendar(id),
    ory_id UUID NOT NULL, -- 关联外部系统的UUID
    created_at TIMESTAMPTZ NOT NULL DEFAULT now()
);

为保护calendar表，仅允许成员查询自己关联的日历，创建了以下RLS策略：

CREATE POLICY tenant_isolation_policy ON event_manager.calendar USING (EXISTS (
                        SELECT 1 FROM event_manager.calendar_member
                        WHERE calendar.id = calendar_member.calendar_id 
                        AND calendar_member.ory_id = current_setting('app.current_tenant')::UUID
))

但执行以下INSERT...RETURNING查询时出现权限错误：因为calendar的查询权限依赖calendar_member表的关联行，而插入calendar_member时需要引用新创建的calendar行，形成依赖矛盾。

WITH ins AS (
        INSERT INTO event_manager.calendar
        DEFAULT VALUES
        RETURNING
            id,
            created_at
        ),
        ins_member AS (
            INSERT INTO event_manager.calendar_member(calendar_id, ory_id, role_id)
            SELECT 
                ins.id AS "calendar_id",
                current_setting('app.current_tenant')::UUID AS "ory_id",
                1 AS "role_id"
            FROM ins
        )
    SELECT NOW() -- 此处仅做简单返回

请问如何调整RLS策略使其生效？是否必须在插入时绕过RLS？

不需要绕过RLS，只需调整calendar表的RLS策略，补充插入权限规则并优化查询判断逻辑，即可解决依赖矛盾：

1. 添加INSERT策略，允许用户创建日历
   先给calendar表添加插入权限策略，确保用户能正常发起创建操作：

   CREATE POLICY allow_calendar_insert ON event_manager.calendar
   FOR INSERT WITH CHECK (current_setting('app.current_tenant')::UUID IS NOT NULL);
   

   若业务允许所有用户创建日历，可简化为WITH CHECK (true)。

2. 修改查询策略，兼容刚创建的日历
   原策略仅检查calendar_member中的关联行，但刚插入的日历还未完成成员关联，需补充判断：允许查询当前事务内刚创建的日历。利用PostgreSQL系统列xmin和事务ID函数实现：

   ALTER POLICY tenant_isolation_policy ON event_manager.calendar
   USING (
       EXISTS (
           SELECT 1 FROM event_manager.calendar_member
           WHERE calendar.id = calendar_member.calendar_id 
           AND calendar_member.ory_id = current_setting('app.current_tenant')::UUID
       )
       OR
       -- 允许访问当前事务内刚插入的日历行
       xmin = pg_current_xact_id()
   );
   

   xmin记录行的插入事务ID，pg_current_xact_id()返回当前事务ID，两者匹配时说明是本次事务新建的行，此时即使未关联成员也能正常返回。

3. 验证效果
   调整后执行原INSERT...RETURNING语句即可正常运行：事务内插入日历后，RETURNING能通过xmin匹配拿到新行ID，用该ID插入calendar_member后，后续查询该日历时会通过calendar_member的关联行验证权限。

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