RLS policy using an OR clause

[jordanpapaleo]

I have a profile table that I populate when a user is created. The RLS policy is set to only allow select for users. That works fine. The issue I am getting is when I use a OR in the using. It is not being respected.

alter policy "Enable update for users based on email"
on "public"."profiles"
to public
using (
  ((auth.uid() = id) OR ((role = 'admin'::text) OR (role = 'team'::text)))
);

I have asked around at work and I have used AI. The general consensus is that this policy should return the the user profile for the user requesting it or it should return all the profiles for users that have a role in the profile table of 'admin' or 'team'.

AI suggested that Supabase may have something that going on preventing the OR from working correct but I have no way to validate that.

Kind of in a big pickle right now and not sure where to go.

[GaryAustin1]

The code you show has nothing to do with Supabase except for the auth.uid() function. The rest is all Postgres.

What is role though? That would be a column in your profiles table I guess. But not sure how that helps you in this context as you would get rows with the role column = to admin or team.

Also for update you have to meet the select policy also.

[jordanpapaleo]

Here is the table

[jordanpapaleo]

I may just have to go back and set it to using true then change all my queries :(

[GaryAustin1]

So your policy will show a user's own row if auth.uid() matches id. Then it will also show any row with 'admin' in the role column or 'team' in the role column. Not sure how those are useful to you. It has nothing to do with the current user executing the call, but with the a fixed compare of that column to a string.

Maybe say what your goal is. If it is you have users with that role access all rows or something like that then you would need a policy that actually queries the table itself based on auth.uid() to see if the user has that role and then allow access to all rows based on that. That would be done with a security definer function.

Also you are not clear on what policy this is for. Update as in the title? ALL as you don't specify? Select? You really should have a seperate policy for each operation with a FOR clause, but the UI might be handling that for you.

[jordanpapaleo]

Oh man, so I am totally backwards on this then.

Goal:

1. Allow a user to only be able to select and update their own row
2. Allow users with the role of admin or team to be able to select all rows

[GaryAustin1]

There are many examples of that and an AI might be able to get you close to that.
https://supabase.com/docs/guides/database/postgres/row-level-security#use-security-definer-functions
Shows a partial example of what you are trying to do.

[jordanpapaleo]

Thank you Gary. I'll try that first thing in the morning.

AI is the one who got me in trouble but we will work it out ;)