How to Log Auth Errors in Supabase

1-- Create the auth error logs table
2CREATE TABLE public.auth_error_logs (
3  id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
4  error_type TEXT NOT NULL,
5  error_message TEXT NOT NULL,
6  error_code TEXT,
7  user_email TEXT,
8  user_id UUID,
9  ip_address TEXT,
10  user_agent TEXT,
11  metadata JSONB DEFAULT '{}',
12  created_at TIMESTAMPTZ DEFAULT now()
13);
14
15-- Enable RLS
16ALTER TABLE public.auth_error_logs ENABLE ROW LEVEL SECURITY;
17
18-- Allow authenticated admins to read logs
19CREATE POLICY "Admins can read auth error logs"
20ON public.auth_error_logs FOR SELECT
21TO authenticated
22USING (
23  (SELECT auth.jwt() ->> 'role') = 'admin'
24);
25
26-- Allow inserts via Edge Function (service role bypasses RLS)
27-- No INSERT policy needed when using service_role key

1// supabase/functions/log-auth-error/index.ts
2import { corsHeaders } from '../_shared/cors.ts';
3import { createClient } from 'npm:@supabase/supabase-js@2';
4
5Deno.serve(async (req) => {
6  if (req.method === 'OPTIONS') {
7    return new Response('ok', { headers: corsHeaders });
8  }
9
10  try {
11    const { error_type, error_message, error_code, user_email, metadata } =
12      await req.json();
13
14    // Create client with service_role to bypass RLS
15    const supabase = createClient(
16      Deno.env.get('SUPABASE_URL')!,
17      Deno.env.get('SUPABASE_SERVICE_ROLE_KEY')!
18    );
19
20    const { error } = await supabase.from('auth_error_logs').insert({
21      error_type,
22      error_message,
23      error_code: error_code || null,
24      user_email: user_email || null,
25      user_agent: req.headers.get('user-agent'),
26      metadata: metadata || {},
27    });
28
29    if (error) throw error;
30
31    return new Response(
32      JSON.stringify({ logged: true }),
33      { headers: { ...corsHeaders, 'Content-Type': 'application/json' } }
34    );
35  } catch (err) {
36    return new Response(
37      JSON.stringify({ error: err.message }),
38      { headers: { ...corsHeaders, 'Content-Type': 'application/json' }, status: 500 }
39    );
40  }
41});

1import { createClient } from '@supabase/supabase-js';
2
3const supabase = createClient(
4  process.env.NEXT_PUBLIC_SUPABASE_URL!,
5  process.env.NEXT_PUBLIC_SUPABASE_ANON_KEY!
6);
7
8async function logAuthError(
9  errorType: string,
10  errorMessage: string,
11  errorCode?: string,
12  email?: string
13) {
14  try {
15    await supabase.functions.invoke('log-auth-error', {
16      body: {
17        error_type: errorType,
18        error_message: errorMessage,
19        error_code: errorCode,
20        user_email: email,
21        metadata: { page: window.location.pathname },
22      },
23    });
24  } catch (e) {
25    console.error('Failed to log auth error:', e);
26  }
27}
28
29// Wrap sign-in with error logging
30async function signIn(email: string, password: string) {
31  const { data, error } = await supabase.auth.signInWithPassword({
32    email,
33    password,
34  });
35
36  if (error) {
37    await logAuthError('sign_in_failed', error.message, error.status?.toString(), email);
38    throw error;
39  }
40
41  return data;
42}

1// Set up global auth event monitoring
2supabase.auth.onAuthStateChange(async (event, session) => {
3  // Log specific events that indicate problems
4  if (event === 'TOKEN_REFRESHED' && !session) {
5    await logAuthError(
6      'token_refresh_failed',
7      'Session token refresh returned no session',
8      undefined,
9      undefined
10    );
11  }
12
13  if (event === 'SIGNED_OUT') {
14    // Could be intentional or forced — log for audit trail
15    console.log('User signed out');
16  }
17
18  // Log all events for audit purposes
19  console.log('Auth event:', event, session?.user?.email);
20});

1-- Find the most common auth errors in the last 24 hours
2SELECT error_type, error_message, COUNT(*) as occurrences
3FROM auth_error_logs
4WHERE created_at > now() - INTERVAL '24 hours'
5GROUP BY error_type, error_message
6ORDER BY occurrences DESC;
7
8-- Detect potential brute force attempts (5+ failures per email)
9SELECT user_email, COUNT(*) as failed_attempts,
10  MIN(created_at) as first_attempt,
11  MAX(created_at) as last_attempt
12FROM auth_error_logs
13WHERE error_type = 'sign_in_failed'
14  AND created_at > now() - INTERVAL '1 hour'
15GROUP BY user_email
16HAVING COUNT(*) >= 5
17ORDER BY failed_attempts DESC;
18
19-- Recent errors with full details
20SELECT error_type, error_message, user_email, user_agent, created_at
21FROM auth_error_logs
22ORDER BY created_at DESC
23LIMIT 50;