Hey everyone, question on RLS policies. I have two types of users, regular and admin. Regular users can manage their own records only, while admins can manage all records. I was able to do this on a records table with the following policy. (uid() IN ( SELECT profiles.id FROM profiles WHERE (profiles.is_admin = true))) However this doesn't work for the profiles table. Any ideas on what I'm doing wrong?

Hello @victorwhiskey! This thread has been automatically created from your message in #843999948717555735 a few seconds ago. We have already mentioned the @User so that they can see your message and help you as soon as possible! Want to unsubscribe from this thread? Right-click the thread in Discord (or use the ``...`` menu) and select "Leave Thread" to unsubscribe from future updates. Want to change the title? Use the ``/title`` command! We have solved your problem? Click the button below to archive it.

The policy on Admins is as follows

You can’t have RLS do a select on its own table which then runs RLS again, recursively.

victorwhiskey (2022-05-16)

From searching recursion in top search field above… https://discord.com/channels/839993398554656828/965276401462812763/965303534620930048

You could potentially simplify it to this on the profiles table:

sql
(id === uid() OR is_admin)

This requires that your profile table has an

id

column which is set to the corresponding UID record in the

auth.users

table, and an

is_admin

boolean column Conditions output if we look at it on an individual row basis: - ID matches, user is admin: Can access all rows - ID matches, user is not admin: Can access this row - ID doesn't match, user is admin: Can access all rows - ID doesn't match, user is not admin: Can not access this row

This is what I need, I'll give it a try. Thanks!

You're better off creating a table in the public schema like public.user_data or something like that

That was the intent for the profiles table, but apparently I can't have the is_admin column there

I have some info on how to do that...then it gets stored in the auth.users record in the app_metadata field

seems like that might be the best way to do it

You can use this to set the field: https://supabase.com/docs/reference/javascript/auth-api-updateuserbyid#updates-a-users-app_metadata

Cool thanks, I'll give that a try!

Then you can get the field with something like this:

CREATE OR REPLACE FUNCTION auth.get_claims() RETURNS "jsonb"
    LANGUAGE "sql" STABLE
    AS $$
  select 
      coalesce(nullif(current_setting('request.jwt.claims', true), '')::jsonb -> 'app_metadata', '{}'::jsonb)::jsonb
$$;