I have a table Account(id, status, owner) with the following RLS policies:
For SELECT:
CREATE POLICY "Allow users to select active accounts" ON "public"."Account"
AS PERMISSIVE FOR SELECT
TO public
USING ((owner = auth.uid()) AND (status = 'active'))
For UPDATE:
CREATE POLICY "Allow users to update their own accounts" ON "public"."Account"
AS PERMISSIVE FOR UPDATE
TO public
USING ((owner = auth.uid()))
WITH CHECK ((owner = auth.uid()))
Selecting works fine, but when I try updating a record to status='deleted' I get an RLS violation error. I am using the JS SDK (latest version) for this update with this code:
await supabase.from("Account").update({"status":"deleted"}).eq('id', id)
If I change the SELECT policy and remove the condition AND (status = 'active') -- then the update works. So it seems that this is what's triggering the issue. However, as I am not selecting with the update (and it should return minimal by default), I don't understand why it's causing this issue. What am I missing here?