RLS compare with ROLE for permission

Hello, how can i compare the ROLE from my profile table to give access to data? I have a profile table like (example):

    id uuid references auth.users on delete cascade not null,
    role user_role not null default 'guest',

    id uuid references auth.users on delete cascade not null,
    role user_role not null default 'guest',

i have 2 account 1 guest and 1 admin, i set the policy that members can see only their own data, but i want a policy to see if its admin (role) can see all, is its possible? how? i tried like

using ((select role from public.profiles where id = auth.uid()) = 'admin')

using ((select role from public.profiles where id = auth.uid()) = 'admin')

no success

18 Replies

garyaustin•4d ago

That should work if you meet RLS on profiles. If role is a an enum type you may need to cast it. It would be faster performance wise to do a function the uses auth.uid() internally, is security definer, and does that select, and returns the user role as a string or enum.

UssaguiOP•4d ago

Thanks for the reply, I'm really green at this so not sure what to do ... yes it's an enum, there is my schema (removed some fields to simplify)

drop table if exists profiles;
TRUNCATE auth.users cascade;

create type user_role  as enum ('admin', 'guest');


create table 
  profiles (
    id uuid references auth.users on delete cascade not null,
...
    role user_role not null default 'guest',

    primary key (id)
  );

drop table if exists profiles;
TRUNCATE auth.users cascade;

create type user_role  as enum ('admin', 'guest');


create table 
  profiles (
    id uuid references auth.users on delete cascade not null,
...
    role user_role not null default 'guest',

    primary key (id)
  );

It would be faster performance wise to do a function the uses auth.uid() internally, is security definer, and does that select, and returns the user role as a string or enum.

can you explain me better / guide me please?

garyaustin•4d ago

This shows a similar example... https://supabase.com/docs/guides/database/postgres/row-level-security#use-security-definer-functions Do you meet select RLS on profiles?

UssaguiOP•4d ago

what do you mean with Do you meet select RLS on profiles? ?

garyaustin•4d ago

Your policy you show does a select from profiles. In order to that the user has to meet select RLS for that row.

UssaguiOP•4d ago

you mean if the user is admin?

[{"idx":0,"id":"907869d3-8561-4c46-8f83-9e1ef63dd042","created_at":"2025-11-25 21:54:07.501312+00","username":"testaccount1","full_name":"Test Account","bio":"The main testing account","dark_mode":false,"avatar_url":"","role":"admin"}]

[{"idx":0,"id":"907869d3-8561-4c46-8f83-9e1ef63dd042","created_at":"2025-11-25 21:54:07.501312+00","username":"testaccount1","full_name":"Test Account","bio":"The main testing account","dark_mode":false,"avatar_url":"","role":"admin"}]

still not working from the guide ... but if i use the default "can see own data" from profile it works

UssaguiOP•4d ago

UssaguiOP•4d ago

this one works

garyaustin•4d ago

Yes the policy you show there allows the user to read their own row. That is required for your other policy to work.

UssaguiOP•4d ago

ok so this one is ok and i will keep it there, correct? now i will add the other one

garyaustin•4d ago

Well it is OK if you want the user to be able to see his profile row. But it is also required to do the select you do in your first policy you show.

UssaguiOP•4d ago

yes because since its an admin i will have 1 for own profile (when login) and another query to see all profiles (but only admin can do that query) hmmm lemme check if i got it corretly

UssaguiOP•4d ago

like this?

UssaguiOP•4d ago

nvm the title

garyaustin•4d ago

I'm confused now. Was your first policy you showed on profiles or some other table? I assumed it was on some other table.

UssaguiOP•4d ago

no, always profiles

garyaustin•4d ago

Then you will need to use a security definer function to do the select. You can't select from the same table in a select policy on the table. It causes recursion as it has to have permission to select, but to get it it has to select... I'm out for a bit.

UssaguiOP•4d ago

ow, then i will sleep, its late here, we can continue tomorrow if you dont mind ... thanks!

Gaming

Programming

RLS compare with ROLE for permission

Did you find this page helpful?