mirror of
https://github.com/pacnpal/thrilltrack-explorer.git
synced 2025-12-20 04:51:11 -05:00
71 lines
2.6 KiB
SQL
71 lines
2.6 KiB
SQL
-- Phase 1: Critical Security Fixes (Views Fixed)
|
|
-- Fix public data exposure and missing RLS policies
|
|
|
|
-- =========================================
|
|
-- 1. FIX PROFILES TABLE - Remove public access
|
|
-- =========================================
|
|
|
|
-- Drop existing policies
|
|
DROP POLICY IF EXISTS "Hide deactivated profiles from public" ON public.profiles;
|
|
DROP POLICY IF EXISTS "Users can view their own profile" ON public.profiles;
|
|
DROP POLICY IF EXISTS "Moderators can view all profiles" ON public.profiles;
|
|
|
|
-- Add proper restricted policies
|
|
CREATE POLICY "Users can view their own profile"
|
|
ON public.profiles
|
|
FOR SELECT
|
|
USING (auth.uid() = user_id);
|
|
|
|
CREATE POLICY "Moderators can view all profiles"
|
|
ON public.profiles
|
|
FOR SELECT
|
|
USING (is_moderator(auth.uid()));
|
|
|
|
-- =========================================
|
|
-- 2. FIX REQUEST_METADATA TABLE - Remove public access
|
|
-- =========================================
|
|
|
|
-- Drop existing policies
|
|
DROP POLICY IF EXISTS "Service role can read request metadata" ON public.request_metadata;
|
|
DROP POLICY IF EXISTS "Service role only access" ON public.request_metadata;
|
|
DROP POLICY IF EXISTS "Moderators can view metadata with MFA" ON public.request_metadata;
|
|
|
|
-- Add service role only policy
|
|
CREATE POLICY "Service role only access"
|
|
ON public.request_metadata
|
|
FOR ALL
|
|
USING (auth.role() = 'service_role');
|
|
|
|
-- Add moderator policy with MFA requirement
|
|
CREATE POLICY "Moderators can view metadata with MFA"
|
|
ON public.request_metadata
|
|
FOR SELECT
|
|
USING (is_moderator(auth.uid()) AND has_aal2());
|
|
|
|
-- =========================================
|
|
-- 3. VIEWS SECURITY NOTE
|
|
-- =========================================
|
|
-- filtered_profiles and moderation_sla_metrics are VIEWS, not tables.
|
|
-- Views inherit RLS policies from their underlying tables.
|
|
-- Since we've secured the base tables (profiles, content_submissions, etc.),
|
|
-- these views are now also secured.
|
|
|
|
COMMENT ON VIEW public.filtered_profiles IS 'View that filters profiles based on privacy settings. Inherits RLS policies from profiles table.';
|
|
|
|
COMMENT ON VIEW public.moderation_sla_metrics IS 'View providing moderation SLA metrics. Inherits RLS policies from content_submissions table.';
|
|
|
|
-- =========================================
|
|
-- 4. FIX EMAIL_ALIASES - Require MFA for admin access
|
|
-- =========================================
|
|
|
|
-- Drop existing admin policy
|
|
DROP POLICY IF EXISTS "email_aliases_select_admin" ON public.email_aliases;
|
|
|
|
-- Recreate with MFA requirement
|
|
CREATE POLICY "email_aliases_select_admin"
|
|
ON public.email_aliases
|
|
FOR SELECT
|
|
USING (
|
|
COALESCE(((auth.jwt() ->> 'is_admin'::text))::boolean, false) = true
|
|
AND has_aal2()
|
|
); |