Row-Level Security recursion on trip membership
Problem
Trip data is shared with collaborators through a trip_members table, and the RLS policy on that table needed to check trip membership to decide what a user could see.
Cause
A self-referencing RLS policy: evaluating the policy on trip_members required querying trip_members again, which re-triggered the same policy and recursed.
Fix
Extracted the membership check into a standalone is_trip_member() SQL function marked SECURITY DEFINER, which runs with the function owner's privileges instead of re-triggering the calling policy. search_path is pinned to public so the function cannot be redirected to an attacker-controlled schema.
Why this fix
A SECURITY DEFINER function was the minimal fix that kept RLS as the real enforcement boundary — loosening the policy or moving the check into application code would have quietly reopened the access-control gap RLS exists to close.
create or replace function public.is_trip_member(p_trip_id uuid, p_user_id uuid)
returns boolean
language sql stable security definer
set search_path = public
as $$
select exists (
select 1 from public.trips t
where t.id = p_trip_id and (t.user_id = p_user_id or t.created_by = p_user_id)
) or exists (
select 1 from public.trip_members tm
where tm.trip_id = p_trip_id and tm.user_id = p_user_id and tm.status = 'accepted'
);
$$;