Patrick Toal
108c042921
fix(edge-fn): use user.id instead of claims.sub; fixes 500s and false cert_required fix(migrations): drop broad reservations SELECT policy; add reservation_slots view with security_invoker=false fix(tests): correct weekSlot() keys from start/end to start_time/end_time fix(tests): spread overlap test slots across separate ISO weeks fix(tests): update e2e assertion to match actual authenticated home text fix(app): hide IonMenu before user is authenticated feat(dx): add test:all script running unit, integration, and e2e in sequence docs(claude-md): document SELinux fix, Edge Function auth pattern, security_invoker behaviour
77 lines
3.3 KiB
PL/PgSQL
77 lines
3.3 KiB
PL/PgSQL
-- Fix infinite recursion in members RLS.
|
|
--
|
|
-- The original "Admins can read all members" and "Admins can manage all members"
|
|
-- policies check role by querying members itself, which causes infinite recursion
|
|
-- when any authenticated user accesses the members table.
|
|
--
|
|
-- Solution: SECURITY DEFINER helper functions that bypass RLS to check the caller's
|
|
-- role, then reference those functions from the policies.
|
|
|
|
create or replace function public.current_user_role()
|
|
returns text
|
|
language sql
|
|
security definer
|
|
stable
|
|
set search_path = public
|
|
as $$
|
|
select role from public.members where user_id = auth.uid() limit 1;
|
|
$$;
|
|
|
|
create or replace function public.current_user_has_role(roles text[])
|
|
returns boolean
|
|
language sql
|
|
security definer
|
|
stable
|
|
set search_path = public
|
|
as $$
|
|
select exists (
|
|
select 1 from public.members
|
|
where user_id = auth.uid() and role = any(roles)
|
|
);
|
|
$$;
|
|
|
|
-- Drop the recursive policies on members
|
|
drop policy if exists "Admins can read all members" on public.members;
|
|
drop policy if exists "Admins can manage all members" on public.members;
|
|
|
|
-- Replace with non-recursive equivalents using the SECURITY DEFINER helper
|
|
create policy "Admins can read all members" on public.members
|
|
for select using (public.current_user_has_role(array['admin', 'boatswain', 'instructor']));
|
|
|
|
create policy "Admins can manage all members" on public.members
|
|
for all using (public.current_user_has_role(array['admin']));
|
|
|
|
-- Also fix all other tables that query members inline (same recursion risk)
|
|
drop policy if exists "Admins can manage boats" on public.boats;
|
|
create policy "Admins can manage boats" on public.boats
|
|
for all using (public.current_user_has_role(array['admin', 'boatswain']));
|
|
|
|
drop policy if exists "Admins can manage interval templates" on public.interval_templates;
|
|
create policy "Admins can manage interval templates" on public.interval_templates
|
|
for all using (public.current_user_has_role(array['admin', 'boatswain']));
|
|
|
|
drop policy if exists "Admins can manage intervals" on public.intervals;
|
|
create policy "Admins can manage intervals" on public.intervals
|
|
for all using (public.current_user_has_role(array['admin', 'boatswain']));
|
|
|
|
drop policy if exists "Admins can read all reservations" on public.reservations;
|
|
create policy "Admins can read all reservations" on public.reservations
|
|
for select using (public.current_user_has_role(array['admin', 'boatswain']));
|
|
|
|
drop policy if exists "Admins can manage all reservations" on public.reservations;
|
|
create policy "Admins can manage all reservations" on public.reservations
|
|
for all using (public.current_user_has_role(array['admin', 'boatswain']));
|
|
|
|
drop policy if exists "Admins can manage reference docs" on public.reference_docs;
|
|
create policy "Admins can manage reference docs" on public.reference_docs
|
|
for all using (public.current_user_has_role(array['admin']));
|
|
|
|
-- booking_config and holidays policies (added in later migration, same pattern)
|
|
drop policy if exists "Admins can manage booking config" on public.booking_config;
|
|
create policy "Admins can manage booking config" on public.booking_config
|
|
for all using (public.current_user_has_role(array['admin']));
|
|
|
|
drop policy if exists "Admins can manage holidays" on public.holidays;
|
|
create policy "Admins can manage holidays" on public.holidays
|
|
for all using (public.current_user_has_role(array['admin']));
|