Supabase PSQL row level security policy for update vs insert

Question

I have a workspaces and profiles_workspaces (cols = worspace_id, profile_id) table and want to implement a soft-delete, so workspaces has a is_deleted boolean column. So for deletion I'd just do an update call to workspaces to set is_deleted to true.

The SELECT policy on workspaces is USING (id IN (SELECT profiles_workspaces.workspace_id FROM profiles_workspaces) AND is_deleted is FALSE) to allow users to only see non-deleted workspaces they are added to.

The UPDATE policy is simply true for both USING and CHECK since for now I'm good to let someone update as long as they can see it.

When I set that is_deleted bool on a workspace row manually in the DB all is good. The client doesn't see that workspace anymore. However, when I want to "delete" a workspace supabase won't perform the update, saying it violates a RLS policy.

When I remove the AND is_deleted is FALSE from the SELECT policy I can do updates no problem.

So my question is: How do I allow people to update the is_deleted field to true without letting them see workspaces where is_deleted is true? I know I can just filter the supabase select statement but then a user could just hit the API directly and see "deleted" workspaces they were added on to before by altering the API payload, so not letting those rows be seen via RLS seems far more simple.

Edit: Full Policy Statements:

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) 

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

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

Table schemas

workspaces

• id uuid
• name varchar(255)
• slug varchar(255)
• is_deleted boolean default false

profiles_workspaces

• profile_id uuid
• workspace_id uuid

And the profiles.id is the same as supabase's auth.users.id, in case anyone's wondering about the select policy for profiles_workspaces.

EDIT 2 Workaround

Looks like it's a PSQL bug (thank you LaurenzAlbe!). So here's a workaround that does the trick. Not super clean but it works.

1. I added the is_visible boolean column to workspaces
2. Replace is_deleted IS FALSE in the SELECT policy with is_visible IS TRUE
3. Add this function

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. Add trigger

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

Note it has to be AFTER update not BEFORE. When I tried the function to run on BEFORE UPDATE (with the THEN being new.is_visible = false;) I would get the same RLS policy error when trying to update is_deleted on a workspace. However the AFTER UPDATE function works!

Answer 1

I was myself surprised that a FOR SELECT policy should be used to check new rows and cause an error, so I asked the mailing list. It seems that this behavior is intentional, and the reasons given were

• it would be surprising if you could perform an UPDATE, but the updated row version seemingly vanished, so row level security should prevent that

• if you could update a row so that you can no longer see the new version, you could trigger a constraint conflict with a row that you cannot see, which allowed you to gain information about those invisible rows

You will have to use a workaround.