Testing PostgreSQL RLS Policies With Jest (No pgTAP)
Test PostgreSQL row-level security with Jest and node-postgres, no pgTAP required. Catch tenant isolation bugs in CI before a bad grant reaches production.
On this page
You turn on Row-Level Security, write a CREATE POLICY, run one manual query as "tenant A" in psql, see it filtered correctly, and ship it. Six weeks later a support ticket lands: a customer saw another customer's invoices. Nobody touched the policy on purpose. Someone added a new query path through a service role, a migration dropped and recreated the table without re-enabling RLS, or a teammate granted BYPASSRLS to the app's connection pool user to unblock an unrelated permissions error and forgot to revoke it.
RLS is only as trustworthy as your last test of it. A one-off psql check doesn't survive the next schema change. This is where most PostgreSQL RLS guides point you at pgTAP and stop there. pgTAP is a fine tool, but it's a separate SQL-based test framework with its own syntax and its own CLI runner, pg_prove. If you're on a NestJS or Express backend, Jest is already wired into your CI. You don't need a second test framework to catch a tenant isolation bug, you need a Jest test that actually proves one can't happen.
The Problem With Eyeballing RLS
A manual psql check answers exactly one question: does this policy work right now, against today's schema, with today's roles. It says nothing about tomorrow. Three ordinary changes are enough to quietly break tenant isolation without anyone noticing at review time.
A schema migration that runs DROP TABLE and recreates it loses Row-Level Security entirely, because RLS is a per-table flag, not something that travels with the column definitions. If your migration tool (see the guide to Drizzle ORM migrations with drizzle-kit for one way to manage this safely) doesn't re-run ENABLE ROW LEVEL SECURITY after a table rebuild, the policy is gone and every query silently returns unfiltered rows. A new service role added for a background job can bypass RLS entirely if nobody remembers to apply the same policy to it. And the single most common regression: someone grants BYPASSRLS to the application's connection pool user to work around an unrelated permissions error during an incident, and never revokes it once the incident is resolved.
None of these show up in a code review of the policy SQL, because the policy SQL didn't change. They show up in production, usually as a support ticket, because nothing in CI was watching for them.
pgTAP vs Jest, Pick One on Purpose
| pgTAP | Jest + node-postgres | |
|---|---|---|
| Runs where | Inside Postgres via pg_prove | Your existing test runner |
| Syntax | SQL-based TAP assertions | JavaScript/TypeScript you already write |
| CI setup | New tool, new step | Already in your pipeline |
| Best for | DBA-heavy teams, schema-first workflows | Node.js backend teams, app-first workflows |
If your team already lives in Jest, stay there. Supabase's own documentation describes writing pgTAP tests for RLS as inaccessible to most web developers, which tracks: it means learning a SQL-native TAP assertion library on top of learning RLS itself. A growing crop of small libraries (pgsql-test, pglite-test) exist specifically because manual RLS test-writing in JavaScript is a known pain point, and they wrap Jest with per-test rollback helpers similar to the pattern below.
This guide sticks to plain node-postgres and Jest with no extra dependency, because the pattern is simple enough that a helper function and a handful of tests cover it completely.
The Pattern That Actually Catches Bugs
Most RLS write-ups test the happy path: log in as tenant A, see tenant A's data. That's necessary, but it's not the test that catches regressions. The test that catches regressions seeds a row as tenant A, then queries as tenant B, and asserts the row comes back empty. If that assertion ever fails, isolation has broken, and CI goes red before a customer notices.
The single most common false pass is running the test as the database user that owns the table, or as a superuser. PostgreSQL exempts table owners and superusers from RLS by default (FORCE ROW LEVEL SECURITY changes that for the owner, but never for a superuser). Every test below runs through a dedicated, low-privilege role that mirrors your real application's connection role, precisely so a broken policy cannot hide behind an over-privileged test connection.
Testing RLS Step by Step
The example below tests a single invoices table scoped by a tenant_id column, using a session variable set at the top of each request. The same shape works for any tenant-scoped table: swap the table and column names and the tests still hold.
- 1
Create an isolated, non-superuser test connection
Before writing a single assertion, create a role that behaves like your real application's database user: it can read and write the table, but it owns nothing and holds no elevated privileges. Testing as the table owner or as
postgresis the fastest way to write a suite that passes for the wrong reason.sql — Create the low-privilege role used by every test belowCREATE ROLE app_test_role LOGIN; GRANT SELECT, INSERT, UPDATE, DELETE ON invoices TO app_test_role; ALTER TABLE invoices ENABLE ROW LEVEL SECURITY; ALTER TABLE invoices FORCE ROW LEVEL SECURITY; - 2
Write the SELECT isolation test
A shared helper does the heavy lifting: open a transaction, set the tenant session variable for that transaction only, run the query, then roll back so seeded data never leaks into the next test.
js — queryAsTenant helper and the isolation testconst { Pool } = require('pg'); async function queryAsTenant(pool, tenantId, sql, params = []) { const client = await pool.connect(); try { await client.query('BEGIN'); await client.query(`SET LOCAL app.tenant_id = '${tenantId}'`); const result = await client.query(sql, params); await client.query('ROLLBACK'); return result.rows; } finally { client.release(); } } test('tenant B cannot see tenant A invoices', async () => { await seedInvoice({ tenantId: 'tenant-a', id: 'inv-1' }); const rows = await queryAsTenant( testPool, 'tenant-b', 'SELECT * FROM invoices WHERE id = $1', ['inv-1'] ); expect(rows).toHaveLength(0); });SET LOCALscopes the session variable to the current transaction, and theROLLBACKat the end means seeded data never persists between tests. No manual cleanup, no shared state bleeding from one test into the next. - 3
Test INSERT and UPDATE policies with WITH CHECK
A policy with only a
USINGclause protects reads but not writes. It's the single most common mistake in a hand-written RLS policy: the SELECT test passes, the reviewer approves it, and nobody notices that tenant B can still insert a row and simply label it as tenant A's.js — Proving WITH CHECK is actually enforcedtest('tenant B cannot insert a row claiming tenant A', async () => { await expect( queryAsTenant( testPool, 'tenant-b', "INSERT INTO invoices (tenant_id, amount) VALUES ('tenant-a', 500)" ) ).rejects.toThrow(/row-level security/i); });If this test passes without a
WITH CHECKclause on the policy, that's a real bug, not a flaky test. Write the same shape for UPDATE: seed a row owned by tenant B, then attempt to update itstenant_idto tenant A and assert the same rejection. UPDATE is the one operation that needs both clauses, sinceUSINGcontrols which existing rows can be targeted andWITH CHECKcontrols what the resulting row is allowed to look like. - 4
Catch the accidental BYPASSRLS grant
Add one regression test that fails loudly the moment someone grants
BYPASSRLSwhere it shouldn't be. This is the exact test that would have caught the "forgot to revoke after an incident" scenario from the introduction, before it shipped rather than after a customer complained.js — A cheap regression test worth keeping forevertest('app role does not have BYPASSRLS', async () => { const { rows } = await adminPool.query( "SELECT rolbypassrls FROM pg_roles WHERE rolname = 'app_role'" ); expect(rows[0].rolbypassrls).toBe(false); }); - 5
Wire it into GitHub Actions
A real Postgres instance is required, since RLS is enforced by the Postgres planner itself and cannot be mocked at the query-builder level. A
postgresservice container gives every pull request a real database without any extra infrastructure to maintain.yaml — .github/workflows/test.ymlservices: postgres: image: postgres:17 env: POSTGRES_PASSWORD: test ports: - 5432:5432 options: >- --health-cmd pg_isready --health-interval 10sRun migrations against the service container, then
jest --testPathPattern=rlson every pull request. Isolation bugs get caught in review, not in a support ticket.
Common Mistakes This Pattern Catches
Every one of these has shipped to production in a real codebase, and every one of them passes code review because the policy SQL looks correct at a glance. The tests above exist specifically to catch what a read-through of the SQL cannot.
- Seed a row as tenant A and query as tenant B, asserting zero rows come back, not just that tenant A's own query works
- Run every test through a dedicated low-privilege role, never as the table owner or a superuser
- Test INSERT and UPDATE with WITH CHECK explicitly, not only SELECT with USING
- Add a standing regression test asserting the app role does not have BYPASSRLS
- Confirm FORCE ROW LEVEL SECURITY is set, or the table owner silently bypasses every policy
- Run the suite against a real postgres service container in CI, since RLS cannot be mocked at the query-builder level
The underlying lesson is the same across all six: RLS is enforced by the database, so it has to be tested against the database. Anything that mocks the query layer or runs with elevated privileges is testing something other than the thing that actually protects your customers' data.
If you are building this out across many tenant-scoped tables, the RLSBuilder tool generates both the policy SQL and a matching Jest test from the same three inputs, so there is no drift between what a policy claims to do and what is actually verified. And if you want to brush up on the surrounding SQL fundamentals this pattern leans on (transactions, constraints, upserts), the SQL interview questions guide covers the same ground interviewers ask about.
Frequently Asked Questions
Do I need pgTAP to test Row-Level Security?
No. pgTAP is one option, not a requirement.
- pgTAP: SQL-based, runs via
pg_prove, good fit if your team already writes SQL-first tests. - Jest + node-postgres: JavaScript-based, runs in your existing CI job, good fit for Node.js backend teams.
- pgsql-test / pglite-test: newer libraries that wrap Jest with per-test rollback helpers, worth a look once you outgrow hand-rolled connection setup.
Why does my RLS test pass even though the policy is broken?
This almost always means the test ran as the table owner or a superuser.
- Table owners bypass RLS by default, unless
FORCE ROW LEVEL SECURITYis set. - Superusers always bypass RLS, no exception, and
FORCEdoes not change this. - The fix: create a dedicated low-privilege test role that matches your real application's database role, and run every test through it.
What's the difference between USING and WITH CHECK in a policy?
| Clause | Applies to | What it controls |
|---|---|---|
| USING | SELECT, existing rows on UPDATE/DELETE | Which rows are visible or targetable |
| WITH CHECK | INSERT, new/updated row on UPDATE | Whether the resulting row is allowed to exist |
Always write both. A policy with only USING protects reads but lets a user insert or update a row tagged as someone else's tenant.
In practice this means every CREATE POLICY for a tenant-scoped table needs two clauses in the same statement, not two separate policies: USING (tenant_id = current_setting('app.tenant_id')::uuid) to filter what's visible, and WITH CHECK (tenant_id = current_setting('app.tenant_id')::uuid) to reject any row a query tries to write with the wrong tenant. Postgres evaluates USING against the row as it exists before the statement and WITH CHECK against the row as it would exist after, which is why an UPDATE needs both: one clause decides which existing rows the user can touch, the other decides what they're allowed to turn those rows into.
Can I test RLS policies without spinning up a real Postgres instance?
Not reliably. RLS is enforced by the Postgres planner itself, not something you can mock at the query-builder level.
- In-memory or mocked Postgres: will not evaluate real RLS policies, gives you false confidence.
- A real Postgres instance (local Docker, or a
postgresservice container in CI): the only way to get a true pass/fail on policy behavior. - pglite-test: runs an actual in-process Postgres build, closer to real but still worth verifying against a real server before trusting it in CI.
Do I need to test DELETE policies too?
Yes, using the same shape as the SELECT test: seed a row as tenant A, attempt to delete it as tenant B, and assert that either the delete affects zero rows or that a RETURNING clause comes back empty. A missing DELETE policy is easy to overlook because it produces no error, a broad DELETE FROM invoices WHERE amount > 0 from any tenant simply succeeds and removes rows it should never have touched.
DELETE only checks the USING clause, since there's no new row being written, so it's tempting to assume the same policy covering SELECT already covers DELETE for free. That's usually true if you wrote one policy with FOR ALL, but not if you wrote separate FOR SELECT and FOR INSERT policies and forgot a FOR DELETE (or a second FOR ALL) entirely. Without any DELETE policy on a table with RLS enabled, Postgres denies every delete by default rather than allowing it, so the failure mode you're testing for is a policy that's too permissive, not one that's missing.
Can I generate this SQL and test boilerplate automatically?
Yes. The RLSBuilder tool takes a table name, tenant column, and tenant ID type, and generates both the CREATE POLICY SQL (with the WITH CHECK clause included by default) and a matching Jest test file using the same queryAsTenant pattern shown in this guide. It runs entirely in the browser, so no schema details leave your machine.
The generated policy and the generated test are built from the same three inputs, table name, tenant column, and tenant ID type, so they can't drift apart the way hand-written policy SQL and hand-written tests can when one gets edited and the other doesn't. That's the main failure mode this guide is trying to prevent: a policy that changes during a refactor while the test suite still asserts the old shape and keeps passing anyway. Pasting the output into your migration and your test file gets you the isolation and WITH CHECK tests from steps two and three above without retyping the boilerplate for every new tenant-scoped table.
How do I test RLS with Supabase's auth.uid()?
The same pattern applies, but the session setup differs. Supabase policies typically compare against auth.uid(), which reads from a JWT claim set by request.jwt.claims rather than a plain session variable. In a test, set that claim directly before running the query:
SELECT set_config(
'request.jwt.claims',
json_build_object('sub', '11111111-1111-1111-1111-111111111111')::text,
true
);Wrap that in the same BEGIN / query / ROLLBACK transaction shown in the isolation test above, and the rest of the pattern (seed as one user, query as another, assert zero rows) is identical.
The one thing worth double-checking in a Supabase project specifically: policies that call auth.uid() only resolve correctly when that JWT claim is actually set on the connection, so a test that connects with a plain superuser or service-role key and never sets request.jwt.claims will see every row regardless of the policy, for the same reason a superuser bypasses RLS in any other Postgres setup. If a Supabase RLS test passes without ever calling set_config('request.jwt.claims', ...), treat that as a signal the test isn't exercising the policy at all.
Related Articles
Drizzle ORM Migrations: A Practical drizzle-kit Guide
Learn the full Drizzle ORM migration workflow: push vs migrate, drizzle-kit setup, Turso/libSQL config, team conflicts, and production best practices.
pg_durable: Durable Workflows in PostgreSQL vs Temporal and Cron+Queue
Microsoft's pg_durable brings durable workflow execution inside PostgreSQL. Here is the syntax, what it replaces, and an honest comparison against Temporal and cron+queue patterns.
40 SQL Interview Questions and Answers (2026)
40 SQL and relational database interview questions covering joins, indexes, ACID, window functions, CTEs, MySQL vs PostgreSQL, and query challenges.