SELECT行级安全策略为何阻止PATCH API更新is_deleted字段？

我在customer表中用is_deleted字段实现软删除，为了避免每次查询都手动添加is_deleted = false条件，创建了如下SELECT行级安全策略：

CREATE POLICY "can view customer" ON "public"."customer" USING (((is_deleted = false) AND (EXISTS ( SELECT 1 FROM admin_user WHERE (admin_user.uid = uid())))));

但执行下面的软删除更新请求时，触发了行级安全违规错误：

export const softDeleteCustomer = async(id: number) => {
    await supabaseClient.from('customer').update({
      is_deleted: true
    }).eq('id', id);
}

错误信息：

{
  "code":"42501",
  "details":null,
  "hint":null,
  "message":"new row violates row-level security policy for table \"customer\""
}

移除策略里的is_deleted = false条件后更新正常，但我搞不懂为什么SELECT策略会影响更新操作，求解答。

这是因为PostgreSQL的行级安全（RLS）处理UPDATE操作时会做两层检查：

1. USING检查：验证你是否有权限访问要更新的旧行（这里你的SELECT策略USING条件会被复用，确保旧行是未删除状态且你是管理员，这部分没问题）
2. WITH CHECK检查：验证更新后的新行是否符合表的RLS策略要求

你只创建了SELECT策略，没有专门定义UPDATE策略，PostgreSQL会默认把SELECT策略的USING条件作为UPDATE的WITH CHECK规则。当你把is_deleted改成true后，新行不满足is_deleted = false的条件，自然触发了违规。

解决办法很简单，专门创建一个针对UPDATE的策略，允许管理员执行软删除：

CREATE POLICY "can soft delete customer" ON "public"."customer"
FOR UPDATE USING (EXISTS ( SELECT 1 FROM admin_user WHERE admin_user.uid = uid()))
WITH CHECK (EXISTS ( SELECT 1 FROM admin_user WHERE admin_user.uid = uid()));

如果想更精准限制操作范围，只允许修改is_deleted字段：

CREATE POLICY "can soft delete customer" ON "public"."customer"
FOR UPDATE OF is_deleted
USING (EXISTS ( SELECT 1 FROM admin_user WHERE admin_user.uid = uid()))
WITH CHECK (is_deleted = true);

这样既保留了SELECT策略对未删除行的过滤逻辑，又能正常执行软删除操作。

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