Supabase Role-Based Access Control with Policies Only (No Row Level Code)

IMPORTANT

Supabase’s Row Level Security (RLS) is just native Postgres—everything here works in vanilla Postgres too.

We’ll build three roles—anon, user, admin—and restrict CRUD on a projects table entirely via SQL policies.

Table of Contents
1. Prerequisites & Concepts
2. Create Roles in Supabase Auth
3. Schema Setup
4. Policies
5. Test with SQL
6. Calling from Supabase JS
7. Gotchas & Best Practices
8. Automate with Migration Tooling
9. Further Reading
10. Conclusion

1. Prerequisites & Concepts

Supabase project with Auth enabled.
Basic SQL.

Term	Meaning
Role	JWT claim (`role`) set by Supabase Auth.
Policy	Postgres RLS rule that returns boolean per row.
Function policy	Policy executing arbitrary SQL instead of expression.

2. Create Roles in Supabase Auth

Supabase already issues two built-in roles: authenticated & anon. We’ll add admin via JWT custom claims.

2.1 Invite Admins

import { createClient } from '@supabase/supabase-js'
const supabase = createClient(url, key, {
  auth: { autoRefreshToken: false },
})
await supabase.auth.admin.inviteUser({
  email: 'jane@corp.com',
  user_metadata: { role: 'admin' },
})

The claim role propagates to jwt.claims.role inside SQL.

3. Schema Setup

create table projects (
  id uuid primary key default gen_random_uuid(),
  owner uuid references auth.users not null,
  name text not null,
  private boolean default false,
  created_at timestamptz default now()
);

Enable RLS:

alter table projects enable row level security;

4. Policies

4.1 Read Access

create policy "Public projects readable" on projects
  for select using (
    not private or auth.uid() = owner or (current_setting('request.jwt.claims.role', true) = 'admin')
  );

4.2 Insert

create policy "Only users create own project" on projects
  for insert with check ( owner = auth.uid() );

4.3 Update

create policy "Owner or admin can update" on projects
  for update using (
    auth.uid() = owner or current_setting('request.jwt.claims.role', true) = 'admin'
  );

4.4 Delete (Admins only)

create policy "Admins delete" on projects
  for delete using ( current_setting('request.jwt.claims.role', true) = 'admin' );

5. Test with SQL

set role authenticated; -- simulate logged-in user
set request.jwt.claims.role = 'user';
set request.jwt.claims.sub = '00000000-...';
select * from projects; -- returns public + owned

Change role to admin and verify delete works.

6. Calling from Supabase JS

const { data } = await supabase.from('projects').delete().eq('id', id)
// Will fail with 403 for normal users

Supabase surfaces 42501 Postgres error as 403 JSON.

7. Gotchas & Best Practices

Always run ANALYZE after heavy policy changes for planner.
Use EXPLAIN POLICY (Postgres 16) to debug evaluation.
Keep policies idempotent: create or replace policy ... not supported—drop then create.

8. Automate with Migration Tooling

supabase migration new rbac_projects → add SQL above.

CI example GitHub Action:

- run: supabase db push --non-interactive

9. Further Reading

Supabase docs: RLS Deep Dive.
Postgres official RLS guide.
pgaudit for logging policy hits.

10. Conclusion

You now have role-based access purely with Postgres policies—no backend code required. Your API keys inherit these rules instantly, keeping your stack lean and secure. 🔒🚀

Table of Contents