Postgres行级安全(RLS)限制性SELECT策略下无法更新软删除字段问题

问题根因 #

你当前配置的RLS策略存在两个适配问题：

1. *_owner是作用于所有操作的ALL类型策略，没有对软删除场景的UPDATE操作做单独适配，修改后deleted=true的行没有匹配的策略允许通过。
2. UPDATE语句的RETURNING子句会触发SELECT权限校验，已标记为删除的行会被*_deleted策略过滤，所以会返回空，容易被误认为操作失败。

解决方案 #

原有仅作用于SELECT的*_deleted策略不需要修改，已经满足「SELECT自动隐藏已删除记录」的需求，只需要拆分原有ALL类型的owner策略，新增软删除专属的UPDATE策略即可。

TEAMS表策略修改 #

-- 删除原有全操作owner策略
DROP POLICY IF EXISTS teams_owner ON public."TEAMS";

-- 插入策略：仅允许所有者插入未删除的队伍
CREATE POLICY teams_owner_insert ON public."TEAMS"
    AS PERMISSIVE
    FOR INSERT
    TO app_users
    WITH CHECK (
        owner = CURRENT_USER
        AND deleted = false
    );

-- 普通更新策略：仅允许所有者修改未删除队伍的非删除状态字段
CREATE POLICY teams_owner_update_normal ON public."TEAMS"
    AS PERMISSIVE
    FOR UPDATE
    TO app_users
    USING (
        owner = CURRENT_USER
        AND deleted = false
    )
    WITH CHECK (
        owner = CURRENT_USER
        AND deleted = false
    );

-- 软删除专属更新策略：仅允许所有者将自己的未删除队伍标记为删除
CREATE POLICY teams_owner_update_soft_delete ON public."TEAMS"
    AS PERMISSIVE
    FOR UPDATE
    TO app_users
    USING (
        owner = CURRENT_USER
        AND deleted = false
    )
    WITH CHECK (
        owner = CURRENT_USER
        AND deleted = true
    );

PLAYERS表策略修改 #

-- 删除原有全操作owner策略
DROP POLICY IF EXISTS players_owner ON public."PLAYERS";

-- 插入策略：仅允许所属队伍所有者插入未删除的球员
CREATE POLICY players_owner_insert ON public."PLAYERS"
    AS PERMISSIVE
    FOR INSERT
    TO app_users
    WITH CHECK (
        (SELECT "TEAMS".owner FROM "TEAMS" WHERE "TEAMS".id = team_id) = CURRENT_USER
        AND deleted = false
    );

-- 普通更新策略：仅允许所属队伍所有者修改未删除球员的非删除状态字段
CREATE POLICY players_owner_update_normal ON public."PLAYERS"
    AS PERMISSIVE
    FOR UPDATE
    TO app_users
    USING (
        (SELECT "TEAMS".owner FROM "TEAMS" WHERE "TEAMS".id = team_id) = CURRENT_USER
        AND deleted = false
    )
    WITH CHECK (
        (SELECT "TEAMS".owner FROM "TEAMS" WHERE "TEAMS".id = team_id) = CURRENT_USER
        AND deleted = false
    );

-- 软删除专属更新策略：仅允许所属队伍所有者将未删除球员标记为删除
CREATE POLICY players_owner_update_soft_delete ON public."PLAYERS"
    AS PERMISSIVE
    FOR UPDATE
    TO app_users
    USING (
        (SELECT "TEAMS".owner FROM "TEAMS" WHERE "TEAMS".id = team_id) = CURRENT_USER
        AND deleted = false
    )
    WITH CHECK (
        (SELECT "TEAMS".owner FROM "TEAMS" WHERE "TEAMS".id = team_id) = CURRENT_USER
        AND deleted = true
    );

效果验证 #

1. 所属用户执行UPDATE SET deleted=true语句可以正常执行，行的deleted字段会被成功修改为true。
2. 普通SELECT查询无法看到deleted=true的记录，符合需求。
3. 若UPDATE语句携带RETURNING子句会返回空，这是SELECT策略过滤的正常表现，可通过查看语句影响行数确认操作成功，Postgraphile也会正确返回影响计数不会抛出权限错误。

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