S
Supabase•2mo ago
Buzz

How can I achieve this with RLS?

Hi, I have a table called "studios" and a table called "team_members", which have a studio attached to them. I want to have a RLS policy to state that only team members can select their own studios that they're a member of, but then I need a RLS policy to select the team_members which are apart of that studio (therefore making me select the studio), causing an infinite loop I'm now getting a recurrsion error on my RLS policies. Put simply: 1. I want to select my studio as a team member 2. To do this, I need to check if I'm a team member, so I need to select team_members of the studio 3. To do this, I need access to the studio 4. infinite loop Policies: studios SELECT 
create policy "Owner or team member can select studio" on studios
for select using (
  studios.owner = auth.uid()
  or exists (
    select 1 from team_members
    where team_members.studio = studios.id
      and team_members.user_id = auth.uid()
  )
);
create policy "Owner or team member can select studio" on studios
for select using (
  studios.owner = auth.uid()
  or exists (
    select 1 from team_members
    where team_members.studio = studios.id
      and team_members.user_id = auth.uid()
  )
);
team_members SELECT: 
create policy "Owner or team member can select team members" on team_members
for select using (
  exists (
    select 1 from studios
    where studios.id = team_members.studio
      and studios.owner = auth.uid()
  )
  or exists (
    select 1 from team_members as tm2
    where tm2.studio = team_members.studio
      and tm2.user_id = auth.uid()
  )
);
create policy "Owner or team member can select team members" on team_members
for select using (
  exists (
    select 1 from studios
    where studios.id = team_members.studio
      and studios.owner = auth.uid()
  )
  or exists (
    select 1 from team_members as tm2
    where tm2.studio = team_members.studio
      and tm2.user_id = auth.uid()
  )
);
{
  code: '42P17',
  details: null,
  hint: null,
  message: 'infinite recursion detected in policy for relation "studios"'
}
{
  code: '42P17',
  details: null,
  hint: null,
  message: 'infinite recursion detected in policy for relation "studios"'
}
How can this be resolved so team members can safely select all the studios they are a team member of, and also team members can view all other team members?
1 Reply
garyaustin
garyaustin•2mo ago
You need to move select call in the same table’s select policy to a security definer function to avoid RLS on the select call

Did you find this page helpful?