Supabase行级安全策略：软删除更新与查询可见性冲突解决

场景与现有配置 #

表结构 #

• workspaces：存储工作区信息，字段包括id(uuid)、name(varchar(255))、slug(varchar(255))、is_deleted(boolean，默认false)，通过将is_deleted设为true实现软删除。
• profiles_workspaces：用户-工作区关联表，字段为profile_id(uuid)、workspace_id(uuid)，其中profile_id与Supabaseauth.users.id一一对应。

现有RLS策略 #

1. workspaces表SELECT策略：仅允许已认证用户查看自己加入的未删除工作区

create policy "Enable SELECT for the workspace their user is added to"
on "public"."workspaces"
as permissive
for select
to authenticated
USING (id IN (SELECT profiles_workspaces.workspace_id FROM profiles_workspaces) AND is_deleted is FALSE) 

2. workspaces表UPDATE策略：允许已认证用户更新可见的工作区

create policy "Enable UPDATE for the workspace their user is added to" 
on "public"."workspaces"
as permissive 
for update
to authenticated
using (true) with check (true);

3. profiles_workspaces表SELECT策略：仅允许用户查看自己的工作区关联记录

create policy "Enable SELECT for the workspace list their user is added to"
on "public"."profiles_workspaces"
as permissive
for select
to authenticated
using (
    (profile_id = (auth.jwt()->>'sub')::uuid)
);

遇到的问题 #

手动在数据库中设置is_deleted = true时，客户端无法再查询到该工作区，符合预期；但通过Supabase执行UPDATE操作将is_deleted设为true时，会触发RLS策略违反错误。移除SELECT策略中的AND is_deleted is FALSE后，更新操作可正常执行。

需求：既要允许用户通过UPDATE将is_deleted设为true完成软删除，又要通过RLS严格控制，禁止用户查看is_deleted = true的工作区（拒绝仅靠客户端过滤，防止用户修改API payload绕过限制）。

解决方案 #

该问题源于PostgreSQL的RLS策略执行逻辑bug，通过新增辅助字段+触发器的方式可规避冲突，具体步骤如下：

1. 为workspaces表添加is_visible布尔字段（默认值设为true）

ALTER TABLE public.workspaces ADD COLUMN is_visible boolean DEFAULT true;

2. 修改workspaces的SELECT策略，用is_visible IS TRUE替代原有的is_deleted IS FALSE条件

ALTER POLICY "Enable SELECT for the workspace their user is added to"
ON public.workspaces
FOR SELECT
USING (id IN (SELECT profiles_workspaces.workspace_id FROM profiles_workspaces) AND is_visible IS TRUE);

3. 创建处理工作区删除状态的函数

CREATE OR REPLACE FUNCTION public.handle_workspace_deletion()
 RETURNS trigger
 LANGUAGE plpgsql
 SECURITY DEFINER set search_path = public
AS $function$
    begin
        IF new.is_deleted is true and old.is_visible is true then
        update public.workspaces set is_visible = false where id = new.id;
        end if;
        return new;
    end;
$function$
;

4. 创建AFTER UPDATE触发器（必须使用AFTER类型，BEFORE类型会触发相同的RLS策略错误）

create trigger on_workspace_deleted
  after update on public.workspaces
  for each row execute procedure public.handle_workspace_deletion();

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