Short version for the impatient: if your Postgres row level security policies are only covered by tests that mock the database, they are not covered. I learned this the embarrassing way on a multi-tenant Node app back in June, and a harness that landed on the Postgres news feed this week (pgsql-test) is the first thing I’ve seen that fixes the workflow problem rather than just the tooling problem.
Here’s the June story. I had a documents table with an RLS policy that read the tenant id from a session setting. The repository layer had a nice green test suite. Every test mocked pg and asserted that the right SQL string went out. The policy itself referenced app.tenant_id. The middleware that set the session variable wrote app.tenant. No test could have caught that, because no test ever ran the policy. A colleague noticed in staging when she saw a document from a customer she’d never heard of. Nothing shipped to production, but only because she happened to be looking.
Why a mock can’t see a policy
The pgsql-test announcement makes a point I’d have argued with two years ago: a mock tests how your code handles a result, not whether Postgres will produce it. A mock doesn’t fire a trigger. It doesn’t evaluate a foreign key. It doesn’t check which database role your query actually runs under. And it definitely doesn’t run a row level security policy, because the policy lives in the database and the mock replaced the database.
I used to counter that with “that’s what integration tests are for.” True, but look at what my integration tests looked like. A shared dev database that everybody wrote to. A beforeAll that truncated six tables. Fixtures that drifted from the migrations. Tests that passed alone and failed together. Nobody ran them locally, so they only ran in CI, so a red build meant twenty minutes of guessing. The fast loop lived in the unit tests, and the unit tests couldn’t see the database. So the rules that mattered most got the least testing.
The announcement puts it better than I did: when rules are easier to test in application code than in Postgres, they end up in application code, even when Postgres is the better place to enforce them. That’s exactly how my tenant check ended up duplicated in a middleware and a policy, with two different spellings.
What pgsql-test actually does
The harness spins up an ephemeral Postgres, deploys your schema, seeds it once, and then wraps every test in a transaction that gets rolled back. Your assertions run in Jest or whatever runner you already have. There’s an admin client (pg) for setup and an app client (db) for the queries you’re actually testing. It’s MIT licensed and on npm and PyPI.
The part that matters for RLS is setContext(). It applies a role and any session settings your policies read, scoped to the current transaction via SET LOCAL and set_config(..., true). No JWT is validated. You’re telling the database “pretend the request came from this identity” and then checking what it returns.
Here’s roughly what my old test looked like. I’ve trimmed the noise:
// documents.repo.test.ts (before)
import { listDocuments } from './documents.repo';
const query = jest.fn();
jest.mock('pg', () => ({ Pool: jest.fn(() => ({ query })) }));
test('lists documents for the tenant', async () => {
query.mockResolvedValue({ rows: [{ id: 101 }] });
const rows = await listDocuments({ tenantId: 'acme' });
expect(query).toHaveBeenCalledWith(
expect.stringContaining('FROM app.documents'),
expect.anything()
);
expect(rows).toEqual([{ id: 101 }]);
});
That test asserts that I asked for documents and that I got back what I told the mock to return. It would pass with no policy on the table at all.
And here’s the same idea with a real database underneath:
// documents.rls.test.ts (after)
import { getConnections } from 'pgsql-test';
let db, teardown;
beforeAll(async () => {
({ db, teardown } = await getConnections());
});
afterAll(() => teardown());
beforeEach(() => db.beforeEach());
afterEach(() => db.afterEach());
test('acme sees only its own documents', async () => {
db.setContext({ role: 'authenticated', 'app.tenant_id': 'acme' });
const result = await db.query('SELECT id FROM app.documents ORDER BY id');
expect(result.rows).toEqual([{ id: 101 }]);
});
test('a tenant with no documents sees nothing', async () => {
db.setContext({ role: 'authenticated', 'app.tenant_id': 'nobody' });
const result = await db.query('SELECT id FROM app.documents');
expect(result.rows).toEqual([]);
});
Notice the query has no WHERE tenant_id = .... That’s the whole point. If the policy is wrong, or the session variable is misspelled, the first test returns both seeded rows and fails. My June bug would have died in a pull request instead of in staging.
The second test is the one I now consider mandatory. Testing what a user can see is the easy half. Testing what they cannot see is where leaks hide, and it’s the test almost nobody writes because the happy path already went green.
The superuser trap
This one bit me within an hour of trying the harness, so I’ll save you the hour. Superusers bypass row level security. So does the table owner, unless you’ve run ALTER TABLE ... FORCE ROW LEVEL SECURITY. The Postgres docs on row security policies spell this out, and I’d read them, and I still wrote my first RLS test using the admin connection and wondered why the “nobody” tenant could see everything.
The fix is boring: your tests have to run under the same role your application uses. In pgsql-test that means using the db client with setContext({ role: 'authenticated', ... }), not the pg admin client. If your app connects as the schema owner (a lot of small Laravel and Node apps do, because it’s the path of least resistance), then RLS isn’t protecting you in production either, and the test will tell you that too. Which is uncomfortable, but better to hear it from Jest.
While you’re there, check the role has NOBYPASSRLS. It’s the default, but someone on the team will have granted BYPASSRLS to “fix” a migration at some point. I found one of those in a project I inherited last year, and the commit message was “temp”.
pgTAP is still fine, this is just a different seat
I want to be fair to pgTAP, because I’ve used it for years and it’s not going anywhere. pgTAP writes the tests in SQL and runs them inside the database. If your team is database-first, or you have a DBA who owns the schema, that’s the right home for the tests.
The difference with pgsql-test is where you sit while you write them. It targets the application layer, so a JavaScript or Python developer writes a policy test in the same file and the same runner as everything else, watch mode included. The announcement says the harness is also wrapped for Supabase (supabase-test), Drizzle (drizzle-orm-test), PostGraphile (graphile-test) and an in-process PGlite variant (pglite-test) for when your schema doesn’t need extensions PGlite can’t load. I’ve only tried the plain one so far.
There’s a claim in there I haven’t reproduced: a supabase-test run of 246 tests across 44 databases finishing in four seconds, quoted from Supabase’s CEO. My own numbers are less flashy. A schema with eleven tables and about forty tests took nine seconds on my laptop, and most of that was the first getConnections() deploy. Per-test rollback was fast enough that I stopped thinking about it. If you have a heavier seed, that first deploy is going to be your bottleneck, not the tests.
What I got wrong the first time
Three things, in the order I hit them.
I seeded inside beforeEach. That threw away the whole point of the harness, because seeding ran forty times. The seed goes in the initial deploy, and every test rolls back to it.
I used the same UUID for two tenants in different fixtures, because I copy-pasted. Both tests passed. Both tests were lying. Pick visibly different identifiers in fixtures (acme, globex) so a leak shows up as an obviously wrong row instead of a plausible one.
I forgot that SET LOCAL only lasts for the transaction. That’s a feature here, since db.afterEach() rolls it back and the next test starts clean. But if you open a second connection inside a test to “check something”, it won’t have the context, and you’ll spend a while confused. Ask me how I know.
Where this fits with the rest of the database checks
I’ve written before about linting Postgres schemas in CI for unindexed foreign keys, and this is the natural next step. That lint answers “is the schema shaped well?”. A policy test answers “does the schema behave the way I think it does under the role the app uses?”. They’re different questions and I now want both in the same pipeline.
The announcement mentions a companion tool called safegres that grades a deployed schema for security and performance regressions and fails CI if the grade drops. I haven’t run it yet, so I’m not going to pretend I have an opinion. It’s on the list.
For the client work I do through my portfolio, multi-tenant Postgres with RLS is the default I reach for on anything with more than one customer in a database. Until now the honest answer to “how do you test the policies?” was “carefully, in staging”. That answer was never good enough and I knew it.
The one test to write this week
Don’t migrate your suite. Write one test. Pick the single table where a leak would hurt most, seed two rows owned by two different tenants, and assert that tenant A sees exactly one row and tenant B sees exactly the other one. Run it under the application role, not the admin one.
If it passes, you’ve confirmed something your mocks never could. If it fails, you’ve found the bug I found in June, except in a pull request instead of a customer’s account. Either way it took an afternoon, and the second test is a copy of the first.
