rlsautotest

Deterministic pgTAP test generation for Postgres / Supabase Row-Level Security.

Featured in Supabase's July 2026 Developer Update under "Made with Supabase."

Status: beta (v0.x). Actively developed and the CLI may still change - but it's built to never emit a false-passing test: anything it can't verify soundly is marked, not faked.

Point it at your database. It reads your RLS policies from the catalog and auto-generates both the tests and the seed data: a native pgTAP suite that proves, per table, per command, per identity, who can SELECT / INSERT / UPDATE / DELETE which rows, plus a per-identity access-matrix report and a CI gate that fails the build on any leak or unprotected table.

pip install rlsautotest

# Quick check: write a per-identity access report, then open rls-report.html in your browser (nothing saved)
rlsautotest --db-url "$DATABASE_URL" --schema public --html rls-report.html

# Or: generate a native pgTAP suite to commit and run in CI (pg_prove / supabase test db / psql)
rlsautotest --db-url "$DATABASE_URL" --schema public --emit supabase/

âš ï¸� Point --db-url at a disposable copy of your database, never production. rlsautotest probes each policy by seeding rows and running real SELECT/INSERT/UPDATE/DELETE. Every probe is wrapped in a transaction and rolled back (nothing is committed), but the statements do run (table locks, triggers, sequences fire). --emit, --report, and --html all connect and probe; only --describe and the static checks (lint/snapshot/diff) just read the catalog.

Running the suite - on a local Supabase (supabase test db), pg_prove, or in CI - is covered in INSTALL.md.

Demo

Path A - quick check (no files saved): one command points at your database and reports who can touch what; an unprotected table is caught immediately.

Path B - generate a suite to commit + run in CI: generate native pgTAP and run it with pg_prove (video).

Path.B.mp4

What it does

RLS is the security boundary of a Postgres or Supabase app, and Supabase's own docs note that writing pgTAP tests for it is "inaccessible to most web developers." So most RLS goes untested. rlsautotest closes that gap without you writing a line of test SQL.

You point it at your database and it:

1. reads your RLS policies from the catalog,
2. generates the test data and identities that exercise each policy (owners, other users, anon, role-holders, tenants),
3. proves, per table, per command, per identity, who can SELECT / INSERT / UPDATE / DELETE which rows,
4. emits a native pgTAP suite you commit and run with supabase test db, pg_prove, or psql,
5. and gives you a per-identity access report plus a CI gate that fails the build on a leak or an unprotected table.

Verifying RLS for HIPAA and SOC 2

Storing PHI or other regulated data in PostgreSQL? RLS is the access control that keeps each patient, tenant, or customer to their own rows. It's the technical safeguard HIPAA's Security Rule requires (45 CFR §164.312(a)(1), Access Control), enforced in the database itself on any Postgres, whether that's Supabase, RDS, Neon, or your own server. On Supabase specifically, their HIPAA guidance calls out RLS for exactly this and offers a BAA.

But RLS only counts if it actually enforces what it claims. A policy with the wrong column or an always-true USING (true) passes review while silently exposing PHI across tenants. rlsautotest produces the evidence that it holds: a per-identity proof, per table and per command, that PHI can't cross a tenant boundary or reach an unauthorized role, plus a committed pgTAP suite and a CI gate that fails the build the moment a policy leaks. That's access-control verification you can put in front of a reviewer.

The same evidence serves SOC 2. RLS is a logical access control, so a passing suite is direct proof for the Common Criteria access-control requirement (CC6.1, logical access to protected data restricted to authorized users), turning "we use RLS" into "here is the test run showing our RLS restricts access as designed."

It verifies the access-control safeguard, not your whole HIPAA or SOC 2 program. Your BAA (with Supabase, your cloud provider, or however you host), encryption, audit logging, and the rest remain separate obligations.

Further reading: Your Postgres RLS is a compliance control. Is it tested?

How it does it

• Auto-generates the data, not just the tests. This is what makes the generated tests mean something. An auto-generated test only proves anything if the data driving it is also generated to match the policy and the identity; otherwise it passes against empty or mismatched rows and proves nothing. rlsautotest does this with reverse-predicate seeding: it works backward from each policy's predicate to the exact rows and identities (owner, other user, other tenant, role-holder, anon) that drive it true and false. So "the owner can see their row" is checked against a row that is actually theirs, and "another tenant can't" against a real, different tenant. You don't hand-write fixtures or scenarios.
• Proves policies are correct, not just present. It becomes each identity (owner, other user, anon, role-holder) and checks actual access, so a policy that's enabled but wrong (USING (true), the wrong column, an always-true predicate) is caught, not just "RLS is on."
• Every test asserts a real, owned row. Assertions check the exact rows an identity can and can't see, so a passing suite means something: break a policy and the test turns red.
• Proves multi-tenant isolation. It seeds two tenants' data and claims and verifies one tenant sees only its own rows: the core invariant of most apps, checked directly.
• Models "denied" the right way. Row-level filtering is verified as zero rows visible; a missing grant is verified as a permission error. The two are distinguished, so a block is proven for the right reason.
• Catches the cross-policy WITH CHECK leak. This fires when a table has two or more permissive UPDATE (or INSERT) policies whose per-policy WITH CHECK is a narrow value constraint (a partition, e.g. one allowed status per role), but the discriminator that's meant to limit who may write what (role, owner, tenant) sits only in USING, not repeated in WITH CHECK. Postgres OR-combines every permissive policy's WITH CHECK independently of which USING matched, so the effective check becomes the union of all of them, with no discriminator. Any identity that can target a row can then write any value any policy permits (e.g. a cutter setting status='Completed'). rlsautotest enumerates the value space and proves, per identity, exactly which forbidden values are accepted. Fix: repeat the role/owner/tenant guard inside each WITH CHECK, or enforce the transition in a trigger / SECURITY DEFINER function, or make the constraint AS RESTRICTIVE so it ANDs instead of ORs.
• Handles real schemas. It seeds foreign-key parents in dependency order, so tables with required relationships are actually tested, and it handles the tricky policies: owner (auth.uid()), tenant/JWT-claim, membership (EXISTS/IN), array membership (= ANY), role lookups ((select role from profiles where id = auth.uid())), RBAC functions (authorize() / has_role()), recursive hierarchies, escape-hatch OR admin grants, and permissive + AS RESTRICTIVE composition.
• Seeds rows it doesn't have a rule for, by asking the database. When a table's constraints defeat a templated insert, a probe-and-repair synthesizer tries the insert, reads the real error, and fixes it: filling NOT NULL columns, seeding single and composite foreign-key parents, varying values to clear UNIQUE conflicts, and neutralizing a CHECK that delegates to a function (then restoring it). The result is that even an opaque-function-gated table with a composite FK and a function-backed CHECK still gets a valid row to test against, with no per-schema hand-coding.
• Solves predicates it has never seen, instead of only matching known shapes. When a policy doesn't fit any named pattern (say a numeric clearance threshold, (auth.jwt() ->> 'clearance')::int >= sensitivity), a general solver reads the predicate's operand roles, derives inputs that should make it pass and fail, and verifies both against your database before writing a test, so coverage extends to novel policies, and anything it can't confirm stays marked rather than guessed.
• Sound by design: never a false pass. Tests are derived from your policies and the catalog, not guessed by an LLM. When a policy can't be proven soundly (e.g. an opaque function) it's marked clearly instead of turned into a green checkmark. And if a test's data precondition can't be established (the seed for a row fails), the engine still probes, then marks that cell UNRELIABLE and fails loudly rather than letting a seeding failure masquerade as a policy outcome.
• Native, ownable output. Standard pgTAP into supabase/tests/database/rls/, runnable by supabase test db, pg_prove, or plain psql. Uses the basejump test helpers when present, or ships a tiny offline shim when they aren't, so it runs online or air-gapped.
• Static checks too. It flags open USING (true) reads, WITH CHECK (true) writes, asymmetric USING/WITH CHECK, self-referential (recursive) policies, RLS-on-but-no-policy, and policy drift via snapshot/diff.
• Maps the bypass surface around your policies. Correct policies can still be undone by how the data is reached, so it also reports the objects and roles that sidestep RLS: owner-rights (SECURITY DEFINER) views and functions a client can reach, materialized views, BYPASSRLS/superuser roles, and RLS-on-but-not-FORCEd tables (see Beyond the policies: bypass surfaces).
• Flags a column-scoped grant that a broader grant defeats. GRANT UPDATE (display_name, bio) says "only these columns," but a leftover table-wide grant (or one inherited from another role) reaches every other column again, so the scope you wrote never actually held. rlsautotest compares each role's column grant against its effective column reach and flags exactly that contradiction - the column-level analogue of the cross-policy WITH CHECK leak (see Column-level security).

What it generates

supabase/tests/database/rls/     # our own folder, separate from your hand-written tests
  000-setup-tests-hooks.sql      # pgTAP + helpers (or offline shim if basejump absent)
  010-rls-enabled.test.sql       # guard: fails if any API-reachable table has RLS OFF
  101-rls-profiles.test.sql      # one file per table, native flat pgTAP
  102-rls-notes.test.sql
.rlsautotest/debug/               # nested/structured copies for debugging

Each test is Arrange-Act-Assert: seed as a privileged role (RLS bypassed), act as a mocked identity (authenticate_as / set_config('request.jwt.claims', …) + SET ROLE), assert the visible/affected rows, with SAVEPOINT isolation so a write test can't corrupt the next one.

Modes

Command	What you get
--emit DIR	full suite layout under DIR/ (default; helper-based, looks native)
--no-helpers	fully self-contained tests (inline set_config/SET ROLE, no helper/000 dependency)
--report	run the suite and print the per-identity access matrix (--report-json for CI)
--html FILE	run the suite and write the access matrix as an HTML report
--no-fail	with --report/--html: don't exit non-zero on problems (default does, for CI gating)
--table T	a single table instead of the whole schema
--describe	show the identity classes the generator derived for a table
doctor	subcommand: verify the probe environment (role privileges, pgTAP, policy-function ownership); writes doctor.json to attach to bug reports

The report

One grid per table (rows are identities, columns are commands), so it reads like a permissions table:

notes                          SELECT  INSERT  UPDATE  DELETE
service_role                     ✓       ✓       ✓       ✓     bypasses RLS
authenticated, authorized        ✓       ✓       ✓       ✓
authenticated, not authorized    ·       ·       ·       ·
anon                             ·       ·       ·       ·

✓ = can, · = blocked. The one thing that lights up red is a ✓ where it should be ·: an authenticated-but-not-authorized user or anon that can act (a security hole). It jumps out without decoding anything. service_role is shown for completeness; it bypasses RLS by design. A table with RLS off is flagged loud (it has no row-level protection at all).

The identity rows are deliberately worded so they aren't mistaken for database roles: authenticated, authorized and authenticated, not authorized are the same Postgres role (authenticated) under different JWT identities/claims. Only service_role, authenticated, and anon are actual Postgres roles. "Authorized" vs "not authorized" is simply whether that identity passes the table's policies (owns the row, is in the right tenant/org, or has the required role).

Beyond the policies: bypass surfaces

A correct set of table policies can still be undone by how the data is reached. A SECURITY DEFINER view or function runs with its owner's rights, so it can hand a client rows that client's own RLS would hide. A materialized view is always a frozen, owner-populated copy of its source. A role with BYPASSRLS (or superuser) skips RLS entirely. And an RLS-enabled table that isn't FORCEd is bypassed by its own owner. None of these show up when you only test the policies, which is exactly where real leaks tend to hide.

rlsautotest maps that surface straight from the catalog, alongside the policy checks, and reports each object or role that can sidestep RLS, with a severity and the reason:

• Views and materialized views that run with owner's rights (not security_invoker), are readable by anon/authenticated, and read an RLS-protected table.
• SECURITY DEFINER functions a client can EXECUTE that reach RLS-protected data (or whose body is opaque), plus any with a mutable search_path (a search-path-injection risk).
• Roles that bypass RLS (BYPASSRLS/superuser) and aren't a sanctioned platform role, flagged higher when a client can log in as or SET ROLE into them. Use --allow-bypass-role to allowlist the ones you expect.
• Tables with RLS enabled but not FORCEd whose owner isn't a superuser.

These are review flags, not pass/fail: a SECURITY DEFINER view is often exactly what you intended. They appear in rlsautotest lint and in a Bypass surfaces section of the --report / --html output (and in --report-json), so a reviewer sees, in one place, every way RLS could be sidestepped and can confirm each is deliberate. Reachability is judged by effective privilege (has_table_privilege / has_function_privilege, which include grants to PUBLIC), so a function a client can call only through Postgres's default PUBLIC EXECUTE grant is surfaced even when you never granted it explicitly.

Column-level security

Postgres lets you scope a grant to specific columns: GRANT UPDATE (display_name, bio) ON profiles TO authenticated says a user may edit those two columns and nothing else. That scoping is real security - it is how you keep role, is_admin, or tenant_id out of a user's reach - but it is silently undone the moment a broader grant is also in force. A leftover table-wide GRANT UPDATE ON profiles, or a privilege inherited from another role, reaches every column again. The column list you wrote still sits in the catalog looking like a boundary, while the effective privilege runs straight past it.

This is the exact column-level analogue of the cross-policy WITH CHECK leak: a restriction expressed in one place, defeated by a wider grant that unions back in. rlsautotest flags that one contradiction and nothing else. It makes no guess about which columns are "sensitive" (there is no column-name heuristic), and it never flags a bare table-wide grant on its own - a grant with no column scoping expresses no intent to scope, so there is nothing to contradict. It compares the developer's expressed scope (pg_attribute.attacl, the column grant) against effective reach (has_column_privilege, which folds in table-wide and inherited grants); the leaked columns are simply effective minus granted.

Each table's --report / --html grid carries a column level security block beside the row-level one, per role and per command (SELECT / INSERT / UPDATE; DELETE is whole-row in Postgres, so it has no column-level privilege and shows n/a):

• granted: id, display_name +2 (green) - a column grant is in force and effective reach matches it exactly: the scope holds, and the listed columns are the permission you gave.
• ⚠ leaks: role, is_admin +1 (red) - a broader grant reaches past the column grant; the named columns are the ones that slipped the scope.
• · - no column-level grant in force, so no scope was expressed and nothing is flagged.

The same facts appear in --report-json under column_security, and - the part that makes this enforceable rather than merely visible - the emitted pgTAP suite bakes it in. For every column-scoped cell, --emit writes one assertion that fails if any column beyond the grant is reachable. A scope that holds today becomes a committed test that turns red the day someone adds a table-wide grant that widens past it; and a scope that is already leaking when you generate the suite is emitted as a real not ok that names the leaked columns, never quietly passed over. As everywhere else in rlsautotest, a detected contradiction is surfaced, not smoothed away.

The usual fix is to drop the redundant table-wide grant and keep only the column grants (or, if the wide grant is genuinely what you intend, drop the column grants so there is no false boundary). examples/colsec.sql is the runnable illustration: three tables with identical row policies whose only difference is the column grid - one scoped (green), one leaking (red), and one bare table-wide grant that is correctly left silent.

When something looks wrong: rlsautotest doctor

The probe needs real privileges from the connection role: SET ROLE to the client roles (anon, authenticated, service_role), CREATE on the target schema (for the pgTAP shim and seed helpers), and the ability to replace any function your policies delegate to (for the mock-wiring proof). If the role can't do one of these, the affected cells show ‼ UNRELIABLE instead of a result, the suite fails loudly on those tests, and the run exits 1. It never bakes a result it couldn't actually observe.

doctor checks all of this in seconds and prints the exact remedy for each failed check (for example, on Supabase local: connect as supabase_admin, which owns the helper functions). It is read-only: every write it attempts is savepoint-wrapped and rolled back.

rlsautotest doctor --schema public --db-url "postgresql://..."

It also writes doctor.json alongside the on-screen report. Filing an issue? Attach that file. It contains catalog metadata only (role, function and table names, sqlstates, check results, and a per-table classification summary). No row data, no credentials. It usually lets a problem be reproduced and fixed without any back-and-forth about your environment.

Catching unprotected tables in CI (important)

A naive "generate tests, commit them, run them" setup has a dangerous blind spot: a table with no RLS at all generates no test, so the suite stays green and the exposure ships silently. rlsautotest closes that hole from two directions:

1. The report is a CI gate. --report and --html exit non-zero (1) when they find a problem: a table that's RLS-off-but-reachable (anon/authenticated can touch it), a check where a forbidden identity can act, or a broken/unreadable table (e.g. a self-referential policy that throws infinite recursion detected in policy, locking out every client role). So a single command fails the build:

rlsautotest --db-url "$DATABASE_URL" --schema public --report   # exits 1 if anything is exposed/leaking

Pass --no-fail to print the report without failing the pipeline (local or non-blocking use). Exit codes: 0 = clean, 1 = problems found.

2. A schema-wide guard test. --emit also writes 010-rls-enabled.test.sql, which asserts that every table reachable by anon/authenticated has RLS enabled. A table shipped without RLS becomes a real not ok, so even teams that only run supabase test db on the committed files (and never re-run the generator) get a red build:

not ok 1 - public.exposed_tbl: RLS must be enabled (table is reachable by anon/authenticated)

It's scoped to reachable tables, so a genuinely-internal table with no client grant won't raise a false alarm.

GitHub Actions

name: rls
on: [push, pull_request]
jobs:
  rls:
    runs-on: ubuntu-latest
    services:
      postgres:
        image: postgres:16
        env: { POSTGRES_PASSWORD: postgres }
        ports: ["5432:5432"]
        options: >-
          --health-cmd pg_isready --health-interval 10s --health-timeout 5s --health-retries 5
    steps:
      - uses: actions/checkout@v4
      - uses: actions/setup-python@v5
        with: { python-version: "3.12" }
      - run: pip install rlsautotest
      # apply your migrations into the throwaway DB first, then:
      - name: Verify RLS
        env:
          DATABASE_URL: postgresql://postgres:postgres@localhost:5432/postgres
        run: rlsautotest --db-url "$DATABASE_URL" --schema public --report

The build goes red the moment a policy leaks or a reachable table is missing RLS.

What it's tested on

The examples/ folder is the runnable test corpus, covering the common and the hard RLS patterns: owner (auth.uid()), tenant/JWT-claim, membership (EXISTS), array claims (= ANY), RBAC functions, recursive policies, session-GUC, permissive + AS RESTRICTIVE composition, and role-gated status state machines (multi-policy UPDATE). On every commit, CI loads the owner-scoped and the multi-tenant example schemas, generates the suite, and runs it, so the core validation is reproducible rather than a claim. The corpus also includes deliberately broken cases that the tool is required to catch: a self-referential policy, and a role-gated state machine with a cross-policy WITH CHECK leak (a role can write a status its own policy forbids). It's been exercised against real-world Supabase schemas too, to harden the generator.

Honest limitations

rlsautotest proves your database enforces what your policies declare. It cannot know your intent: a wrong policy will be faithfully (and greenly) confirmed. It tests the permissions your policies define; commands left with no policy show as · (implicit deny) and aren't asserted unless you opt in. Policies behind opaque/external functions it can't reason about are reported, not faked.

Requirements

• Python 3.10+
• A Postgres database. pgTAP is handled for you. rlsautotest uses your database's pgTAP if present (Supabase ships it) and otherwise loads a small built-in copy, so there's nothing to install on the server.
• A throwaway/local database holding a copy of your schema + policies. Every command that connects (--emit, --report, --html) probes by seeding rows and running real SELECT/INSERT/UPDATE/DELETE, each rolled back, nothing committed, but the statements do run. Point it at a disposable copy, never production. (--describe and the static lint/snapshot/diff checks only read the catalog.)

Part of the UnitAutogen family

rlsautotest is the free, open-source PostgreSQL member of UnitAutogen. We build automated unit-test generators for databases: tools that read your schema and generate the tests for you, instead of you hand-writing them. The test frameworks themselves are open source (pgTAP on Postgres, tSQLt on SQL Server); what UnitAutogen adds is the generator that writes the tests (and the data) for them.

The same idea runs deeper on other engines:

• PostgreSQL: rlsautotest (this project, free) and automated unit-test + branch-coverage generation for PL/pgSQL functions, emitting pgTAP.
• SQL Server: automated unit-test generation and branch coverage for stored procedures, emitting tSQLt (the open-source SQL Server test framework).
• Oracle, Azure SQL: in development.

If your team needs automated database test generation beyond Postgres RLS (SQL Server, Oracle, Azure), get in touch.

Credits

Built on pgTAP and pg_prove (David Wheeler), the basejump Supabase test helpers, and pglast / libpg_query for parsing. Thanks to the Supabase and PostgreSQL communities.

License

Copyright (c) 2026 Munaf Ibrahim Khatri.

Licensed under Apache-2.0. See LICENSE and NOTICE.