thrilltrack-explorer/supabase/migrations/20251102024855_c101eb69-cdec-4e96-b794-839935e8c2a3.sql

-- Relax blog_posts RLS policies to remove AAL2 requirement for SELECT
-- This allows admins to view blog posts without constant MFA step-up
-- Write operations still require AAL2 for security

-- Drop the existing combined admin policy
DROP POLICY IF EXISTS "Admins and superusers can manage blog posts" ON public.blog_posts;

-- Create separate SELECT policy without AAL2 requirement
CREATE POLICY "Admins can view all blog posts"
ON public.blog_posts
FOR SELECT
TO authenticated
USING (
  has_role(auth.uid(), 'admin'::app_role) OR 
  has_role(auth.uid(), 'superuser'::app_role)
);

-- Create INSERT policy with AAL2 requirement for write operations
CREATE POLICY "Admins can create blog posts with AAL2"
ON public.blog_posts
FOR INSERT
TO authenticated
WITH CHECK (
  (has_role(auth.uid(), 'admin'::app_role) OR has_role(auth.uid(), 'superuser'::app_role))
  AND ((NOT EXISTS (
    SELECT 1 FROM auth.mfa_factors 
    WHERE user_id = auth.uid() AND status = 'verified'::auth.factor_status
  )) OR has_aal2())
);

-- Create UPDATE policy with AAL2 requirement for write operations
CREATE POLICY "Admins can update blog posts with AAL2"
ON public.blog_posts
FOR UPDATE
TO authenticated
USING (
  (has_role(auth.uid(), 'admin'::app_role) OR has_role(auth.uid(), 'superuser'::app_role))
  AND ((NOT EXISTS (
    SELECT 1 FROM auth.mfa_factors 
    WHERE user_id = auth.uid() AND status = 'verified'::auth.factor_status
  )) OR has_aal2())
);

-- Create DELETE policy with AAL2 requirement for write operations
CREATE POLICY "Admins can delete blog posts with AAL2"
ON public.blog_posts
FOR DELETE
TO authenticated
USING (
  (has_role(auth.uid(), 'admin'::app_role) OR has_role(auth.uid(), 'superuser'::app_role))
  AND ((NOT EXISTS (
    SELECT 1 FROM auth.mfa_factors 
    WHERE user_id = auth.uid() AND status = 'verified'::auth.factor_status
  )) OR has_aal2())
);