RLS select is filtering rows by the column contact_deleted... so I can't so updates to that column?

[malalecherocks]

Imagine we have a table called contact that has a column contact_deleted and in RLS SELECT we establish the rule that only allows contact_deleted = false

this way when our users do select it will only return rows of the contacts that haven't been deleted.

My problem is that when I try to do an UPDATE to establish the column contact_deleted to true, it gives me an error, it doesn't allow me to update it because as far as I read updates depends on selects.

I came to the conclusion that what I was trying to do is not possible, is that correct? Or maybe I was doing something wrong?

[GaryAustin1]

What are your update RLS policies? You would need using policy (on the current row) to not check for contact_deleted. The with check policy could be the same (it defaults to the same if not specified) as you are modifying the contact_deleted value.

If it is the select policy, then setting "returning minimal" would not do the select:
https://supabase.com/docs/reference/javascript/update#parameters

[malalecherocks]

It makes sense that if you are going to update and the update itself needs to use a select under the hood it makes sense that the user should have permission to do that select. But my example goes further...

the thing is that that select we are trying to do: "deleted is not false" is met by the user, but the problem is that seems it's entering in a conflict with the update when the column we are trying to update its being update with the value "true"

so weird... thanks anyway for taking a look at it, if anyone knows more about this please through some light

[GaryAustin1]

FWIW, I agree with your thinking.

[GaryAustin1]

So using an rpc call to a security definer function (bypasses RLS) to toggle your flag does not seem like a huge amount of work, versus 2nd flags and such.

[malalecherocks]

But if that RPC function bypass RLS anyone could call it and start setting that flag for all the rows… I think the solution I’m thinking could be the best would be…

for that specific update I call my api on my server, I check the user auth getting the user using:

** const { user } = await supabase.auth.api.getUserByCookie(req)**

although I read is having lots of issues specially with sveltekit since January.

once I have the user I can now confirm his user_id is authenticated and now I can just simoly bypass the RLS using the supabase object with the secret key.

is this correct? That’s what I was going to do today

[GaryAustin1]

In the rpc function you check using auth.uid() or auth.role for security how you like. Similar to RLS then do the insert. It is a very common thing to do.