https://supabase.com/ logo
#help
Title
# help
t

tandyman

03/27/2022, 5:54 PM
What is the BEST practice, for having admin users of the software, that have the ability to bypass RLS and have full access to all data? It's a business requirement, and a must.
n

Needle

03/27/2022, 5:54 PM
Hello @tandyman! This thread has been automatically created from your message in #843999948717555735 a ``few seconds ago``. Pinging @User so that they see this as well! 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.
s

Scott P

03/27/2022, 5:59 PM
I would consider adding an
is_admin
column to your public users table (if you have one), or adding that property to the metadata of the user. Your RLS policies would then include an
OR
statement where
is_admin
.
t

tandyman

03/27/2022, 6:01 PM
Ok
Would the syntax look like this? So like
(auth.uid() = agent_id) OR auth.is_admin)
The stripe starter thing that I started building with, did provide a public users table, so yes I just added that column 👍
I keep getting syntax errors...trying to figure out how to write it
s

Scott P

03/27/2022, 6:10 PM
Yeah, something like that:
Copy code
sql
((auth.uid() = agent_id) OR is_admin)
Brackets are important
t

tandyman

03/27/2022, 6:10 PM
((uid() = agent_id) OR (user.is_admin)) fails
s

Scott P

03/27/2022, 6:12 PM
You shouldn't need the
user
on the start of the or check as it's going to look up the column in the table that the policy applies to. Personal preference is to also use
auth.uid()
over just
uid()
. I don't think it'd make a difference, but it ensures it's looking in the correct schema.
t

tandyman

03/27/2022, 6:12 PM
hmm, it says is_admin doesn't exist.
What happens now in the tool, is each time I save, it automatically re-writes auth.uid() to uid(), sadly
user.is_admin
,
auth.is_admin
and
is_admin
all fail. Sometimes it says doesn't exist, or missing a FROM in the policy.
s

Scott P

03/27/2022, 6:17 PM
I've just added the
is_admin
column onto a test project, and both the following allowed me to add the select policy:
Copy code
sql
(is_admin OR (auth.uid() = id))
or
Copy code
sql
((auth.uid() = id) OR is_admin)
For me,
id
is the column where my users ID is stored, but I don't see how calling it
agent_id
would differ
t

tandyman

03/27/2022, 6:19 PM
so, agent_id is just on that table on setting the policy on
So the policy is for a table called client_profiles, and agent_id is a part of that:
Probably because the foreign key is to the auth.users table, instead of the public one....right?
That would make sense to me. I'm going to update that FK.
bah
I still get
is_admin
doesn't exist
even if I simplify the heck out of the policy:
Copy code
CREATE POLICY "only admins can access" ON public.client_profiles FOR SELECT USING (is_admin);
^ that's the preview before I try to hit save policy
Maybe
is_admin
needs to be in auth.users, rather than public.users?
g

garyaustin

03/27/2022, 6:34 PM
Your policy is looking for is_admin in client_profiles
t

tandyman

03/27/2022, 6:34 PM
Right, so how do I access then?
It's strange to me that it works for scott just fine.
g

garyaustin

03/27/2022, 6:35 PM
he put it in the table he was doing RLS on
t

tandyman

03/27/2022, 6:35 PM
ahh
g

garyaustin

03/27/2022, 6:36 PM
You will have to select it, I like putting that in a function with "stable" myself so it acts like auth.uid(), something like isAdmin().
t

tandyman

03/27/2022, 6:38 PM
how in the world do i do that
🙂
^ under here?
g

garyaustin

03/27/2022, 6:40 PM
First just get a OR with select working... Hate to dig into functions at same time..
t

tandyman

03/27/2022, 6:40 PM
when you say I have to select it, I have no idea what you are saying
I'm sorry.
The only templates that supabase gives are just using auth.whatever().
Further, what is interesting is in auth.users, I see a is_super_admin, but I cannot edit anything in the auth.users table in the supabase UI
I was wondering if that is usable to just use that.
g

garyaustin

03/27/2022, 6:55 PM
OR (SELECT is_admin FROM public.users WHERE auth.uid() = id) is probably what you want where the last id is the uuid column in your public.users table. Don't mess with the auth.users table.
If you have RLS on public.users then that will have to have OR (is_admin) to bypass... you don't need select there.
t

tandyman

03/27/2022, 7:03 PM
Nice!
Ok, that works.
Having that recipe/formula I can adjust that repeatedly to what I need. I didn't know you can select within policy rule like that.
g

garyaustin

03/27/2022, 7:04 PM
RLS policy is just an extra where statement
t

tandyman

03/27/2022, 7:05 PM
I see.
So, above those are my policies on public.users table.
It seems to work with those, I didn't need to modify anything. Does that seem right?
g

garyaustin

03/27/2022, 7:07 PM
Yeah, in this case you are the uid for that row so don't need the admin thing there like I had said.
t

tandyman

03/27/2022, 7:07 PM
Ahh, but for other people who are admins, I will need to add the is_admin there.
g

garyaustin

03/27/2022, 7:08 PM
No, cause they will be logged in
t

tandyman

03/27/2022, 7:08 PM
I'm tracking with ya.
g

garyaustin

03/27/2022, 7:09 PM
Not sure why you have a public users table and a profile table. I combine the two, but it does not hurt, just more to deal with.
t

tandyman

03/27/2022, 7:09 PM
client_profiles is something very different...it's a bit ambiguous.
g

garyaustin

03/27/2022, 7:10 PM
K
t

tandyman

03/27/2022, 7:10 PM
Life insurance agents. They have their clients, and they create profiles for them...and it contains all of their info, scenarios, debts, etc.
^ updated
Thx @User again. Also @User also for you getting me 75% of the way there. You guys unblocked me and it's much appreciated.
Community is everything.