RLS: allow to select all team members of teams that user is also part of

[b2m9]

Hi Supabase community, I need your help to wrap my head around an RLS statement.

I have a users and teams table and use members to express membership between those two tables. Each user can be part of multiple teams. The members table is simple:

| id | team_id | user_id |
| 1  | 1       | 1       |
| 2  | 2       | 1       |
| 3  | 2       | 2       |
| 4  | 3       | 2       |

My question is: how to restrict a user with uid() is only able to select rows of teams that they are also part of? In the example above, they (user_id = 1) should be able to get all members of team 1 and 2 (first 3 rows) but not the members of team 3.

I played around with something like this:

team_id in (select team_id from members where auth.uid() = user_id)

But it just results in an error saying infinite recursion detected in policy for relation.

Any help is appreciated 🙏

[praj18]

Have you checked out discussion #811? This seems very similar to what you're facing

[b2m9]

The issue is that the naive solution causes the following error: infinite recursion detected in policy for relation

team_id in (select team_id from members where auth.uid() = user_id)

Now, it seems that using a security definer function works around this issue - for a reason I don't fully understand (it seems that function bypass RLS, hence no recursion). To stick with the example from above, you can solve the problem like this:

CREATE OR REPLACE FUNCTION get_teams_for_user(user_id BIGINT)
RETURNS SETOF BIGINT
LANGUAGE sql
STABLE
SECURITY DEFINER
SET search_path = public
AS $$
  SELECT team_id from members where user_id = $1
$$;

All it does is to outsource the select statement from the statement above. Adjust the data types accordingly. The policy then looks like this:

CREATE POLICY "Select all memberships of all teams user is part of" ON public.members
FOR SELECT USING (
  team_id IN (SELECT get_teams_for_user(uid()))
);

Thanks to @praj18 for pointing me in the right direction.

[walton-alex]

Really helpful - I had the same issue. Thank you!