moviedice/supabase/migrations/00002_rls_policies.sql

-- 00002_rls_policies.sql
-- MovieDice: Row Level Security policies for all tables

-- =============================================================================
-- Enable RLS on all tables
-- =============================================================================
ALTER TABLE public.users ENABLE ROW LEVEL SECURITY;
ALTER TABLE public.groups ENABLE ROW LEVEL SECURITY;
ALTER TABLE public.group_members ENABLE ROW LEVEL SECURITY;
ALTER TABLE public.movies ENABLE ROW LEVEL SECURITY;
ALTER TABLE public.landing_reel_posters ENABLE ROW LEVEL SECURITY;

-- =============================================================================
-- users: SELECT/UPDATE own row only
-- =============================================================================
CREATE POLICY users_select_own ON public.users
  FOR SELECT USING (auth.uid() = id);

CREATE POLICY users_update_own ON public.users
  FOR UPDATE USING (auth.uid() = id)
  WITH CHECK (auth.uid() = id);

-- =============================================================================
-- groups: SELECT only if user is a member
-- =============================================================================
CREATE POLICY groups_select_member ON public.groups
  FOR SELECT USING (
    EXISTS (
      SELECT 1 FROM public.group_members
      WHERE group_members.group_id = groups.id
        AND group_members.user_id = auth.uid()
    )
  );

-- =============================================================================
-- group_members: complex policies
-- =============================================================================

-- SELECT: can see members of groups you belong to
CREATE POLICY group_members_select ON public.group_members
  FOR SELECT USING (
    EXISTS (
      SELECT 1 FROM public.group_members AS gm
      WHERE gm.group_id = group_members.group_id
        AND gm.user_id = auth.uid()
    )
  );

-- INSERT: blocked for anon users (server-side via service role key only)
-- No INSERT policy = denied by default with RLS enabled

-- DELETE: admin of the group OR self (leaving)
CREATE POLICY group_members_delete ON public.group_members
  FOR DELETE USING (
    group_members.user_id = auth.uid()
    OR EXISTS (
      SELECT 1 FROM public.group_members AS gm
      WHERE gm.group_id = group_members.group_id
        AND gm.user_id = auth.uid()
        AND gm.role = 'admin'
    )
  );

-- UPDATE: prevent role escalation
-- Only admins of the group can update roles, and only they can set role to 'admin'
CREATE POLICY group_members_update ON public.group_members
  FOR UPDATE USING (
    EXISTS (
      SELECT 1 FROM public.group_members AS gm
      WHERE gm.group_id = group_members.group_id
        AND gm.user_id = auth.uid()
        AND gm.role = 'admin'
    )
  )
  WITH CHECK (
    -- Only allow setting role to 'admin' if the current user is an admin of this group
    role = 'member'
    OR EXISTS (
      SELECT 1 FROM public.group_members AS gm
      WHERE gm.group_id = group_members.group_id
        AND gm.user_id = auth.uid()
        AND gm.role = 'admin'
    )
  );

-- =============================================================================
-- movies: full CRUD for group members
-- =============================================================================

-- SELECT: only if member of the owning group
CREATE POLICY movies_select ON public.movies
  FOR SELECT USING (
    EXISTS (
      SELECT 1 FROM public.group_members
      WHERE group_members.group_id = movies.group_id
        AND group_members.user_id = auth.uid()
    )
  );

-- INSERT: member of group + added_by must be auth.uid()
CREATE POLICY movies_insert ON public.movies
  FOR INSERT WITH CHECK (
    added_by = auth.uid()
    AND EXISTS (
      SELECT 1 FROM public.group_members
      WHERE group_members.group_id = movies.group_id
        AND group_members.user_id = auth.uid()
    )
  );

-- UPDATE: member of group + cannot change added_by
CREATE POLICY movies_update ON public.movies
  FOR UPDATE USING (
    EXISTS (
      SELECT 1 FROM public.group_members
      WHERE group_members.group_id = movies.group_id
        AND group_members.user_id = auth.uid()
    )
  )
  WITH CHECK (
    added_by IS NOT DISTINCT FROM (
      SELECT m.added_by FROM public.movies m WHERE m.id = movies.id
    )
    AND EXISTS (
      SELECT 1 FROM public.group_members
      WHERE group_members.group_id = movies.group_id
        AND group_members.user_id = auth.uid()
    )
  );

-- DELETE: member of the owning group
CREATE POLICY movies_delete ON public.movies
  FOR DELETE USING (
    EXISTS (
      SELECT 1 FROM public.group_members
      WHERE group_members.group_id = movies.group_id
        AND group_members.user_id = auth.uid()
    )
  );

-- =============================================================================
-- landing_reel_posters: public read, no client write
-- =============================================================================
CREATE POLICY landing_reel_posters_select ON public.landing_reel_posters
  FOR SELECT USING (true);

-- No INSERT/UPDATE/DELETE policies = service role only (RLS bypassed by service role key)