Supabase PSQL 更新与插入的行级安全策略

问：

我有一个 and （cols = worspace_id， profile_id） 表，并且想要实现软删除，所以有一个布尔列。因此，对于删除，我只需执行更新调用以设置为 。workspacesprofiles_workspacesworkspacesis_deletedworkspacesis_deletedtrue

该策略允许用户仅查看他们添加到的未删除工作区。SELECTworkspacesUSING (id IN (SELECT profiles_workspaces.workspace_id FROM profiles_workspaces) AND is_deleted is FALSE)

该政策仅适用于两者，因为现在我很高兴让某人更新，只要他们能看到它。UPDATEtrueUSINGCHECK

当我在数据库中的工作区行上手动设置该布尔值时，一切都很好。客户端不再看到该工作区。但是，当我想“删除”工作区时，supabase 不会执行更新，说它违反了 RLS 策略。is_deleted

当我从策略中删除时，我可以毫无问题地进行更新。AND is_deleted is FALSESELECT

所以我的问题是：我如何允许人们更新字段，而不让他们看到工作区，其中为 true？我知道我可以过滤 supabase select 语句，但用户可以直接点击 API 并通过更改 API 有效负载来查看他们之前添加到的“已删除”工作区，因此不让这些行通过 RLS 查看似乎要简单得多。is_deletedtrueis_deleted

编辑：完整的政策声明：

工作区 - 选择

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) 

工作区 - 更新

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);

profiles_workspaces - 选择

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)
);

表架构

工作区

• ID uuid
• 名称 varchar（255）
• 蛞蝓瓦查尔（255）
• is_deleted boolean default false

profiles_workspaces

• profile_id uuid
• workspace_id uuid

并且与 supabase 的相同，以防有人想知道 的选择策略。profiles.idauth.users.idprofiles_workspaces

编辑 2 解决方法

看起来这是一个PSQL错误（谢谢LaurenzAlbe！因此，这里有一个解决方法可以解决问题。不是超级干净，但它有效。

1. 我将布尔列添加到is_visibleworkspaces
2. 在 SELECT 策略中替换为is_deleted IS FALSEis_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. 添加触发器

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

请注意，它必须是 AFTER 更新而不是 BEFORE。当我尝试在 BEFORE UPDATE 上运行该函数时（使用 ），当我尝试在工作区上更新时，我会遇到相同的 RLS 策略错误。但是，该功能有效！THENnew.is_visible = false;is_deletedAFTER UPDATE