Test Row Level Security policies with pgTAP

Prove each Row Level Security (RLS) policy allows and blocks what you meant by running pgTAP tests against your local database.

For more information about how policies work, see Row Level Security.

Copy the following prompt to write RLS policy tests for your application:

Write and run a policy test#

The example tests a todos table whose policy lets each user access only their own rows.

1. Open a terminal in your project and start the local stack:

   1
   supabase start

2. Create a migration for the table and its policy:

   1
   supabase migration new create_todos

   In the new supabase/migrations/<timestamp>_create_todos.sql file, add the following SQL:

   1
   create table todos (
   2
     id uuid primary key default gen_random_uuid(),
   3
     task text not null,
   4
     user_id uuid references auth.users not null,
   5
     completed boolean default false
   6
   );
   7
   8
   alter table todos enable row level security;
   9
   10
   create policy "Users can only access their own todos"
   11
   on todos for all
   12
   to authenticated
   13
   using ((select auth.uid()) = user_id);
   14
   15
   -- index the column the policy filters on
   16
   create index todos_user_id_idx on todos using btree (user_id);

   Apply the migration to your local database:

   1
   supabase db reset

   supabase db reset recreates the local database from your migrations and seed file, so it deletes any data they don't create. To apply only the new migration, run supabase migration up instead.

3. Create the test file:

   1
   supabase test new todos_rls

4. In supabase/tests/todos_rls_test.sql, write the tests:

   1
   begin;
   2
   create extension if not exists pgtap with schema extensions;
   3
   select plan(4);
   4
   5
   insert into auth.users (id, email)
   6
   values
   7
     ('123e4567-e89b-12d3-a456-426614174000', 'user1@test.com'),
   8
     ('987fcdeb-51a2-43d7-9012-345678901234', 'user2@test.com');
   9
   10
   insert into public.todos (task, user_id)
   11
   values
   12
     ('User 1 Task 1', '123e4567-e89b-12d3-a456-426614174000'),
   13
     ('User 1 Task 2', '123e4567-e89b-12d3-a456-426614174000'),
   14
     ('User 2 Task 1', '987fcdeb-51a2-43d7-9012-345678901234');
   15
   16
   -- as user 1
   17
   set local role authenticated;
   18
   set local request.jwt.claim.sub = '123e4567-e89b-12d3-a456-426614174000';
   19
   20
   select results_eq(
   21
     'select count(*) from todos',
   22
     array[2::bigint],
   23
     'User 1 sees only their 2 todos'
   24
   );
   25
   26
   -- returning proves the row was written, not only that nothing failed
   27
   select results_eq(
   28
     $$insert into todos (task, user_id)
   29
       values ('New Task', '123e4567-e89b-12d3-a456-426614174000'::uuid)
   30
       returning task$$,
   31
     array['New Task'],
   32
     'User 1 can create their own todo'
   33
   );
   34
   35
   -- as user 2
   36
   set local request.jwt.claim.sub = '987fcdeb-51a2-43d7-9012-345678901234';
   37
   38
   select results_eq(
   39
     'select count(*) from todos',
   40
     array[1::bigint],
   41
     'User 2 sees only their 1 todo'
   42
   );
   43
   44
   select is_empty(
   45
     $$update todos set task = 'Hacked!'
   46
       where user_id = '123e4567-e89b-12d3-a456-426614174000'::uuid
   47
       returning 1$$,
   48
     'User 2 cannot modify User 1 todos'
   49
   );
   50
   51
   select * from finish();
   52
   rollback;

   The file runs in one transaction that rolls back at the end, so the test data never persists. set local role and request.jwt.claim.sub set the role and user each assertion runs as, the way a signed-in user's request does.

5. Run the tests:

   1
   supabase test db

   1
   psql:todos_rls_test.sql:2: NOTICE:  extension "pgtap" already exists, skipping
   2
   ./todos_rls_test.sql .. ok
   3
   All tests successful.
   4
   Files=1, Tests=4,  0 wallclock secs ( 0.01 usr +  0.00 sys =  0.01 CPU)
   5
   Result: PASS

   If any assertion fails, fix the policy or the test, then rerun supabase test db until the output ends with Result: PASS.

The policy now has a test that fails if a change lets one user read or edit another user's todos.

Test the policy from your application#

Test the same policy through the Supabase client your app uses. Unlike pgTAP tests, application-level tests can't use a transaction to keep their data from persisting.

The following Vitest suite mirrors the pgTAP tests above. It needs @supabase/supabase-js and vitest installed. Set SUPABASE_URL, SUPABASE_PUBLISHABLE_KEY, and SUPABASE_SECRET_KEY to the API_URL, PUBLISHABLE_KEY, and SECRET_KEY values that supabase status -o env prints.

1
import crypto from 'crypto'
2
import { createClient } from '@supabase/supabase-js'
3
import { beforeAll, describe, expect, it } from 'vitest'
4
5
describe('Todos RLS', () => {
6
  // Generate unique IDs for this test suite to avoid conflicts with other tests
7
  const USER_1_ID = crypto.randomUUID()
8
  const USER_2_ID = crypto.randomUUID()
9
10
  const supabase = createClient(process.env.SUPABASE_URL!, process.env.SUPABASE_PUBLISHABLE_KEY!)
11
12
  beforeAll(async () => {
13
    // Setup test data specific to this test suite
14
    const adminSupabase = createClient(process.env.SUPABASE_URL!, process.env.SUPABASE_SECRET_KEY!)
15
16
    // Create test users with unique IDs
17
    await adminSupabase.auth.admin.createUser({
18
      id: USER_1_ID,
19
      email: `user1-${USER_1_ID}@test.com`,
20
      password: 'password123',
21
      // We want the user to be usable right away without email confirmation
22
      email_confirm: true,
23
    })
24
    await adminSupabase.auth.admin.createUser({
25
      id: USER_2_ID,
26
      email: `user2-${USER_2_ID}@test.com`,
27
      password: 'password123',
28
      email_confirm: true,
29
    })
30
31
    // Create initial todos
32
    await adminSupabase.from('todos').insert([
33
      { task: 'User 1 Task 1', user_id: USER_1_ID },
34
      { task: 'User 1 Task 2', user_id: USER_1_ID },
35
      { task: 'User 2 Task 1', user_id: USER_2_ID },
36
    ])
37
  })
38
39
  it('should allow User 1 to only see their own todos', async () => {
40
    // Sign in as User 1
41
    await supabase.auth.signInWithPassword({
42
      email: `user1-${USER_1_ID}@test.com`,
43
      password: 'password123',
44
    })
45
46
    const { data: todos } = await supabase.from('todos').select('*')
47
48
    expect(todos).toHaveLength(2)
49
    todos?.forEach((todo) => {
50
      expect(todo.user_id).toBe(USER_1_ID)
51
    })
52
  })
53
54
  it('should allow User 1 to create their own todo', async () => {
55
    await supabase.auth.signInWithPassword({
56
      email: `user1-${USER_1_ID}@test.com`,
57
      password: 'password123',
58
    })
59
60
    const { error } = await supabase.from('todos').insert({ task: 'New Task', user_id: USER_1_ID })
61
62
    expect(error).toBeNull()
63
  })
64
65
  it('should allow User 2 to only see their own todos', async () => {
66
    // Sign in as User 2
67
    await supabase.auth.signInWithPassword({
68
      email: `user2-${USER_2_ID}@test.com`,
69
      password: 'password123',
70
    })
71
72
    const { data: todos } = await supabase.from('todos').select('*')
73
    expect(todos).toHaveLength(1)
74
    todos?.forEach((todo) => {
75
      expect(todo.user_id).toBe(USER_2_ID)
76
    })
77
  })
78
79
  it('should prevent User 2 from modifying User 1 todos', async () => {
80
    await supabase.auth.signInWithPassword({
81
      email: `user2-${USER_2_ID}@test.com`,
82
      password: 'password123',
83
    })
84
85
    // Attempt to update the todos we shouldn't have access to
86
    // RLS filters out the rows, so the update succeeds but changes nothing
87
    const { error: updateError } = await supabase
88
      .from('todos')
89
      .update({ task: 'Hacked!' })
90
      .eq('user_id', USER_1_ID)
91
    expect(updateError).toBeNull()
92
93
    // Log back in as User 1 to verify their todos weren't changed
94
    await supabase.auth.signInWithPassword({
95
      email: `user1-${USER_1_ID}@test.com`,
96
      password: 'password123',
97
    })
98
99
    // Fetch the todos seeded in beforeAll, so other tests' rows don't matter
100
    const { data: todos, error } = await supabase
101
      .from('todos')
102
      .select('task')
103
      .eq('user_id', USER_1_ID)
104
      .in('task', ['User 1 Task 1', 'User 1 Task 2'])
105
106
    // Verify that User 1 still sees both seeded todos, unchanged
107
    expect(error).toBeNull()
108
    expect(todos?.map((todo) => todo.task).sort()).toEqual(['User 1 Task 1', 'User 1 Task 2'])
109
  })
110
})

Keep application tests independent#

Use the following approaches so tests don't collide over shared data:

• Unique identifiers: generate unique IDs for each test suite.
• Cleanup: delete the data a suite creates in an afterAll or afterEach hook.
• Isolated data sets: use prefixes or namespaces in data to separate test cases.

Write quality tests with agents#

Give the following rules to the agent that writes your tests. Each rule keeps the suite focused on the mistakes a policy test exists to catch.

• Agree on the rules first. List each role and operation the policy must allow or block, such as "a signed-in user can't read another user's todo." Write one test per rule. A rule with no test is a gap, however many assertions the file has.
• Test the cross-user case. RLS exists to stop one user from reaching another user's rows. For each table that holds user data, assert that a second user can't read, update, or delete the first user's rows.
• Break each policy and watch the suite fail. For a policy that already exists, add a temporary policy on the same table that uses using (true), run supabase test db, and check that a test fails. Then drop the temporary policy. Dropping the real policy instead proves nothing, because with no policy every read is denied and denial tests still pass.
• Don't change a policy to make a test pass. When a test and a policy disagree, report which assertion failed and which policy it covers, and let the owner decide which one is wrong.
• Test through roles. Run each assertion as anon or authenticated with request.jwt.claim.sub set, and check the rows returned or the error raised. A check on pg_policies passes while the policy is still wrong.
• Filter reads on tables everyone can read. Select only the rows your test inserted, by ID. An exact match on the whole table fails once other data exists.

Related content#

• Policy tests in Row Level Security cover all four operations
• Advanced pgTAP testing
• pgTAP extension