Supabase

Testing Supabase Row Level Security Policies with pgTAP

HelpMeTest

19 May 2026 — 8 min read

Row Level Security is one of Supabase's most powerful features — and one of the most dangerous to get wrong. A misconfigured RLS policy can silently expose every row in a table to every authenticated user, or lock out legitimate access entirely. Neither failure is obvious until something goes wrong in production.

The good news: RLS policies are pure PostgreSQL, which means they are testable with pgTAP — a full TAP-compliant unit testing framework that runs inside the database. This guide covers how to write comprehensive RLS tests, simulate different user roles, and build the coverage patterns you need to sleep soundly after a schema change.

Why Test RLS Policies Separately

Application-level tests (Playwright, Cypress, Jest) verify that your UI behaves correctly. They do not verify that the database enforces access rules independently of the application. An attacker who bypasses your API layer, a misconfigured service role key in a serverless function, or a future developer who forgets that a table has RLS — these all exploit gaps that application tests miss.

pgTAP tests run inside PostgreSQL, as specific roles, against specific policies. They are your last line of defense.

Setting Up pgTAP

Supabase's local development stack (supabase start) includes pgTAP by default. For a fresh project:

supabase start
# pgTAP is available in the local instance automatically

To run tests, use pg_prove — a Perl-based TAP runner for PostgreSQL:

# Install pg_prove (part of TAP::Parser::SourceHandler::pgTAP)
cpanm TAP::Parser::SourceHandler::pgTAP

<span class="hljs-comment"># Run all test files
pg_prove -U postgres -d postgres tests/**/*.sql

<span class="hljs-comment"># Or run a single file
pg_prove -U postgres -d postgres tests/rls/posts_policy_test.sql

Alternatively, pipe tests directly through psql:

psql -U postgres -d postgres -f tests/rls/posts_policy_test.sql

The Schema We'll Test

Throughout this guide we'll use a simple multi-tenant blog schema:

-- Enable RLS on the posts table
create table public.posts (
  id         uuid primary key default gen_random_uuid(),
  user_id    uuid references auth.users(id) not null,
  title      text not null,
  content    text,
  published  boolean default false,
  created_at timestamptz default now()
);

alter table public.posts enable row level security;

-- Policies
create policy "users can see their own posts"
  on public.posts for select
  using (auth.uid() = user_id);

create policy "users can see published posts from anyone"
  on public.posts for select
  using (published = true);

create policy "users can insert their own posts"
  on public.posts for insert
  with check (auth.uid() = user_id);

create policy "users can update their own posts"
  on public.posts for update
  using (auth.uid() = user_id);

create policy "users can delete their own posts"
  on public.posts for delete
  using (auth.uid() = user_id);

Writing pgTAP Tests

A pgTAP test file is a SQL file that calls TAP assertion functions. Every test starts with plan() specifying how many assertions you expect, runs the assertions, and ends with finish().

-- tests/rls/posts_policy_test.sql
begin;

select plan(12);

-- Create test users
insert into auth.users (id, email) values
  ('00000000-0000-0000-0000-000000000001', 'alice@example.com'),
  ('00000000-0000-0000-0000-000000000002', 'bob@example.com');

-- Create test data
insert into public.posts (id, user_id, title, published) values
  ('post-alice-private', '00000000-0000-0000-0000-000000000001', 'Alice Private', false),
  ('post-alice-public',  '00000000-0000-0000-0000-000000000001', 'Alice Public',  true),
  ('post-bob-private',   '00000000-0000-0000-0000-000000000002', 'Bob Private',   false),
  ('post-bob-public',    '00000000-0000-0000-0000-000000000002', 'Bob Public',    true);

-- =============================================
-- Test SELECT policies as Alice
-- =============================================

set local role authenticated;
set local request.jwt.claims = '{"sub": "00000000-0000-0000-0000-000000000001"}';

select results_eq(
  $$ select count(*)::int from public.posts $$,
  $$ values (3) $$,
  'Alice sees her own posts + all published posts (3 total)'
);

select is_empty(
  $$ select * from public.posts where id = 'post-bob-private' $$,
  'Alice cannot see Bob''s private post'
);

select isnt_empty(
  $$ select * from public.posts where id = 'post-bob-public' $$,
  'Alice can see Bob''s published post'
);

-- =============================================
-- Test SELECT policies as Bob
-- =============================================

set local request.jwt.claims = '{"sub": "00000000-0000-0000-0000-000000000002"}';

select results_eq(
  $$ select count(*)::int from public.posts $$,
  $$ values (3) $$,
  'Bob sees his own posts + all published posts (3 total)'
);

select is_empty(
  $$ select * from public.posts where id = 'post-alice-private' $$,
  'Bob cannot see Alice''s private post'
);

select finish();
rollback;

Notice the pattern: we set local role authenticated and then set request.jwt.claims to simulate what Supabase's PostgREST layer injects. The auth.uid() function reads from request.jwt.claims -> 'sub', so this is an exact simulation of a real authenticated request.

Testing INSERT Policies

INSERT policies use WITH CHECK rather than USING. This means the check runs against the row being inserted, not existing rows. Testing them requires attempting inserts and verifying success or failure.

-- Test INSERT as Alice
set local role authenticated;
set local request.jwt.claims = '{"sub": "00000000-0000-0000-0000-000000000001"}';

-- Alice can insert her own post
select lives_ok(
  $$ insert into public.posts (user_id, title) values
       ('00000000-0000-0000-0000-000000000001', 'New Alice Post') $$,
  'Alice can insert a post as herself'
);

-- Alice cannot insert a post as Bob
select throws_ok(
  $$ insert into public.posts (user_id, title) values
       ('00000000-0000-0000-0000-000000000002', 'Impersonation Attempt') $$,
  '42501',
  null,
  'Alice cannot insert a post with Bob''s user_id'
);

The throws_ok assertion takes the SQLSTATE code as its second argument. 42501 is PostgreSQL's insufficient_privilege error — the code thrown when an RLS WITH CHECK fails.

Testing UPDATE and DELETE Policies

UPDATE and DELETE require rows to already exist. The test pattern is: set up the row, switch roles, attempt the operation, verify the outcome.

-- =============================================
-- Test UPDATE policies
-- =============================================

set local role authenticated;
set local request.jwt.claims = '{"sub": "00000000-0000-0000-0000-000000000001"}';

-- Alice can update her own post
select lives_ok(
  $$ update public.posts set title = 'Updated Title'
     where id = 'post-alice-private' $$,
  'Alice can update her own post'
);

-- Alice updating Bob's post silently affects 0 rows (UPDATE USING filters the target set)
select results_eq(
  $$ with updated as (
       update public.posts set title = 'Hijacked'
       where id = 'post-bob-private'
       returning id
     ) select count(*)::int from updated $$,
  $$ values (0) $$,
  'Alice''s update on Bob''s post affects 0 rows (silently filtered by USING)'
);

-- =============================================
-- Test DELETE policies
-- =============================================

set local request.jwt.claims = '{"sub": "00000000-0000-0000-0000-000000000002"}';

-- Bob can delete his own post
select lives_ok(
  $$ delete from public.posts where id = 'post-bob-public' $$,
  'Bob can delete his own post'
);

-- Bob cannot delete Alice's post (silently filtered)
select results_eq(
  $$ with deleted as (
       delete from public.posts where id = 'post-alice-public'
       returning id
     ) select count(*)::int from deleted $$,
  $$ values (0) $$,
  'Bob''s delete on Alice''s post affects 0 rows'
);

An important subtlety: UPDATE and DELETE policies with USING silently filter the target set — they do not throw errors. This is correct PostgreSQL behavior, but it means your assertion is results_eq with a row count of 0, not throws_ok. If you expect an error here and get silence, the policy is actually working correctly.

Testing the Unauthenticated Role

Always test what happens when there is no authenticated session. The anon role in Supabase represents unauthenticated requests.

-- =============================================
-- Test anon (unauthenticated) access
-- =============================================

set local role anon;

-- Anon cannot see any posts (no policy grants anon access)
select results_eq(
  $$ select count(*)::int from public.posts $$,
  $$ values (0) $$,
  'Unauthenticated users see zero posts'
);

-- Anon cannot insert
select throws_ok(
  $$ insert into public.posts (user_id, title) values
       ('00000000-0000-0000-0000-000000000001', 'Anon Attack') $$,
  '42501',
  null,
  'Unauthenticated users cannot insert posts'
);

Testing the Service Role Bypass

The Supabase service role (service_role) bypasses RLS entirely. This is intentional for server-side operations, but it means your backend code using the service role key can read and write everything. Test that this bypass works as expected — and document it explicitly so future developers don't assume RLS protects service-role calls.

-- =============================================
-- Verify service_role bypasses RLS
-- =============================================

set local role service_role;

select results_eq(
  $$ select count(*)::int from public.posts $$,
  $$ values (4) $$,
  'service_role sees all 4 posts (RLS bypass)'
);

Policy Coverage Patterns

A systematic approach to RLS coverage means testing every combination of role × operation × data state:

Role	SELECT own	SELECT other	SELECT published	INSERT own	INSERT as other	UPDATE own	UPDATE other	DELETE own	DELETE other
`authenticated` (as owner)	✓	—	✓	✓	✗	✓	0 rows	✓	0 rows
`authenticated` (as other)	—	✗	✓	—	✗	—	0 rows	—	0 rows
`anon`	✗	✗	✗	✗	✗	—	—	—	—
`service_role`	✓	✓	✓	✓	✓	✓	✓	✓	✓

Build one test assertion per cell in this matrix. If you have 5 tables with RLS, that is roughly 200 assertions — all running inside the database in under a second.

Catching Policy Gaps with Negative Tests

The most dangerous RLS bugs are the ones that allow access they shouldn't. Always include negative tests that assert access is denied:

-- These must ALL return 0 rows or throw 42501
-- If any of these pass, you have a policy gap

set local role authenticated;
set local request.jwt.claims = '{"sub": "00000000-0000-0000-0000-000000000001"}';

-- Alice must not be able to see unpublished posts from other users
select is_empty(
  $$ select * from public.posts
     where user_id != '00000000-0000-0000-0000-000000000001'
     and published = false $$,
  'SECURITY: Alice cannot see other users'' unpublished posts'
);

Prefix these assertions with SECURITY: in the test description to make them visually prominent in CI output.

Integrating with CI

Add a Makefile target or npm script to run RLS tests as part of your pipeline:

# package.json
{
  <span class="hljs-string">"scripts": {
    <span class="hljs-string">"test:rls": <span class="hljs-string">"supabase start && pg_prove -U postgres -d postgres tests/rls/**/*.sql",
    <span class="hljs-string">"test:rls:watch": <span class="hljs-string">"nodemon --watch supabase/migrations --watch tests/rls -e sql --exec 'npm run test:rls'"
  }
}

In GitHub Actions:

- name: Start Supabase
  run: supabase start

- name: Run RLS tests
  run: pg_prove -U postgres -d postgres tests/rls/**/*.sql

What Happens When a Migration Changes a Policy

When you modify an RLS policy in a migration, run the full pgTAP suite immediately. A migration that renames a column, changes a foreign key, or alters the auth schema can silently break policies that reference those objects. pgTAP catches this before it reaches production.

A useful pre-commit hook:

#!/bin/sh
<span class="hljs-comment"># .git/hooks/pre-commit
changed=$(git diff --cached --name-only <span class="hljs-pipe">| grep <span class="hljs-string">'supabase/migrations/')
<span class="hljs-keyword">if [ -n <span class="hljs-string">"$changed" ]; <span class="hljs-keyword">then
  <span class="hljs-built_in">echo <span class="hljs-string">"Migrations changed — running RLS tests..."
  pg_prove -U postgres -d postgres tests/rls/**/*.sql <span class="hljs-pipe">|| <span class="hljs-built_in">exit 1
<span class="hljs-keyword">fi

Pairing RLS Tests with End-to-End Tests

pgTAP verifies that the database enforces your policies correctly. But you also need end-to-end tests to verify that your application presents those policies correctly — that a user who can't see a post also doesn't see it in the UI, that a 403 from the API is handled gracefully, that error messages don't leak data.

For the end-to-end layer, HelpMeTest provides AI-powered test automation with Robot Framework and Playwright. You can describe your access control scenarios in plain English, run them as 24/7 health monitors, and catch regressions the moment a deployment breaks something. It complements your pgTAP layer by covering the full stack — from the browser down to the HTTP response — at $100/month.

Summary

RLS policy testing with pgTAP gives you database-level confidence that no application code can bypass. The key patterns:

Use set local role and set local request.jwt.claims to simulate exact user sessions
Test every combination of role × operation × data state
Use throws_ok for INSERT violations and results_eq with 0 rows for silent UPDATE/DELETE filtering
Always include negative tests labeled SECURITY: to catch access that should be denied
Verify the anon and service_role behaviors explicitly
Run the full suite on every migration change

Your RLS policies are only as reliable as the tests that prove them.