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))
);
Chester T. is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
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_usertable for
filtering, avoiding recursive queries on thetripsoruserstables. - 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
userspolicy uses a self-join on the
trip_usertable 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;