How to Restrict Access by Role in Supabase RLS

1-- Option A: Read role from JWT claims (fast, no query)
2-- Set role via admin API: supabase.auth.admin.updateUserById(userId, { app_metadata: { role: 'admin' } })
3-- Access in RLS:
4(select auth.jwt()->'app_metadata'->>'role') = 'admin'
5
6-- Option B: Read role from a roles table (flexible, queryable)
7-- Create a user_roles table and join in policies:
8EXISTS (
9  SELECT 1 FROM user_roles
10  WHERE user_roles.user_id = (select auth.uid())
11  AND user_roles.role = 'admin'
12)

1// Server-side: Set a user's role using the admin API
2// MUST use the service role key — never expose this client-side
3import { createClient } from '@supabase/supabase-js'
4
5const supabaseAdmin = createClient(
6  process.env.SUPABASE_URL!,
7  process.env.SUPABASE_SERVICE_ROLE_KEY!
8)
9
10// Assign admin role
11const { data, error } = await supabaseAdmin.auth.admin.updateUserById(
12  userId,
13  { app_metadata: { role: 'admin' } }
14)
15
16// Assign editor role
17await supabaseAdmin.auth.admin.updateUserById(
18  userId,
19  { app_metadata: { role: 'editor' } }
20)
21
22// The JWT now contains: { app_metadata: { role: 'admin' } }
23// Accessible in SQL via: auth.jwt()->'app_metadata'->>'role'

1-- Table: articles
2ALTER TABLE articles ENABLE ROW LEVEL SECURITY;
3
4-- Admins can do everything
5CREATE POLICY "Admins have full access" ON articles
6  FOR ALL TO authenticated
7  USING (
8    (select auth.jwt()->'app_metadata'->>'role') = 'admin'
9  );
10
11-- Editors can read all and update their own
12CREATE POLICY "Editors can read all articles" ON articles
13  FOR SELECT TO authenticated
14  USING (
15    (select auth.jwt()->'app_metadata'->>'role') IN ('editor', 'admin')
16  );
17
18CREATE POLICY "Editors can update own articles" ON articles
19  FOR UPDATE TO authenticated
20  USING (
21    (select auth.jwt()->'app_metadata'->>'role') = 'editor'
22    AND (select auth.uid()) = author_id
23  );
24
25-- Viewers can only read published articles
26CREATE POLICY "Viewers read published" ON articles
27  FOR SELECT TO authenticated
28  USING (
29    status = 'published'
30    OR (select auth.jwt()->'app_metadata'->>'role') IN ('editor', 'admin')
31  );

1-- Create the roles table
2CREATE TABLE user_roles (
3  id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
4  user_id uuid REFERENCES auth.users(id) ON DELETE CASCADE NOT NULL,
5  role text NOT NULL CHECK (role IN ('admin', 'editor', 'viewer')),
6  created_at timestamptz DEFAULT now(),
7  UNIQUE(user_id, role)
8);
9
10-- Index for fast lookups in RLS policies
11CREATE INDEX idx_user_roles_user_id ON user_roles (user_id);
12
13-- RLS on the roles table itself (only admins can manage)
14ALTER TABLE user_roles ENABLE ROW LEVEL SECURITY;
15
16CREATE POLICY "Users can read own roles" ON user_roles
17  FOR SELECT TO authenticated
18  USING ((select auth.uid()) = user_id);
19
20CREATE POLICY "Admins can manage all roles" ON user_roles
21  FOR ALL TO authenticated
22  USING (
23    EXISTS (
24      SELECT 1 FROM user_roles
25      WHERE user_id = (select auth.uid()) AND role = 'admin'
26    )
27  );
28
29-- Use in other table policies
30CREATE POLICY "Editors can update articles" ON articles
31  FOR UPDATE TO authenticated
32  USING (
33    EXISTS (
34      SELECT 1 FROM user_roles
35      WHERE user_id = (select auth.uid())
36      AND role IN ('editor', 'admin')
37    )
38  );

1-- Test as an admin (in SQL Editor)
2SET request.jwt.claims = '{"sub": "admin-uuid", "role": "authenticated", "app_metadata": {"role": "admin"}}';
3SET role = 'authenticated';
4SELECT * FROM articles; -- Should return all
5INSERT INTO articles (title, author_id) VALUES ('Test', 'admin-uuid'); -- Should work
6
7-- Test as a viewer
8SET request.jwt.claims = '{"sub": "viewer-uuid", "role": "authenticated", "app_metadata": {"role": "viewer"}}';
9SET role = 'authenticated';
10SELECT * FROM articles; -- Should return only published
11INSERT INTO articles (title, author_id) VALUES ('Test', 'viewer-uuid'); -- Should fail
12
13-- Reset
14RESET role;
15RESET request.jwt.claims;