SupabaseS
Supabase•6mo 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()
  )
);



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()
  )
);


{
  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?
Was this page helpful?