PostgreSQL行级安全疑问：UPDATE时USING子句为何触发检查？

问题分析与误解点 #

你的问题核心是对PostgreSQL行级安全（RLS）中UPDATE策略的USING和WITH CHECK作用，以及策略拆分后的默认行为理解有误：

1. 拆分UPDATE策略时忽略USING条件：
   当你单独创建UPDATE策略只指定WITH CHECK(true)时，PostgreSQL会默认将该策略的USING条件设为false——这意味着没有任何行符合被更新的筛选条件，所以执行UPDATE会返回“更新0行”的结果。

2. FOR ALL策略搭配RETURNING的隐式检查：
   你第一次使用FOR ALL策略时，若执行UPDATE时带了RETURNING子句，返回的新行必须满足SELECT策略的USING条件（deleted_at is null），但更新后的行deleted_at不为null，因此触发“new row violates row-level security policy”报错。这并非UPDATE的WITH CHECK限制，而是RETURNING本质是对新行执行SELECT，需要符合SELECT策略的可见性规则。

正确的策略配置 #

要实现“隐藏已删除记录 + 允许标记记录为已删除”的目标，只需针对性创建两个策略：

1. 创建SELECT策略，隐藏已删除行：

   CREATE POLICY select_active_persons ON person
       FOR SELECT
       USING (deleted_at IS NULL);
   

2. 创建UPDATE策略，允许更新未删除行并标记为已删除：

   CREATE POLICY update_mark_deleted ON person
       FOR UPDATE
       USING (deleted_at IS NULL)  -- 仅未删除的行可被更新
       WITH CHECK (true);          -- 允许将行更新为任意状态（包括已删除）
   

   如果你想更严格地限制只能将未删除行标记为已删除（禁止反向操作），可以把WITH CHECK改为：

   WITH CHECK (deleted_at IS NOT NULL);
   

3. 确保权限配置正确：
   确认已授予tester角色必要的操作权限：

   GRANT SELECT, UPDATE ON person TO tester;
   

验证效果 #

切换到tester角色执行以下语句：

-- 标记Alice为已删除，无RETURNING则正常执行
UPDATE person SET deleted_at = NOW() WHERE name = 'Alice';

-- 若需返回更新后的行，默认会因为新行不符合SELECT策略而报错，这是预期行为
-- 若必须返回，可考虑调整SELECT策略或使用SECURITY DEFINER函数（非必要不推荐）

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