How to Write Row Level Security Policies in Supabase

1-- Enable RLS on a table
2alter table public.todos enable row level security;
3
4-- Verify RLS is enabled
5select tablename, rowsecurity
6from pg_tables
7where schemaname = 'public';
8
9-- Find tables WITHOUT RLS (security audit)
10select tablename from pg_tables
11where schemaname = 'public' and not rowsecurity;

1-- Policy for public access (no login required)
2create policy "Anyone can read published posts"
3  on public.posts for select
4  to anon, authenticated
5  using (published = true);
6
7-- Policy for authenticated users only
8create policy "Logged-in users can read all posts"
9  on public.posts for select
10  to authenticated
11  using (true);
12
13-- Never grant write access to anon unless absolutely necessary
14-- BAD: create policy "Anyone can insert" on public.posts for insert to anon ...
15-- GOOD: Only authenticated users can write
16create policy "Auth users can insert"
17  on public.posts for insert
18  to authenticated
19  with check (true);

1-- Users can only see their own todos
2create policy "Users can view own todos"
3  on public.todos for select
4  to authenticated
5  using ((select auth.uid()) = user_id);
6
7-- Public read access for a blog
8create policy "Anyone can read published articles"
9  on public.articles for select
10  to anon, authenticated
11  using (status = 'published');
12
13-- Team-based access: users see data from their team
14create policy "Team members can view team data"
15  on public.projects for select
16  to authenticated
17  using (
18    team_id in (
19      select team_id from public.team_members
20      where user_id = (select auth.uid())
21    )
22  );

1-- Users can only create their own todos
2create policy "Users can insert own todos"
3  on public.todos for insert
4  to authenticated
5  with check ((select auth.uid()) = user_id);
6
7-- Users can only create posts where they are the author
8create policy "Authors can create posts"
9  on public.posts for insert
10  to authenticated
11  with check (
12    (select auth.uid()) = author_id
13    and status in ('draft', 'published')
14  );
15
16-- Allow inserts with additional field validation
17create policy "Users can add team members to own teams"
18  on public.team_members for insert
19  to authenticated
20  with check (
21    team_id in (
22      select id from public.teams
23      where owner_id = (select auth.uid())
24    )
25  );

1-- Users can update their own todos
2create policy "Users can update own todos"
3  on public.todos for update
4  to authenticated
5  using ((select auth.uid()) = user_id)
6  with check ((select auth.uid()) = user_id);
7
8-- Authors can update their own posts but cannot change authorship
9create policy "Authors can update own posts"
10  on public.posts for update
11  to authenticated
12  using ((select auth.uid()) = author_id)
13  with check ((select auth.uid()) = author_id);
14
15-- Admins can update any post (using JWT custom claims)
16create policy "Admins can update any post"
17  on public.posts for update
18  to authenticated
19  using (
20    (select auth.jwt() -> 'app_metadata' ->> 'role') = 'admin'
21  );

1-- Users can delete their own todos
2create policy "Users can delete own todos"
3  on public.todos for delete
4  to authenticated
5  using ((select auth.uid()) = user_id);
6
7-- Only admins can delete posts
8create policy "Admins can delete any post"
9  on public.posts for delete
10  to authenticated
11  using (
12    (select auth.jwt() -> 'app_metadata' ->> 'role') = 'admin'
13  );
14
15-- Team owners can delete team projects
16create policy "Team owners can delete projects"
17  on public.projects for delete
18  to authenticated
19  using (
20    team_id in (
21      select id from public.teams
22      where owner_id = (select auth.uid())
23    )
24  );

1-- Complete example: Secure a todos table
2alter table public.todos enable row level security;
3
4create policy "select_own" on public.todos
5  for select to authenticated
6  using ((select auth.uid()) = user_id);
7
8create policy "insert_own" on public.todos
9  for insert to authenticated
10  with check ((select auth.uid()) = user_id);
11
12create policy "update_own" on public.todos
13  for update to authenticated
14  using ((select auth.uid()) = user_id)
15  with check ((select auth.uid()) = user_id);
16
17create policy "delete_own" on public.todos
18  for delete to authenticated
19  using ((select auth.uid()) = user_id);
20
21-- Performance: index the column used in policies
22create index idx_todos_user_id on public.todos using btree (user_id);
23
24-- Or use a combined policy for all operations
25-- (simpler but less granular)
26create policy "full_access_own" on public.todos
27  for all to authenticated
28  using ((select auth.uid()) = user_id)
29  with check ((select auth.uid()) = user_id);

1-- Test as anon: should return empty for authenticated-only tables
2-- curl with just the anon key
3
4-- Test as authenticated user: should return only their rows
5-- Use the Supabase Dashboard > API > Authentication section
6
7-- Quick test in SQL Editor (bypasses RLS - for admin verification only)
8select * from public.todos;
9
10-- Test RLS as a specific role
11set role authenticated;
12set request.jwt.claims = '{"sub": "user-uuid-here"}';
13select * from public.todos;
14reset role;