S
Supabase•8mo ago
Strange

Quick question about RLS join policies

Hello! I remember seeing a post somewhere about increasing the performance of RLS by improving the performance of the queries, and it went something like this with joins: Bad: 
using (
(select auth.uid()) in (
  select user_id from public.access_control
  where role = 'admin'
)
)
using (
(select auth.uid()) in (
  select user_id from public.access_control
  where role = 'admin'
)
)
Good: 
using (
  select user_id from public.access_control
  where user_id = (select auth.uid()) and role = 'admin'
)
)
using (
  select user_id from public.access_control
  where user_id = (select auth.uid()) and role = 'admin'
)
)
6 Replies
Strange
StrangeOP•8mo ago
Is this accurate?
garyaustin
garyaustin•8mo ago
Strange
StrangeOP•8mo ago
YES that's the doc. thank you!
garyaustin
garyaustin•8mo ago
I would write a security definer function to return if the user is an admin. Then wrap that function in a select.
Strange
StrangeOP•8mo ago
this is the section i was looking for https://supabase.com/docs/guides/database/postgres/row-level-security#minimize-joins. i knew i read it somewhere, i just couldnt find it
garyaustin
garyaustin•8mo ago
You have auth.uid() the function can use to search the role table. So you pass it nothing I've done the same thing passing it a role and returning the user is that role or not. For what it is worth: https://github.com/GaryAustin1/custom-properties/blob/main/roles.sql#L23

Did you find this page helpful?