A policy that has never run is not a security boundary

Twenty-five migrations reached production without ever being executed. When we finally ran them, every failure was in the test harness. That is less reassuring than it sounds.

This line is from one of our architecture decision records, written in July while scoping a feature that needed row-level security:

Nothing in CI applies migrations, and the authoring machine has no local Postgres, Docker or Deno. All SQL in this repo currently ships unvalidated.

Our backend is Postgres, and nearly all of its security is row-level security. Who can read a post, who can see a usage row, who can write a device record: all of it is policy SQL in numbered migration files. There were twenty-five of them. Each was written, reviewed by reading, applied to production by hand, and executed for the first time against real data.

The application code was well covered. The mobile app alone had 63 test files, and they ran on every pull request. The SQL had zero.

That shape is common, and the reason is worth being precise about. Migrations feel tested because the application that uses them is tested. But the app's suite mocks the database client, so it exercises every branch that reads a policy's result without ever evaluating the policy. The migration is the one artefact in the repository that runs only in production.

Why nobody fixed it

The cost fell on a hypothetical future person. The friction fell on whoever was working that day.

Running migrations locally needs a Postgres. The machine that wrote them is a Windows laptop with no psql, no Docker and no local Postgres, and both available package managers wanted an elevation prompt to install one. Meanwhile the SQL kept working in production, which is the most persuasive argument there is for doing nothing.

What executing the SQL would not have caught

Two of those unvalidated migrations had real security defects in production, and the harness we eventually built would not have found either of them.

The first was a table holding device credentials, created without enable row level security. Not a wrong policy; no policy mechanism at all. The second was a security definer function that took a caller-supplied user ID and was never revoked from unauthenticated clients.

Both applied cleanly to a real database. Both were found by a person reading SQL during a review, and both were closed by a later migration. A harness that only runs migrations would have reported success on each. Keep that in mind through the rest of this: execution is necessary, and it is not what found the worst bugs.

The gate

What changed things was not discipline. It was a design that could not proceed without execution.

The sync rewrite put a Postgres view over a table of per-user data, and the view needed one option:

create view public.limit_states with (security_invoker = true) as …

A Postgres view runs as its owner by default, so the base table's row-level security is evaluated as the owner and effectively switched off. Omit those three words and every user's row is readable by every other user, through the view. Nothing errors. The app works.

You cannot convince yourself that line is right by reading it. The only way to know is to connect as one user, select another user's row, and get nothing back.

Our design reviews are AI-assisted: an architect role proposes, a critic role attacks, and the team decides. The critic's turn on this design:

You are hand-writing a security control you have no way to execute.

The record resolved it by cutting scope and adding a gate:

A migration validation harness is a hard precondition for any RLS work. Nothing in this repo has ever executed its own SQL before shipping. No RLS boundary ships until migrations can be applied and policy assertions run — including a negative test that the policy denies what it should deny.

The last clause is the one that makes this a security story. A test that the right user can read their own row passes identically when the policy has been deleted. Only an assertion that fails when the boundary disappears carries information.

The record is dated 27 July. The harness was running in CI on 29 July. Seven weeks of unvalidated SQL had produced no harness. One blocked design produced it in two days.

The harness, and the stub that lied

A CI job, a Postgres service container, and a shell script:

test-migrations:
  services:
    postgres:
      image: postgres:16
  steps:
    - run: bash supabase/test/run-migrations.sh

The script drops the public schema, stubs the handful of platform-managed objects the migrations assume, applies every migration in order, and runs each assertion file.

The stub is where the first lesson is. The obvious version creates auth.users with only an id, since that is all any foreign key references. The harness comment explains why that is wrong:

Not just id. Two things reach past it and both are hard errors, not skips: 0018 joins on au.email, and handle_new_user() reads new.email and new.raw_user_meta_data, and it fires on every insert into auth.users a test fixture does — so a missing column fails the ASSERTIONS, not the migration, which is a much more confusing way to find out.

A test environment is a claim about production. Every simplification in it is an assumption you have stopped checking.

Four failures, none of them in the SQL

The job had never run. It took four attempts to go green:

The missing publication. A realtime publication the migrations expect does not exist in bare Postgres. The run died at migration 4 of 26.
The thin stub. auth.users had only id, as above.
The trigger that woke up. Fixing the stub made the signup trigger fire for the first time, and it collided fixture handles against a unique index. The fixtures had been written against a stub where the trigger was inert.
The revoke that meant something else. A migration's revoke … from public, anon also stripped authenticated. That migration's own assertion caught it.

The four new migrations in the change all applied cleanly on first contact with a database. The SQL written knowing it would be executed was right the first time. Every failure was in the scaffolding. That says something uncomfortable about the twenty-five written knowing nothing would check them.

The failure that was real

Failures one to three are the price of building a fake version of a managed platform. Number four is a real difference between environments.

On bare Postgres, authenticated gets EXECUTE on new functions through PUBLIC. On our platform it holds an explicit grant, because the platform sets default privileges that way. So revoke all on function … from public, anon does two different things. On the platform it removes the public grant and leaves the explicit one. On bare Postgres it removes the only grant authenticated had.

The migration applied fine in both. What noticed was an assertion that the function was still callable by authenticated after being revoked from anon. The fix was to name authenticated explicitly, so the statement means the same thing everywhere. Then we checked the real system rather than trusting the fake:

has_function_privilege('authenticated', 'get_aura(uuid)') = t

A revoke is not a self-contained instruction. Its effect depends on a grant graph that belongs to your platform, not your repository. Every revoke you write is a statement about default privileges you did not set.

This was found by a harness less faithful to production than production is. That is the case for building the imperfect harness rather than waiting for a perfect one.

A runner for the machine that writes the SQL

CI alone did not solve the original problem. A loop that needs a push and a four-minute wait is a loop developers route around, and the laptop writing the SQL still could not run any of it.

So there is a second runner, in Node, using embedded-postgres (real Postgres binaries, no admin rights, no container runtime) and node-postgres in place of psql.

The interesting decision in it is not the database. It is this, from its header:

Same setup SQL — parsed out of run-migrations.sh at runtime rather than copied, so the two cannot drift. This repo has been bitten by duplicated contracts before.

A second harness that copies the first one's setup block is two harnesses for about a month. After that it is one harness and one lie. Reading the shell script and extracting its SQL is a little ugly, and it cannot drift.

It is strict about what it reads. It strips the one psql meta-command the suite uses (node-postgres already stops on error, which is all ON_ERROR_STOP bought) and refuses to run on any other meta-command rather than quietly changing what a file means.

Building it surfaced two more real bugs, both older than the runner:

Writes were untestable. The harness granted only SELECT to authenticated, directly under a comment saying the platform grants everything and lets RLS do the gating. Every write-path assertion died on "permission denied" before reaching the policy it meant to test. A whole class of test had been impossible to write, and nobody noticed because nobody had written one yet. The moderation assertions were the first to insert as a real user.
The locale. initdb follows the OS locale, so the database came up as WIN1252 and a migration containing an arrow character could not be encoded. The runner now creates UTF8 from template0.

And one caveat, recorded in the commit rather than glossed: there are now three Postgres versions in play. CI runs 16, the embedded runner supplies 18.4, and production is pinned to 17. Close enough to validate. Not identical to production.

The commit that admits it has no coverage

One migration went out of order. The moderation migration (block relationships, content takedown, a term guard) was applied to production by hand before the local runner existed. Its assertions came afterwards, in a commit whose subject says so:

test(db): assertions for 0026 — NEVER EXECUTED, see header

The body:

Owed rather than optional: 0026 was applied to production by hand before this suite existed, because the authoring machine has no Postgres. That inverts the plan's own test-first order, and the file says so at the top rather than implying coverage it has not earned.

A file named *.test.sql is a claim. Holding the assertions back would have lost the work; shipping them as ordinary tests would have made a claim nobody had checked. The header keeps the work and withdraws the claim. The runner that executed them landed two hours later.

The same commit lists what the assertions exist to catch, and it is a good working definition of a negative case. Four of the pinned failures would commit cleanly and show up only as behaviour:

the block-set function returning NULL instead of an empty array: every user sees an empty feed
the auto-hide trigger without security definer: hides nothing, silently, while the insert that should have fired it succeeds
an unescaped regex metacharacter in a seeded term: all posting throws
authenticated losing EXECUTE on one function: every feed read returns 403

Plus one no happy-path test can see. The block function runs as its owner and bypasses RLS, so a predicate that dropped its "and it's my own block" half would wipe the target's follower graph while every other assertion still passed.

What it cost

Maintenance. Six assertion files, two runners, a stub of a managed platform's auth schema, and a standing duty to update the stub whenever the platform changes. That is real overhead for a small team.

Parity. Three Postgres majors across CI, local and production. The auth stub is correct only in the ways someone thought to make it correct.

Scope. It does not catch design errors. RLS never enabled and a missing revoke both apply cleanly through any harness. The harness proves a policy does what it says. It cannot tell you that you forgot to write one.

What we would do differently

Run migrations in CI from migration one. A Postgres service container is about ten lines of YAML. The cost of adding it on day one is lower than on any later day.
Require a negative case per assertion file, mechanically. A rule that lives only in a decision record lasts as long as people remember it. A CI check that fails any assertion file without an expected denial outlives them.
Pair the harness with a review checklist for what it cannot see. Every new table: is RLS enabled? Every security definer function: is it revoked from anon? Both of the real defects here would have been one line on that list.

A test environment is a hypothesis

Three of our four failures were artefacts of the fake. The fourth was real, and the only way to tell them apart was to check the actual system. In every case the temptation was to adjust the fake until it went green.

A positive assertion on a policy passes with the policy deleted. If a test file has no negative case, it is documentation of the happy path.