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.<\/p>\n
Here’s the June story. I had a The pgsql-test announcement<\/a> 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.<\/p>\n 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 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.<\/p>\n 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 ( The part that matters for RLS is Here’s roughly what my old test looked like. I’ve trimmed the noise:<\/p>\n 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.<\/p>\n And here’s the same idea with a real database underneath:<\/p>\n Notice the query has no 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.<\/p>\n 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 The fix is boring: your tests have to run under the same role your application uses. In pgsql-test that means using the While you’re there, check the role has I want to be fair to pgTAP, because I’ve used it for years and it’s not going anywhere. pgTAP<\/a> 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.<\/p>\n 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 ( There’s a claim in there I haven’t reproduced: a Three things, in the order I hit them.<\/p>\n I seeded inside 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 ( I forgot that documents<\/code> 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<\/code> and asserted that the right SQL string went out. The policy itself referenced app.tenant_id<\/code>. The middleware that set the session variable wrote app.tenant<\/code>. 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.<\/p>\nWhy a mock can’t see a policy<\/h2>\n
beforeAll<\/code> 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.<\/p>\nWhat pgsql-test actually does<\/h2>\n
pg<\/code>) for setup and an app client (db<\/code>) for the queries you’re actually testing. It’s MIT licensed and on npm<\/a> and PyPI.<\/p>\nsetContext()<\/code>. It applies a role and any session settings your policies read, scoped to the current transaction via SET LOCAL<\/code> and set_config(..., true)<\/code>. No JWT is validated. You’re telling the database “pretend the request came from this identity” and then checking what it returns.<\/p>\n\/\/ documents.repo.test.ts (before)\nimport { listDocuments } from '.\/documents.repo';\n\nconst query = jest.fn();\njest.mock('pg', () => ({ Pool: jest.fn(() => ({ query })) }));\n\ntest('lists documents for the tenant', async () => {\n query.mockResolvedValue({ rows: [{ id: 101 }] });\n const rows = await listDocuments({ tenantId: 'acme' });\n expect(query).toHaveBeenCalledWith(\n expect.stringContaining('FROM app.documents'),\n expect.anything()\n );\n expect(rows).toEqual([{ id: 101 }]);\n});\n<\/code><\/pre>\n\/\/ documents.rls.test.ts (after)\nimport { getConnections } from 'pgsql-test';\n\nlet db, teardown;\n\nbeforeAll(async () => {\n ({ db, teardown } = await getConnections());\n});\nafterAll(() => teardown());\nbeforeEach(() => db.beforeEach());\nafterEach(() => db.afterEach());\n\ntest('acme sees only its own documents', async () => {\n db.setContext({ role: 'authenticated', 'app.tenant_id': 'acme' });\n const result = await db.query('SELECT id FROM app.documents ORDER BY id');\n expect(result.rows).toEqual([{ id: 101 }]);\n});\n\ntest('a tenant with no documents sees nothing', async () => {\n db.setContext({ role: 'authenticated', 'app.tenant_id': 'nobody' });\n const result = await db.query('SELECT id FROM app.documents');\n expect(result.rows).toEqual([]);\n});\n<\/code><\/pre>\nWHERE tenant_id = ...<\/code>. 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.<\/p>\nThe superuser trap<\/h2>\n
ALTER TABLE ... FORCE ROW LEVEL SECURITY<\/code>. The Postgres docs on row security policies<\/a> 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.<\/p>\ndb<\/code> client with setContext({ role: 'authenticated', ... })<\/code>, not the pg<\/code> 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.<\/p>\nNOBYPASSRLS<\/code>. It’s the default, but someone on the team will have granted BYPASSRLS<\/code> 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”.<\/p>\npgTAP is still fine, this is just a different seat<\/h2>\n
supabase-test<\/code>), Drizzle (drizzle-orm-test<\/code>), PostGraphile (graphile-test<\/code>) and an in-process PGlite variant (pglite-test<\/code>) for when your schema doesn’t need extensions PGlite can’t load. I’ve only tried the plain one so far.<\/p>\nsupabase-test<\/code> 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()<\/code> 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.<\/p>\nWhat I got wrong the first time<\/h2>\n
beforeEach<\/code>. 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.<\/p>\nacme<\/code>, globex<\/code>) so a leak shows up as an obviously wrong row instead of a plausible one.<\/p>\nSET LOCAL<\/code> only lasts for the transaction. That’s a feature here, since db.afterEach()<\/code> 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.<\/p>\nWhere this fits with the rest of the database checks<\/h2>\n