Topic: PostgreSQL

Supabase RLS Beyond the Policy UI: Make Authorization SQL Testable

Keep Supabase RLS, least-privilege grants, operation-specific policies, and allow-or-deny SQL tests in migrations so every environment can replay and review them.

Supabase Dashboard’s Policy UI is useful. It helps explore a table and makes a rule easy to inspect. But the next developer who receives the project, or a CI job that rebuilds the database, needs more than a screenshot. It needs SQL that Git can replay.

Consider public.tasks for a collaborative to-do list. It has id, owner_id, title, and done. Signed-in users can manage only their own tasks. Signed-out requests cannot access the table. To make that rule reviewable, the migration, table privileges, policies, and tests need to live together.

Bring Dashboard changes back to migrations

The Dashboard is a fine place to explore a policy, but important changes should return to SQL. If the remote database already contains a manual change, supabase db pull can capture the schema change so that the generated migration can be reviewed. For local verification, supabase db reset rebuilds the local database, reapplies migrations, and loads seed data when configured. The Supabase migrations guide separates creating, comparing, replaying, and pushing changes. It does not make the Dashboard the only source of truth.

For tasks, start a migration by keeping the table, RLS, and client-role privileges together:

create table public.tasks (
  id uuid primary key default gen_random_uuid(),
  owner_id uuid not null references auth.users(id),
  title text not null,
  done boolean not null default false
);

alter table public.tasks enable row level security;

revoke all on table public.tasks from anon, authenticated;
grant select, insert, update, delete on table public.tasks to authenticated;

The order matters for two reasons. A table created in a SQL migration does not enable RLS by itself. A policy also does not replace a GRANT. Supabase first checks whether a role may run an operation against a table, then checks which rows an RLS policy allows. If SELECT is missing, a correctly written read policy still loses at the privilege check.

Revoke the current client-role privileges first, then add back only the operations the application needs. That keeps privileges minimal. The Supabase RLS guide treats grants and policies as separate checks that belong in the same review.

anon is the database role for a signed-out request

anon is the PostgreSQL role Supabase uses for an unauthenticated API request. A request with a valid signed-in session uses the authenticated role. This is different from an anonymous user account in Supabase Auth. An anonymous Auth user does not have a permanent identity, but still accesses the database as authenticated and can be distinguished with a JWT claim.

TO authenticated therefore means that a policy applies to signed-in requests. It does not automatically block every other role from the table. Each role still needs the appropriate table GRANT. The service_role has full table privileges and bypasses RLS, so it belongs only in trusted server-side code, never in a browser.

Write one policy for each operation

Reading, creating, changing, and deleting rows do not all inspect the same state. Separating the operations makes an unintended permission expansion easier to notice:

create policy "task owners read"
on public.tasks for select to authenticated
using ((select auth.uid()) = owner_id);

create policy "task owners insert"
on public.tasks for insert to authenticated
with check ((select auth.uid()) = owner_id);

create policy "task owners update"
on public.tasks for update to authenticated
using ((select auth.uid()) = owner_id)
with check ((select auth.uid()) = owner_id);

create policy "task owners delete"
on public.tasks for delete to authenticated
using ((select auth.uid()) = owner_id);

USING checks an existing row. It controls which rows a request can read and which rows an UPDATE or DELETE can target. WITH CHECK evaluates the proposed row after a write. An UPDATE needs both conditions: one stops a user from targeting someone else’s task, and the other stops a user from changing their own task’s owner_id to another person. The official examples also note that a Data API UPDATE needs a matching SELECT policy to behave as expected.

Test whether access is really denied

Create an SQL test in supabase/tests/ and run its pgTAP assertions with supabase test db. Wrap the test in a transaction so its data and request identity do not leak into the next case:

begin;

select plan(3);

set local role authenticated;
set local request.jwt.claim.sub = '11111111-1111-1111-1111-111111111111';

select results_eq(
  $$select title from public.tasks where id = 'aaaaaaaa-aaaa-aaaa-aaaa-aaaaaaaaaaaa'$$,
  array['write migration'],
  'owner reads the task'
);

set local request.jwt.claim.sub = '22222222-2222-2222-2222-222222222222';

select is_empty(
  $$update public.tasks
      set done = true
      where id = 'aaaaaaaa-aaaa-aaaa-aaaa-aaaaaaaaaaaa'
      returning id$$,
  'another user updates no task'
);

select * from finish();
rollback;

The important part is not the testing syntax. Each case explicitly sets a role and request identity, then proves both an allowed result and a denied result. A successful write should use RETURNING to inspect what actually changed. A denied write should confirm that no row changed. The absence of an exception is not enough.

If one row in a multi-row INSERT fails WITH CHECK, the whole SQL statement fails with a policy violation. It does not write the valid rows and silently skip the invalid one. Test batch writes for this reason. The statement stays atomic, and the client cannot mistake a partial write for a complete one.

Connect the UI, SQL, and CI

The working flow can stay short:

1. Enable RLS in a migration, narrow grants, and create a policy for each operation.
2. Replay the complete schema locally with supabase db reset.
3. In supabase/tests/, verify denial for anon, access for the owner, and denial for another signed-in user.
4. Run supabase test db, review the migration, then advance it through the established deployment process.

supabase db diff can help generate a change proposal. Authorization work still needs code review that identifies each table privilege a role receives and each row set a policy exposes. Recent developer discussion about RLS testing comes back to the same need: a policy existing is not proof of its behaviour. That discussion is adoption context only. Supabase and PostgreSQL documentation remain the source for semantics.

What I learned

• I will review table GRANTs and RLS policies separately. A precise policy does not add a missing table privilege.
• I will treat anon as the PostgreSQL role for a signed-out request, rather than confusing it with an anonymous Supabase Auth account.
• I will test both allowed and denied operations, including batch-write atomicity and whether a cross-user write leaves data unchanged.
• I will bring Dashboard exploration back into migrations and SQL tests so the same authorization rules can be replayed in another environment.

External references

• Supabase: Row Level Security
• Supabase: Database migrations
• Supabase: Testing overview
• Supabase: Local development workflow
• Reddit: How do you actually test your RLS policies before shipping?

Further learning

• Everything you need to know about Postgres Row Level Security | POSETTE 2024: Paul Copplestone explains the SQL policy model. Use current official documentation for CLI and Supabase workflow details.