How to Test Supabase RLS: Database and API Tests

Testing Supabase row-level security needs two layers. Database tests should impersonate the anon and authenticated roles and prove both allowed and denied operations. Application tests should make the same requests through a Supabase client using the public publishable key. One layer catches policy logic. The other catches the session, grants, API, and function surface your users actually reach. The checks below run in the same order every time: the logged-out probe first, then the second account, then the function surface that skips RLS entirely.

Run these tests against a local project or a disposable staging project with known fixtures. A production database is a poor test target because write and delete checks can trigger real side effects. Keep the secret key (the service_role key on older projects) in server-only setup code; every client under test should use the public publishable key plus, where required, a test user’s session.

How do you set up the RLS test suite?

Four commands take you from an empty repo to a running RLS test. Run them from the project root:

supabase init
supabase start
supabase test new rls
supabase test db

The current CLI source writes supabase/tests/${name}_test.sql, so supabase test new rls creates ./supabase/tests/rls_test.sql. The CLI reference says supabase test db reads tests from supabase/tests and accepts files ending in .sql or .pg.

pgTAP is the runner. Enable it once in a migration so local, CI, and staging all have it:

create extension if not exists pgtap with schema extensions;

Static fixture rows belong in supabase/seed.sql, which the local stack applies when it resets the database. Put the known rows your assertions target in there so every environment starts from the same state.

What should a complete Supabase RLS test prove?

A complete Supabase RLS test proves the expected result for each role, operation, and ownership boundary. At minimum, test a logged-out request, user A accessing user A’s row, user B attempting the same row, and user B trying to change its owner or tenant identifier.

Supabase’s current testing overview documents both approaches. Its pgTAP example switches between two user identities and checks a forbidden update. Its application example makes the equivalent requests through supabase-js. pgTAP has no built-in blind spot around a second user; missing cases come from the assertions the suite never included.

How do you check if RLS is enabled in Supabase?

Check the RLS flag, the ordinary SQL grants, and the policies as separate controls. Supabase enables RLS by default for tables created in the Dashboard, while tables created through the SQL Editor or migrations need an explicit alter table ... enable row level security. If RLS is enabled and no applicable policy exists, PostgreSQL uses default-deny behavior. If RLS is disabled, access still depends on the table privileges granted to anon and authenticated.

This query lists the RLS state of every table in the public schema:

select schemaname, tablename, rowsecurity
from pg_tables
where schemaname = 'public'
order by tablename;

That result does not prove a table is reachable through the Data API. Grants and the project’s exposed-schema settings are separate. Supabase began making explicit grants the default for new projects in 2026, so cross-check the Dashboard’s Data API exposure badge and the migration’s GRANT statements. The Data API exposure rollout leaves RLS behavior unchanged: grants decide whether a role can reach the table, then RLS decides which rows it can access.

The Supabase Security and Performance Advisors add useful independent checks. They flag an exposed table with RLS disabled, a policy attached while RLS is disabled, and RLS enabled with no policy. Review the advisor after every migration instead of treating a clean result as permanent.

If Supabase is throwing “new row violates row-level security policy” back at you, RLS is already evaluating that write. The remaining question is whether the WITH CHECK condition is too strict or the request carries the wrong user or tenant value. That diagnosis differs from a table whose RLS flag is off.

How do you run the logged-out publishable-key test safely?

A logged-out Supabase client created with a publishable key uses the PostgreSQL anon role. For a table that is intentionally exposed to anon but protected by RLS, query one known fixture by its test ID and assert that zero rows return. A broad .select() against an empty table can look secure while proving very little.

import { createClient } from "@supabase/supabase-js";
import { describe, expect, it } from "vitest";

const loggedOut = createClient(SUPABASE_URL, SUPABASE_PUBLISHABLE_KEY, {
  auth: {
    persistSession: false,
    autoRefreshToken: false,
    detectSessionInUrl: false,
  },
});

describe("orders RLS", () => {
  it("hides a protected order from anon", async () => {
    const { data, error } = await loggedOut
      .from("orders")
      .select("id")
      .eq("id", TEST_ORDER_ID);

    expect(error).toBeNull();
    expect(data).toHaveLength(0);
  });
});

This example treats an API error as a test failure because its contract expects the table to be reachable and RLS to hide the row. If the intended contract revokes anon access or removes the relation from the Data API, assert the expected permission or missing-relation error instead. Do not accept either result interchangeably, or an accidental grant change can pass unnoticed.

Supabase’s API-key documentation confirms that a publishable key with no user session maps to anon; the same key plus a valid user JWT maps to authenticated. Legacy projects may still call the public key anon, but new projects should use the publishable key. A secret key, called the service_role key on legacy projects and still the phrase most Supabase threads use, belongs only in secure setup code because it bypasses RLS.

Repeat this test for every table and view the public client reads. Test an intentionally public object as a positive control too. A suite where every query returns zero may indicate a broken test identity rather than strong authorization.

Fixture setup and test isolation

Mint the fixtures in a global setup file with the secret key, never inside the test that asserts the boundary. Create user A and user B with auth.admin.createUser() and email_confirm: true so they can sign in immediately, then insert their rows. Anything static goes in supabase/seed.sql.

The isolation rule differs by layer, and mixing the two is a common cause of flaky suites. pgTAP wraps each file in begin ... rollback, so database tests clean up after themselves. Application tests run over HTTP and commit, so give every run unique identifiers from crypto.randomUUID() or delete the fixtures in an afterAll hook.

How do you assert the right failure instead of just zero rows?

Zero rows and a denied write are different results, and only some of them prove the policy did the work. Assert the specific code your contract expects:

The first two rows share a code and differ only in the message, which is why “the test threw an error” is not an assertion. A helper that pins both keeps a revoked grant from masquerading as a working policy:

export function expectRlsDenied(error) {
  if (!error) {
    throw new Error("expected the write to be denied, it succeeded");
  }
  if (error.code !== "42501") {
    throw new Error(`expected 42501, got ${error.code}: ${error.message}`);
  }
  if (!error.message.includes("row-level security policy")) {
    throw new Error(`42501 came from a grant, not a policy: ${error.message}`);
  }
}

The PostgREST error reference documents the PGRST codes and the HTTP status each Postgres code maps to (42501 becomes 403 for an authenticated caller and 401 otherwise). Storage calls return a StorageError rather than a PostgrestError, so there is no Postgres code to match on. Assert the status and the message there instead.

How do two accounts expose cross-tenant RLS failures?

Two-account testing uses a known row owned by user A and a separate session for user B. User A must succeed. User B must get zero rows or a denied mutation when making the same request against A’s identifier.

Test each operation your app exposes:

Role-based policies read auth.jwt(), and which field they read decides whether the test means anything. Supabase’s row-level security reference is direct about it: raw_app_meta_data cannot be changed by the user, so it is safe to authorize on, while raw_user_meta_data can be updated by the signed-in user and is not. If a policy reads user metadata, write the test that proves the bypass: have user B set their own metadata role and then attempt the admin operation. For an MFA-gated policy, sign a test user in without the second factor and assert that the operation is denied while auth.jwt()->>'aal' still reads aal1.

Use the same query shape as the application. If the UI sends tenant_id in a request body, test that body. If it filters in the URL, test that filter. RLS executes at the database regardless of where the identifier appears, so the important property is the caller and the target row, not the transport syntax.

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. Running the write half of this test alongside the read half is what catches a policy that lets user B corrupt a row instead of only viewing it, the same boundary failure behind the data-loss bugs AI-built apps ship.

What does a useful pgTAP RLS suite cover?

A pgTAP suite turns those ownership checks into repeatable database tests that run in CI. The official Supabase example sets the authenticated role, changes request.jwt.claim.sub between two fixture users, and verifies their visible rows and rejected writes inside a transaction.

begin;
select plan(2);

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

select results_eq(
  'select count(*) from public.orders',
  'values (2::bigint)',
  'user A sees only two owned orders'
);

select is_empty(
  $$
    update public.orders
    set status = 'cancelled'
    where user_id = '22222222-2222-2222-2222-222222222222'::uuid
    returning 1
  $$,
  'user A cannot update user B orders'
);

select * from finish();
rollback;

The fixtures must exist before those assertions run. Add separate cases for anon, user B, insert ownership, owner-changing updates, deletes, and any custom JWT claims your policies read.

The single request.jwt.claim.sub setting above only carries a user ID. A policy that calls auth.jwt() needs the whole claim set, which is the newer JSON form:

set local role authenticated;
set local request.jwt.claims = '{
  "sub": "11111111-1111-1111-1111-111111111111",
  "role": "authenticated",
  "aal": "aal2",
  "app_metadata": {"role": "admin"}
}';

The same two statements work interactively in the SQL Editor with set session role in place of set local role, which is how Supabase’s RLS troubleshooting guide reproduces a policy by hand. Reset with set session role postgres; when you are done.

Run the suite on every pull request

Supabase recommends running supabase test db in continuous integration so a later migration cannot weaken a policy unnoticed. This is the whole workflow file:

# .github/workflows/db-tests.yml
name: Database tests

on:
  push:
    branches: [main]
  pull_request:
    branches: [main]

jobs:
  test:
    runs-on: ubuntu-latest
    steps:
      - uses: actions/checkout@v4
      - uses: supabase/setup-cli@v3
      - run: supabase start
      - run: supabase test db

The pull_request trigger is the load-bearing part, and the job has to be a required check, or the gate is advice rather than a gate. Add a step running the application suite after supabase start if you want both layers on the same check.

Application tests still earn their place. They verify that session storage, JWTs, Data API grants, request filters, and generated client code line up with the database tests. A green pgTAP suite plus a failing client test points to integration or API exposure. Two failing layers point back to the schema or fixtures.

A useful RLS test names the caller, the target row, the operation, and the result that must be denied.

Is there a Supabase RLS checker, and can you trust it?

Partly. supabase test db is the CLI that runs RLS tests, but you still write the assertions. Six tools cover different slices of the problem, and each one has a boundary it cannot reach:

The helper library saves the most time of the six. It installs through dbdev, the Postgres package manager, and Supabase’s advanced pgTAP guide documents the install:

select dbdev.install('basejump-supabase_test_helpers');
create extension if not exists "basejump-supabase_test_helpers"
  version '0.0.6';

That gives you tests.create_supabase_user('user_a'), tests.authenticate_as('user_a'), tests.clear_authentication(), tests.get_supabase_uid('user_a'), and tests.rls_enabled('public'). The last one asserts that every table in a schema has RLS on, in one line, instead of the catalog query earlier in this post.

None of the six supplies the app-specific two-account write assertions on its own. A checker probes with one identity. The Advisor reads flags. The RLS Tester reads SELECT under impersonation. The helper library only speeds up the suite you still have to write. The check that catches real cross-tenant bugs, user B updating user A’s row and succeeding, exists only if you write it.

How should callable functions and SECURITY DEFINER code be tested?

RLS policies are attached to tables. Views and functions can expose those rows under a different execution context, and PostgreSQL functions also have their own EXECUTE privileges. Supabase warns that functions in exposed schemas can be called through the Data API. A SECURITY DEFINER function executes with its owner’s privileges. When that owner is the table owner or has BYPASSRLS, the function may reach rows the caller could not access directly.

The declaration does not bypass RLS in every configuration. The execution role and table privileges decide that behavior. The risk still deserves a separate inventory because a function can accept a caller-supplied user ID, perform privileged work, and return the result without applying the ownership rule the table policy would have enforced.

select
  n.nspname as schema_name,
  p.oid::regprocedure::text as function_signature,
  pg_get_userbyid(p.proowner) as owner,
  p.prosecdef as security_definer,
  has_function_privilege('anon', p.oid, 'execute') as anon_can_execute,
  has_function_privilege('authenticated', p.oid, 'execute') as authenticated_can_execute
from pg_proc p
join pg_namespace n on n.oid = p.pronamespace
where n.nspname in ('public', 'graphql_public')
order by n.nspname, function_signature;

Replace that schema list with the schemas the project’s Data API actually exposes. The signature column keeps overloaded functions separate, while the owner identifies the role whose privileges a SECURITY DEFINER function receives. For every callable function, test its public argument list through .rpc() as anon, user A, and user B. The function should derive identity from auth.uid() or another verified claim when identity affects authorization. Review its owner, search_path, and grants as well. Supabase’s database-function security guidance requires a safe search_path for SECURITY DEFINER code and recommends revoking default execution before granting only the roles that need it.

Views are the second surface, and they fail quietly. A view over an RLS table runs with its owner’s rights by default, which usually means it ignores the policies underneath. On Postgres 15 and above, create it with security_invoker so it obeys them:

create view public.order_summary
with (security_invoker = true)
as select id, total, user_id from public.orders;

Assert that flag in the suite, then repeat the user B read against the view itself, not just the table. A view marked as covered because the table under it has policies is exactly how this bug ships. On older Postgres versions, revoke anon and authenticated access to the view or keep it in an unexposed schema.

Storage is the third. Buckets and objects are rows in the storage schema and take RLS policies like any other table, but a table-focused suite never touches them. Call supabase.storage.from(bucket).download(path) and .upload(path, file) as anon and as user B against a known object owned by user A, then assert the denial. A .list() that returns nothing is a filtered read, not proof that a direct download by path is blocked.

Writing the policies themselves well, which roles they name, which columns they compare, is a separate job. Across the AxonBuild corpus, 9 of 21 third-party apps had a gap in row-level security. Every finding behind that denominator was verified against the code, and the sharpest case involved a privileged function trusting a caller-supplied identifier.

Which RLS checks should run before every database release?

The smallest durable release gate covers schema state, positive access, negative access, functions, and the real client path.

1. 01 Run the Supabase Security Advisor and confirm every Data API table has the intended RLS state and grants.
2. 02 Run pgTAP as anon, user A, and user B for every operation each policy governs, including ownership-changing updates.
3. 03 Run application-level tests with a publishable key and real test sessions against the same fixtures.
4. 04 Inventory callable functions, then test their arguments, execution grants, owner privileges, and search path.
5. 05 Repeat the user B read against every view over an RLS table, and against storage objects, not just the tables themselves.
6. 06 Keep secret keys in server-only fixture setup and run destructive cases only in local or disposable staging data.
7. 07 Make the full suite a deployment gate after every policy, grant, function, and exposed-schema change.

Common questions about testing Supabase RLS

Can the Supabase Dashboard test RLS policies?

Supabase introduced an RLS Tester feature preview in April 2026 for role impersonation and SELECT queries. It can show which policies are evaluated, but mutation testing remains outside that preview. Keep pgTAP and application-level tests for inserts, updates, deletes, functions, and continuous integration.

Do I need application tests if pgTAP already impersonates two users?

Yes. pgTAP can test the policy logic and negative cases accurately. Application tests cover the public API key, the real JWT, Data API grants, request shape, and callable functions as the shipped client uses them. The layers should share fixtures and expected outcomes.

How do I run RLS tests in CI?

Add a GitHub Actions job that checks out the repo, installs the CLI with supabase/setup-cli, runs supabase start, then runs supabase test db. Trigger it on pull_request and mark the check required, so a policy or migration change cannot merge while a test fails. The workflow file earlier in this post is the complete version.

Can I test RLS policies in the SQL editor?

Yes, for a single check by hand. Run set session role authenticated;, then set request.jwt.claims to '{"role":"authenticated","sub":"<user-uuid>"}';, then your query, then set session role postgres; to reset. It is good for reproducing one case, and it replaces nothing, because nothing about it repeats on the next commit.

Do I need a separate Supabase project for testing?

Use a local stack from supabase start or a disposable staging project, never production. RLS tests include inserts, updates, and deletes against known fixtures, and running those against live data fires real side effects like emails, webhooks, and billing rows. A local stack also resets between runs, which is what makes results comparable.

Why do my RLS tests pass when the policy is broken?

The usual cause is that the test client picked up the secret (service role) key from the environment, which bypasses RLS, so every request succeeds and every loose negative assertion passes with it. The other three: the fixture table was empty, so a zero-row result proved nothing; the test user was created without email_confirm: true and never signed in, so requests ran as anon; or the assertion accepted any error rather than the expected code. Call auth.getUser() at the start of the suite and assert it returns the fixture user.

How do I test RLS on storage buckets?

Sign in as user B, call download() and upload() on an object path owned by user A, and assert the error rather than an empty result. Storage policies live on storage.objects and storage.buckets, so a pgTAP suite that only covers your application tables never reaches them. Repeat both calls with a logged-out client to cover the anon case.

Is RLS enough on its own?

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. RLS also cannot replace careful function grants, server-side authorization, secret handling, rate limits, or tests for business rules outside the database.