Keeping policies correct over time: the approaches compared
Manual review, linters, pgTAP suites, CI assertions, continuous monitoring - honest coverage and blind spots of each approach to keeping Supabase RLS correct.
Five approaches exist for keeping row-level security correct over time, and every team eventually assembles some combination. Here is an honest comparison - what each covers, what each cannot see, and how they compose into coverage that survives real development.
Authorization correctness decays. Not because teams get worse, but because schemas change: migrations add tables, assistants regenerate policies, restores roll back state, dashboards invite hotfixes. Whatever keeps policies correct has to survive that churn — and no single approach does.
The framing matters because teams tend to adopt one approach enthusiastically — a test suite, a lint pass, an annual review — and treat it as the answer. Each approach below is genuinely good at what it covers and structurally blind elsewhere; the honest comparison isn't ranking them but mapping their edges so your combination leaves no unwatched seams.
This article compares the five practical approaches honestly, including the ones this site sells against and the one we sell. Every claim about blind spots applies to our own tooling where relevant; the goal is a map you can use to assemble layered coverage, not an advertisement dressed as analysis.
The comparison at a glance
| Approach | Catches | Cannot see | Runs |
|---|---|---|---|
| Manual review | Intent mismatches, design flaws | Anything between reviews; human fatigue | When scheduled |
| Static linters | Known anti-pattern shapes | Semantic drift from intent; runtime-only issues | On demand / CI |
| pgTAP-style test suites | Broken invariants on covered tables | Tables not yet covered; future features | CI / local |
| CI catalog assertions | Missing flags/policies on new tables | Runtime changes bypassing pipeline | Per build |
| Continuous monitoring | Transitions between deploys, outside exposure | Intent (still yours) | Always |
Read the last column first: cadence is the hidden variable. An approach that catches everything but runs annually provides annual protection. The right question isn't "which approach" but "which gaps remain after my combination" — the sections below make each gap concrete.
One reading note: "cannot see" columns describe structural limits, not implementation quality. A better linter still can't know your intent; a better test suite still doesn't cover tables nobody wrote tests for. The limits come from each approach's vantage point — code, catalog, runtime, or calendar — and vantage points don't merge.
Manual review
The founder re-reads the policy set quarterly; a senior engineer reviews migration PRs. This is the default starting point, and it retains unique strengths: only humans can compare policies against intent ("should editors see drafts?"), reason about novel shapes, and weigh risk trade-offs.
Manual review is also the only approach that improves with context. A reviewer who knows the product's roadmap reads differently — spotting that the upcoming sharing feature will need membership policies, or that last month's "temporary" widening now has a proper fix available. No automated layer accumulates that kind of understanding; tools detect states, humans anticipate needs.
The blind spots are equally structural: review happens at moments, not continuously; attention degrades across forty-table dumps; and reviewers anchor on what changed rather than what now exists. A migration that drops a policy via CASCADE looks like three innocent lines unless someone checks dependencies — which manual reviewers reliably do until deadline pressure arrives.
Practical upgrades for teams relying on manual review: use checklists (the catalog queries from our audit walkthrough turn memory into procedure); timebox dumps (forty policies read carefully beats four hundred read skimmed); and write findings down — unrecorded reviews leave no baseline for the next reviewer to diff against.
Best used for: intent questions, novel patterns, final judgment on severity. Worst used as: the only defense.
A note on cadence: manual review scales with change frequency, not calendar. Teams shipping daily need their reviewers reading policy diffs weekly; a stable quarterly product can genuinely wait a quarter. The review interval should track the rate of authorization-relevant change - which is itself something worth measuring once.
Static linters
Linters parse SQL and flag known anti-patterns: RLS disabled, constant-true policies, missing search paths, unwrapped auth calls. Supabase ships advisor lints; third-party tools extend the catalog — our own comparison with database linting lives at SQL linter versus live probe. Their virtue is speed and determinism — seconds per run, zero false ambiguity.
Linters shine at exactly what manual review is worst at: exhaustive consistency across large schemas. A human reviewing fifty tables will eventually skim; a lint pass evaluates every policy identically every time. They're also the cheapest layer to run — no fixtures, no personas, no test data — which means they get run, and a layer that runs beats a stronger layer that doesn't.
Their ceiling is semantic: a linter knows using (true) is suspicious, not whether it's intentional; it can't know a CASCADE deleted the gate enforcing your compliance posture; it evaluates whatever schema it's pointed at, whenever pointed. Lints are shape-checking — powerful precisely because shapes recur, limited exactly when meaning diverges from shape.
Best used for: pre-deploy gates on known patterns, baseline hygiene everywhere. Blind to: drift between runs, behavioral outcomes, anything requiring knowledge of your model.
Test suites (pgTAP and friends)
In-database tests that impersonate personas and assert isolation — the approach detailed in our testing guide. Their strength is precision: each assertion names an invariant ("outsider cannot read another's document") and fails loudly when violated. Wired into CI, they convert authorization regressions into failed builds.
Suites also encode intent better than any other layer: a test titled "collaborators read shared workspace docs" documents the model and verifies it, in one artifact. When policies get refactored, the suite proves behavior survived the rewrite — something shape-checking lints cannot do, since two syntactically different policy sets can produce identical access.
The structural limit is coverage authorship: tests protect tables someone wrote tests for. A new table ships without tests silently — the suite stays green while the new surface goes unproven. Suites also test the schema as it exists at test time; a restore next month replaces that schema wholesale without consulting your test file. And suites rot slowly as products change: assertions written for last year's roles keep passing against this year's audience model, testing a system that no longer exists.
Best used for: proving invariants on sensitive tables, regression-proofing fixes. Blind to: uncovered surfaces, post-pipeline changes, and intent drift.
CI catalog assertions
The lightweight layer: catalog queries run per build asserting global properties — every public table RLS-enabled, no tautology quals, no write policies lacking WITH CHECK. Where test suites prove behavior per table, assertions prove coverage: nothing ships unprotected.
Their superpower is being judgment-free. A review can be argued with; an assertion that found notifications without RLS cannot be negotiated into passing. Teams describe the same adoption arc as type checking or formatting gates — initial friction, then a generation of contributors who never encounter the failure class because the pipeline won't let it exist.
Blind spots mirror the boundary: CI sees only what flows through CI. Dashboard edits, restores, and hotfix branches change production state without a build. And assertions check existence/shape, never whether a passing policy matches intent.
Best used for: mechanical guarantees enforced on every deploy. Blind to: everything outside the pipeline.
A useful companion assertion extends the same pattern to policy quality: count policies per table and flag tables where a single command has three or more permissive policies - the sprawl signature from the policy-sprawl guide. Assertions like these cost minutes and encode real review judgment into the pipeline.
Continuous monitoring
The always-running layer: scheduled catalog snapshots diffed over time, external probes checking what anonymous callers actually reach, alerts on transitions — new tables without RLS, policies appearing or widening, keys surfacing in bundles. RowShield's home ground, stated plainly.
What monitoring adds is temporal coverage: it watches between deploys and after incidents, catching the restore-shaped and dashboard-edited changes that bypass everything else. External probes add ground truth — what the public internet can actually reach — independent of what your repo believes. And transition-based alerting changes the psychology of drift: instead of hoping someone notices stasis-gone-wrong at the next review, every relevant change generates a signal addressed to whoever owns authorization.
Honest limits: monitoring detects transitions but cannot judge intent either (a widened policy might be a deliberate feature); it acts after change rather than preventing it; and alerts require owners who respond. Monitoring completes a layered defense — it doesn't replace the layers beneath.
Assembling the layers
Coverage composes by filling blind spots:
Manual review -> intent judgment (quarterly + on design changes)
CI assertions -> no unprotected tables ship (every deploy)
Test suites -> invariants hold on sensitive tables (every deploy)
Lints -> known shapes stay clean (every deploy)
Continuous watching -> transitions and outside truth (always)
Each line assumes the ones above it exist; removing any line reopens its specific gap. Teams assembling this stack should order by cost-per-coverage: CI assertions first (minutes, mechanical), probes/tests second, monitoring third, manual reviews last but never skipped — intent questions don't automate away.
The failure mode to avoid is treating the stack as all-or-nothing: "we can't afford continuous monitoring, so we'll do reviews." Layers aren't substitutes; a cheap partial layer beats an absent perfect one every time. Quarterly, review the signals that an assembly has gaps: nobody can say when authorization was last verified, or by whom; findings from the last review have no owner or due date attached; new tables ship without anyone asking their access model; staging and production policy sets have never been compared. Any signal firing means a layer is missing or unowned — both fixable in an afternoon.
A realistic small-team assembly: catalog assertions in CI (an afternoon), the five-probe battery wired into the test runner (another afternoon), quarterly policy reading against the findings template, and outside scanning automated from day one because it requires zero internal infrastructure and because nobody remembers calendars during launches. Four layers, under two days of total setup, every row of the comparison table covered by something that actually runs — with human attention reserved for judgment calls.
Budget note for planning conversations: the layers differ less in cost than in when they cost. Reviews and tests cost effort now and continuously. Monitoring costs setup now and pennies continuously. Incidents cost everything at once, later. Choosing layers on that arithmetic is one of the highest-leverage security decisions a small team makes — usually once, early, with consequences compounding for years. Choosing between them on upfront price alone systematically picks the options that defer costs into incidents.
Two questions test whether an assembly is real. First, which layer catches a CASCADE-dropped policy? The layered answer is instructive: linters miss it entirely (the policy simply disappears; no bad shape remains); test suites catch it if the affected table has coverage and the suite runs after the migration; CI assertions catch the count delta only if you diff policy counts, not just flags; monitoring with transition alerts catches it automatically whenever it happens. When you read coverage tables like this article's, trace your scariest failure through every row.
Second, how much of this applies pre-product? One layer, minimum: the catalog assertion in CI, because unprotected-table defects start before users do. Add the test battery when the first real user data lands, and monitoring when deploys outpace memory. The stack scales by adding layers as stakes rise — which is why starting cheap-and-mechanical beats waiting for a perfect full stack.
Common questions
Which single approach if we can only have one?
CI catalog assertions — the cheapest, most mechanical guard against the most common defect (unprotected tables shipping). But treat that as a floor, not a strategy: assertions say nothing about policy quality, behavioral outcomes, or changes outside the pipeline.
How do lints and continuous monitoring overlap?
Both detect known-shape defects; they differ in vantage and timing. Lints examine code/schema at a moment (pre-commit, pre-deploy); monitors watch live state over time and probe from outside. A lint catches a bad policy before merge; a monitor catches it appearing via dashboard edit Tuesday night. Mature setups run both, tuned so their findings don't double-alert.
Our test suite passes constantly. Isn't that evidence monitoring is redundant?
Only if the suite covers every table and only if production equals staging. Suites pass partly because they're scoped to known surfaces; drift lives in new or mutated state outside that scope. Green suites plus periodic outside scans is the honest combination - the scan results also validate whether your suite's coverage matches reality. And a suite that has never failed deserves suspicion: either your policies have genuinely never regressed, or the tests stopped probing anything meaningful without anyone noticing.
What does RowShield itself cover — and not?
Candidly: automated detection of the pattern classes documented across this site (open tables, tautologies, missing checks, unindexed predicates, key exposure), continuous transition alerting, external probing, remediation SQL proposed for human review. Not: deciding intent, writing your product's access spec, or replacing the tests and reviews above. We fill the always-watching layer; the free scan shows what that layer sees on your project today.
How do these approaches relate to compliance evidence?
Directly: auditors ask for access reviews (manual layer), testing evidence (suites), change management (CI assertions), and ongoing monitoring (the always-layer). A team running the full stack generates compliance artifacts as a byproduct — dated findings documents, test runs, alert histories. Retrofitting that evidence for an audit without the underlying layers is where compliance projects get expensive.
See what the always-on layer would catch on your project: run the free scan — paste your app URL and compare automated findings against your current coverage.
RowShield is an independent product and is not affiliated with, endorsed by, or sponsored by Supabase, Inc.