Automating Supabase RLS Policy Testing with pgTAP — How to Codify Role-Based Access Scenarios in SQL

I'm probably not the only one who wrote RLS policies for the first time, thought "it'll be fine," pushed to production, and broke into a cold sweat afterward. I ran a query in the dashboard, it seemed to be blocking correctly, so I relaxed — only to find out later that the SQL editor was running as a superuser and completely bypassing RLS. The embarrassment of that moment is still vivid.

As has been reported, hundreds of apps built with AI coding tools have suffered incidents where databases were exposed with RLS entirely disabled. The AI wrote the policies, but nobody verified they actually worked. These incidents have driven growing recommendations to validate RLS policies with automated tests. This post covers the pattern of codifying role-based access scenarios as SQL-level unit tests using pgTAP.

To follow along, you'll need a Supabase project, the Supabase CLI (v1.11.4 or higher), and basic SQL knowledge.

Core Concepts

Why RLS Fails Silently

Row Level Security works by having PostgreSQL evaluate a policy expression for each row and include only rows where it returns true. The critical point is that SELECT, UPDATE, and DELETE silently ignore rows that don't pass the policy — no error is thrown. Only INSERT raises a 42501 insufficient_privilege error on violation; the other three quietly return empty results.

Silent failure: Behavior where a security policy violation produces no error and instead returns an empty result. From a developer's perspective, it's difficult to distinguish "it passed" from "it was blocked" based solely on the query result.

This characteristic makes manual testing prone to gaps. "The query returned nothing, so it must be blocked" — that assumption could easily be wrong if the query itself was malformed or RLS was disabled. This is exactly the trap I fell into when I first tried to validate RLS using the SQL editor.

How pgTAP Solves This Problem

pgTAP is a TAP (Test Anything Protocol)-based testing framework for PostgreSQL. Tests are declared inside SQL scripts using plan(), assertion functions, and finish(), wrapped in a transaction that ends with ROLLBACK. After the tests run, the database state is restored to what it was before.

TAP (Test Anything Protocol): A protocol that outputs test results in a standardized text format. Results appear in a format like ok 1 - owner can read, which CI tools parse to determine pass/fail.

The key to RLS verification is role switching and JWT claim injection. By setting SET LOCAL role and SET LOCAL request.jwt.claims in a test session, PostgreSQL can impersonate a specific user context. Supabase's auth.uid() and auth.role() functions read these PostgreSQL session settings to return JWT claims. In other words, if you inject your desired value for request.jwt.claims in a test, that value is reflected directly in auth.uid() and auth.role().

The JWT sub claim is the user's unique ID — in Supabase, auth.uid() returns this value.

-- Role switching principle (written directly without helpers)
SET LOCAL role TO 'authenticated';
SET LOCAL request.jwt.claims TO '{"sub": "user-uuid-here", "role": "authenticated"}';
 
-- This session now behaves as a user with that JWT
-- auth.uid() returns "user-uuid-here", auth.role() returns "authenticated"
SELECT * FROM posts; -- RLS policies are applied

The supabase-test-helpers package abstracts this entire process into a single tests.authenticate_as('username') call.

Three Core Roles and Their Gotchas

The roles most commonly used in Supabase RLS policies:

One important gotcha: because service_role completely bypasses RLS, accidentally setting a test to run as service_role produces a test that verifies nothing about your RLS policies. "All tests are passing, so why is data leaking in production?" — that's exactly this scenario.

Environment Setup

The following setup is required before running the example code.

1. Enable the pgTAP extension

supabase db extension enable pgtap

2. Install supabase-test-helpers

database.dev is a package manager for PostgreSQL — like npm, it lets you install and manage community SQL packages. supabase-test-helpers is a collection of helper functions specifically designed for Supabase RLS testing, and it's available there.

-- supabase/tests/000-setup.sql
-- dbdev (the database.dev client) must be installed
select dbdev.install('basejump/supabase_test_helpers');
 
-- Verify that helper functions loaded correctly
select has_function(
  'tests',
  'authenticate_as',
  ARRAY['text'],
  'supabase_test_helpers is installed correctly'
);

Since 000-setup.sql runs first in alphabetical order, it's common practice to put shared fixture setup and helper installation verification in this file.

Practical Application

Example 1: Verifying an RLS Policy That Allows Only the Post Owner to Read and Modify

Let's start with the most common pattern. When a posts table has a policy that allows "only the owner can read or modify their own posts," we verify all three roles — owner, stranger, and unauthenticated user — in one go.

First, the schema for the table under test:

-- Example migration file (preconditions for the test)
create table posts (
  id      uuid primary key default gen_random_uuid(),
  user_id uuid references auth.users(id) on delete cascade,
  title   text not null
);
 
alter table posts enable row level security;
 
-- Only the owner can read, modify, or delete their own posts
create policy "owner access" on posts
  for all
  using (auth.uid() = user_id);

Here's the pgTAP test that verifies this policy:

-- supabase/tests/002-posts-rls.sql
BEGIN;
SELECT plan(4);
 
-- Create two test users
-- tests.create_supabase_user() inserts a test user into auth.users,
-- auto-generates a UUID, and returns it. Cleaned up on ROLLBACK.
SELECT tests.create_supabase_user('owner');
SELECT tests.create_supabase_user('stranger');
 
-- Authenticate as owner and insert a post
SELECT tests.authenticate_as('owner');
INSERT INTO posts (title, user_id)
VALUES ('Secret Post', tests.get_supabase_uid('owner'));
 
-- 1. The owner can see their own post
SELECT results_eq(
  $$ SELECT title FROM posts $$,
  $$ VALUES ('Secret Post'::text) $$,
  'owner can read their own post'
);
 
-- 2. A stranger cannot see it
SELECT tests.authenticate_as('stranger');
SELECT is_empty(
  $$ SELECT title FROM posts $$,
  'another user cannot read someone else''s post'
);
 
-- 3. An unauthenticated user cannot see it
SELECT tests.clear_authentication();
SELECT is_empty(
  $$ SELECT title FROM posts $$,
  'anon user cannot read posts'
);
 
-- 4. Stranger attempts to modify — use RETURNING * with is_empty to verify silent blocking
SELECT tests.authenticate_as('stranger');
SELECT is_empty(
  $$ UPDATE posts SET title = 'hacked' RETURNING * $$,
  'another user cannot modify the post'
);
 
SELECT * FROM finish();
ROLLBACK;

Key patterns to note in the code:

Example 2: Verifying INSERT Blocking — Using throws_ok

Now that we've seen SELECT and UPDATE blocking, let's look at why INSERT is handled differently. Unlike UPDATE, INSERT throws an error on RLS violation, so in this case we use throws_ok instead of is_empty.

-- Attempt to INSERT with another user's user_id
SELECT tests.authenticate_as('stranger');
 
SELECT throws_ok(
  $$ INSERT INTO posts (title, user_id)
     VALUES ('Forged Post', tests.get_supabase_uid('owner')) $$,
  '42501',
  NULL,
  'RLS should block INSERT under someone else''s identity'
);

The second argument to throws_ok is the PostgreSQL error code. 42501 corresponds to insufficient_privilege. Honestly, I used to write is_empty for INSERT blocking too, and was confused when the test blew up with an error. I learned the hard way that the blocking mechanism differs between INSERT and SELECT/UPDATE/DELETE.

Example 3: Test File Organization and CI Integration

Test files run in alphabetical order, so using numbered prefixes to control execution order is a well-established convention.

supabase/tests/
  000-setup.sql        ← helper installation check, shared fixtures
  001-auth-rls.sql     ← RLS tests for auth-related tables
  002-posts-rls.sql    ← RLS tests for posts
  003-comments-rls.sql ← RLS tests for comments

This GitHub Actions integration is sufficient to get started:

# .github/workflows/test.yml
name: Database Tests
 
on: [pull_request]
 
jobs:
  test:
    runs-on: ubuntu-latest
    steps:
      - uses: actions/checkout@v3
 
      - uses: supabase/setup-cli@v1
        with:
          version: latest
 
      - name: Start Supabase local stack
        run: supabase start
 
      - name: Apply migrations
        run: supabase db reset
 
      - name: Run pgTAP tests
        run: supabase test db

supabase db reset is the step that applies migrations. If you skip it and run supabase test db directly, the tests will run against a state with no tables and fail entirely. I spent a long time confused the first time I set up CI because I skipped this step, so make sure you don't.

Pros and Cons Analysis

Advantages

Disadvantages and Caveats

The Most Common Mistakes in Practice

1. Validating RLS using the SQL editor — The SQL editor in the Supabase dashboard runs as the postgres superuser, completely bypassing RLS policies. Saying "it was blocking correctly in the editor" is effectively the same as "I never actually saw it block." This is the trap I fell into initially, and the source of the cold sweat mentioned in the introduction.

2. Expecting UPDATE/DELETE blocking to surface as errors — Unlike INSERT, blocking of UPDATE/DELETE/SELECT does not throw an error. Using throws_ok will cause the test itself to fail. Use is_empty with RETURNING * to verify the result instead.

3. Mismatching the plan() count with the actual number of assertions — If you declare plan(3) but only write 2 assertions, pgTAP fails the test with "not enough tests." Whenever you add or remove a test, update the plan() number accordingly.

Closing Thoughts

Declaring an RLS policy is not enough on its own — it only becomes trustworthy when accompanied by tests that codify role-based scenarios in code. At first, "writing tests in SQL" may feel unfamiliar, but once you get the hang of it, you'll find it's actually the most intuitive approach to security verification.

Three steps you can take right now:

1. Start with the helper installation — Enable pgTAP with supabase db extension enable pgtap, create supabase/tests/000-setup.sql, and install basejump/supabase_test_helpers from database.dev (the PostgreSQL-native package manager). That gives you convenience functions like tests.authenticate_as() right away.
2. Start with your most important table — Trying to cover all tables at once is overwhelming. Pick one table that holds personal data, write a 001-tablename-rls.sql that verifies just three roles — owner, stranger, and anon — and the pattern will feel natural quickly.
3. Attach it to CI — Add supabase db reset followed by supabase test db to your GitHub Actions workflow in that order, and every future RLS policy change will be verified automatically. Set it up once, and it will eliminate the anxiety of "I wonder if anything broke" on every PR.

References

• Testing Overview | Supabase
• Advanced pgTAP Testing | Supabase
• pgTAP Extension | Supabase
• Row Level Security | Supabase
• RLS Performance and Best Practices | Supabase
• Automated testing with GitHub Actions | Supabase
• supabase-test-helpers | GitHub
• A Guide to testing on Supabase using pgTAP | Basejump Blog
• Testing RLS Policies in PostgreSQL with pgTAP | Blair Jordan (Medium)
• basejump/supabase_test_helpers | database.dev
• pgTAP Official Site
• Postgres RLS Limitations and Alternatives | Bytebase