thrilltrack-explorer/supabase/migrations/20251014193514_24af409c-7d6c-4978-987e-5b0ae056364a.sql

-- Security Enhancement Migration: Session Management and Identity Operations
-- Adds functions for secure session management with MFA verification and rate limiting

-- ============================================================================
-- PHASE 1: Session IP Hashing for Privacy
-- ============================================================================

-- Hash IP address for privacy-preserving display
CREATE OR REPLACE FUNCTION public.hash_session_ip(session_ip inet)
RETURNS text
LANGUAGE plpgsql
IMMUTABLE
SET search_path = public
AS $$
BEGIN
  -- Return last 8 chars of SHA256 hash with asterisks prefix for privacy
  RETURN '****' || RIGHT(encode(digest(session_ip::text || 'session_salt_2025', 'sha256'), 'hex'), 8);
END;
$$;

-- ============================================================================
-- PHASE 2: Drop and Recreate Get User's Own Sessions with Hashed IPs
-- ============================================================================

-- Drop the existing function first
DROP FUNCTION IF EXISTS public.get_my_sessions();

-- Get user's own sessions with hashed IPs for security
CREATE OR REPLACE FUNCTION public.get_my_sessions()
RETURNS TABLE (
  id uuid,
  created_at timestamptz,
  updated_at timestamptz,
  refreshed_at timestamptz,
  user_agent text,
  ip text,
  not_after timestamptz,
  aal text
)
LANGUAGE plpgsql
SECURITY DEFINER
SET search_path = auth, public
AS $$
BEGIN
  RETURN QUERY
  SELECT 
    s.id,
    s.created_at,
    s.updated_at,
    s.refreshed_at,
    s.user_agent,
    public.hash_session_ip(s.ip) as ip,
    s.not_after,
    s.aal::text
  FROM auth.sessions s
  WHERE s.user_id = auth.uid()
  ORDER BY s.refreshed_at DESC NULLS LAST;
END;
$$;

-- ============================================================================
-- PHASE 3: Drop and Recreate Revoke User's Own Session
-- ============================================================================

-- Drop the existing function first
DROP FUNCTION IF EXISTS public.revoke_my_session(uuid);

-- Revoke user's own session with audit logging
CREATE OR REPLACE FUNCTION public.revoke_my_session(session_id uuid)
RETURNS void
LANGUAGE plpgsql
SECURITY DEFINER
SET search_path = auth, public
AS $$
BEGIN
  -- Only delete own sessions
  DELETE FROM auth.sessions
  WHERE id = session_id
    AND user_id = auth.uid();
    
  -- Log the action
  INSERT INTO public.profile_audit_log (user_id, changed_by, action, changes)
  VALUES (
    auth.uid(),
    auth.uid(),
    'session_revoked',
    jsonb_build_object(
      'session_id', session_id, 
      'timestamp', now(),
      'self_revoked', true
    )
  );
END;
$$;

-- ============================================================================
-- PHASE 4: Revoke Session with MFA Verification (Privileged Users)
-- ============================================================================

-- Revoke session with MFA verification (for moderators/admins)
CREATE OR REPLACE FUNCTION public.revoke_session_with_mfa(
  target_session_id uuid,
  target_user_id uuid
)
RETURNS jsonb
LANGUAGE plpgsql
SECURITY DEFINER
SET search_path = auth, public
AS $$
DECLARE
  v_rate_limit_key text;
  v_attempts integer;
  v_window_start timestamptz;
BEGIN
  -- Only moderators can revoke others' sessions
  IF NOT public.is_moderator(auth.uid()) THEN
    RAISE EXCEPTION 'Insufficient permissions' USING ERRCODE = 'P0003';
  END IF;
  
  -- Require MFA for moderators
  IF NOT public.has_aal2() THEN
    RAISE EXCEPTION 'MFA verification required' USING ERRCODE = 'P0004';
  END IF;
  
  -- Rate limit: 10 revocations per hour
  v_rate_limit_key := 'session_revocation_' || auth.uid()::text;
  
  -- Check existing rate limit
  SELECT attempts, window_start 
  INTO v_attempts, v_window_start
  FROM public.rate_limits
  WHERE user_id = auth.uid()
    AND action = 'session_revocation'
    AND window_start > now() - interval '1 hour';
  
  -- Enforce rate limit
  IF v_attempts >= 10 THEN
    RAISE EXCEPTION 'Rate limit exceeded. Try again after %', 
      (v_window_start + interval '1 hour')::text
      USING ERRCODE = 'P0001';
  END IF;
  
  -- Update or create rate limit record
  INSERT INTO public.rate_limits (user_id, action, attempts, window_start)
  VALUES (auth.uid(), 'session_revocation', 1, now())
  ON CONFLICT (user_id, action)
  WHERE window_start > now() - interval '1 hour'
  DO UPDATE SET 
    attempts = rate_limits.attempts + 1,
    window_start = CASE 
      WHEN rate_limits.window_start < now() - interval '1 hour' 
      THEN now() 
      ELSE rate_limits.window_start 
    END;
  
  -- Revoke the session
  DELETE FROM auth.sessions
  WHERE id = target_session_id
    AND user_id = target_user_id;
  
  -- Audit log
  INSERT INTO public.admin_audit_log (admin_user_id, target_user_id, action, details)
  VALUES (
    auth.uid(),
    target_user_id,
    'session_revoked_by_moderator',
    jsonb_build_object(
      'session_id', target_session_id,
      'timestamp', now(),
      'mfa_verified', true
    )
  );
  
  RETURN jsonb_build_object('success', true);
END;
$$;

-- ============================================================================
-- PHASE 5: Grant Permissions
-- ============================================================================

GRANT EXECUTE ON FUNCTION public.hash_session_ip TO authenticated;
GRANT EXECUTE ON FUNCTION public.get_my_sessions TO authenticated;
GRANT EXECUTE ON FUNCTION public.revoke_my_session TO authenticated;
GRANT EXECUTE ON FUNCTION public.revoke_session_with_mfa TO authenticated;