How to properly implement an RLS policy for a many-to-many relationship?

I have a trips table and a users table, along with a trip_user junction table to handle the many-to-many relationship of the trips and users table.

I want to implement an RLS policy that only allows authenticated users to view data of users which are part of the same trip as they are.

I've been trying to implement this in my Supabase app, but I keep running into error:

infinite recursion detected in policy for relation "trip_user"

Here is a snippet of my current implementation.

alter policy "Enable users to view their trips"
on "public"."trips"
to authenticated
using (
(id IN ( SELECT trip_user.trip_id
   FROM trip_user))
);

alter policy "Enable users to view their users that are part of the same trip"
on "public"."trips"
to authenticated
using (
(trip_id IN ( SELECT trip.id
   FROM trip))
);

You’re getting an infinite recursion detected in policy error because the current RLS policies on the trips table and users table directly reference the same tables they are applied to, leading to an infinite loop.

To implement row-level security (RLS) for a many-to-many relationship in PostgreSQL (or Supabase), you need to avoid recursion by relying on the trip_user junction table for filtering logic:

Lets assume:

users Table: User details (id is the primary key).

trips Table: Trip details (id is the primary key).

trip_user Table: Junction table linking trips and users (trip_id, user_id as foreign keys).

RLS Policy for trips:

ALTER POLICY "Enable users to view their trips"
ON public.trips
TO authenticated
USING (
  EXISTS (
    SELECT 1
    FROM public.trip_user
    WHERE trip_user.trip_id = trips.id
      AND trip_user.user_id = auth.uid()
  )
);

RLS Policy for users

ALTER POLICY "Enable users to view users in the same trips"
ON public.users
TO authenticated
USING (
  EXISTS (
    SELECT 1
    FROM public.trip_user AS tu1
    JOIN public.trip_user AS tu2
      ON tu1.trip_id = tu2.trip_id
    WHERE tu1.user_id = auth.uid()
      AND tu2.user_id = users.id
  )
);

RLS Policy for trip_user (Optional - Use this if users need to query the trip_user table):

ALTER POLICY "Enable users to view trip-user relationships for their trips"
ON public.trip_user
TO authenticated
USING (
  EXISTS (
    SELECT 1
    FROM public.trip_user
    WHERE trip_user.trip_id = trip_user.trip_id
      AND trip_user.user_id = auth.uid()
  )
);

Explanation

• Avoid Infinite Recursion: Policies use the trip_user table for filtering, avoiding recursive queries on the trips or users tables.
• Dynamic User Identification: The auth.uid() function (available in Supabase) fetches the current user's ID, ensuring policies are dynamically applied to the authenticated user.
• Self-Join for Shared Trips: The users policy uses a self-join on the trip_user table to identify users who share the same trip as the authenticated user.

Make sure you run this so the RLS is enabled:

ALTER TABLE public.trips ENABLE ROW LEVEL SECURITY;
ALTER TABLE public.users ENABLE ROW LEVEL SECURITY;
ALTER TABLE public.trip_user ENABLE ROW LEVEL SECURITY;