---
title: "How to test Supabase RLS: 30 of 87 permissions did nothing"
description: "Testing Supabase RLS per policy misses what users see. A role-by-role proof against a copy of production found 30 of 87 permission boxes doing nothing."
url: "https://madsalt.tech/blog/how-to-test-supabase-rls/"
author: "Denys Zaichenko"
language: "en"
published: 2026-10-14
---

# How to test Supabase RLS: 30 of 87 permissions did nothing

TL;DR

Test Supabase row level security the way a member meets it: sign in as each role, run the real action with one permission off and then on, and check that the result changes. On a production app with five editable roles, this found that 30 of the 87 permission boxes an admin could save changed nothing in the database. A policy-by-policy test cannot see that, because it tests the policy you wrote, not the box on the screen.

I'm Denys, a full-stack and AI engineer. Earlier this year I built a project and time tracker for an audit firm on Supabase, where every access rule lives in the database. Below is the method I use to prove those rules, what it found, and what it costs to run.

## Why are policy tests not enough to test Supabase RLS?

A policy test answers "does this policy do what I wrote?". Your users ask a different question: "if I untick this box for this role, can they still do it?". On a small app those questions have the same answer. On an app with a settings page where admins edit a permission matrix, they drift apart.

The app had 41 tables with RLS enabled and 402 `CREATE POLICY` statements across 255 migrations. The admin screen showed a matrix of areas (projects, tasks, timesheets, files and so on) against actions (view, create, edit, archive), one column per role. Every saved box was a promise to the admin. Nothing checked that the database kept it.

Supabase documents the standard tools well: [pgTAP tests](https://supabase.com/docs/guides/database/extensions/pgtap) and the [test helpers](https://supabase.com/docs/guides/local-development/testing/pgtap-extended) let you create users, switch to them and assert what they can read. Use them. The method below sits on top: it is driven by the matrix the admin sees, not by the list of policies.

## How does the matrix proof work?

Each box in the matrix becomes a probe. A probe signs in as a member of the role, performs one real action, and records the outcome. It does this twice: once with the box off and once with the box on, everything else equal. If the outcome changes, the box is enforced. If it does not, the box does nothing, however convincing it looks on screen.

1. **One box in the matrix**. Example: Standard Member · Tasks · Create
2. **Box off: act as a member of the role**. Create a task through the same path the app uses. Record the outcome.
3. **Box on: repeat the same action**. Same user, same data. Only the box changed.
4. **Compare the two outcomes**. The verdict depends only on what the database did.
- **Outcome changed**. Enforced. Refused when off, allowed when on.
- **Same both ways**. Does nothing. The screen promises control that does not exist.
- **Screen and database disagree**. A bug. The page shows a button the database refuses, or the reverse.

*Figure 1. Every verdict comes from a pair of real outcomes, never from reading the code.*

"Outcome" here means whatever a consumer of the permission does: a policy returns rows or refuses a write, a database function raises, a trigger blocks, or an edge function answers 403. One box can guard several of these, so the 87 boxes took 279 checks per role. Each check ended in one of these verdicts:

| Verdict                   | Checks per role | What it means                                                        |
| ------------------------- | --------------- | -------------------------------------------------------------------- |
| Effect                    | 227             | The outcome flipped with the box                                     |
| Dead                      | 30              | The outcome stayed the same with the box off and on                  |
| Unreachable               | 19              | The action cannot be reached for this role at all, proven separately |
| Edge function             | 1               | Enforced by an edge function, checked over HTTP                      |
| Screen only               | 1               | Changes only what the page shows, covered by a front-end test        |
| Screen and database agree | 1               | What the page offers matches what the database allows                |
| Fail                      | 0               | A check whose outcome did not match its expectation                  |

## What did the proof find?

The run on 15 September 2026 covered all five roles an admin can edit (Admin, Senior, Standard, Restricted and a cloned custom role). The results were identical across roles.

![87 permission boxes an admin could save. By what actually enforced each box. Same for all five roles. Enforced by the database: 55; Enforced elsewhere: 1 edge function, 1 screen only: 2; Changed nothing: 30.](https://madsalt.tech/charts/en/how-to-test-supabase-rls.svg)

*Figure 2. The 87 permission boxes an admin could save, by what actually enforced them. Same result for each of the five roles.*

None of the 30 let anyone in. For 29 of them, no policy, function or trigger ever read the box; the last one stopped mattering after a policy was rewritten. That is still a real problem: an admin who unticks "Delete" for a role believes the role cannot delete. We hid all 30 rather than wire them up, because each one would have needed a product decision, and a box that does nothing should not be on the screen while you make it.

The same work surfaced four more defects:

| What was wrong                                                                         | How it showed                                                 | Fix                                                      |
| -------------------------------------------------------------------------------------- | ------------------------------------------------------------- | -------------------------------------------------------- |
| 30 boxes did nothing                                                                   | Dead verdict, identical outcome off and on                    | Hidden from the matrix                                   |
| Three database functions let an action through when a task had no assignee             | A probe that should have been refused was allowed             | The functions now refuse when the value is missing       |
| Resetting a timesheet to draft depended on the wrong box                               | Screen and database disagreed for one role                    | Gated on the reopen permission it belongs to             |
| A member saw new permissions only after a reload                                       | The database had changed, the page had not                    | Permissions refresh every 60 seconds and on window focus |
| A column default broke its own check constraint, so inserts without that column failed | Found by the security review of the fix, during the same work | Default changed to a value the constraint allows         |

The missing-assignee bug was the one that mattered. The guards were written like this in PL/pgSQL:

```sql
if not (has_permission or assignee_id = auth.uid()) then
  raise exception 'forbidden';
end if;
```

When `assignee_id` is null, `assignee_id = auth.uid()` is null, `false or null` is null, `not null` is still null, and `if` treats null as false. The exception never fires. In production no task had an assignee set, so this was not an edge case: it applied to every task in scope. The fix wraps the condition in `coalesce(…, false)` so a missing value refuses, and it shipped first, on its own, before the rest of the batch.

## How do you run it against a copy of production?

A proof against a schema that differs from production proves nothing about production. The harness runs on the local Supabase stack, in a separate database that is a copy of the production schema plus the migrations under review. Before every run it compares a fingerprint of both local databases: 799 rows describing tables, policies, functions and grants. Any difference stops the run.

1. **Production schema**. Copied to one trusted machine. It never goes to a hosted CI runner.
2. **Local stack: app database**. Served by the local API and auth, used by the end-to-end checks.
3. **Local stack: isolated proof database**. Same schema, used only by the matrix proof.
4. **Fingerprint check**. 799 rows per database. Any difference stops the run before a single probe.
5. **Probes run as each role**. As real signed-in members, through the same paths the app uses.
6. **No trace left**. Row counts at the end must equal row counts at the start.

*Figure 3. The proof only counts when the database it ran on matches production.*

Two details made the harness trustworthy:

- **Break it on purpose first.** Before trusting a green run, I replaced one insert policy with one that accepts everything and ran the proof again. It had to go red on the boxes that policy guards. A harness that stays green under a broken policy is decoration.
- **Check the saved state too.** The first pass starts every role from a clean baseline. A second pass starts from each role's current saved matrix, because a box can behave differently next to the boxes an admin actually ticked.

## How do you keep it green after release?

A proof you run once goes stale with the next migration. The proof now runs before every push that touches migrations, the auth code or the matrix itself.

1. **git push**. The change touches migrations, auth or the matrix.
2. **Pre-push hook: matrix proof**. Runs locally against the proof database. Red blocks the push.
3. **Push reaches main**. Deploy runs as usual.
4. **Self-hosted CI run**. The same proof again, on the machine that holds the production schema copy.

*Figure 4. The blocking gate is local, because the production schema copy stays on one machine.*

The proof is 135 tests in 11 files, next to about 3,100 ordinary unit tests in the same project.

## What did not work?

- **Hosted CI could not run it.** The proof needs the production schema copy, and I did not want that on a hosted runner. So the blocking gate is a pre-push hook, and CI on a self-hosted machine is a second check that the deploy does not wait for.
- **Fixed row counts broke.** The first "no trace" check compared against fixed numbers. The next legitimate migration added a permission row and turned it red. It now compares the counts at the end with the counts at the start.
- **Skipped is not passed.** The end-to-end checks need the local edge runtime. When it is down, they skip, and a skip is easy to read as green.
- **It is slow on a busy laptop.** Per-role passes needed longer timeouts on a loaded machine.
- **It proves what the probes describe.** A box nobody wrote a probe for is reported as a coverage gap, not as safe.

## How to repeat this on your Supabase app

1. List every permission the screen lets someone save: role × area × action. That list is the spec, not your policies.
2. For each box, write down the real action it should control and how a refusal shows (no rows, an error, a 403).
3. Build a database that matches production and fingerprint it, so a schema drift stops the run.
4. Run each action as a signed-in member, box off then box on, and compare.
5. Break one policy on purpose and confirm the proof goes red.
6. Hide or wire every box that does nothing, then make the proof a gate before push.

If your app already has RLS but you are not sure it does what the settings screen says, the same method works as an audit of an existing codebase, the first step of a [project rescue](https://madsalt.tech/services/project-rescue/). I wrote about the project behind these numbers in the [audit firm time tracking case](https://madsalt.tech/work/audit-firm-time-tracking/).

Not sure your RLS matches your settings screen?

Happy to compare notes on how you test it. No pitch.

[Get in touch](https://madsalt.tech/#contact)