What is Snappy Idioms Dot Com?

An interactive, language-learning experience. Snappy Idioms Dot Com uses imagination, context, and active practice to build fluency, not just memorisation.

Supabase RLS Security Smoke Test

Validate all security policies after v5.1 patch deployment

What This Test Does

✓ Creates test data: Users (student, teacher, admin, tutor), classes, enrollments, idioms, circles

✓ Tests anon access: Can only read public content (approved idioms, active tutors)

✓ Tests student access: Can read own data, enrolled classes, own sessions

✓ Tests teacher access: Can manage own classes, insert/update/delete enrollments

✓ Tests tutor access: Can read own sessions, not other tutors' sessions

✓ Tests admin access: Can read/write all tables, including admin-only tables

✓ Tests helper functions: current_user_email(), current_user_is_admin(), get_user_role(), is_enrolled_in_class()

✓ Reports results: Shows ✅ for pass, ❌ for fail with clear messages

How to Run

Step 1: Copy the SQL below

Step 2: Open Supabase SQL Editor

Step 3: Paste and run the entire script

Step 4: Review the NOTICE messages in the output

Step 5: Look for any ❌ warnings - these indicate security issues

Step 6: Optionally uncomment cleanup section to remove test data

Expected Results

✅ All tests should show green checkmarks (✅)

✅ Anon users see only public content

✅ Students see only own data and enrolled classes

✅ Teachers can manage own classes and enrollments

✅ Tutors see only own sessions

✅ Admins have full access

✅ Helper functions return correct values

⚠️ Any ❌ warnings indicate security policy issues that need fixing

-- =====================================================
-- Snappy English - RLS Security Smoke Test
-- Run AFTER v5.1 patch to validate all security policies
-- Tests anon, student, teacher, tutor, and admin access
-- =====================================================

-- =====================================================
-- SETUP: Create test users and data
-- =====================================================

-- Insert test users (run as admin/service role)
INSERT INTO public.users (id, email, full_name, role, is_educator)
VALUES 
  ('11111111-1111-1111-1111-111111111111', 'student@test.com', 'Test Student', 'user', false),
  ('22222222-2222-2222-2222-222222222222', 'teacher@test.com', 'Test Teacher', 'user', true),
  ('33333333-3333-3333-3333-333333333333', 'admin@test.com', 'Test Admin', 'admin', false),
  ('44444444-4444-4444-4444-444444444444', 'tutor@test.com', 'Test Tutor', 'user', false)
ON CONFLICT (id) DO UPDATE SET 
  email = EXCLUDED.email,
  role = EXCLUDED.role,
  is_educator = EXCLUDED.is_educator;

-- Create test class
INSERT INTO public.classes (id, class_name, teacher_email, class_code)
VALUES ('aaaaaaaa-aaaa-aaaa-aaaa-aaaaaaaaaaaa', 'Test Class', 'teacher@test.com', 'TEST123')
ON CONFLICT (id) DO UPDATE SET teacher_email = EXCLUDED.teacher_email;

-- Enroll student
INSERT INTO public.class_enrollment (class_id, student_email, status)
VALUES ('aaaaaaaa-aaaa-aaaa-aaaa-aaaaaaaaaaaa', 'student@test.com', 'active')
ON CONFLICT DO NOTHING;

-- Create test idiom
INSERT INTO public.idioms (id, phrase, meaning, example_sentence, category, difficulty, is_approved)
VALUES ('bbbbbbbb-bbbb-bbbb-bbbb-bbbbbbbbbbbb', 'Test Idiom', 'Test meaning', 'Test sentence', 'general', 'beginner', true)
ON CONFLICT (id) DO UPDATE SET is_approved = EXCLUDED.is_approved;

-- Create unapproved idiom (should only be visible to admin)
INSERT INTO public.idioms (id, phrase, meaning, example_sentence, category, difficulty, is_approved)
VALUES ('cccccccc-cccc-cccc-cccc-cccccccccccc', 'Hidden Idiom', 'Hidden meaning', 'Hidden sentence', 'general', 'beginner', false)
ON CONFLICT (id) DO UPDATE SET is_approved = EXCLUDED.is_approved;

-- Create test study circle
INSERT INTO public.study_circles (id, name, creator_email)
VALUES ('dddddddd-dddd-dddd-dddd-dddddddddddd', 'Test Circle', 'student@test.com')
ON CONFLICT (id) DO UPDATE SET creator_email = EXCLUDED.creator_email;

-- Add circle membership
INSERT INTO public.circle_membership (circle_id, user_email)
VALUES ('dddddddd-dddd-dddd-dddd-dddddddddddd', 'student@test.com')
ON CONFLICT DO NOTHING;

-- Create test tutor
INSERT INTO public.tutors (id, full_name, email, hourly_rate_gbp, status)
VALUES ('eeeeeeee-eeee-eeee-eeee-eeeeeeeeeeee', 'Test Tutor', 'tutor@test.com', 50.00, 'active')
ON CONFLICT (id) DO UPDATE SET email = EXCLUDED.email;

-- Create test tutoring session
INSERT INTO public.tutoring_sessions (id, student_email, tutor_id, session_date, duration_minutes, cost_golden_eggs)
VALUES ('ffffffff-ffff-ffff-ffff-ffffffffffff', 'student@test.com', 'eeeeeeee-eeee-eeee-eeee-eeeeeeeeeeee', NOW() + INTERVAL '1 day', 45, 30)
ON CONFLICT (id) DO UPDATE SET student_email = EXCLUDED.student_email;

RAISE NOTICE '✅ Test data created';

-- =====================================================
-- TEST 1: ANON USER ACCESS (Public Content Only)
-- =====================================================

DO $$
DECLARE
  test_count INT;
BEGIN
  -- Simulate anon by clearing session
  PERFORM set_config('request.jwt.claims', NULL, true);
  
  RAISE NOTICE '---';
  RAISE NOTICE 'TEST 1: ANON USER ACCESS';
  RAISE NOTICE '---';
  
  -- Can read approved idioms
  SELECT COUNT(*) INTO test_count FROM public.idioms WHERE is_approved = true;
  IF test_count > 0 THEN
    RAISE NOTICE '✅ Anon can read approved idioms (count: %)', test_count;
  ELSE
    RAISE WARNING '❌ Anon cannot read approved idioms';
  END IF;
  
  -- Cannot read unapproved idioms
  SELECT COUNT(*) INTO test_count FROM public.idioms WHERE is_approved = false;
  IF test_count = 0 THEN
    RAISE NOTICE '✅ Anon cannot read unapproved idioms';
  ELSE
    RAISE WARNING '❌ Anon can read unapproved idioms (should be hidden)';
  END IF;
  
  -- Can read active tutors
  SELECT COUNT(*) INTO test_count FROM public.tutors WHERE status = 'active';
  IF test_count > 0 THEN
    RAISE NOTICE '✅ Anon can read active tutors (count: %)', test_count;
  ELSE
    RAISE WARNING '❌ Anon cannot read active tutors';
  END IF;
  
  -- Cannot read users
  SELECT COUNT(*) INTO test_count FROM public.users;
  IF test_count = 0 THEN
    RAISE NOTICE '✅ Anon cannot read users table';
  ELSE
    RAISE WARNING '❌ Anon can read users (should be blocked)';
  END IF;
  
  -- Cannot read circle memberships
  SELECT COUNT(*) INTO test_count FROM public.circle_membership;
  IF test_count = 0 THEN
    RAISE NOTICE '✅ Anon cannot read circle memberships';
  ELSE
    RAISE WARNING '❌ Anon can read circle memberships (should be blocked)';
  END IF;
  
END $$;

-- =====================================================
-- TEST 2: STUDENT USER ACCESS
-- =====================================================

DO $$
DECLARE
  test_count INT;
  student_id UUID := '11111111-1111-1111-1111-111111111111';
BEGIN
  -- Simulate student auth
  PERFORM set_config('request.jwt.claims', json_build_object('sub', student_id)::text, true);
  
  RAISE NOTICE '---';
  RAISE NOTICE 'TEST 2: STUDENT USER ACCESS (student@test.com)';
  RAISE NOTICE '---';
  
  -- Can read own user record
  SELECT COUNT(*) INTO test_count FROM public.users WHERE id = student_id;
  IF test_count = 1 THEN
    RAISE NOTICE '✅ Student can read own user record';
  ELSE
    RAISE WARNING '❌ Student cannot read own user record';
  END IF;
  
  -- Cannot read other users
  SELECT COUNT(*) INTO test_count FROM public.users WHERE id != student_id;
  IF test_count = 0 THEN
    RAISE NOTICE '✅ Student cannot read other users';
  ELSE
    RAISE WARNING '❌ Student can read other users (count: %)', test_count;
  END IF;
  
  -- Can read approved idioms
  SELECT COUNT(*) INTO test_count FROM public.idioms WHERE is_approved = true;
  IF test_count > 0 THEN
    RAISE NOTICE '✅ Student can read approved idioms';
  ELSE
    RAISE WARNING '❌ Student cannot read approved idioms';
  END IF;
  
  -- Cannot read unapproved idioms
  SELECT COUNT(*) INTO test_count FROM public.idioms WHERE is_approved = false;
  IF test_count = 0 THEN
    RAISE NOTICE '✅ Student cannot read unapproved idioms';
  ELSE
    RAISE WARNING '❌ Student can read unapproved idioms (should be hidden)';
  END IF;
  
  -- Can read own circle membership
  SELECT COUNT(*) INTO test_count FROM public.circle_membership WHERE user_email = 'student@test.com';
  IF test_count = 1 THEN
    RAISE NOTICE '✅ Student can read own circle membership';
  ELSE
    RAISE WARNING '❌ Student cannot read own circle membership';
  END IF;
  
  -- Can read classes they are enrolled in
  SELECT COUNT(*) INTO test_count 
  FROM public.classes c
  WHERE EXISTS (
    SELECT 1 FROM public.class_enrollment e
    WHERE e.class_id = c.id AND e.student_email = 'student@test.com'
  );
  IF test_count > 0 THEN
    RAISE NOTICE '✅ Student can read classes they are enrolled in';
  ELSE
    RAISE WARNING '❌ Student cannot read enrolled classes';
  END IF;
  
  -- Can read own tutoring sessions
  SELECT COUNT(*) INTO test_count FROM public.tutoring_sessions WHERE student_email = 'student@test.com';
  IF test_count > 0 THEN
    RAISE NOTICE '✅ Student can read own tutoring sessions';
  ELSE
    RAISE WARNING '❌ Student cannot read own tutoring sessions';
  END IF;
  
  -- Cannot manage other students' enrollments
  BEGIN
    DELETE FROM public.class_enrollment WHERE student_email = 'other@test.com';
    RAISE WARNING '❌ Student can delete other enrollments (should be blocked)';
  EXCEPTION WHEN insufficient_privilege THEN
    RAISE NOTICE '✅ Student cannot delete other enrollments';
  END;
  
END $$;

-- =====================================================
-- TEST 3: TEACHER USER ACCESS
-- =====================================================

DO $$
DECLARE
  test_count INT;
  teacher_id UUID := '22222222-2222-2222-2222-222222222222';
BEGIN
  -- Simulate teacher auth
  PERFORM set_config('request.jwt.claims', json_build_object('sub', teacher_id)::text, true);
  
  RAISE NOTICE '---';
  RAISE NOTICE 'TEST 3: TEACHER USER ACCESS (teacher@test.com)';
  RAISE NOTICE '---';
  
  -- Can read own classes
  SELECT COUNT(*) INTO test_count FROM public.classes WHERE teacher_email = 'teacher@test.com';
  IF test_count > 0 THEN
    RAISE NOTICE '✅ Teacher can read own classes (count: %)', test_count;
  ELSE
    RAISE WARNING '❌ Teacher cannot read own classes';
  END IF;
  
  -- Cannot read other teachers' classes
  SELECT COUNT(*) INTO test_count FROM public.classes WHERE teacher_email != 'teacher@test.com';
  IF test_count = 0 THEN
    RAISE NOTICE '✅ Teacher cannot read other teachers classes';
  ELSE
    RAISE WARNING '❌ Teacher can read other teachers classes (count: %)', test_count;
  END IF;
  
  -- Can read enrollment for own classes
  SELECT COUNT(*) INTO test_count 
  FROM public.class_enrollment e
  WHERE EXISTS (
    SELECT 1 FROM public.classes c
    WHERE c.id = e.class_id AND c.teacher_email = 'teacher@test.com'
  );
  IF test_count > 0 THEN
    RAISE NOTICE '✅ Teacher can read enrollments for own classes';
  ELSE
    RAISE WARNING '❌ Teacher cannot read enrollments';
  END IF;
  
  -- Can insert enrollment for own class
  BEGIN
    INSERT INTO public.class_enrollment (class_id, student_email, status)
    VALUES ('aaaaaaaa-aaaa-aaaa-aaaa-aaaaaaaaaaaa', 'newstudent@test.com', 'active');
    RAISE NOTICE '✅ Teacher can insert enrollment for own class';
    DELETE FROM public.class_enrollment WHERE student_email = 'newstudent@test.com';
  EXCEPTION WHEN OTHERS THEN
    RAISE WARNING '❌ Teacher cannot insert enrollment: %', SQLERRM;
  END;
  
  -- Can update enrollment for own class
  BEGIN
    UPDATE public.class_enrollment 
    SET status = 'inactive' 
    WHERE class_id = 'aaaaaaaa-aaaa-aaaa-aaaa-aaaaaaaaaaaa' 
      AND student_email = 'student@test.com';
    RAISE NOTICE '✅ Teacher can update enrollment for own class';
    -- Reset
    UPDATE public.class_enrollment SET status = 'active' 
    WHERE class_id = 'aaaaaaaa-aaaa-aaaa-aaaa-aaaaaaaaaaaa' 
      AND student_email = 'student@test.com';
  EXCEPTION WHEN OTHERS THEN
    RAISE WARNING '❌ Teacher cannot update enrollment: %', SQLERRM;
  END;
  
  -- Can delete enrollment for own class
  BEGIN
    INSERT INTO public.class_enrollment (class_id, student_email, status)
    VALUES ('aaaaaaaa-aaaa-aaaa-aaaa-aaaaaaaaaaaa', 'temp@test.com', 'active');
    
    DELETE FROM public.class_enrollment 
    WHERE class_id = 'aaaaaaaa-aaaa-aaaa-aaaa-aaaaaaaaaaaa' 
      AND student_email = 'temp@test.com';
    RAISE NOTICE '✅ Teacher can delete enrollment for own class';
  EXCEPTION WHEN OTHERS THEN
    RAISE WARNING '❌ Teacher cannot delete enrollment: %', SQLERRM;
  END;
  
END $$;

-- =====================================================
-- TEST 4: TUTOR USER ACCESS
-- =====================================================

DO $$
DECLARE
  test_count INT;
  tutor_id UUID := '44444444-4444-4444-4444-444444444444';
BEGIN
  -- Simulate tutor auth
  PERFORM set_config('request.jwt.claims', json_build_object('sub', tutor_id)::text, true);
  
  RAISE NOTICE '---';
  RAISE NOTICE 'TEST 4: TUTOR USER ACCESS (tutor@test.com)';
  RAISE NOTICE '---';
  
  -- Can read own tutoring sessions
  SELECT COUNT(*) INTO test_count 
  FROM public.tutoring_sessions ts
  WHERE EXISTS (
    SELECT 1 FROM public.tutors t
    WHERE t.id = ts.tutor_id AND t.email = 'tutor@test.com'
  );
  IF test_count > 0 THEN
    RAISE NOTICE '✅ Tutor can read own sessions (count: %)', test_count;
  ELSE
    RAISE WARNING '❌ Tutor cannot read own sessions';
  END IF;
  
  -- Cannot read other tutors' sessions
  SELECT COUNT(*) INTO test_count 
  FROM public.tutoring_sessions ts
  WHERE NOT EXISTS (
    SELECT 1 FROM public.tutors t
    WHERE t.id = ts.tutor_id AND t.email = 'tutor@test.com'
  );
  IF test_count = 0 THEN
    RAISE NOTICE '✅ Tutor cannot read other tutors sessions';
  ELSE
    RAISE WARNING '❌ Tutor can read other tutors sessions (count: %)', test_count;
  END IF;
  
END $$;

-- =====================================================
-- TEST 5: ADMIN USER ACCESS
-- =====================================================

DO $$
DECLARE
  test_count INT;
  admin_id UUID := '33333333-3333-3333-3333-333333333333';
BEGIN
  -- Simulate admin auth
  PERFORM set_config('request.jwt.claims', json_build_object('sub', admin_id)::text, true);
  
  RAISE NOTICE '---';
  RAISE NOTICE 'TEST 5: ADMIN USER ACCESS (admin@test.com)';
  RAISE NOTICE '---';
  
  -- Can read all users
  SELECT COUNT(*) INTO test_count FROM public.users;
  IF test_count >= 4 THEN
    RAISE NOTICE '✅ Admin can read all users (count: %)', test_count;
  ELSE
    RAISE WARNING '❌ Admin cannot read all users';
  END IF;
  
  -- Can read unapproved idioms
  SELECT COUNT(*) INTO test_count FROM public.idioms WHERE is_approved = false;
  IF test_count > 0 THEN
    RAISE NOTICE '✅ Admin can read unapproved idioms (count: %)', test_count;
  ELSE
    RAISE WARNING '❌ Admin cannot read unapproved idioms';
  END IF;
  
  -- Can read all classes
  SELECT COUNT(*) INTO test_count FROM public.classes;
  IF test_count > 0 THEN
    RAISE NOTICE '✅ Admin can read all classes (count: %)', test_count;
  ELSE
    RAISE WARNING '❌ Admin cannot read all classes';
  END IF;
  
  -- Can read all circle memberships
  SELECT COUNT(*) INTO test_count FROM public.circle_membership;
  IF test_count > 0 THEN
    RAISE NOTICE '✅ Admin can read all circle memberships (count: %)', test_count;
  ELSE
    RAISE WARNING '❌ Admin cannot read all circle memberships';
  END IF;
  
  -- Can read flagged accounts (admin-only table)
  SELECT COUNT(*) INTO test_count FROM public.flagged_accounts;
  RAISE NOTICE '✅ Admin can read flagged_accounts (count: %)', test_count;
  
  -- Can manage content pricing
  SELECT COUNT(*) INTO test_count FROM public.content_pricing;
  RAISE NOTICE '✅ Admin can read content_pricing (count: %)', test_count;
  
END $$;

-- =====================================================
-- TEST 6: HELPER FUNCTIONS
-- =====================================================

DO $$
DECLARE
  email TEXT;
  is_admin BOOLEAN;
  role TEXT;
  is_enrolled BOOLEAN;
  student_id UUID := '11111111-1111-1111-1111-111111111111';
BEGIN
  -- Simulate student auth
  PERFORM set_config('request.jwt.claims', json_build_object('sub', student_id)::text, true);
  
  RAISE NOTICE '---';
  RAISE NOTICE 'TEST 6: HELPER FUNCTIONS';
  RAISE NOTICE '---';
  
  -- Test current_user_email()
  SELECT public.current_user_email() INTO email;
  IF email = 'student@test.com' THEN
    RAISE NOTICE '✅ current_user_email() returns correct email: %', email;
  ELSE
    RAISE WARNING '❌ current_user_email() failed. Got: %', COALESCE(email, 'NULL');
  END IF;
  
  -- Test current_user_is_admin() as student
  SELECT public.current_user_is_admin() INTO is_admin;
  IF is_admin = false THEN
    RAISE NOTICE '✅ current_user_is_admin() returns false for student';
  ELSE
    RAISE WARNING '❌ current_user_is_admin() returned true for student';
  END IF;
  
  -- Test get_user_role()
  SELECT public.get_user_role(student_id) INTO role;
  IF role = 'user' THEN
    RAISE NOTICE '✅ get_user_role() returns correct role: %', role;
  ELSE
    RAISE WARNING '❌ get_user_role() failed. Got: %', COALESCE(role, 'NULL');
  END IF;
  
  -- Test is_enrolled_in_class()
  SELECT public.is_enrolled_in_class('aaaaaaaa-aaaa-aaaa-aaaa-aaaaaaaaaaaa', 'student@test.com') INTO is_enrolled;
  IF is_enrolled = true THEN
    RAISE NOTICE '✅ is_enrolled_in_class() correctly identifies enrollment';
  ELSE
    RAISE WARNING '❌ is_enrolled_in_class() failed';
  END IF;
  
  -- Test current_user_is_admin() as admin
  PERFORM set_config('request.jwt.claims', json_build_object('sub', '33333333-3333-3333-3333-333333333333')::text, true);
  SELECT public.current_user_is_admin() INTO is_admin;
  IF is_admin = true THEN
    RAISE NOTICE '✅ current_user_is_admin() returns true for admin';
  ELSE
    RAISE WARNING '❌ current_user_is_admin() returned false for admin';
  END IF;
  
END $$;

-- =====================================================
-- CLEANUP TEST DATA (OPTIONAL)
-- =====================================================

-- Uncomment to clean up test data after validation
-- DELETE FROM public.circle_membership WHERE circle_id = 'dddddddd-dddd-dddd-dddd-dddddddddddd';
-- DELETE FROM public.study_circles WHERE id = 'dddddddd-dddd-dddd-dddd-dddddddddddd';
-- DELETE FROM public.tutoring_sessions WHERE id = 'ffffffff-ffff-ffff-ffff-ffffffffffff';
-- DELETE FROM public.tutors WHERE id = 'eeeeeeee-eeee-eeee-eeee-eeeeeeeeeeee';
-- DELETE FROM public.class_enrollment WHERE class_id = 'aaaaaaaa-aaaa-aaaa-aaaa-aaaaaaaaaaaa';
-- DELETE FROM public.classes WHERE id = 'aaaaaaaa-aaaa-aaaa-aaaa-aaaaaaaaaaaa';
-- DELETE FROM public.idioms WHERE id IN ('bbbbbbbb-bbbb-bbbb-bbbb-bbbbbbbbbbbb', 'cccccccc-cccc-cccc-cccc-cccccccccccc');
-- DELETE FROM public.users WHERE id IN ('11111111-1111-1111-1111-111111111111', '22222222-2222-2222-2222-222222222222', '33333333-3333-3333-3333-333333333333', '44444444-4444-4444-4444-444444444444');

RAISE NOTICE '---';
RAISE NOTICE '✅ SMOKE TEST COMPLETED';
RAISE NOTICE 'Review warnings above - any ❌ indicates a security policy issue';
RAISE NOTICE '---';

What to Do If Tests Fail

If anon can read unapproved content:

Check that idioms policy uses is_approved OR public.current_user_is_admin()

If student can read other users:

Verify users SELECT policy uses id = auth.uid() in USING clause

If teacher can't manage enrollments:

Check enrollment policies include teacher_email checks in both USING and WITH CHECK

If helper functions return NULL:

Ensure public.users table has matching records for auth.uid()

If tutor sees all sessions:

Verify tutoring_tutor_read policy checks tutors.email = current_user_email()