How to Protect Admin Routes with Supabase

1-- Add role column to profiles table
2ALTER TABLE public.profiles
3ADD COLUMN role TEXT NOT NULL DEFAULT 'user'
4CHECK (role IN ('user', 'admin', 'moderator'));
5
6-- Create an index for role-based queries
7CREATE INDEX idx_profiles_role ON profiles USING btree (role);
8
9-- Promote a user to admin
10UPDATE profiles SET role = 'admin' WHERE id = 'user-uuid-here';
11
12-- Create a function to check if current user is admin
13CREATE OR REPLACE FUNCTION public.is_admin()
14RETURNS BOOLEAN
15LANGUAGE sql
16SECURITY DEFINER
17SET search_path = ''
18AS $$
19  SELECT EXISTS (
20    SELECT 1 FROM public.profiles
21    WHERE id = (SELECT auth.uid())
22    AND role = 'admin'
23  );
24$$;

1-- Admin-only table: only admins can read and write
2CREATE TABLE public.admin_settings (
3  id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
4  key TEXT UNIQUE NOT NULL,
5  value JSONB NOT NULL,
6  updated_at TIMESTAMPTZ DEFAULT now()
7);
8
9ALTER TABLE public.admin_settings ENABLE ROW LEVEL SECURITY;
10
11-- Only admins can SELECT
12CREATE POLICY "Only admins can read settings"
13ON public.admin_settings FOR SELECT
14TO authenticated
15USING (public.is_admin());
16
17-- Only admins can INSERT/UPDATE/DELETE
18CREATE POLICY "Only admins can modify settings"
19ON public.admin_settings FOR ALL
20TO authenticated
21USING (public.is_admin())
22WITH CHECK (public.is_admin());
23
24-- Regular table with admin override: users see own data, admins see all
25CREATE POLICY "Users see own data, admins see all"
26ON public.orders FOR SELECT
27TO authenticated
28USING (
29  (SELECT auth.uid()) = user_id
30  OR public.is_admin()
31);

1import { useEffect, useState } from 'react';
2import { createClient } from '@supabase/supabase-js';
3
4const supabase = createClient(
5  process.env.NEXT_PUBLIC_SUPABASE_URL!,
6  process.env.NEXT_PUBLIC_SUPABASE_ANON_KEY!
7);
8
9interface AdminGuardProps {
10  children: React.ReactNode;
11  fallback?: React.ReactNode;
12}
13
14export function AdminGuard({ children, fallback }: AdminGuardProps) {
15  const [isAdmin, setIsAdmin] = useState<boolean | null>(null);
16
17  useEffect(() => {
18    async function checkAdmin() {
19      const { data: { user } } = await supabase.auth.getUser();
20
21      if (!user) {
22        window.location.href = '/login';
23        return;
24      }
25
26      const { data: profile } = await supabase
27        .from('profiles')
28        .select('role')
29        .eq('id', user.id)
30        .single();
31
32      if (profile?.role === 'admin') {
33        setIsAdmin(true);
34      } else {
35        setIsAdmin(false);
36        window.location.href = '/';
37      }
38    }
39
40    checkAdmin();
41  }, []);
42
43  if (isAdmin === null) return fallback || <div>Loading...</div>;
44  if (!isAdmin) return null;
45
46  return <>{children}</>;
47}

1// Next.js App Router: Server-side admin check
2// app/api/admin/settings/route.ts
3import { createClient } from '@/lib/supabase/server';
4import { NextResponse } from 'next/server';
5
6export async function GET() {
7  const supabase = await createClient();
8
9  // Verify the user is authenticated
10  const { data: { user }, error: authError } = await supabase.auth.getUser();
11  if (authError || !user) {
12    return NextResponse.json({ error: 'Unauthorized' }, { status: 401 });
13  }
14
15  // Check admin role
16  const { data: profile } = await supabase
17    .from('profiles')
18    .select('role')
19    .eq('id', user.id)
20    .single();
21
22  if (profile?.role !== 'admin') {
23    return NextResponse.json({ error: 'Forbidden' }, { status: 403 });
24  }
25
26  // Process admin request
27  const { data: settings } = await supabase
28    .from('admin_settings')
29    .select('*');
30
31  return NextResponse.json(settings);
32}

1-- Create a trigger that adds the role to JWT claims on login
2CREATE OR REPLACE FUNCTION public.custom_access_token_hook(event JSONB)
3RETURNS JSONB
4LANGUAGE plpgsql
5AS $$
6DECLARE
7  claims JSONB;
8  user_role TEXT;
9BEGIN
10  SELECT role INTO user_role
11  FROM public.profiles
12  WHERE id = (event->>'user_id')::UUID;
13
14  claims := event->'claims';
15  claims := jsonb_set(claims, '{user_role}', to_jsonb(COALESCE(user_role, 'user')));
16
17  event := jsonb_set(event, '{claims}', claims);
18  RETURN event;
19END;
20$$;
21
22-- RLS policy using JWT claims (faster, no extra query)
23CREATE POLICY "Admins can access admin table via JWT"
24ON public.admin_settings FOR ALL
25TO authenticated
26USING (
27  (SELECT auth.jwt() ->> 'user_role') = 'admin'
28)
29WITH CHECK (
30  (SELECT auth.jwt() ->> 'user_role') = 'admin'
31);