Supabase v5.1 Security Patch (CORRECTED)

-- =====================================================
-- Snappy English - Supabase v5.1 Security Patch (CORRECTED)
-- Run AFTER v4 migration
-- Final hardening: SECURITY DEFINER safety, policy refinements, indexes
-- FIXES: RLS validation, enrollment policies, circle membership, ownership
-- =====================================================

-- =====================================================
-- 1. HARDEN SECURITY DEFINER FUNCTIONS
-- =====================================================

-- Recreate with SET search_path for security and explicit COALESCE
CREATE OR REPLACE FUNCTION public.current_user_email()
RETURNS text
LANGUAGE sql
SECURITY DEFINER
STABLE
SET search_path = public, pg_temp
AS $$
  SELECT u.email
  FROM public.users u
  WHERE u.id = auth.uid()
  LIMIT 1;
$$;

CREATE OR REPLACE FUNCTION public.current_user_is_admin()
RETURNS boolean
LANGUAGE sql
SECURITY DEFINER
STABLE
SET search_path = public, pg_temp
AS $$
  SELECT COALESCE((
    SELECT (u.role = 'admin')
    FROM public.users u
    WHERE u.id = auth.uid()
  ), false);
$$;

-- Set ownership to postgres for security
ALTER FUNCTION public.current_user_email() OWNER TO postgres;
ALTER FUNCTION public.current_user_is_admin() OWNER TO postgres;

-- Revoke and re-grant (idempotent)
REVOKE ALL ON FUNCTION public.current_user_email() FROM PUBLIC;
REVOKE ALL ON FUNCTION public.current_user_is_admin() FROM PUBLIC;
GRANT EXECUTE ON FUNCTION public.current_user_email() TO anon, authenticated;
GRANT EXECUTE ON FUNCTION public.current_user_is_admin() TO anon, authenticated;

-- =====================================================
-- 2. REFINE PUBLIC CONTENT POLICIES
-- =====================================================

-- Most public content already correctly scoped in v4
-- Verified: idioms (approved), grammar_topics, vocabulary, animations
-- leaderboard, crosswords, settings, content_pricing all use anon + authenticated

-- =====================================================
-- 3. ADD MISSING RLS POLICIES (CORRECTED)
-- =====================================================

-- Circle Membership - RESTRICT read to members/creators/admins
DROP POLICY IF EXISTS "circle_membership_read" ON public.circle_membership;
CREATE POLICY "circle_membership_read" ON public.circle_membership
  FOR SELECT TO authenticated
  USING (
    user_email = public.current_user_email()
    OR EXISTS (
      SELECT 1 FROM public.study_circles sc
      WHERE sc.id = circle_membership.circle_id
        AND (sc.creator_email = public.current_user_email() OR public.current_user_is_admin())
    )
    OR public.current_user_is_admin()
  );

-- Circle Membership - allow members to leave (delete their own membership)
DROP POLICY IF EXISTS "circle_membership_delete_self" ON public.circle_membership;
CREATE POLICY "circle_membership_delete_self" ON public.circle_membership
  FOR DELETE TO authenticated
  USING (user_email = public.current_user_email());

-- Class Enrollment - allow teachers to INSERT students
DROP POLICY IF EXISTS "enrollment_teacher_insert" ON public.class_enrollment;
CREATE POLICY "enrollment_teacher_insert" ON public.class_enrollment
  FOR INSERT TO authenticated
  WITH CHECK (
    EXISTS (
      SELECT 1 FROM public.classes c
      WHERE c.id = class_enrollment.class_id
        AND (c.teacher_email = public.current_user_email() OR public.current_user_is_admin())
    ) OR public.current_user_is_admin()
  );

-- Class Enrollment - allow teachers to UPDATE enrollment
DROP POLICY IF EXISTS "enrollment_teacher_update" ON public.class_enrollment;
CREATE POLICY "enrollment_teacher_update" ON public.class_enrollment
  FOR UPDATE TO authenticated
  USING (
    EXISTS (
      SELECT 1 FROM public.classes c
      WHERE c.id = class_enrollment.class_id
        AND (c.teacher_email = public.current_user_email() OR public.current_user_is_admin())
    )
  )
  WITH CHECK (
    EXISTS (
      SELECT 1 FROM public.classes c
      WHERE c.id = class_enrollment.class_id
        AND (c.teacher_email = public.current_user_email() OR public.current_user_is_admin())
    )
  );

-- Class Enrollment - allow teachers to DELETE enrollment
DROP POLICY IF EXISTS "enrollment_teacher_delete" ON public.class_enrollment;
CREATE POLICY "enrollment_teacher_delete" ON public.class_enrollment
  FOR DELETE TO authenticated
  USING (
    EXISTS (
      SELECT 1 FROM public.classes c
      WHERE c.id = class_enrollment.class_id
        AND (c.teacher_email = public.current_user_email() OR public.current_user_is_admin())
    )
  );

-- Assignment Submissions - teacher update WITH CHECK consistency (FIXED)
DROP POLICY IF EXISTS "submissions_teacher_update" ON public.assignment_submissions;
CREATE POLICY "submissions_teacher_update" ON public.assignment_submissions
  FOR UPDATE TO authenticated
  USING (
    EXISTS (
      SELECT 1 FROM public.teacher_assignments ta
      WHERE ta.id = assignment_submissions.assignment_id
        AND ta.teacher_email = public.current_user_email()
    )
  )
  WITH CHECK (
    EXISTS (
      SELECT 1 FROM public.teacher_assignments ta
      WHERE ta.id = assignment_submissions.assignment_id
        AND ta.teacher_email = public.current_user_email()
    )
  );

-- Tutoring Sessions - tutors can read their own sessions
DROP POLICY IF EXISTS "tutoring_tutor_read" ON public.tutoring_sessions;
CREATE POLICY "tutoring_tutor_read" ON public.tutoring_sessions
  FOR SELECT TO authenticated
  USING (
    EXISTS (
      SELECT 1 FROM public.tutors t
      WHERE t.id = tutoring_sessions.tutor_id
        AND t.email = public.current_user_email()
    ) OR public.current_user_is_admin()
  );

-- =====================================================
-- 4. ADDITIONAL PERFORMANCE INDEXES
-- =====================================================

-- Indexes for email-based RLS lookups (critical for performance)
CREATE INDEX IF NOT EXISTS idx_referrals_referrer_email ON public.referrals(referrer_email);
CREATE INDEX IF NOT EXISTS idx_tutoring_sessions_tutor_id ON public.tutoring_sessions(tutor_id);
CREATE INDEX IF NOT EXISTS idx_tutors_email ON public.tutors(email);
CREATE INDEX IF NOT EXISTS idx_tutors_status ON public.tutors(status);
CREATE INDEX IF NOT EXISTS idx_circle_membership_user_email ON public.circle_membership(user_email);
CREATE INDEX IF NOT EXISTS idx_vocab_pack_access_user_email ON public.vocabulary_pack_access(user_email);
CREATE INDEX IF NOT EXISTS idx_ai_session_access_user_email ON public.ai_session_access(user_email);
CREATE INDEX IF NOT EXISTS idx_toolkit_access_user_email ON public.toolkit_access(user_email);
CREATE INDEX IF NOT EXISTS idx_notebooklm_access_user_email ON public.notebooklm_access(user_email);
CREATE INDEX IF NOT EXISTS idx_educator_stories_status ON public.educator_stories(status);
CREATE INDEX IF NOT EXISTS idx_notebooklm_collections_active ON public.notebooklm_collections(is_active);
CREATE INDEX IF NOT EXISTS idx_notebooklm_curriculum_active ON public.notebooklm_curriculum(is_active);

-- Composite indexes for common query patterns
CREATE INDEX IF NOT EXISTS idx_user_progress_user_status ON public.user_progress(created_by, status);
CREATE INDEX IF NOT EXISTS idx_idioms_approved_category ON public.idioms(is_approved, category);
CREATE INDEX IF NOT EXISTS idx_class_enrollment_student_status ON public.class_enrollment(student_email, status);
CREATE INDEX IF NOT EXISTS idx_assignments_class_status ON public.teacher_assignments(class_id, status);

-- =====================================================
-- 5. ADD HELPFUL DATABASE FUNCTIONS
-- =====================================================

-- Helper: Get user's role (useful for app logic)
CREATE OR REPLACE FUNCTION public.get_user_role(user_id UUID)
RETURNS text
LANGUAGE sql
SECURITY DEFINER
STABLE
SET search_path = public, pg_temp
AS $$
  SELECT role
  FROM public.users
  WHERE id = user_id
  LIMIT 1;
$$;

ALTER FUNCTION public.get_user_role(UUID) OWNER TO postgres;
GRANT EXECUTE ON FUNCTION public.get_user_role(UUID) TO authenticated;

-- Helper: Check if user is enrolled in class
CREATE OR REPLACE FUNCTION public.is_enrolled_in_class(p_class_id UUID, p_user_email TEXT)
RETURNS boolean
LANGUAGE sql
SECURITY DEFINER
STABLE
SET search_path = public, pg_temp
AS $$
  SELECT EXISTS (
    SELECT 1
    FROM public.class_enrollment
    WHERE class_id = p_class_id
      AND student_email = p_user_email
      AND status = 'active'
  );
$$;

ALTER FUNCTION public.is_enrolled_in_class(UUID, TEXT) OWNER TO postgres;
GRANT EXECUTE ON FUNCTION public.is_enrolled_in_class(UUID, TEXT) TO authenticated;

-- =====================================================
-- 6. AUDIT TRIGGERS (OPTIONAL - for production monitoring)
-- =====================================================

-- Create audit log table (optional but recommended for production)
CREATE TABLE IF NOT EXISTS public.audit_log (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  created_date TIMESTAMPTZ DEFAULT NOW(),
  table_name TEXT NOT NULL,
  operation TEXT NOT NULL CHECK (operation IN ('INSERT', 'UPDATE', 'DELETE')),
  user_id UUID,
  user_email TEXT,
  record_id UUID,
  old_data JSONB,
  new_data JSONB,
  ip_address INET
);

CREATE INDEX IF NOT EXISTS idx_audit_log_created_date ON public.audit_log(created_date);
CREATE INDEX IF NOT EXISTS idx_audit_log_table_name ON public.audit_log(table_name);
CREATE INDEX IF NOT EXISTS idx_audit_log_user_email ON public.audit_log(user_email);

-- Audit function
CREATE OR REPLACE FUNCTION public.audit_trigger_function()
RETURNS TRIGGER
LANGUAGE plpgsql
SECURITY DEFINER
SET search_path = public, pg_temp
AS $$
BEGIN
  INSERT INTO public.audit_log (
    table_name,
    operation,
    user_id,
    user_email,
    record_id,
    old_data,
    new_data
  ) VALUES (
    TG_TABLE_NAME,
    TG_OP,
    auth.uid(),
    public.current_user_email(),
    COALESCE(NEW.id, OLD.id),
    CASE WHEN TG_OP = 'DELETE' THEN to_jsonb(OLD) ELSE NULL END,
    CASE WHEN TG_OP IN ('INSERT', 'UPDATE') THEN to_jsonb(NEW) ELSE NULL END
  );
  
  RETURN COALESCE(NEW, OLD);
END;
$$;

ALTER FUNCTION public.audit_trigger_function() OWNER TO postgres;

-- Apply audit triggers to sensitive tables (optional - enable as needed)
-- Example: Uncomment to enable audit on users table
-- DROP TRIGGER IF EXISTS audit_users ON public.users;
-- CREATE TRIGGER audit_users
--   AFTER INSERT OR UPDATE OR DELETE ON public.users
--   FOR EACH ROW EXECUTE FUNCTION public.audit_trigger_function();

-- =====================================================
-- 7. VALIDATION CHECKS (CORRECTED)
-- =====================================================

-- Verify all critical tables have RLS enabled (FIXED - use pg_class)
DO $$
DECLARE
  unprotected_tables TEXT[];
BEGIN
  SELECT array_agg(c.relname)
  INTO unprotected_tables
  FROM pg_class c
  JOIN pg_namespace n ON n.oid = c.relnamespace
  WHERE n.nspname = 'public'
    AND c.relkind = 'r'
    AND c.relrowsecurity = false;
  
  IF array_length(unprotected_tables, 1) > 0 THEN
    RAISE WARNING 'Tables without RLS: %', unprotected_tables;
  ELSE
    RAISE NOTICE '✅ All tables have RLS enabled';
  END IF;
END $$;

-- Verify helper functions exist
DO $$
BEGIN
  IF NOT EXISTS (SELECT 1 FROM pg_proc WHERE proname = 'current_user_email') THEN
    RAISE EXCEPTION 'Helper function current_user_email() not found';
  END IF;
  
  IF NOT EXISTS (SELECT 1 FROM pg_proc WHERE proname = 'current_user_is_admin') THEN
    RAISE EXCEPTION 'Helper function current_user_is_admin() not found';
  END IF;
  
  RAISE NOTICE '✅ Helper functions verified';
END $$;

-- =====================================================
-- COMPLETED - v5.1 Security Patch Applied
-- =====================================================
-- ✅ SECURITY DEFINER functions hardened with SET search_path
-- ✅ Helper functions owned by postgres
-- ✅ current_user_is_admin() uses explicit COALESCE
-- ✅ Missing RLS policies added (circle membership read/delete)
-- ✅ Teachers can INSERT/UPDATE/DELETE class enrollments
-- ✅ Teacher submission update WITH CHECK added
-- ✅ Tutor session read policy
-- ✅ Performance indexes for email lookups and common queries
-- ✅ Helper functions for app logic
-- ✅ Optional audit logging infrastructure
-- ✅ Validation checks FIXED (use pg_class.relrowsecurity)
-- =====================================================
-- Next: Test with non-admin/teacher/student users to verify access control
-- =====================================================