How to Test Supabase RLS: What pgTAP Alone Misses

Testing Supabase RLS has two different meanings, and most guides only cover one of them. The first is whether the policy you wrote behaves the way you intended, which is what a pgTAP suite checks from inside a trusted database connection. The second is whether the API sitting in front of that policy actually enforces it against a stranger who never authenticated, or a second account trying to read the first one’s rows. A policy can pass every pgTAP assertion you wrote and still leak, because pgTAP tests what you remembered to assert, and a real attacker doesn’t limit themselves to your assertions.

Proving Supabase RLS holds takes three checks, and I run them in the same order on every Supabase audit: log out and hit the API with nothing but the public key, log in as a second account and go after the first account’s data, and enumerate the one class of Postgres function that skips RLS entirely, whether or not you ever wrote a policy for it.

How to check RLS is actually on

Before testing whether a policy holds, confirm one exists. Supabase’s Table Editor marks a table “RLS enabled” or “RLS disabled” directly in its row of the table list, and the same fact lives in Postgres itself: pg_class.relrowsecurity is true only for tables where someone has run alter table ... enable row level security. Supabase’s row-level-security guide is direct that these are two separate actions: enabling RLS and writing a policy for it. Their own wording on what happens in between is blunt: once RLS is on, “no data will be accessible via the API when using a publishable key, until you create policies.” A table with RLS off is readable and writable by anyone holding the project’s anon key.

If you’re already past this stage and Supabase is throwing “new row violates row-level security policy” back at you, that’s the opposite problem: a policy that’s on and refusing you specifically, not one that’s missing. Everything from here assumes the simpler starting question: does a policy exist, and does it check the right thing.

The 60-second anon-key probe

The fastest test needs no login and no account. Point the Supabase JS client at your project using only the public anon key, the same key that already ships in your browser bundle, and ask for a table you never intended to expose:

import { createClient } from '@supabase/supabase-js'

// The anon key only. No session, no login.
const supabase = createClient(SUPABASE_URL, SUPABASE_ANON_KEY)

const { data, error } = await supabase.from('orders').select()
console.log(data?.length ?? 0, error)

That’s the exact shape supabase-js’s own reference documents for a plain select, pointed at a real table instead of a demo one. If it returns real rows, RLS is off on that table, or a policy exists that doesn’t check who’s asking. If it returns an empty array or a permission error, the anon path is closed, which is one of three things this post checks, not all three. Run it against every table that holds anything a customer would call private, rather than just the one you’re already worried about; the habit of protecting the table everyone remembers and skipping the one added last month is how gaps like this survive to launch.

The two-account test

The anon-key probe only catches a table that’s open to everyone logged out. It says nothing about a policy that checks “is someone logged in” and stops there, without checking who. That failure needs two real accounts, not two browser tabs on the same session.

Create a second account in your own app, and once you’re logged in as it, try to read or write the first account’s row by changing an id inside the request body your app already sends, not the page URL. A URL-based id is often caught by whatever routing or middleware you have; an id set inside a POST or PATCH payload usually reaches the database with nothing in front of it but the policy itself, which is closer to the path a working exploit takes. If account B’s request for account A’s row returns data, or account B’s update against account A’s row succeeds, the policy is checking session status and not ownership, the single most common way a correct-looking RLS setup still lets one customer touch another’s account.

This test has no shortcut through a linter or a static scanner, because both accounts have to be real, logged in, and used against each other. A tool can confirm a policy references auth.uid() somewhere in its clause, and that presence is all it confirms, the same presence-versus-correctness gap behind most of the holes AI coding tools ship by default. Only a second account proves the comparison is actually happening against the row being requested, and running the write half of this test alongside the read half is what catches a policy that lets account B corrupt a row instead of only viewing it, the same boundary failure behind the data-loss bugs AI-built apps ship by default.

What pgTAP proves, and where it stops

pgTAP is the method Supabase’s own testing guide recommends for testing RLS policies directly against the database: a Postgres extension that runs inside supabase test db, asserting facts about your schema from a SQL test file the CLI scaffolds for you. It’s the right tool for what it’s built for. A minimal RLS test confirms the policies attached to a table are the ones you expect, then simulates a specific user and checks that a query returns only that user’s rows:

-- supabase/tests/rls.sql
begin;
select plan(2);

select policies_are(
  'public', 'orders',
  ARRAY['Users can view their own orders', 'Users can insert their own orders']
);

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

select results_eq(
  'select count(*) from orders',
  ARRAY[2::bigint],
  'A logged-in user sees only their own 2 orders'
);

select * from finish();
rollback;

That test is correct, and it will fail loudly the day someone edits the policy and breaks it. Its ceiling is the connection it runs on: a trusted database session you control, asserting exactly the scenarios you thought to write. It never goes through PostgREST, never carries an anon key that isn’t yours, and never simulates a second account you didn’t script in. A test suite with a hundred green pgTAP assertions and zero of them aimed at a stranger holding the anon key has tested the policy’s syntax and never touched the boundary an actual visitor reaches.

RLS tests what you remembered to assert. An attacker tests what you forgot.

The blind spot: SECURITY DEFINER functions that trust the caller

Every check so far assumes RLS is the only gate between a request and a row. Postgres has a second gate that ignores RLS by definition: a function declared security definer carries its creator’s privileges rather than the caller’s, which is exactly what lets it skip row-level security on purpose, for the legitimate cases that need it. The same property makes it the one bypass that no RLS policy, no pgTAP suite written against policies, and no static RLS linter will ever catch, because none of them read what the function itself decides to trust. A function that takes a user id as a plain argument instead of reading it off the authenticated session hands out whatever that argument names to anyone who calls it, and the table’s own RLS policy never gets a vote. Missing it is no mark against the policy work you already did: security definer is the one feature Postgres built specifically to skip the check you just spent this whole post verifying.

The check that catches this doesn’t live in any test suite I’ve seen ship by default. At the start of every Supabase audit, I query information_schema.routines for every function where security_type = 'DEFINER', then read what each one actually does before deciding it’s fine.

select routine_schema, routine_name
from information_schema.routines
where security_type = 'DEFINER';

It’s usually a short list, a dozen functions at most, and the ones worth worrying about take an id as an argument instead of comparing against the caller’s own session. Across the AxonBuild audit corpus, 9 of 21 third-party apps had a gap in row-level security, each finding verified against the code, not pattern-matched, and the sharpest variant of that gap was exactly this shape: a policy that was entirely correct, sitting under a function that never checked it.

Writing the policies themselves well, which roles they name, which columns they compare, is a separate skill; this post assumes you already have policies and is only asking whether they hold. Whether every one of them is enforced against the API the way your app actually calls it is what a Beyond the Demo Audit enumerates: every RPC and every policy checked against the queries your app actually runs, not against whether a policy merely exists.

A repeatable RLS test checklist

None of the checks above take long once you’ve run them a first time. Copy this list and run it at every schema change, not once at launch.

1. 01 Confirm RLS is enabled on every table holding user data, in the Table Editor or by checking pg_class.relrowsecurity directly.
2. 02 Log out completely and query each of those tables with only the public anon key. Anything returned that you did not create yourself is a leak.
3. 03 Create a second account, log in as it, and try to read or write the first account’s row by changing an id inside a request body, not the URL.
4. 04 Run a pgTAP suite against your policies for the scenarios you already know about. It will not find the ones above, but it catches a policy someone edits and breaks next month.
5. 05 Query information_schema.routines for every function where security_type is DEFINER, and check each one against the same second-account test, since RLS never sees them.
6. 06 Repeat all five after every schema change, since a new table or a new function can silently reopen any of them.

A non-technical founder can run the first three without reading a line of SQL. The last two need someone who can read a schema, which is the reason this list is worth running with technical help rather than working through it alone.

Common questions about testing Supabase RLS

My RLS policy exists, but data still leaks. Why?

Usually one of three reasons: the policy checks that someone is logged in without comparing the row to who they are, the anon key is being used somewhere the service_role key was supposed to be, or a security definer function reaches the same table and skips the policy entirely. Run the anon-key probe and the two-account test above against the specific table. The answer is almost always in which of the two actually fails.

Do I need pgTAP if I already run the anon-key and two-account tests?

Keep pgTAP anyway; it does a different job. pgTAP runs in CI on every schema change once it’s wired in, without anyone remembering to log in as a second account by hand. The black-box tests are what prove a boundary holds today. pgTAP is what keeps it holding after someone edits the policy next month.

Is RLS enough on its own?

Not entirely. Everything above proves whether the policies you have are enforced the way you think. Whether row-level security by itself is a complete authorization strategy for an app with roles, admin paths, and service integrations is a separate question this post doesn’t answer. Treat a clean result from the five checks above as proof the policies you wrote hold, not as proof that authorization is solved, and treat the whole exercise as one piece of whether an AI-built app is actually ready for real users, not the whole of it.