Metadata-Version: 2.5
Name: pgrls
Version: 0.57.0
Summary: Static analyzer for Postgres Row-Level Security — 68 lint rules for tenant and per-user row-scoping bugs, performance traps, and hygiene (19 auto-fixable); a Z3 tenant-isolation prover (pgrls verify) that proves no row leaks to an anonymous session, across tenants, or through a view, or shows the leaking row; gold-standard RLS scaffolding (pgrls generate); runtime seq-scan analysis (pgrls perf); a semantic policy-diff command for CI gating; and a pytest plugin for RLS isolation tests.
Project-URL: Homepage, https://github.com/pgrls/pgrls
Project-URL: Repository, https://github.com/pgrls/pgrls
Project-URL: Issues, https://github.com/pgrls/pgrls/issues
Project-URL: Changelog, https://github.com/pgrls/pgrls/blob/main/CHANGELOG.md
Project-URL: Documentation, https://github.com/pgrls/pgrls#readme
Author: Dmitry Maranik
License: MIT License
        
        Copyright (c) 2026 Dmitry Maranik
        
        Permission is hereby granted, free of charge, to any person obtaining a copy
        of this software and associated documentation files (the "Software"), to deal
        in the Software without restriction, including without limitation the rights
        to use, copy, modify, merge, publish, distribute, sublicense, and/or sell
        copies of the Software, and to permit persons to whom the Software is
        furnished to do so, subject to the following conditions:
        
        The above copyright notice and this permission notice shall be included in all
        copies or substantial portions of the Software.
        
        THE SOFTWARE IS PROVIDED "AS IS", WITHOUT WARRANTY OF ANY KIND, EXPRESS OR
        IMPLIED, INCLUDING BUT NOT LIMITED TO THE WARRANTIES OF MERCHANTABILITY,
        FITNESS FOR A PARTICULAR PURPOSE AND NONINFRINGEMENT. IN NO EVENT SHALL THE
        AUTHORS OR COPYRIGHT HOLDERS BE LIABLE FOR ANY CLAIM, DAMAGES OR OTHER
        LIABILITY, WHETHER IN AN ACTION OF CONTRACT, TORT OR OTHERWISE, ARISING FROM,
        OUT OF OR IN CONNECTION WITH THE SOFTWARE OR THE USE OR OTHER DEALINGS IN THE
        SOFTWARE.
License-File: LICENSE
Keywords: linter,postgres,postgresql,rls,row-level-security,security
Classifier: Development Status :: 4 - Beta
Classifier: Environment :: Console
Classifier: Intended Audience :: Developers
Classifier: License :: OSI Approved :: MIT License
Classifier: Operating System :: OS Independent
Classifier: Programming Language :: Python :: 3
Classifier: Programming Language :: Python :: 3.11
Classifier: Programming Language :: Python :: 3.12
Classifier: Programming Language :: Python :: 3.13
Classifier: Topic :: Database
Classifier: Topic :: Security
Classifier: Topic :: Software Development :: Quality Assurance
Classifier: Typing :: Typed
Requires-Python: >=3.11
Requires-Dist: click>=8.2
Requires-Dist: pglast<7,>=6
Requires-Dist: psycopg[binary]>=3.1
Requires-Dist: z3-solver>=4.13
Provides-Extra: dev
Requires-Dist: build>=1.0; extra == 'dev'
Requires-Dist: hypothesis>=6.0; extra == 'dev'
Requires-Dist: mypy>=2.0; extra == 'dev'
Requires-Dist: pytest>=8.0; extra == 'dev'
Requires-Dist: ruff<0.16,>=0.15; extra == 'dev'
Requires-Dist: testcontainers[postgres]>=4.0; extra == 'dev'
Requires-Dist: twine>=5.0; extra == 'dev'
Provides-Extra: diff-apply
Requires-Dist: testcontainers[postgres]>=4.0; extra == 'diff-apply'
Provides-Extra: diff-z3
Requires-Dist: z3-solver>=4.13; extra == 'diff-z3'
Provides-Extra: ephemeral
Requires-Dist: testcontainers[postgres]>=4.0; extra == 'ephemeral'
Provides-Extra: lsp
Requires-Dist: pygls>=2.0; extra == 'lsp'
Provides-Extra: mcp
Requires-Dist: fastmcp>=2.0; extra == 'mcp'
Provides-Extra: testing
Requires-Dist: pytest>=8.0; extra == 'testing'
Description-Content-Type: text/markdown

# pgrls

[![PyPI version](https://img.shields.io/pypi/v/pgrls.svg)](https://pypi.org/project/pgrls/)
[![Python versions](https://img.shields.io/pypi/pyversions/pgrls.svg)](https://pypi.org/project/pgrls/)
[![License: MIT](https://img.shields.io/pypi/l/pgrls.svg)](https://github.com/pgrls/pgrls/blob/main/LICENSE)
[![CI](https://img.shields.io/github/actions/workflow/status/pgrls/pgrls/test.yml?branch=main&label=tests)](https://github.com/pgrls/pgrls/actions/workflows/test.yml)
[![Downloads](https://img.shields.io/pypi/dm/pgrls.svg)](https://pypistats.org/packages/pgrls)

**[▶ 60-second demo](https://raw.githubusercontent.com/pgrls/pgrls/main/docs/screencast.svg)** · **[Quickstart](docs/QUICKSTART.md)** · **[Rule reference](docs/RULES.md)** · **[Docs site](https://pgrls.github.io/pgrls-docs/)** · **[CHANGELOG](CHANGELOG.md)** · **[PyPI](https://pypi.org/project/pgrls/)**

> **Static analyzer for Postgres Row-Level Security.**
> Catches the policy bugs eyeball-review misses — broken row scoping (across tenants *and* between users in the same tenant), inverted auth checks, write-side holes; 19 of 68 rules mechanically auto-fixable.
> `pgrls diff` classifies every migration **SAFE / BREAKING / REQUIRES_REVIEW / DANGEROUS** so CI gates on real regressions, not safe schema changes.
> MIT, framework-agnostic (Supabase, PostgREST, Hasura, Django, raw SQL), CI-native (text / JSON / SARIF / Markdown / HTML / GitHub-PR-comment / GitHub annotations / JUnit XML / GitLab Code Quality).

<!--
  Animated SVG cast generated by termtosvg from the demo recipe in
  docs/screencast.md. Click-through opens the raw SVG which animates
  in the browser (GitHub's <img> sandbox renders the first frame as a
  static preview). Both src and href use absolute raw.githubusercontent
  URLs so PyPI's project page renders the image too — relative paths
  break there. Re-record with `bash docs/screencast.md`'s recipe after
  a feature ships.
-->
<p align="center">
  <a href="https://raw.githubusercontent.com/pgrls/pgrls/main/docs/screencast.svg">
    <img src="https://raw.githubusercontent.com/pgrls/pgrls/main/docs/screencast.svg" alt="pgrls 60-second tour" width="780">
  </a>
</p>

> **Beta — actively maintained.** 68 lint rules, 19 mechanically auto-fixable, [semantic policy-diff command](#diff--pgrls-snapshot--pgrls-diff), pytest plugin for RLS isolation tests. Tested on PostgreSQL 15, 16, 17. Stable JSON / SARIF schema for CI integrations. The [CHANGELOG](CHANGELOG.md) records every release; current build is shown by the PyPI badge above.
>
> - **Lint & fix** — `pgrls lint` checks a live database against all sixty-eight rules and reports findings as text, JSON, SARIF, Markdown, HTML, GitHub-PR-comment (`--format pr-comment`), GitHub Actions annotations (`--format github`), JUnit XML (`--format junit`), or GitLab Code Quality (`--format gitlab`) for CI. `pgrls fix` auto-remediates the mechanically-fixable rules (SEC001, SEC002, SEC004, SEC010, SEC011, SEC015, SEC017, SEC019, SEC020, SEC030, SEC031, SEC032, SEC044, PERF001, PERF003, PERF004, HYG003, VIEW001, VIEW002) — to stdout or a migration-ready `.sql` file (`--output`). `pgrls lint --baseline` records existing findings so CI fails only on *new* ones, letting a team adopt pgrls on a legacy database without clearing the whole backlog first.
> - **Generate** — `pgrls generate` scaffolds gold-standard RLS for tables that lack it — per-tenant (`tenant_id`) or per-user (`--model owner`, incl. the Supabase `auth.uid()` form): ENABLE + FORCE, an isolation policy, a restrictive floor, and the index, output designed to lint clean. Don't trust your ORM's RLS; generate correct RLS, then lint it. `--strict-binding` scaffolds the *loud* variant — see below.
> - **Test** — the `pgrls.testing` pytest plugin for writing RLS tests: role switching, per-test transactions, and tenant-isolation assertions.
> - **Snapshot & diff** — `pgrls snapshot` / `pgrls diff` is a semantic RLS-policy diff that classifies every change SAFE / BREAKING / REQUIRES_REVIEW / DANGEROUS. Z3-based predicate analysis is built in, plus migration-as-input — apply a migration to an ephemeral Postgres and diff the result (`pip install pgrls[diff-apply]`), with `CREATE EXTENSION` auto-detection and a cached-baseline Docker image (managed with `pgrls cache`) for fast re-runs.
> - **TypeScript port** — [`pgrls-test`](https://www.npmjs.com/package/pgrls-test) on npm implements the same RLS-testing contract for JS/TS — both `pg` and `postgres.js` driver adapters, vitest-friendly. See [`ts/`](ts/) in this repo.
> - **VS Code extension** — [`pgrls/pgrls-vscode`](https://github.com/pgrls/pgrls-vscode) wraps the CLI; `pgrls: Lint database` surfaces findings as diagnostics in the Problems panel, with hover documentation per rule.

## Install

pgrls needs **Python 3.11+** (and Postgres 15+ to lint against) — check `python --version` first. On an older Python, `pip install pgrls` fails with the cryptic `Could not find a version that satisfies the requirement pgrls` rather than a clear version error.

```bash
pip install pgrls   # requires Python 3.11+
```

pgrls is tested in CI against PostgreSQL 15–17 (see [`.github/workflows/test.yml`](.github/workflows/test.yml) for the matrix).

## Lint without a live database

No database to point at? Hand pgrls your migrations and it builds a throwaway Postgres for you — applies them in order, introspects, lints, and tears it down:

```bash
pip install 'pgrls[ephemeral]'          # adds testcontainers; needs Docker
pgrls lint --migrations ./migrations    # auto-detects Supabase / Prisma / Flyway / sqitch / plain .sql
pgrls lint --supabase                   # shortcut for ./supabase/migrations + the auth.* stubs and roles
```

`--migrations` takes a directory or a single `.sql` file; the layout is auto-detected (override with `--migrations-layout` / `--migrations-glob`), and `--create-role NAME` pre-creates any role your policies reference. `--pg-image` picks the image the ephemeral database is built from (default `$PGRLS_EPHEMERAL_PG_IMAGE`, else `postgres:17-alpine`). It's the zero-setup way to gate RLS in CI — no service container, no `DATABASE_URL`, just Docker.

**No Docker either?** Hand pgrls the DDL directly — it parses it offline (no
Postgres, no container) and checks what it can determine from the SQL alone:

```bash
pgrls lint --sql-file schema.sql                 # repeat --sql-file per file (order matters)
cat migrations/*.sql | pgrls lint --sql-file -   # or pipe a concatenated diff
pgrls snapshot --sql-file schema.sql -o schema.json  # snapshot from DDL, no DB
pgrls fix --sql-file schema.sql > fixes.sql      # emit-only offline
pgrls generate --sql-file schema.sql             # scaffold RLS offline
pgrls generate --strict-binding                  # ...and fail loudly when no tenant is bound
pgrls generate --tenant-column account_id        # discriminator to detect (default tenant_id, or user_id under --model owner)
```

Two migration revisions can be compared with **no database at all** — snapshot
each side's DDL offline, then diff them, so a PR that silently drops a tenant
predicate fails the gate without the target database ever being touched:

```bash
git show origin/main:schema.sql | pgrls snapshot --sql-file - -o base.json
pgrls snapshot --sql-file schema.sql -o head.json
pgrls diff base.json head.json --fail-on dangerous   # exit 1 on a DANGEROUS RLS change
```

Or get **one PR verdict** — the regression check *and* a fresh lint of the changed
schema, in a single Markdown report + exit code (the payload a CI check posts):

```bash
pgrls pr base.json head.json                 # markdown report; exit 1 if either gate trips
pgrls pr base.json head.json --fail-on dangerous --lint-fail-on error
```

Offline analysis is **sound but partial**: rules that need live catalog state
(BYPASSRLS roles, SECURITY DEFINER functions, triggers, …) can't fire, so
pgrls *skips and lists them* (`skipped_rules` in `--format json`). **An offline
exit 0 is not a clean bill of health** — add `--require-full-coverage` to make
a partial offline run fail your CI gate. It's the zero-dependency way to lint
the RLS in a PR's `.sql` diff.

## Real-world bugs pgrls catches

The kind of mistake that ships to prod despite policy review:

```sql
CREATE POLICY tenant_read ON public.documents
    FOR SELECT
    USING (auth.uid() IS NULL OR owner = auth.uid());
```

Looks fine — and is structured the way many RLS tutorials show it. But `auth.uid()` returns `NULL` for any connection without a session JWT. For those connections the `IS NULL` branch is `true`, the `OR` short-circuits, and the policy admits *every* row of `public.documents`. It's a recurring pattern in multi-tenant Supabase / PostgREST projects — the kind of thing a public CVE write-up names by hindsight.

`pgrls` flags it as **SEC004** (severity `error` — the policy applies to every role; restricted to `authenticated` it is a `warning`, since a token-less request cannot reach it) in milliseconds. With `--explain`, the rule's reference paragraph is appended underneath the finding (lines hard-wrapped here for the README; the real output is one long line per paragraph):

```
$ pgrls lint --rule SEC004 --explain
  ERROR  SEC004  public.documents.tenant_read
         Policy 'tenant_read' on public.documents has a top-level
         `auth_func() IS NULL` disjunct in its USING clause, and the policy
         applies to anon / PUBLIC. For token-less connections that disjunct
         is true, satisfying the policy and exposing every row. Remove the
         IS NULL disjunct or replace with an explicit deny.

         The pattern: a policy with USING (auth_func() IS NULL OR <real check>).
         auth_func() returns NULL for anonymous connections, so the IS NULL
         disjunct is true and the OR is satisfied without ever evaluating the
         real check. Anonymous clients see all rows.

pgrls: 1 error.
```

RLS isn't only about keeping tenants apart. The same bug class bites *within* a single tenant, when rows are meant to be per-user:

```sql
CREATE TABLE documents (id uuid, tenant_id int, owner_id uuid, body text);
ALTER TABLE documents ENABLE ROW LEVEL SECURITY;
CREATE POLICY tenant_scope ON documents
    USING (tenant_id = current_setting('app.tenant')::int);
```

Cross-tenant reads are blocked, so this passes a tenant-isolation review. But there's an `owner_id` column and nothing keys on it, so every user *in* a tenant reads every other user's documents. If that table holds drafts, DMs, or private uploads, it's a leak. **SEC027** (info) flags the table so you decide: add a per-user predicate, or confirm it's intentionally tenant-shared and allowlist it.

[Browse the full rule catalogue](docs/RULES.md) for the other 66 — missing `WITH CHECK`, `BYPASSRLS` roles, per-row auth-function evaluation, search-path attacks, view-mediated RLS bypasses, and more.

## Usage

Scaffold a config (optional — `pgrls` runs with zero config):

```bash
pgrls init                     # writes a commented pgrls.toml; --force to overwrite
pgrls init --preset supabase   # tailor the conventions to a stack
```

`--preset` (`generic` · `supabase` · `postgrest` · `neon`) tailors the documented tenancy convention and the exact `pgrls generate` command for that stack — rules stay at their defaults.

Point `pgrls` at any Postgres database:

```bash
export DATABASE_URL="postgres://user:pass@host:5432/db"
pgrls lint
```

Or pass the URL directly:

```bash
pgrls lint --database-url "postgres://user:pass@host:5432/db"
```

Limit the scan to specific schemas:

```bash
pgrls lint --schemas public,tenant
```

Point at a non-default config file, or pick an output format:

```bash
pgrls lint --config ./config/pgrls.toml --format text     # human-readable (default)
pgrls lint --config ./config/pgrls.toml --format json     # machine-readable for CI
pgrls lint --config ./config/pgrls.toml --format sarif    # GitHub Code Scanning
pgrls lint --config ./config/pgrls.toml --format markdown   # rendered CI reports / runbooks
pgrls lint --config ./config/pgrls.toml --format pr-comment # collapsible per-rule GitHub PR comment
pgrls lint --config ./config/pgrls.toml --format github     # GitHub Actions run annotations
pgrls lint --config ./config/pgrls.toml --format junit    # JUnit XML for CI test reports
pgrls lint --config ./config/pgrls.toml --format html     # standalone HTML report
```

Or run only specific rules — handy when scoping a SARIF report in CI, or
investigating one rule in isolation. `--rule` is case-insensitive,
repeatable, and overrides `[lint] disable` in the config so you can pull a
disabled rule back in for one run without editing the config:

```bash
pgrls lint --rule SEC001 --rule SEC003
```

Or run everything *except* certain rules with `--exclude-rule` (the
complement of `--rule`; case-insensitive, repeatable):

```bash
pgrls lint --exclude-rule SEC022 --exclude-rule PERF002
```

Trim the printed report to the findings you care about with
`--min-severity` (display-only — the exit code still reflects every finding
per `--fail-on`, so hiding info noise can't flip CI green), and write the
report to a file instead of stdout with `--output`/`-o`:

```bash
pgrls lint --min-severity warning            # hide info-level nudges from output
pgrls lint --format sarif --output pgrls.sarif
```

Pass `--explain` to append each rule's reference paragraph beneath its
finding in the text output, so a CI log carries the *why* next to the
*where* without a separate `pgrls explain <RULE>` lookup. Text format
only — JSON / SARIF / Markdown / GitHub / JUnit keep their schemas stable.

### Example output

Text (default):

```
  ERROR  SEC001  public.users
         Table public.users does not have row-level security enabled.
         Add ENABLE ROW LEVEL SECURITY or include the table in
         [lint.rules.SEC001].allowlist if it is a public reference table.

pgrls: 1 error.
```

JSON (`--format json`):

```json
{
  "violations": [
    {
      "rule_id": "SEC001",
      "severity": "error",
      "title": "RLS not enabled on table",
      "message": "Table public.users does not have row-level security enabled. Add ENABLE ROW LEVEL SECURITY or include the table in [lint.rules.SEC001].allowlist if it is a public reference table.",
      "location": "public.users"
    }
  ],
  "summary": { "errors": 1, "warnings": 0, "infos": 0, "others": 0, "total": 1 }
}
```

The JSON shape is the public CI contract — top-level keys, per-violation keys, and summary keys are stable across releases. Pipe through `jq` to filter, count, or transform; ship to a dashboard; upload as a build artifact.

SARIF (`--format sarif`) emits a SARIF v2.1.0 document. GitHub Code Scanning, Azure DevOps, and other static-analysis aggregators consume it directly — see the GitHub Actions recipe below for the upload step that puts findings inline on PRs.

GitLab CI (`--format gitlab`) emits a **Code Quality** report (CodeClimate JSON). Unlike the SARIF/SAST path — a GitLab *Ultimate* (paid-tier) feature — the Code Quality widget works on **every** GitLab tier including Free, so this is the way to surface pgrls findings in a merge request on GitLab.com or a self-managed instance without an Ultimate subscription:

```yaml
# .gitlab-ci.yml
pgrls:
  image: python:3.12-slim
  script:
    - pip install pgrls
    - pgrls lint --database-url "$DATABASE_URL" --format gitlab --output gl-code-quality.json
  artifacts:
    reports:
      codequality: gl-code-quality.json
  allow_failure: true   # show findings in the MR without hard-failing; drop this line to gate on them
```

Each finding carries a stable `fingerprint`, so GitLab tracks it across pipeline runs (new vs. resolved) and, in the MR view, marks findings introduced by the branch.

Exit codes follow the standard linter convention:

- `0` — clean (or findings below `fail_on`)
- `1` — findings met or exceeded `fail_on` (default `warning`); your schema has an RLS issue
- `2` — `pgrls` itself failed to run (bad config, DB unreachable, fixer SQL rolled back, etc.). Distinct from `1` so CI alerts can route "schema bug" differently from "tool error."

### Baseline — adopt pgrls on a legacy database

Running pgrls against an existing database for the first time often surfaces a backlog of pre-existing findings. `--baseline` lets you ratchet: record today's findings and have CI fail only on *new* ones.

```bash
# First run (file absent): records every current finding, exits 0.
pgrls lint --database-url "$DATABASE_URL" --baseline pgrls-baseline.json

# Later runs: report and fail only on findings NOT in the baseline.
pgrls lint --database-url "$DATABASE_URL" --baseline pgrls-baseline.json
```

The first run writes the baseline file and exits `0`. Every later run suppresses findings already recorded and exits nonzero only when a *new* finding appears — so a team can adopt pgrls without fixing the whole backlog up front, then chip away at the baseline over time. Commit the baseline file to the repo.

To re-baseline after deliberately accepting new findings, pass `--update-baseline` alongside `--baseline FILE`; the baseline is refreshed in place with the current findings (replace, not merge — stale entries for findings that no longer fire are dropped). No need to delete the file first.

### Auto-remediation: `pgrls fix`

`pgrls fix` generates SQL for the rules whose remediation is mechanical. Default mode is dry-run — it prints the SQL but does not modify the database. Pass `--apply` to execute, or `--output <file>` to write a migration-ready `.sql` script (a header plus one `-- [rule] description` comment per statement) instead of printing to stdout.

```bash
# Dry-run: print what would change.
pgrls fix --database-url "$DATABASE_URL"

# Apply for real.
pgrls fix --database-url "$DATABASE_URL" --apply

# Only fix one rule.
pgrls fix --database-url "$DATABASE_URL" --rule SEC002 --apply

# Write the SQL to a migration-ready file instead of stdout.
pgrls fix --database-url "$DATABASE_URL" --output migration.sql

# CI gate: exit 1 if any auto-fixable violations exist (no SQL emitted).
pgrls fix --database-url "$DATABASE_URL" --check
```

Currently fixable: **SEC001** (emits `ALTER TABLE … ENABLE ROW LEVEL SECURITY;`), **SEC002** (emits `ALTER TABLE … FORCE ROW LEVEL SECURITY;`), **SEC004** (emits `ALTER POLICY … USING (…) / WITH CHECK (…)` stripping the inverted-auth `auth_func() IS NULL` disjunct that leaks rows to anonymous clients — each clause independently, so a `FOR INSERT` hole yields a `WITH CHECK`-only statement; only removes a top-level `OR` disjunct so it can never broaden the policy, and abstains when no real check survives), **SEC010** (emits `DROP POLICY … ON …;` for a permissive policy whose clause is the literal `false` — it admits no rows, so dropping it changes no access; abstains when a drop would broaden what the policy governs), **SEC011** (emits `ALTER POLICY … USING (…) / WITH CHECK (…)` stripping an `OR true` debug bypass), **SEC015** (emits `ALTER FUNCTION …(…) SET search_path = …` per SECDEF overload, pinning `pg_temp` last *after* the function's own schema — naming `pg_temp` last is necessary but not sufficient on its own, since an unqualified body reference with nothing else on the path still resolves through `pg_temp` (measured); abstains when the snapshot never captured signatures (pre-v12) and on quoted-comma paths — an *empty* signature is a real zero-argument function and is fixed), **SEC017** (emits `ALTER FUNCTION …(…) NOT LEAKPROOF;` per overload; abstains when the snapshot never captured signatures (pre-v12)), **SEC019** (emits `ALTER POLICY … USING (…) / WITH CHECK (…)` adding the `missing_ok = true` second argument to one-argument `current_setting()` calls — only where the call is a direct comparison operand under an AND-only chain; other positions are left open for review), **SEC020** (emits `ALTER POLICY … WITH CHECK (…)` replacing a constant-`true` `WITH CHECK` with the policy's `USING`), **SEC030** (emits `ALTER TABLE … ALTER COLUMN … SET NOT NULL;` for a nullable tenant discriminator — backfill existing `NULL`s first, or the apply fails and rolls back the batch), **SEC031** (emits `DROP POLICY … ON …;` for a no-op restrictive `USING (true)` floor — it AND-combines to nothing, so dropping it leaves access unchanged), **SEC032** (emits `ALTER TABLE … ENABLE ROW LEVEL SECURITY;` for a table whose policies are dormant because RLS is off), **SEC044** (emits `ALTER DEFAULT PRIVILEGES [FOR ROLE …] [IN SCHEMA …] REVOKE …` withdrawing a default-privilege grant of future-table row access to a low-trust role — only the row-access privileges it flags, keyed on the grantor so the REVOKE actually clears the entry), **PERF001** (rewrites unwrapped auth calls as `(SELECT auth.uid())` in `USING` and/or `WITH CHECK`, emitting only the clause(s) it changes), **PERF003** (emits `CREATE INDEX IF NOT EXISTS pgrls_idx_<hash> ON … (…);` for a policy-predicate column with no leading-column index), **PERF004** (emits `CREATE INDEX IF NOT EXISTS pgrls_idx_<hash> ON … (<expression>);` matching a function-wrapped predicate like `lower(email)` that defeats the plain column index), **HYG003** (emits `DROP POLICY … ON …;` for a policy that exactly duplicates another on the same table), **VIEW001** (emits `ALTER VIEW … SET (security_invoker = true);`), and **VIEW002** (emits `ALTER VIEW … SET (security_barrier = true);`). Other rules need human intent (which role? which column? which policy?) and are not auto-fixed.

## Scaffold RLS — `pgrls generate`

`pgrls fix` repairs RLS you already have; `pgrls generate` writes it from
scratch for tenant tables that have none. The shoot-out showed ORMs emit
broken or absent row security — the counter is to generate correct RLS and
lint it.

For every table that carries a tenant-discriminator column (default
`tenant_id`) and has **no** policies, `generate` emits the complete
gold-standard setup — `ENABLE` + `FORCE` row security, a permissive
tenant-isolation policy, a `RESTRICTIVE` floor, and the supporting index —
**designed to lint clean**:

```bash
# Dry-run: print the SQL for every unprotected tenant_id table.
pgrls generate --database-url "$DATABASE_URL"

# Write a migration, or apply in one all-or-nothing transaction.
pgrls generate --database-url "$DATABASE_URL" --output rls.sql
pgrls generate --database-url "$DATABASE_URL" --apply

# The round-trip the feature guarantees:
pgrls generate --apply && pgrls lint   # → no findings
```

The predicate compares the column to a session value, wrapped in
`(SELECT …)` for per-statement caching and cast to the column's type:

```sql
CREATE POLICY posts_tenant_isolation ON public.posts TO authenticated
    USING (tenant_id = (SELECT current_setting('app.tenant_id', true)::uuid))
    WITH CHECK (tenant_id = (SELECT current_setting('app.tenant_id', true)::uuid));
```

`--convention postgrest` switches the source to
`current_setting('request.jwt.claim.tenant_id', true)`; `--setting-name`,
`--role` (default `authenticated`), and `--no-restrictive` tune the rest.
(The restrictive floor is what silences the SEC007 "all policies
permissive" advisory — `--no-restrictive` trades it back for that info
finding.)
For a non-conventional column, name it explicitly:
`--table public.orgs:org_id`. Tables that already have policies are
**skipped** — `generate` never overwrites hand-written policy intent, so
re-running it is a no-op.

**`--strict-binding`** — the default predicate is *silent* when nothing is
bound: `current_setting(…, true)` returns `NULL`, `tenant_id = NULL` is
`NULL`, and the row is filtered — so a query on a connection that never bound
a tenant returns **zero rows, indistinguishable from "no such row"**. An
application that forgets to bind a tenant 404s instead of failing, and a test
suite connected as the owner of a table that is not `FORCE`'d cannot see it (RLS is not enforced for the
owner). `--strict-binding` scaffolds a `pgrls_require_tenant(setting)` helper
(`pgrls_require_owner` under `--model owner`) that **raises**
`insufficient_privilege` when the setting is unset or empty, and compares
against `(SELECT pgrls_require_tenant('app.tenant_id')::<type>)` — the
wrapper is kept, so the cost stays one call per statement. Also settable as
`[generate] strict_binding = true`. The output still lints clean and verifies
`PROVEN`; **SEC055** then flags any tenant policy left on the silent form.

**Per-user ownership** — `--model owner` scaffolds the other canonical
pattern (rows owned by a user, default column `user_id`) instead of
per-tenant isolation. With `--convention supabase` it emits the idiomatic
`user_id = (SELECT auth.uid())`:

```bash
pgrls generate --model owner --convention supabase --apply
```

`--convention app-guc` / `postgrest` use
`current_setting('app.user_id', …)` /
`current_setting('request.jwt.claim.sub', …)` instead. Scope is the common
single-column case (tenant or owner); per-CRUD and membership-join shapes
stay hand-written.

## RLS posture — `pgrls report`

`pgrls lint` answers *"what's wrong?"*; `pgrls report` answers *"what's the posture overall?"* — a factual, rule-free snapshot of every table's row-level-security state, for audits and onboarding.

```bash
pgrls report --database-url "$DATABASE_URL"                                  # text table + summary
pgrls report --database-url "$DATABASE_URL" --format json                    # machine-readable
pgrls report --database-url "$DATABASE_URL" --format markdown -o posture.md  # write an audit doc
pgrls report --database-url "$DATABASE_URL" --format html -o posture.html    # standalone HTML page, print/PDF-ready
```

Each table gets a coarse status — `protected` (RLS on, FORCE'd, ≥1 permissive policy), `not-forced` (RLS on with a permissive policy, but owner bypasses), `no-policies` (RLS on but no permissive policy → default-deny; covers zero policies *and* restrictive-only tables), `covered-by-parent` (a partition child whose RLS-enabled parent covers queries routed through it — credited when that parent is among the scanned schemas), or `rls-off` — plus an aggregate summary. It runs **no rules** and emits no findings; use `pgrls lint` for that.

## Who can access what — `pgrls matrix`

Where `pgrls report` summarizes each table's posture, `pgrls matrix` answers the audit question directly: **for every role × table × command, which rows can it reach?** It covers `SELECT`, `INSERT`, `UPDATE`, `DELETE` and `TRUNCATE`, with one verdict per cell. It reads a live database and runs **no rules**.

```bash
pgrls matrix --database-url "$DATABASE_URL"                                 # role × table × command grid
pgrls matrix --database-url "$DATABASE_URL" --roles anon,authenticated     # only these role columns
pgrls matrix --database-url "$DATABASE_URL" --format json                   # full predicates, machine-readable
pgrls matrix --database-url "$DATABASE_URL" --format html -o access.html     # standalone audit page
```

Each cell is one of:

- **`OPEN`** — every row. The role holds the command's privilege and RLS lets every row through, either in its own session or through a door (below):
  - **the privilege** comes from a grant (its own, one it inherits, or `PUBLIC`'s), ownership, `pg_read_all_data` / `pg_write_all_data`, or superuser. A column-level grant counts too: cells are about rows, and the sensitive-columns section below is about columns;
  - **every row passes** when RLS is off, the role is superuser or `BYPASSRLS`, it holds the owner's privileges on a table that is not `FORCE`d, the command is `TRUNCATE` (RLS never applies to it), or an applicable permissive policy is unconditionally `true` with nothing restrictive narrowing it.
- **`COND`** — some rows. The predicate (in `--format json` / `html`) is the OR of the applicable permissive clauses AND-ed with the restrictive ones — `WITH CHECK` for `INSERT`, `USING` for the other commands. A cell reached through a door names the door in its note and carries the door's predicate.
- **`DENIED`** — no privilege on any path pgrls models, or RLS is on with no applicable permissive policy (Postgres default-denies). For `TRUNCATE`, also a table that another table's foreign key references, when the same statement can't empty that one too: the role needs `TRUNCATE` on it or on a table above it, since truncating a parent empties its partitions and inheritance children without checking their privileges.
- **`UNDECIDED`** — the rows can't be bounded:
  - a materialized view over the table (its rows were captured at `REFRESH`);
  - a foreign-key action (which rows it rewrites depends on the data);
  - a door whose body sets its own parameters, so the policies run on values it chose;
  - two different filtered paths whose union is unknown. The same filter counts as two when it depends on who runs it: it reads `current_user` or checks a privilege and one path runs inside a SECURITY DEFINER function or trigger (where `current_user` is the owner) or a rule's action; or it sub-selects another table, whose own RLS applies to whoever evaluates the sub-select — a definer view included, where `current_user` stays the caller but the sub-select reads with the view owner's rights (measured: `tenant_id IN (SELECT … FROM memberships)` read 1 row through the view and 2 through a SECURITY DEFINER function).

  Treat it as possibly reachable, never as denied.

**Membership, ownership, data roles.**

- Grants *and* policies reach a role through its `INHERIT` memberships, nested or not. A policy `TO` a group binds exactly the members that inherit the group's privileges, so a `NOINHERIT` member is bound by neither a permissive nor a restrictive policy on it (measured on PG15, 16 and 17).
- `pg_read_all_data` confers `SELECT` and `pg_write_all_data` confers `INSERT` / `UPDATE` / `DELETE`. Neither implies the other, and neither confers `TRUNCATE`.
- Earlier versions matched role names literally, and reported `DENIED` for roles that read every row through a group, through ownership, or through a policy on a group.

**Doors.** A cell counts every door below, not only the role's own session, and a door counts for the command it runs:

- A `security_invoker = false` **view** runs as its owner. That covers reads, and writes too when the view is auto-updatable (measured: 0 rows updated directly, every row through the view). An invoker view resets to the calling role and opens nothing.
- A **`SECURITY DEFINER` function** runs as its owner, statement by statement: a `SELECT` in its body opens `SELECT`, a `DELETE` opens `DELETE`, and so on. SQL and PL/pgSQL bodies are traced.
- A **`SECURITY DEFINER` trigger** runs as its owner for anyone who can fire it, which means writing its table, directly or through another door. There's no `EXECUTE` check: measured, a role holding only `INSERT` on one table emptied another through an `AFTER INSERT` trigger.
  - A statement trigger fires on any statement the privilege allows, even one that touches no row.
  - A `TRUNCATE` of a parent fires the `TRUNCATE` triggers of its partitions and inheritance children, which it empties too.
  - A `BEFORE … FOR EACH ROW` trigger on `INSERT` fires before RLS checks the new row, including for a row an `INSERT` on its partitioned parent routes there.
  - An `UPDATE` that moves a row between partitions fires `DELETE` and `INSERT` triggers there.
  - A trigger function can't be called directly, so `EXECUTE` on one opens nothing.
- A **rewrite rule** runs its actions with the relation owner's privileges and policies, for anyone who writes the relation directly or through another door (measured: `ON INSERT … DO ALSO DELETE FROM archive` emptied a table the inserting role could not touch). An `INSTEAD` rule on a view replaces the view's own auto-update. When a view's only `INSTEAD` rules for a command are conditional, Postgres refuses the write outright, so following the rule over-reports it.
- A **partitioned or inheritance parent** applies its own policies to its children's rows. A partition with no grant and `FORCE`d RLS is still read, updated, deleted and truncated through its parent, and an `INSERT` into a partitioned parent is routed into it.
- A **foreign key** with `ON DELETE` / `ON UPDATE` `CASCADE`, `SET NULL` or `SET DEFAULT` rewrites the referencing rows as their table's owner, with RLS off, for anyone who can delete or update the referenced rows. Which rows depends on the data, so these cells are `UNDECIDED`.

Doors, and the other end of a door, are found in every schema: the grid rows are the tables in `--schemas`, but a view, function, trigger, rule or parent anywhere else still counts.

A door whose SQL can't be traced is listed in its own section, with who can open it (a function's `EXECUTE` holders, or the write that fires a trigger or rule), instead of being attributed to a table. What *can* be traced is still credited, and a `DENIED` cell does not rule out an untraced door. That covers:

- dynamic SQL, and a body in another language;
- a call into a function pgrls can't see, including built-ins that run SQL named by an argument (such as `query_to_xml` or `ts_rewrite`), and an operator whose symbol `pg_catalog` does not ship (one reusing a built-in symbol is not detected — see the limits below);
- statements such as `DO`, `CALL`, or a change of `search_path` or the role.

A body that sets any other parameter (`SET`, `set_config`, or its own `SET` clause) is traced, but its filtered doors are `UNDECIDED` — except for a setting that can't change which rows a query returns, such as a timeout, a planner setting or a logging setting.

A separate **sensitive columns** section lists, per role, the columns whose names match SEC045's patterns (`email`, `ssn`, `phone`, … plus any `[lint.rules.SEC045].patterns` in your config) that the role can read, and the paths that reach each one.

**Columns.** By default the grid shows `PUBLIC`; `anon` and `authenticated` when they exist; and every other role whose reach differs from `PUBLIC`'s somewhere in the grid. That means a cell with a different verdict or predicate, a sensitive column `PUBLIC` can't read, or an untraced door `PUBLIC` can't open. A role that can do exactly what everyone can adds nothing. This compares each cell's verdict and predicate text, so it can miss a role whose extra reach comes only from a relation-reading predicate over a table outside `--schemas` (a membership table in another schema): name such a role with `--roles` to see it. `--roles` fixes the columns; `--include-system-roles` also considers `pg_*` roles.

**What it does not model:**

- **`SET ROLE` and DDL.** Each column is a session running *as that role*, changing no schema.
  - A `NOINHERIT` member that can switch to its group reads what the group's column shows.
  - An owner, or a role that inherits it, can `ALTER` the table or its policies (for example turning `FORCE` off) and then read every row.
  - A holder of the `TRIGGER` privilege can attach a trigger that sees other roles' writes.
  - A role with `CREATEROLE`, or `ADMIN OPTION` on a role, can grant itself that role.
  - A role that can create objects on a SECURITY DEFINER function's `search_path` can shadow what the body resolves (see [SEC015](docs/RULES.md#rule-sec015)).
- **Schema `USAGE`.** It isn't checked, which can over-report.
- **What a door actually returns.** A door is credited with its owner's reach of the table. A view's own `WHERE` or `WITH CHECK OPTION`, column lineage through a view, and what a function actually returns or writes are not traced. All of these over-report.
- **An ordinary (invoker) trigger fired by a door's write.** Fired by a SECURITY DEFINER function's or trigger's write, it runs as that owner; fired by a foreign-key action, as the referencing table's owner. Its body isn't captured in either case. (Through a definer view or a rule, triggers run as the writer and add nothing.) Writes through a view's `INSTEAD OF` trigger, and event triggers, aren't modelled either.
- **A SECURITY DEFINER function run in a door owner's context that the role cannot `EXECUTE` itself.** A definer function called from a column `DEFAULT`, `CHECK` or generated expression, from a policy clause, or from another door's body runs as *that* door's owner; `EXECUTE` is checked against the owner, not the role, so pgrls neither traces it nor lists it (it can show `DENIED`). At the top level the caller's own `EXECUTE` is checked, so that case is credited.
- **A rewrite-rule action reading through a `security_invoker` view when a door fires the rule.** The action is walked as the rule relation's owner, but such an invoker view runs as the writer — a SECURITY DEFINER owner when a door does the write — which is not modelled (can under-report).
- **A partition whose cloned trigger was left enabled by `ALTER TABLE ONLY … DISABLE TRIGGER` on the parent.** pgrls reads the parent trigger's `enabled` flag; a clone re-enabled only on the partition is not captured (can under-report).
- **`TRUNCATE` reachable only through a different inheritance parent.** With classic multiple inheritance, the blocker check commits to the first ancestor the role can truncate; if a foreign key blocks that path but truncating through another ancestor would not, the cell can show `DENIED`.
- **Schema, table or role names containing a literal `.`.** Cells are keyed on `schema.name`, which such a name makes ambiguous; `pgrls matrix` warns and its rows for those objects may be conflated.
- **Access outside SQL privileges.** This covers:
  - the `REPLICATION` attribute (base backups, logical decoding);
  - `pg_read_server_files`, `pg_write_server_files` and `pg_execute_server_program`;
  - a foreign table or `dblink` connection whose user mapping logs in as another role.

  A role with one of these can show `DENIED` everywhere and not appear as a column.
- **Casts and built-in-looking operators.** A cast backed by a user function (or a cast to a domain whose `CHECK` calls one) is not detected. Neither is a user-defined operator that reuses a symbol `pg_catalog` ships, on any types (measured: `public.+ (text, text)` ran for `'a'::text + 'b'::text`), or one of pgvector's distance symbols, which are treated as built-in.
- **`UPDATE`'s write-side check.** The cell shows the rows `USING` lets the update touch, not its `WITH CHECK`.
- **Tautologies.** "Unconditionally `true`" is lexical: `1 = 1` shows as `COND` with that predicate, even though every row is reachable.

## Prove tenant isolation — `pgrls verify`

`pgrls lint` *flags* a suspicious policy; `pgrls verify` **proves** — with the Z3 SMT solver — an isolation property, and hands back the offending row (not just a warning) when it fails. Five threat models via `--mode`:

- **`anon`** (default) — no **anonymous** session can read any row, *for a caller that cannot issue arbitrary SQL* — i.e. one reaching Postgres through PostgREST or an equivalent trusted tier. That premise is load-bearing and the tool cannot check it: setting a dotted placeholder GUC needs no privilege at all, so a deployment that hands an untrusted client a raw `anon` connection string is one `SET app.tenant = 'a'` (or `SET request.jwt.claim.sub = '<victim>'`) away from reading rows this mode proves isolated — measured on PG16, including under `FORCE` RLS. Two anonymous sessions are modelled and a leak under *either* is a leak: the JWT-less connection (every auth function — `auth.uid()`/`role()`/`jwt()`, a custom `--auth-function` helper — `NULL`) and the Supabase **anon-key** caller (PostgREST sets `request.jwt.claims = {"role":"anon"}`, so `auth.role()` is the string `'anon'` and `auth.jwt()` is non-null while `auth.uid()` stays `NULL`) — `USING (auth.role() = 'anon')` is a `LEAK`, `= 'authenticated'` stays `PROVEN`. A custom-GUC read (`current_setting('app.x')`) is assumed unset in every spelling — the one-arg form raises, `(name, true)` yields `NULL`, no rows either way — unless the configuration sets it, in which case the prover reads its **configured value**: `ALTER DATABASE … SET app.flag = 'off'` against `USING (current_setting('app.flag') = 'on')` still proves `PROVEN` (measured: zero rows), while `= 'off'` is a `LEAK` — row-independent, so the verdict is *every row*. Where the setting is compared against a **column** (`tenant = current_setting('app.tenant')`) the configured value becomes the witness row: `a row with tenant='shared' is anonymously readable`. Role-level settings (`ALTER ROLE … SET`) bind to the **login** role and do not propagate through membership, so the states checked are one per login path an anonymous caller can arrive on — `anon` itself, and any role that logs in and then `SET ROLE anon` (PostgREST's `authenticator`) — with a leak in any of them a leak. Where one name is set at several levels the most specific wins, exactly as Postgres resolves it (`ALTER ROLE … IN DATABASE` > `ALTER ROLE` > `ALTER DATABASE` > `ALTER ROLE ALL`). A value introspection could not attribute — a server-configuration entry it may not read, or a GUC passed on the postmaster command line, which appears in no catalog at all — is recorded as set-but-unknown and stays opaque, so it can withhold a proof but never manufacture one. A *leaking* predicate is gated on whether an anon session can actually invoke the policy — its roles reachable from `anon`/`PUBLIC` through `INHERIT` memberships, which is how Postgres applies a policy (`has_privs_of_role`) — so a leak scoped to a role anon does not inherit (e.g. `TO authenticated`) exposes nothing and is **not** a leak, while one `TO` a role anon inherits (`GRANT app_reader TO anon` with `INHERIT`, the default for an `INHERIT` role — Supabase creates `anon` `NOINHERIT`, so a plain grant there does not inherit) still is. A `NOINHERIT` member is bound by neither a permissive nor a restrictive policy `TO` that role (measured: `WITH INHERIT FALSE` read 0 rows under `TO grp USING (true)` and every row past a `TO grp` floor). A *scoped* predicate (`tenant_id = auth.uid()`) proves isolated from the predicate alone (anon's `auth.uid()` is NULL → no match), independent of the policy's `TO` roles. Before any predicate work, `anon` asks whether the anonymous session escapes the table's RLS outright — the anon role holds `BYPASSRLS`/superuser, or holds the table owner's privileges (through `INHERIT` edges) on a table without `FORCE`. Postgres then never consults the policies, so that is a **`LEAK`** whatever the predicate says, including on a table with RLS on and no policies at all (measured: a live anon login read every row of both). Honors `[lint.rules.SEC004].anon_roles`; the role graph is captured in a live-database snapshot from v26 on, so `--against` decides role-scoped policies; on a pre-v26 baseline or an offline `--sql-file` snapshot a *leaking* policy scoped to a non-`{anon, PUBLIC}` role abstains **`UNVERIFIED`** rather than guess.
- **`cross-tenant`** — a session authenticated as *one* tenant cannot read a *different* tenant's row, checked against the policy's own `<column> = <session identity>` scoping equality (the predicate [`pgrls generate`](#scaffold-rls--pgrls-generate) emits).
- **`write`** — such a session cannot **write or delete** *another* tenant's row. A write crosses tenants two ways and both are checked: the **new-row gate** (each write policy's `WITH CHECK`, or the `USING` that `FOR UPDATE`/`FOR ALL` reuses as the new-row check) decides whether a row can be *stamped* for another tenant; the **old-row gate** (`USING`, for `UPDATE`/`DELETE`/`ALL`) decides whether an *existing* row of another tenant can be taken over or destroyed. The write side is the most CVE-adjacent footgun ([CVE-2025-48757](https://nvd.nist.gov/vuln/detail/CVE-2025-48757)): a policy that scopes reads but not writes lets a tenant stamp data for another. `SEC006`/`SEC020`/`SEC028`/`SEC040` are the heuristic fallback.
- **`escalation`** — proves the static [`SEC048`](docs/RULES.md#rule-sec048) reachability finding: a low-trust role that is a member of a table's **owner** (and the owner is not superuser/`BYPASSRLS`) can `SET ROLE` to it and, on the owner's RLS-enabled-but-not-`FORCE`'d tables, bypass RLS entirely (reading *every row*). It composes the role-reachability closure with the `cross-tenant` prover — **`LEAK`** when the table's RLS provably isolates tenants (the bypass defeats it) *or* only partially leaks (the bypass also exposes the other tenants' rows the partial leak hides); **`PROVEN`** only when the table already leaks every row cross-tenant anyway (the bypass adds nothing); **`UNVERIFIED`** when the predicate is unprovable. It also proves the [`SEC042`](docs/RULES.md#rule-sec042) case — a SECURITY DEFINER function EXECUTE-able by anon, `PUBLIC`, or any role anon inherits (an `INHERIT` membership), whose body reads an RLS table and whose owner either is RLS-exempt *for that table* (superuser / `BYPASSRLS`, or simply holding the table owner's privileges while the table is not `FORCE`'d — so the mode is strictly broader than SEC042, which gates on the attribute alone) **or** is granted that table's rows by its own policies under the anonymous auth context, which the definer body then launders to a caller the policies would have denied — the function analogue of the `reachability` door, and measured silent before: anon read 0 rows directly and every row through the function while every mode exited 0 — against that table's `anon` verdict (abstains `UNVERIFIED` on anything it cannot see through: an opaque PL/pgSQL or dynamic-SQL body; one reading via a view / function; one that runs SQL through a built-in (`query_to_xml`, `ts_rewrite`) or an operator whose symbol `pg_catalog` does not ship (one reusing a built-in symbol, or a cast backed by a user function, is not detected — the same limit as `pgrls matrix`); one with a statement other than a query or DML; and one whose body or `SET` clause sets a parameter — a timeout, a planner or a logging setting excepted, since those can't change which rows a query returns. Such a function is considered whoever owns it: a body the mode cannot see may set the claims or call into another door (measured: a PL/pgSQL body that set the claims, a helper that did, and a `CALL` of a procedure that did were each cleared before). A trigger function is not a subject — Postgres refuses to call one directly — so a trigger fired by an anonymous write is left to SEC013 and `pgrls matrix`; honors `[lint.rules.SEC042].anon_roles`). So every PUBLIC-executable SECDEF function whose body it cannot read is an `UNVERIFIED` finding, whoever owns it — including helpers that touch no table at all. That is fail-closed and exits 0 on its own, but it is what turns `--mode escalation --strict` red on a schema full of PL/pgSQL SECDEF helpers; rewrite the helper in SQL so the body can be read, or drop `--strict` for this mode. Turns a noisy SEC048 warning / SEC042 error into an evidenced leak — or clears it.
- **`reachability`** — every mode above reasons about a table's own policies. This one asks whether a **view** hands the rows back anyway. A `security_invoker = false` view executes as its **owner**, so the base table's RLS is evaluated against the owner rather than the caller: an anon-`SELECT`-able view owned by an RLS-exempt role returns *every row* while `--mode anon` correctly reports the table itself `PROVEN`. The gate is narrow, and each clause was measured on live PG16. Paths are walked hop by hop (`view → view → table`): the table's RLS runs as the owner of the *nearest enclosing* `security_invoker = false` view on the path — an invoker-ON outer view over an invoker-OFF inner view still leaks; a chain of invoker-ON views only is not flagged, and an invoker-ON view *resets* the effective user to the calling role (the current user, after any `SET ROLE`) rather than inheriting the definer above it (measured). That effective owner must be exempt — superuser or `BYPASSRLS` (both bypass even under `FORCE`), or, with the table **not** `FORCE`'d, the table owner or an `INHERIT` *member* of it (Postgres's `has_privs_of_role`; a `NOINHERIT` member is not owner-equivalent and is not flagged) — **or** the table's own policies must grant that owner rows under the anonymous auth context (`TO app_role USING (true)`: a definer view owned by a service role launders those rows; the door is only as wide as that grant, so a policy admitting the owner *some* rows on a table that already leaks *some* rows to anon **and that anon can actually read** is `UNVERIFIED`, not a second `LEAK`; where anon holds no table-level `SELECT`, every row the door returns is one the direct read withholds, so it stays a `LEAK`). `BYPASSRLS` escapes the policies but not the privilege check: a non-superuser `BYPASSRLS` owner without `SELECT` on the base table gets `permission denied` (measured), so the owner must actually hold `SELECT` — through a grant, by owning the relation, or through `pg_read_all_data`, which confers it with no grant of its own — and every intermediate hop must be readable by the effective owner too. A view owned by an unrelated role with a plain `SELECT` grant reads **zero** rows and is not flagged; `FORCE` on the base table shuts the ownership case off; a hop the effective owner cannot `SELECT` is a dead path. "Anon can open the view" is wide: a table- *or column-level* `SELECT` grant to `anon`/`PUBLIC` or to any role anon inherits from (an `INHERIT` membership), but also owning the relation, holding its owner's privileges, or `pg_read_all_data` — a view whose access list is empty is still open to a role holding that (measured). The cede above is deliberately *narrower*: it needs a **table-level** `SELECT` reachable through `INHERIT` edges only, since a column-only grant leaves the other columns denied on a direct read while the view hands them over, and a `NOINHERIT` member holds nothing. When the role-membership graph is unavailable (a pre-v26 snapshot, an offline `--sql-file` source) and the answer turns on membership, the table is **`UNVERIFIED`** rather than silently passed. Composes the `anon` verdict with view reachability the way `escalation` composes `cross-tenant` with owner reachability, so a table whose direct anonymous read already returns every row **in every modelled anonymous session** cedes to `--mode anon` rather than double-reporting — but only when anon can actually `SELECT` the table: a `USING (true)` table with no grant to anon is `permission denied` on a direct read while the definer view still returns every row, so that stays a **`LEAK`**. Doors are regular views. A materialized view stores rows captured at REFRESH time under the matview OWNER's RLS context — a different mechanism, so an anon-selectable matview is `SEC054`/`VIEW003`'s finding (not SEC053 — that rule iterates foreign tables, `relkind = 'f'`), while a regular view *over* one is reported **`UNVERIFIED`** rather than passed in silence (measured: a definer view over a superuser-refreshed matview handed anon every row).

```bash
pgrls verify --database-url "$DATABASE_URL"                          # anon proof, exits 1 on any leak
pgrls verify --database-url "$DATABASE_URL" --mode cross-tenant       # prove no tenant reads another tenant's rows
pgrls verify --database-url "$DATABASE_URL" --mode write              # prove no tenant writes a row for another tenant
pgrls verify --database-url "$DATABASE_URL" --mode escalation         # prove the SEC048 reachable owner-bypass actually leaks
pgrls verify --database-url "$DATABASE_URL" --mode reachability       # prove no anon-readable view hands back the rows RLS withholds
pgrls verify --database-url "$DATABASE_URL" --format json            # per-table / per-policy verdicts + counterexamples
pgrls verify --database-url "$DATABASE_URL" --format sarif            # SARIF v2.1.0 for GitHub Code Scanning
pgrls verify --database-url "$DATABASE_URL" --strict                 # also fail on UNVERIFIED
pgrls verify --database-url "$DATABASE_URL" --auth-function auth.user_id   # add a custom auth helper
pgrls verify --database-url "$DATABASE_URL" --emit-repro ./repro      # runnable repro per leak (anon / cross-tenant / write)
pgrls verify --database-url "$DATABASE_URL" --probe                   # confirm the static proof against the live DB
pgrls verify --database-url "$DATABASE_URL" --against main.pgrls.json  # gate: fail only on leaks THIS change introduced
```

The `anon` and `cross-tenant` modes are complementary. The signature inverted-auth policy `auth.uid() IS NULL OR tenant_id = auth.uid()` is an anon **`LEAK`** (an unauthenticated client sees every row) yet cross-tenant **`PROVEN`** (an authenticated tenant only ever sees its own rows — the `IS NULL` branch is false once authenticated). Conversely a `tenant_id = auth.uid() OR is_public` policy is anon-`LEAK` *and* cross-tenant-`LEAK` (another tenant's public rows leak), while `USING (true)` is an anon-`LEAK` but cross-tenant-`UNVERIFIED` (no scoping equality to verify against — already caught by anon mode). Run both for full coverage.

Each RLS-enabled table gets one of three **honest** verdicts (the phrasing below is `anon`-mode; `cross-tenant` frames the same verdicts as "readable by a session of a different tenant"):

- **`PROVEN`** — the read is *unsatisfiable* under the threat model: Z3 proves no row is ever visible to an anonymous session (`anon`) / to a session of a different tenant (`cross-tenant`).
- **`LEAK`** — a row *is* readable, with a concrete counterexample: a characterizing row (`a row with is_public=True is anonymously readable`; cross-tenant: `a row of another tenant with is_public=True is readable`), the unconditional case (`anon`: *every row* — `USING (true)`, the `auth.uid() IS NULL OR …` inversion, now proven rather than guessed; cross-tenant: *a row of any other tenant*), or — for a *conditional* leak the prover can't pin to a single row (an opaque/session-dependent bypass) — the leak reported without a characterizing row.
- **`UNVERIFIED`** — Z3 is unavailable, the predicate is outside the decidable fragment, the solver timed out, or (`cross-tenant` / `write`) the policy has no single `<column> = <session identity>` equality on an identity/discriminator column — the prover's tenant-axis set (SEC021's default names plus the ambiguous bare spellings SEC021 itself excludes), overridable with `[lint.rules.SEC021].identity_columns` in the `--config` file, which replaces it; an **empty** list silences SEC021 but leaves the prover on its default axis, so a gate cannot pass by having its configuration emptied — to verify against. This is the point where the **verifier degrades to the linter** — no claim is made; run `pgrls lint` for the heuristic rules.

`--format sarif` emits a SARIF v2.1.0 document for GitHub Code Scanning that shares the schema and `tool.driver` block with `pgrls lint --format sarif`: each `LEAK` is an `error`-level result located at `schema.table.policy` with the witness phrase as its message; `PROVEN` tables emit nothing; `UNVERIFIED` tables are omitted unless `--strict`, where each becomes a `note`-level result — so the result-set is non-empty exactly when the run would fail the gate. The prover is one rule per `--mode` (`pgrls-anon-isolation` / `pgrls-cross-tenant-isolation` / `pgrls-write-isolation` / `pgrls-escalation-isolation` / `pgrls-view-reachability-isolation`).

`--emit-repro DIR` turns a `LEAK` into something you can run: for each leak it writes a `.sql` script and a pytest that recreate a throwaway copy of the table from the introspected column types, install the leaking policy, and demonstrate the leak per `--mode`. For `anon` it inserts the counterexample row and `SELECT`s it back as the anonymous session that exhibits the leak — the JWT-less one, or the Supabase anon-key session (`request.jwt.claim.role = 'anon'`) when only that session leaks; for `--mode cross-tenant` as a session **authenticated as tenant A** reading a row belonging to a **different tenant B** (the session identity is set via the GUC the policy reads — the JWT-claim GUC for an `auth.*` helper, or a direct `current_setting('<guc>')`). For **`--mode write`** it is the write analog: as tenant A it **INSERTs a row stamped for tenant B** and asserts the write is *admitted* — the leaking `WITH CHECK` let a cross-tenant row through (a *fixed* check rejects it with a row-level-security error). Either way the query runs as a NOSUPERUSER/NOBYPASSRLS runner, so the reproduction is sound (a *fixed* policy returns zero rows / rejects the write) — the proof, reproduced and rolled back. The pytest **passes while the leak exists and turns red once you fix the policy** — a runnable proof of the bug (invert the assertion to keep it as a green regression guard). For a characterized or unconditional leak the inserted row reliably triggers the policy; for a *conditional* leak (no pinned row) the placeholder row is best-effort and the `.sql` header flags it for a hand-edit. Re-running won't clobber a hand-edited reproduction unless `--force`. (`--mode write` covers `FOR INSERT`/`FOR ALL` leaks; a `FOR UPDATE`-only leak is not emitted as an INSERT. `--mode escalation` has no static reproduction — use `--probe`.)

### Gate on newly-introduced leaks — `--against`

Adopting `verify` on a database that already has a backlog of leaks is the same
ratchet problem `pgrls lint --baseline` solves. `--against BASE` verifies the
live schema, then compares against a committed baseline and **fails only on the
leaks this change introduced** — a table proven `PROVEN` in `BASE` (or absent
from it) that the head now proves `LEAK`. Pre-existing leaks are reported but
don't fail the gate, so a team can drop `verify` into CI without first clearing
the whole backlog.

```bash
pgrls snapshot --database-url "$MAIN_DB_URL" --output main.pgrls.json   # once, on main
git add main.pgrls.json && git commit -m "pgrls: verify baseline"
# in PR CI — build the branch DB, then:
pgrls verify --database-url "$PR_DB_URL" --against main.pgrls.json      # exit 1 ⇔ a NEW leak
```

`BASE` is a `pgrls snapshot` JSON or a DB URL. Re-capture the baseline from
the live database with this release: a pre-v26 `BASE` lacks the
role-membership graph, so a `TO authenticated → TO PUBLIC` widening can never
be *new* against it (re-emitting the old file via `pgrls snapshot --snapshot`
does not add the graph). For `--mode escalation` the floor is **v27**: an
older `BASE` did not capture SECURITY DEFINER `SET` clauses, which this
release reads as "may set a parameter" — on the baseline that would make
every anon-callable SQL function reading an RLS table `UNVERIFIED`, and a
`LEAK` this change introduces on one of them would then be graded
pre-existing. So a pre-v27 escalation baseline is instead read optimistically
(functions set no parameter) with a stderr warning; regenerate it with
`pgrls snapshot` for a precise gate. A `BASE` table that was
`UNVERIFIED` is **never** counted as newly-leaking — soundness: a leak is only
attributed to the change when the base *proved* isolation. `--against` honors
every `--mode`, but is rejected with `--probe` and with `--emit-repro` (a
baseline has no live database to probe and no schema to reproduce against —
run those separately); `--format text`/`json` also list the leaks the change
*fixed*,
and `--format sarif` carries just the new leaks for GitHub Code Scanning.

### Confirm the proof against the live database — `--probe`

The static proof reasons over the introspected policy AST. `--probe` keeps it honest by **confirming it against the live database**: for each verified table it connects as the threat-model session (anonymous, or authenticated as one tenant — for `--mode escalation` it `SET ROLE`s through the reaching low-trust member to the table owner), seeds a throwaway row, runs the real query the proof reasons about, and diffs the **observed** behavior against the Z3 verdict — all inside a transaction that is **rolled back**, so nothing is committed and the unprivileged probe role it creates — granted the policies' `TO` roles so role-scoped policies (e.g. `TO authenticated`) actually apply — except under `--mode anon`, where it holds only the configured anon roles, since an anonymous caller is not a member of `authenticated`, but kept `NOSUPERUSER`/`NOBYPASSRLS` so RLS still decides visibility — does not survive the rollback. Per table × `--mode` it reports one of:

| static verdict | observed live | result |
|---|---|---|
| `PROVEN` | no rows / write rejected | **AGREE** — the proof is backed by reality |
| `PROVEN` | rows visible / write admitted | **MISMATCH** — proof↔reality broken (a soundness break, or schema drift since the proof) |
| `LEAK` | rows visible / write admitted | **LEAK CONFIRMED** — the leak reproduced live |
| `LEAK` | no rows / write rejected | **MISMATCH** — the witness is conditional (see `--emit-repro`) or the policy changed |
| `UNVERIFIED` | rows visible / write admitted | **LEAK CONFIRMED** — a reproduced leak the static proof could not claim |
| `UNVERIFIED` | no rows / write rejected | *skipped* — the verifier made no claim and the probe saw no leak (not a proof; fails the gate under `--strict`) |

The headline is the `UNVERIFIED → LEAK CONFIRMED` row: a policy whose reads are scoped but whose `INSERT … WITH CHECK (true)` is wide open is statically `UNVERIFIED`, but `--probe --mode write` authenticates as one tenant, attempts to stamp a row for another, sees it admitted, and **upgrades the honest "no claim" into a reproduced leak**. With `--probe`, `pgrls verify` **exits 1 on any MISMATCH or LEAK CONFIRMED** (and, under `--strict`, on any table the verifier could not decide — whether the probe abstained or ran without seeing a leak); exit 2 is reserved for tool failure.

The probe is deliberately conservative — anything it cannot reproduce live it **abstains** on cleanly (per table, with a one-line reason, never a crash): the connection cannot create its probe role (it needs CREATEROLE or superuser), it has no INSERT path to seed, there is no `<column> = <session identity>` axis to pivot a cross-tenant/write probe on, the leak witness is conditional (no single characterizing row), the policy references other tables, a column has an exotic type the synthesis can't fill, or the finding is the anonymous role's own RLS exemption rather than a policy (there is no policy to pivot on). It reuses `--emit-repro`'s GUC/identity/tenant-value synthesis, with two anon-mode additions: it reads as each anonymous session the prover models — every inherited-GUC state (one per login path), and within each the JWT-less caller, then the Supabase anon-key one (`request.jwt.claim.role = 'anon'`) — and reports rows visible under any of them. The anon-key caller runs in a **second pass over all tables**, because setting its claim GUCs cannot be undone for the connection (a rolled-back placeholder GUC comes back as `''`, never NULL) and doing it inline corrupted the JWT-less reading of every table probed after it — measured: a live 2-row anonymous leak came back `no rows` / `MISMATCH` against its own correct proof. Configured GUC values are **replayed** into the probe session rather than cleared, because the probe cannot log in as `authenticator` to inherit them; a value introspection could not capture is left inherited untouched. `--probe` supports `--format text` (the static proof stacked above the AGREE/MISMATCH/LEAK CONFIRMED table), `--format json`, and `--format sarif` — a SARIF v2.1.0 document where each MISMATCH and LEAK CONFIRMED is an `error`-level result for GitHub Code Scanning (one rule per `--mode`: `pgrls-probe-anon` / `pgrls-probe-cross-tenant` / `pgrls-probe-write` / `pgrls-probe-escalation`, kept distinct from the static `verify` SARIF rules; under `--strict` an abstain becomes a `note`). `--probe --emit-repro` is rejected — run those separately. For `--mode escalation`, `--probe` confirms the SEC048 owner-bypass (the anon-callable-SECDEF SEC042 case is not yet probed — it surfaces as the static verdict only). `--probe --mode reachability` is rejected with a usage error — run the mode without `--probe`; it reports the static verdict only.

It is a *soundness* proof, not a heuristic: it never reports a leak the policy predicate does not admit (or, for the anonymous-role RLS exemption, that Postgres does not grant), and never reports `PROVEN` unless Z3 proves it or Postgres default-denies outright (RLS on with no permissive policy for the mode). The `anon`, `cross-tenant` and `write` provers reason about the policy predicate and the policy's `TO` roles; they do **not** check table `GRANT`s, so a table whose predicate admits every row but which anon holds no `SELECT` on is still reported `LEAK` — an over-report in the safe direction, and `--mode reachability` is where the grant is decided. `pgrls verify` exits non-zero on any leak — drop it in CI as a hard tenant-isolation gate, alongside `pgrls lint`. **Scope:** the `anon` prover first checks whether the anonymous role is exempt from the table's RLS at all, then it and the `cross-tenant` / `write` provers reason over each table's permissive `SELECT`/`ALL` policies; a *leaking* permissive policy on a table that also carries a `RESTRICTIVE` floor is **composed with that floor** and re-proven — a row is visible only if it satisfies *some* permissive **and** *all* restrictive policies, so the prover checks `permissive ∧ floor`: if the floor provably blocks the leaking row the table is `PROVEN`, otherwise the `LEAK` stands (a floor predicate outside the decidable fragment — or a `cross-tenant`/`write` floor the prover can't reduce to a scoping equality — stays `UNVERIFIED`). RLS-disabled tables are out of scope (that is SEC001's job). `cross-tenant` mode verifies the single `<column> = <session identity>` scoping equality `pgrls generate` emits — including when that identity is cast to the tenant column's type (`current_setting(...)::uuid`, `::bigint`/`::int`, …); a policy with no such equality on an identity/discriminator column (or two competing ones) is `UNVERIFIED` there — see `[lint.rules.SEC021].identity_columns`. `--emit-repro` works in the `anon`, `cross-tenant` and `write` modes (not `escalation` or `reachability`). Needs the `z3-solver` dependency (bundled).

## RAG retrieval path — `pgrls vector`

Audits the shape Supabase's own **RAG with Permissions** guide recommends: chunk embeddings in a `document_sections`-style table gated by RLS, retrieved through a `match_documents()` similarity-search function.

The bypass this catches is invisible to every table-level check. RLS is enabled, `FORCE`'d, the policy is correct, and a direct `SELECT` as another tenant returns **zero rows** — yet the retrieval path still leaks, because a `SECURITY DEFINER` retrieval function (which the same ecosystem recommends for RLS *performance*) runs with the owner's privileges and hands back rows the caller's RLS denies. The pieces each look fine; the **composition** is what leaks.

```bash
pgrls vector --database-url "$DATABASE_URL" \
  --probe-role authenticated --set request.jwt.claim.sub="<a-real-user-id>"
```

```
LEAK        public.match_document_sections -> public.document_sections as authenticated
            as authenticated, public.match_document_sections surfaced 1 row(s) whose key a direct
            SELECT on public.document_sections denies — the retrieval path bypasses the table's RLS
              leaked row: (id=1, content='TENANT A SECRET')
NO LEAK     public.match_sections_safe -> public.document_sections as authenticated
            as authenticated, no row public.match_sections_safe surfaced was denied by a direct
            SELECT on public.document_sections (spot-check over 1 synthesized call(s); not a proof
            of isolation for all arguments)

2 retrieval-path probe(s): 1 leak, 1 no-leak, 0 unverified
```

That contrast is the point: **both** functions are `SECURITY DEFINER` over the same table, so a static rule ([`SEC014`](docs/RULES.md#rule-sec014)) flags them identically. Executing the path separates them — the one that omits the tenant filter hands back another tenant's chunk, the one that re-applies it in its own body does not.

**How.** For each discovered (embeddings table → SECDEF retrieval function) pair it compares, as a low-trust role inside a force-rolled-back transaction, the **primary keys** the function surfaces against the keys a direct `SELECT` allows. The table's RLS *defines* what that role may see, so a surfaced-but-denied key is a proven bypass — policy-agnostic (a join-based ownership policy needs no special handling) and needing no seeded fixture data. Correlating on the primary key (not the whole row) is deliberate: a safe function that *transforms* a row it may return — `upper(content)`, a rounded score — would differ from the stored row and look "extra" under a naive full-row diff, but its key is unchanged, so it is not mistaken for a leak. `--probe-role` names the role your API retrieves under; without it every concrete `EXECUTE` grantee is probed, so a per-grantee `USING (true)` on one role can't mask a leak visible to another. `--set GUC=VALUE` stamps the session identity your policies read.

**Detector, not prover.** The synthesized call is a *sample*, so a `LEAK` is sound (a surfaced-but-denied key that is a real row of this table is unambiguously a bypass) but `NO LEAK` is a clean spot-check, **not** a proof of isolation for every argument — hence the verdict is `NO LEAK`, never `PROVEN`. It issues only `SELECT`s and always rolls back, but it *executes* the retrieval function with the definer's privileges (a `statement_timeout` bounds a runaway body; a function that commits a side effect outside the transaction is not contained). Its "all rows" baseline is trustworthy only from an RLS-exempt connection, so **run it as a superuser or `BYPASSRLS` role** — it abstains rather than risk a false clear if the connection role is itself RLS-subject. Exits 1 on any leak. `--format text|json`. It **abstains**, too, when RLS does not restrict the probe role (a vacuous baseline), when the function does not return the table's primary key, when the data cannot discriminate, or when the call can't be synthesized; and it reports any non-SQL (PL/pgSQL) SECDEF functions it could not parse, so a recall gap is never a silent clean bill. Requires [pgvector](https://github.com/pgvector/pgvector).

Runnable end-to-end reproduction (a disposable database, the leak, and the catch): [`examples/rag-retrieval-audit/`](examples/rag-retrieval-audit/).

## MCP server — `pgrls mcp`

`pgrls mcp` runs a [Model Context Protocol](https://modelcontextprotocol.io/) server (over stdio) that gives AI coding agents pgrls's analysis as tools. The headline is **offline** analysis of the DDL the agent just wrote: it passes the `CREATE TABLE` / `CREATE POLICY` SQL as `sql=` and pgrls lints it **and** runs the full Z3 isolation prover with no database.

```bash
pip install 'pgrls[mcp]'   # FastMCP is an optional extra; the plain install stays slim
```

Point an MCP client at it:

```json
{"mcpServers": {"pgrls": {"command": "pgrls", "args": ["mcp"]}}}
```

The server **never mutates a database** — it only ever issues read-only introspection SELECTs, and never auto-applies SQL. The remediation tools (`fix` / `generate`) are **emit-only**: they return SQL text for the agent to review and run through its own channel. It exposes six tools:

- **`lint`** / **`verify`** — accept exactly one schema source: `sql=` (raw DDL, analyzed offline), `database_url=` (a live connection, read-only introspection), or `snapshot=` (a `pgrls snapshot` JSON). `lint` returns the same JSON violation shape as `pgrls lint --format json`; `verify` returns the per-table verdicts and leak witnesses (modes `anon` / `cross-tenant` / `write` / `escalation`).
- **`fix`** / **`generate`** — *emit-only* remediation. `fix` returns the auto-fix SQL for the mechanically-fixable findings (the remediation counterpart of `lint`); `generate` scaffolds gold-standard RLS for unprotected multi-tenant / row-owner tables. Both take the same schema sources as `lint` and return both structured statements and a copy-pasteable `migration` — but never execute it.
- **`explain_rule`** / **`list_rules`** — the rule catalog and a single rule's reference.

On the `sql=` path the response's `warnings` list flags that catalog-only rules (those needing live catalog state — BYPASSRLS roles, `pg_default_acl`, SECURITY DEFINER owners, FKs, indexes, triggers) can't fire, so an empty findings list is **not** a clean bill of health. A `database_url` is treated as a credential: it is never logged, and connection errors are sanitized so the DSN can't leak.

## Editor diagnostics — `pgrls lsp`

`pgrls lsp` runs a [Language Server](https://microsoft.github.io/language-server-protocol/) (over stdio) that lints the `.sql` buffer you are editing **as you type**, in any LSP client — VS Code, Neovim, Helix, JetBrains. It runs the same **offline** engine as `pgrls lint --sql-file`, so findings appear inline, pinned to the exact `CREATE TABLE` / `CREATE POLICY` / `GRANT` line, with the rule id and a link to its reference — no database, no save, no round-trip. It reads your project's `pgrls.toml` (from the workspace root, or — for a lone file opened with no workspace folder — the file's own directory), so disabled rules, per-rule allowlists, severity overrides, and custom rules match your CI gate. The config is read once per session, so restart the server after editing it; an invalid `pgrls.toml` is reported once and linting falls back to defaults.

```bash
pip install 'pgrls[lsp]'   # pygls is an optional extra; the plain install stays slim
```

Point an editor's LSP client at `pgrls lsp` for the `sql` filetype. Neovim:

```lua
vim.api.nvim_create_autocmd("FileType", {
  pattern = "sql",
  callback = function(args)
    vim.lsp.start({ name = "pgrls", cmd = { "pgrls", "lsp" }, root_dir = vim.fn.getcwd() }, { bufnr = args.buf })
  end,
})
```

VS Code / Helix / any LSP client: register a language server whose command is `pgrls lsp` for SQL files (see your client's "generic LSP" docs). The server is **diagnostic-only** — it never connects to a database and never edits your files.

Because it reads a buffer, not a live catalog, the rules that need catalog state (BYPASSRLS roles, SECURITY DEFINER owners, triggers, indexes, …) are skipped exactly as in `--sql-file` — so quiet diagnostics are **not** a proof of safety. Run `pgrls lint` against a live database in CI for full coverage; the LSP is the fast in-editor first line.

## Tracking trends — `pgrls history`

Pair a daily cron with `pgrls lint --format json -o snapshots/$(date -u +%FT%H%M%SZ).json` and ask `pgrls history snapshots/` weekly — "are we gaining ground over time, or is the findings count creeping up?"

```bash
pgrls history snapshots/                       # terminal table
pgrls history snapshots/ --format markdown     # paste-ready GFM (for a weekly update / PR comment)
pgrls history snapshots/ --format html -o trend.html   # standalone trend page, print/PDF-ready
pgrls history snapshots/ --format json -o trend.json   # machine-readable for plotting
```

Each row is one snapshot plus the **NEW** / **FIXED** delta vs. the prior snapshot in chronological order (findings keyed by `(rule_id, location)` so a schema-wide finding stays PERSISTENT rather than NEW+FIXED on every comparison). A trailing summary line names the net change over the full series.

## Runtime seq-scans — `pgrls perf`

`PERF003` predicts a missing index *statically* (the RLS predicate column has no usable index, so the planner *will* seq-scan). `pgrls perf` reads what the database actually did — Postgres's cumulative table statistics (`pg_stat_user_tables`) — and ranks RLS-enabled tables by rows read sequentially, cross-referencing each against PERF003:

```bash
pgrls perf --database-url "$DATABASE_URL"                 # text table + summary
pgrls perf --database-url "$DATABASE_URL" --format json   # also markdown / html
pgrls perf --database-url "$DATABASE_URL" --fail-on-findings   # CI gate
pgrls perf --database-url "$DATABASE_URL" --statements     # blame specific queries (pg_stat_statements)
```

A table PERF003 flagged that is *also* observed seq-scanning is a **confirmed** missing-index candidate; a table PERF003 thought was indexed that still seq-scans means the index **isn't being used** — poor selectivity or stale statistics, which no amount of schema reading would catch. Tune `--min-rows` / `--min-seq-scans` / `--min-seq-pct` to set what counts as pressure (defaults are conservative — small tables seq-scan by the planner's choice).

To gate CI inside your normal lint run, persist a snapshot and point lint at it — `pgrls perf --snapshot .pgrls-perf.json` writes the raw counters, then `pgrls lint --perf .pgrls-perf.json` fires the opt-in [**PERF005**](docs/RULES.md#rule-perf005) rule for each RLS table under pressure (inert without the artifact, exactly like HYG004 with a coverage artifact).

When `pg_stat_statements` is installed, **`--statements`** turns "this table seq-scans" into "*this query* seq-scans it": it parses each recorded statement, keeps those touching a pressured table, and lists the costliest by total execution time — the precise query to fix. It degrades cleanly (a note, base report unchanged) when the extension isn't available.

Honest scope: `pg_stat_user_tables` counts *every* sequential scan on a table, not only those an RLS predicate drove, so the table-level view prioritises where to look rather than proving RLS is the cause (that's what `--statements` resolves). Partitioned tables are under-covered in this release — a partitioned parent records no direct scans (queries hit the children) and partition children don't carry the parent's RLS flag — so their scans may not surface (a false negative, never a false positive). Warm the planner's statistics first (exercise the workload, then `ANALYZE`).

## Configuration

Drop a `pgrls.toml` next to your project. See `pgrls.example.toml` in the repo for a fully commented version.

```toml
[database]
url = "$DATABASE_URL"
schemas = ["public"]

[lint]
disable = []
fail_on = "warning"

[lint.rules.SEC001]
allowlist = ["countries", "currencies"]

# `severity` re-tiers a rule's findings without disabling it —
# "error" | "warning" | "info". SEC019 is an info rule; promoting
# it to error makes a one-arg current_setting() call fail CI.
[lint.rules.SEC019]
severity = "error"
```

### Sharing config across projects — `extends`

A config can layer on top of a shared base with a top-level `extends`
(a path, or a list of paths resolved relative to the file that declares
it) — handy for a monorepo or an org-wide ruleset:

```toml
extends = "../pgrls.base.toml"   # or ["../base.toml", "./team.toml"]

[lint]
fail_on = "error"                # override one key; inherit the rest
```

Tables deep-merge key-by-key (a child can set `[lint.rules.SEC001].severity`
while inheriting the base's `allowlist`); scalars and arrays are *replaced*,
not appended (a child `disable` list wins wholesale). For a list, later
entries override earlier ones, and the declaring file overrides every base.
A cycle in the `extends` chain is an error.

### Editor support — JSON Schema

pgrls ships a JSON Schema for `pgrls.toml` ([`pgrls.schema.json`](pgrls.schema.json)) so editors autocomplete keys and flag typos and invalid values (a misspelled `[lint.rles]`, a bad `fail_on`). `pgrls init` writes a `#:schema` directive on the first line, which the [Even Better TOML](https://marketplace.visualstudio.com/items?itemName=tamasfe.even-better-toml) VS Code extension applies automatically:

```toml
#:schema https://raw.githubusercontent.com/pgrls/pgrls/main/pgrls.schema.json
```

Point any JSON-Schema-aware TOML tooling at that URL for the same validation.

## Testing your RLS — `pgrls.testing`

Install with `pip install pgrls[testing]` to pull in pytest alongside pgrls.

`pgrls.testing` is a pytest plugin that lets you write RLS tests with idiomatic pytest ergonomics. The `pgrls_db` fixture opens a connection, starts a per-test transaction, lets you switch roles + claims for each scenario, and rolls back at end so nothing persists between tests.

```python
def test_user_a_cannot_see_user_bs_invoices(pgrls_db):
    pgrls_db.seed("public.invoices", [
        {"id": "1", "tenant_id": "tenant-a", "amount": 100},
        {"id": "2", "tenant_id": "tenant-b", "amount": 200},
    ])
    with pgrls_db.as_role(
        "authenticated",
        claims={"sub": "user-a", "tenant_id": "tenant-a"},
    ):
        pgrls_db.assert_rows("SELECT id FROM invoices", count=1)
        pgrls_db.assert_invisible(
            "SELECT id FROM invoices WHERE tenant_id = 'tenant-b'"
        )
        pgrls_db.assert_rejected(
            "INSERT INTO invoices (tenant_id, amount) VALUES ('tenant-b', 999)"
        )
```

The plugin assumes the standard PostgREST conventions (`SET LOCAL ROLE` + `request.jwt.claims` GUC). Configure the connection string via one of the following — the first one defined wins:

- A `pgrls_test_database_url` fixture in your `conftest.py`. This *replaces* the plugin's default fixture (pytest fixture shadowing); when you supply one, the env-var fallback below is not consulted. Useful for per-session testcontainers.
- The `PGRLS_TEST_DATABASE_URL` environment variable.
- The `DATABASE_URL` environment variable (fallback).

Setting none of the three causes `pgrls_db` to raise `PgrlsTestConfigError`.

The cross-language contract is documented at [`docs/pgrls-test-protocol.md`](docs/pgrls-test-protocol.md). The **TypeScript port** ships as [`pgrls-test`](https://www.npmjs.com/package/pgrls-test) on npm — same Layer 1 protocol, same wire-level behaviour, idiomatic JS/TS surface (camelCase API, `pg` and `postgres.js` adapters). Source under [`ts/`](ts/) in this repo. The **Go port** ships at [`go/`](go/) (module `github.com/pgrls/pgrls/go`, versioned independently as `go/v0.7.x`): scaffold, Driver interface, pgx + lib/pq adapters, Client API, assertion helpers and conformance suite are all released through `go/v0.7.6` — see [`go/CHANGELOG.md`](go/CHANGELOG.md).

### Coverage — which policies are actually tested

When your `pgrls.testing` suite runs, the plugin records which
`(table, role, command)` tuples each test exercised and writes them to
`.pgrls-coverage.json` on session finish (gitignored; disable with
`pgrls_coverage = false` in your pytest config or `PGRLS_COVERAGE=off`).

`pgrls coverage` cross-references that artifact against the live schema
and reports which policies a test exercised and which were never
touched — the cross-tenant `DELETE` nobody wrote a test for. A policy
is *covered* when a test queried its table, under a role it targets
(or `PUBLIC`), with a matching command.

```bash
pgrls coverage                          # text report (text/json/markdown/html)
pgrls coverage --fail-under 80          # exit 1 if coverage < 80% (CI gate)
pgrls lint --coverage .pgrls-coverage.json   # enables HYG004 for uncovered policies
```

## Diff — `pgrls snapshot` + `pgrls diff`

`pgrls diff` is the semantic policy diff command. Point it at any two
Postgres sources — two snapshot files, a snapshot and a live DB, or two
live DBs — and it classifies every RLS change as SAFE, BREAKING,
REQUIRES_REVIEW, or DANGEROUS. Use it in CI to gate deployments on
actual security regressions without blocking safe migrations.

```bash
# Capture a baseline from the current branch (filter to a schema list
# to keep snapshots small and stable).
pgrls snapshot --database-url "$DATABASE_URL" --schemas app -o base.json

# After applying a migration, compare live DB to the baseline. The
# --schemas filter applies to the URL side only (the snapshot file
# already carries the filter from capture time).
pgrls diff base.json --database-url "$DATABASE_URL" --schemas app
```

The default `--fail-on dangerous` threshold means CI only fails when a
genuinely dangerous change is detected (RLS toggled off, a permissive
policy added, a predicate widened, etc.). Pass `--fail-on requires-review`
for a stricter gate, or set `[diff].fail_on` in `pgrls.toml` to make
the choice persistent (CLI flag → `[diff].fail_on` → built-in
`dangerous`). Output is git-diff-style by default (`--format text`);
use `--format json` or `--format sarif` for CI integrations that
already parse `pgrls lint` output (same `Violation` shape),
`--format markdown` for a paste-ready PR-comment table with
classification badges, or `--format html` for a standalone audit
page (embedded CSS, opens offline, prints to PDF) — same shape
`pgrls report --format html` and `pgrls history --format html` use.

Pass `--explain` to append a one-paragraph rationale beneath each
classified Change in the text output — why a dropped PERMISSIVE
policy is BREAKING rather than DANGEROUS, why a column drop is
REQUIRES_REVIEW, etc. Text format only; JSON / SARIF already carry
the classification tag. (The leaking-row counterexample described
below is separate: it is emitted unconditionally on the Z3 path, not
gated behind `--explain`.)

| Change category                        | Default classification |
|----------------------------------------|------------------------|
| RLS toggled off                        | DANGEROUS              |
| Table dropped                          | BREAKING               |
| Permissive policy added                | DANGEROUS              |
| Restrictive policy dropped             | DANGEROUS              |
| USING predicate widened (OR added)     | DANGEROUS              |
| USING predicate tightened (AND added)  | SAFE                   |
| Roles widened (PUBLIC or new role)     | DANGEROUS              |
| Column dropped (still referenced)      | REQUIRES_REVIEW        |
| GRANT added on non-RLS table to PUBLIC | DANGEROUS              |

A DANGEROUS *semantic-loosening* verdict — the new predicate admits a
strict superset of the old one's rows — also prints a concrete
**leaking row**: a row the new policy admits but the old one rejected
(e.g. `example leaking row: {tenant_id=2}`), in both text and JSON
output. The row is only emitted when its column values are a sound,
self-sufficient witness; when the leak depends on a NULL test or an
opaque value (a function call, a `current_setting(...)` GUC, `COALESCE`,
or `CASE`), pgrls prints the label-only DANGEROUS verdict rather than a
row that would not actually leak.

See [AGENTS.md](AGENTS.md) for the full classification table and AST
pattern documentation.

## Rules

`pgrls lint` ships these rules:

| ID | Severity | Catches |
|---|---|---|
| [SEC001](docs/RULES.md#rule-sec001) | error | Tables in scanned schemas with RLS disabled and no policies (a table with policies but RLS off is SEC032) |
| [SEC002](docs/RULES.md#rule-sec002) | error | Tables with RLS enabled but FORCE ROW LEVEL SECURITY off |
| [SEC003](docs/RULES.md#rule-sec003) | error | Permissive policies granted to PUBLIC |
| [SEC004](docs/RULES.md#rule-sec004) | error | Inverted auth check (Lovable CVE pattern) in USING or WITH CHECK |
| [SEC005](docs/RULES.md#rule-sec005) | warning | Policy expression has no own-column reference |
| [SEC006](docs/RULES.md#rule-sec006) | error | INSERT/UPDATE/ALL policies with no WITH CHECK |
| [SEC007](docs/RULES.md#rule-sec007) | info | All policies on a table are permissive (no RESTRICTIVE floor) |
| [SEC008](docs/RULES.md#rule-sec008) | warning | Permissive policy USING clause is constant `true` (admits every row) |
| [SEC009](docs/RULES.md#rule-sec009) | warning | RLS enabled but no policies defined (silent deny-all) |
| [SEC010](docs/RULES.md#rule-sec010) | warning | Policy `USING`/`WITH CHECK` clause is constant `false` (deny-all anti-pattern) |
| [SEC011](docs/RULES.md#rule-sec011) | warning | Policy expression has an `OR true` branch (debug bypass left in) |
| [SEC012](docs/RULES.md#rule-sec012) | warning | Table has only RESTRICTIVE policies (silent deny-all — needs at least one PERMISSIVE) |
| [SEC013](docs/RULES.md#rule-sec013) | warning | Trigger on RLS-protected table can bypass policies (a `SECURITY DEFINER` trigger function runs as its owner) |
| [SEC014](docs/RULES.md#rule-sec014) | warning | SECURITY DEFINER function bypasses caller's RLS (audit every SECDEF function) |
| [SEC015](docs/RULES.md#rule-sec015) | warning | SECURITY DEFINER function exposed to `pg_temp` search-path shadowing |
| [SEC016](docs/RULES.md#rule-sec016) | warning | Role with the `BYPASSRLS` attribute bypasses every RLS policy |
| [SEC017](docs/RULES.md#rule-sec017) | warning | Function with the `LEAKPROOF` attribute is evaluated below the RLS barrier |
| [SEC018](docs/RULES.md#rule-sec018) | warning | Policy compares a column against `current_user` / `session_user` (no isolation under a shared pool role) |
| [SEC019](docs/RULES.md#rule-sec019) | info | Policy calls `current_setting()` without the `missing_ok` argument (raises on an unset GUC) |
| [SEC020](docs/RULES.md#rule-sec020) | warning | Policy `WITH CHECK` is constant `true` while `USING` restricts (writes accept rows reads never would) |
| [SEC021](docs/RULES.md#rule-sec021) | info | Policy compares an identity column against a hardcoded literal (e.g. `tenant_id = 1`) |
| [SEC022](docs/RULES.md#rule-sec022) | info | RLS-enabled table whose policies are all `FOR SELECT` — no write-side policy, so INSERT/UPDATE/DELETE are denied |
| [SEC023](docs/RULES.md#rule-sec023) | warning | Policy granted to a role carrying `BYPASSRLS` — the role skips the policy entirely, so its `TO` clause is inert |
| [SEC024](docs/RULES.md#rule-sec024) | info | Policy calls `current_setting()` with an unqualified parameter name (a dropped prefix the application cannot `SET`) |
| [SEC025](docs/RULES.md#rule-sec025) | warning | Policy predicate references another table whose RLS is disabled — the cross-table read is only as strong as the referenced table's isolation |
| [SEC026](docs/RULES.md#rule-sec026) | warning | Policy predicate uses `LIKE` / `ILIKE` / `SIMILAR TO` / POSIX regex against an auth-context value (a wildcard-shape GUC matches every row) |
| [SEC027](docs/RULES.md#rule-sec027) | info | RLS table has an owner / user-identity column that no policy scopes by — rows may be visible across users within the same tenant |
| [SEC028](docs/RULES.md#rule-sec028) | warning | Permissive write policy (INSERT/UPDATE/ALL) whose `WITH CHECK` is constant `true` — accepts every write; the `TO` clause gates who, not what |
| [SEC029](docs/RULES.md#rule-sec029) | warning | Role can `SET ROLE` to a `BYPASSRLS` role through membership — escalation path that silently disables every policy (BYPASSRLS is not inherited, but reachable) |
| [SEC030](docs/RULES.md#rule-sec030) | info | Policy scopes by a nullable discriminator column (`tenant_id = current_setting(…)` where the column allows NULL) — NULL rows escape scoping today and leak the moment a NULL-tolerant predicate appears |
| [SEC031](docs/RULES.md#rule-sec031) | warning | RESTRICTIVE policy whose `USING` is constant `true` — AND-combines to a no-op, so it looks like a security floor but enforces none |
| [SEC032](docs/RULES.md#rule-sec032) | error | Table has policies but RLS is not enabled — the policies are dormant (Postgres ignores them) and the table is wide open despite looking RLS-managed |
| [SEC033](docs/RULES.md#rule-sec033) | error | Policy scopes by a user-modifiable JWT claim (`user_metadata` / `raw_user_meta_data`) — the authenticated user can rewrite the value via the auth API, bypassing the check; use `app_metadata` (service-role-only) instead |
| [SEC034](docs/RULES.md#rule-sec034) | warning | Policy gates rows on `auth.email()` — silent denial-of-service-to-self when the user changes email, when SQL `=` is case-sensitive but emails aren't, or when plus-addressing means `x+y@host` ≠ `x@host`; scope by `auth.uid()` instead |
| [SEC035](docs/RULES.md#rule-sec035) | warning | UNIQUE constraint not scoped to the tenant discriminator — a global `UNIQUE(email)` instead of `UNIQUE(tenant_id, email)` leaks cross-tenant existence via duplicate-key errors (the PRIMARY KEY and all-uuid uniques are excluded) |
| [SEC036](docs/RULES.md#rule-sec036) | error | Policy `EXISTS (SELECT FROM auth.users WHERE …)` sub-select with no caller binding — checks "is there any admin at all" instead of "is THIS user an admin", so every authenticated user passes once any matching row exists |
| [SEC037](docs/RULES.md#rule-sec037) | warning | Policy compares `auth.role()` to a value outside the known role set (`anon` / `authenticated` / `service_role`) — comparison never matches and silently denies every row, masking the broken policy |
| [SEC038](docs/RULES.md#rule-sec038) | error | Semantic anonymous-read leak — Z3 proves the USING predicate is unconditionally TRUE for an unauthenticated session (all auth functions NULL), catching inverted-auth variants SEC004's syntactic match misses. |
| [SEC039](docs/RULES.md#rule-sec039) | error | Permissive **write** policy (INSERT/UPDATE/DELETE/ALL) grants the unauthenticated `anon` role — anonymous PostgREST/Supabase clients can modify rows, gated by the policy's clause **and** the table grant (measured: without `GRANT INSERT` the write is `permission denied`; Supabase grants it by default, so the policy is the deciding gate in practice); the write-side analog of SEC003 for the named `anon` role (SELECT-only `anon` policies, the public-read pattern, are not flagged) |
| [SEC040](docs/RULES.md#rule-sec040) | warning | Permissive `FOR ALL` policy whose `USING` scopes by a tenant/owner key but whose explicit `WITH CHECK` binds no identity column at all — a `FOR ALL` insert is governed by WITH CHECK alone, so a caller can INSERT a row stamped with another tenant's id (cross-tenant write); the asymmetry SEC006 (absent WITH CHECK) and SEC028/SEC020 (constant-true) miss. The legitimate "read team, write own" shape (WITH CHECK binds a *different* identity) is not flagged |
| [SEC041](docs/RULES.md#rule-sec041) | warning | Declarative partition child has RLS disabled while its partitioned parent enforces it, and is granted directly to a non-owner role — Postgres doesn't inherit RLS (or grants) to partitions, so a query naming the granted child directly (e.g. PostgREST `GET /child`) bypasses the parent's policies; the complement of SEC001, which deliberately skips this case |
| [SEC042](docs/RULES.md#rule-sec042) | error | `SECURITY DEFINER` function whose owner bypasses RLS (superuser/`BYPASSRLS`) is `EXECUTE`-able by `anon`/`PUBLIC` — an unauthenticated PostgREST `POST /rpc/fn` caller runs owner-privileged, RLS-exempt code; function `EXECUTE` defaults to `PUBLIC`, so this fires even with no explicit `GRANT`. Sharpens SEC014 along the anon-exposure axis |
| [SEC043](docs/RULES.md#rule-sec043) | warning | Classic-`INHERITS` child has RLS disabled while an inheritance ancestor enforces it, and is granted directly to a non-owner role — Postgres doesn't inherit RLS (or grants) to children, so a query naming the granted child directly (e.g. PostgREST `GET /child`) bypasses the parent's policies; the classic-inheritance analogue of SEC041. SEC001 also fires on the child (it doesn't walk classic inheritance) — same fix |
| [SEC044](docs/RULES.md#rule-sec044) | warning | `ALTER DEFAULT PRIVILEGES … GRANT <row-access> ON TABLES TO PUBLIC` auto-grants every *future* table to `PUBLIC`, so a new table whose author forgets `ENABLE ROW LEVEL SECURITY` is silently exposed (incl. to `anon`). A standing least-privilege / defense-in-depth posture, flagged on the `pg_default_acl` entry itself; grantee set is configurable (`anon`/`authenticated` excluded by default — the RLS-gated Supabase pattern). Complements SEC003/SEC001 |
| [SEC045](docs/RULES.md#rule-sec045) | warning | A **column-level** `GRANT` of a content privilege (SELECT/INSERT/UPDATE) on a PII / secret-named column (`email`, `ssn`, `credit_card`, `password`, `api_key`, …) to a low-trust role (`PUBLIC`/`anon`) over-shares the most sensitive field to the least-trusted role. Column grants are rare and deliberate, so it's high-signal; a least-privilege posture finding distinct from SEC003 (a PUBLIC *policy*). PII patterns + grantees configurable; allowlist a deliberate public column |
| [SEC046](docs/RULES.md#rule-sec046) | error | A policy calls a **user-defined `IMMUTABLE`** function whose body reads session/identity state (`current_setting`, `auth.*`, `current_user`) or a table. `IMMUTABLE` lets Postgres constant-fold the call into a reused/cached plan (pooling, PostgREST, prepared statements, PL/pgSQL), so the value frozen for one user is served to the next — a cross-user wrong-row leak. The fix is to declare the function `STABLE`. Inline `current_setting` (a STABLE built-in) and pure IMMUTABLE functions are not flagged |
| [SEC047](docs/RULES.md#rule-sec047) | warning | A **foreign key whose parent (referenced) table has RLS enabled** is a cross-tenant *existence* covert channel when a low-trust role can write the child: FK validation bypasses RLS, so referencing a guessed parent key succeeds (row exists) or errors (it doesn't), even though RLS hides that row from the role's `SELECT` — the FK-validation analog of SEC035's UNIQUE-index oracle. Fires only when the parent has RLS on **and** the child is low-trust-writable (INSERT/UPDATE grant + RLS-off or an anon write policy). A posture over-approximation (does not prove parent row-visibility); allowlist by child table or constraint name |
| [SEC048](docs/RULES.md#rule-sec048) | warning | A **low-trust role that is a member of a table owner** which is **not** superuser/`BYPASSRLS` bypasses RLS on that owner's **enabled-but-not-`FORCE`'d** tables: owner privileges (unlike the `BYPASSRLS` attribute SEC029 covers) ARE inherited through membership, automatically with `INHERIT` or after a `SET ROLE` with `NOINHERIT`. The table-owner analog of SEC029; reports per reachable member role, and co-fires with SEC002 (which reports the table) on the same missing-`FORCE` misconfig. No auto-fix — `FORCE` the owner's tables, revoke the membership, or allowlist the member / owner |
| [SEC049](docs/RULES.md#rule-sec049) | warning | A **PostgREST-exposed table readable by a low-trust role**: a table in an API-exposed schema (default `public`), granted SELECT (table- or column-level) to `anon` / `authenticated` / `PUBLIC`, whose SELECT row-access is unrestricted (RLS off, or RLS on with a permissive `USING (true)` and nothing restrictive to filter), is directly readable at `GET /rest/v1/<table>`. The *conjunction* that SEC001 / SEC003 / SEC008 each report a precondition of — fires once, naming the HTTP-reachable consequence. Conservative: a real policy or any unproven restrictive policy means "protected" → silent (so the normal RLS-gated Supabase grant is not flagged). No auto-fix — enable RLS, restrict the grant, or allowlist intentionally-public tables |
| [SEC050](docs/RULES.md#rule-sec050) | warning | A **Supabase Storage policy not scoped to a bucket**: in `storage.objects` (where all object access is RLS-enforced), a permissive policy that authorizes by owner or path but whose row-reach clause references no `bucket_id` condition applies to **every** bucket — a caller authorized for one bucket can reach objects in another (Supabase's own guidance: "your RLS policy must explicitly specify the `bucket_id` condition"). Literal `USING (true)` is ceded to SEC008; a restrictive `bucket_id` floor keeps it silent; only `storage.objects` is examined (no `storage` schema → no findings). No auto-fix — add a `bucket_id = '<bucket>'` predicate or allowlist the policy. Also emits one per-table `info` note on an offline input that has Storage policies but no `CREATE TABLE` for `storage.objects`, where no verdict is possible |
| [SEC051](docs/RULES.md#rule-sec051) | warning | A **Realtime-published table with RLS disabled**: a table in the Supabase `supabase_realtime` publication (membership resolved via `pg_publication_tables`, so `FOR ALL TABLES` / schema publications are expanded) with row level security off has every row change broadcast to all subscribed clients without policy filtering — one tenant's changes reach every subscriber. The Realtime-channel analog of SEC001, sharpened to the broadcast consequence. RLS-enabled members are filtered by Realtime per-policy and not flagged; a non-Realtime publication is not flagged (configurable). No auto-fix — enable RLS, remove from the publication, or allowlist the table |
| [SEC052](docs/RULES.md#rule-sec052) | error | An **auth user table exposed through an API-schema view**: a view (or matview) in an exposed schema (default `public`) that reads `auth.users` as a FROM-clause source **without** scoping to the calling user runs with the view owner's privileges, so a REST caller reads every user's email / metadata at `GET /rest/v1/<view>` (Supabase advisor `0002_auth_users_exposed`). Reuses SEC036's caller-binding analysis: a view filtered to `id = auth.uid()` is a legitimate "my account" view and is **not** flagged; a regular view with `security_invoker` is safe (runs as the caller). Conservative — unparseable bodies and transitive re-exposers abstain. Not VIEW001 (which needs the target to have RLS; `auth.users` is grant-protected). No auto-fix — set `security_invoker`, restrict columns, scope to `auth.uid()`, or move out of the exposed schema |
| [SEC053](docs/RULES.md#rule-sec053) | error | A **foreign table exposed in an API schema**: a foreign table (`pg_class.relkind = 'f'`, e.g. a `postgres_fdw` / `file_fdw` table or a Supabase *Wrapper* over Stripe / S3) in an exposed schema (default `public`) granted SELECT to `anon` / `authenticated` / `PUBLIC` is directly readable at `GET /rest/v1/<ft>`. A foreign table **cannot carry RLS** (Postgres rejects `ENABLE ROW LEVEL SECURITY` on it), so every remote row is returned unfiltered — the foreign-table sibling of SEC049 (Supabase advisor `0017_foreign_table_in_api`). Conservative: only a direct table-level SELECT grant to a low-trust role counts. No auto-fix — REVOKE the grant, move the table out of the exposed schema, or front it with a **definer-rights** view (the default) that filters rows — a `security_invoker` view runs as the caller and so needs the very grant the remedy revokes |
| [SEC054](docs/RULES.md#rule-sec054) | error | A **materialized view exposed in an API schema**: a matview in an exposed schema (default `public`) granted SELECT to `anon` / `authenticated` / `PUBLIC` whose body reads an RLS-enabled table serves every captured row at `GET /rest/v1/<matview>`. A matview stores rows physically, so RLS on the source is never applied to a read — and there is no `security_invoker` hook (Supabase advisor `0016_materialized_view_in_api`). The confirmed-exposure sharpening of VIEW003 (which warns on *any* matview over an RLS table): the two co-fire on an anon-exposed matview, with SEC054 flagging the API-reachable subset at error severity. A matview over only non-RLS tables is not flagged. No auto-fix — REVOKE the grant, move it out of the exposed schema, give it a per-tenant owner (the body runs as the owner at REFRESH, not as whoever issues it), or replicate per-tenant |
| [SEC055](docs/RULES.md#rule-sec055) | warning | A tenant policy still comparing against **`current_setting(…, true)`** in a schema whose other policies already use a **raising binding helper**. The silent form returns NULL when nothing is bound, so `tenant_id = NULL` filters every row and a connection that never bound a tenant gets an **empty result indistinguishable from "not found"** — the application 404s instead of failing, and a test suite connected as the owner cannot tell the two apart. Fires only where the schema *already* adopted the raising form somewhere, so it reports half-finished conversions rather than flooding projects that never opted in; the trigger is the schema itself, not `pgrls.toml`, so it still reports when CI lints a database without the repo's config beside it. `pgrls generate --strict-binding` scaffolds the helper. Allowlist a platform table read before a tenant is chosen |
| [PERF001](docs/RULES.md#rule-perf001) | warning | Auth function called per-row in policy USING/WITH CHECK (unwrapped) |
| [PERF002](docs/RULES.md#rule-perf002) | warning | Policy expression uses a VOLATILE function (`random()`, `clock_timestamp()`, …) |
| [PERF003](docs/RULES.md#rule-perf003) | warning | Policy predicate column without a leading-column index (sequential scan on every query) |
| [PERF004](docs/RULES.md#rule-perf004) | warning | Policy predicate wraps an indexed column in a function (e.g. `lower(email)`) so the plain index can't serve it — Postgres seq-scans; needs an expression index |
| [PERF005](docs/RULES.md#rule-perf005) | info | RLS table *observed* to sequentially scan in production (opt-in; fed by `pgrls perf --snapshot` via `pgrls lint --perf`) |
| [HYG001](docs/RULES.md#rule-hyg001) | error | Policies referencing columns that don't exist on the table |
| [HYG002](docs/RULES.md#rule-hyg002) | warning | Policy named like a placeholder (`todo`, `fixme`, `tmp`, …) |
| [HYG003](docs/RULES.md#rule-hyg003) | info | Policy is an exact duplicate of another policy on the same table |
| [HYG004](docs/RULES.md#rule-hyg004) | info | Policy has no behavioral test exercising it (needs `pgrls lint --coverage`) |
| [VIEW001](docs/RULES.md#rule-view001) | error | View over RLS-protected table without `WITH (security_invoker = true)` |
| [VIEW002](docs/RULES.md#rule-view002) | warning | View over RLS-protected table without `WITH (security_barrier = true)` |
| [VIEW003](docs/RULES.md#rule-view003) | warning | Materialized view over RLS-protected table (RLS not honored at query time) |
| [VIEW004](docs/RULES.md#rule-view004) | warning | View calls SECURITY DEFINER function that reads an RLS-protected table |

Run `pgrls explain <RULE>` (for example `pgrls explain SEC023`) to print any
rule's full rationale — what it flags, why it matters, how detection works,
and how to allowlist a false positive — on the command line. Bare
`pgrls explain` (no argument) lists the catalog: one line per rule with its
severity and title. Pass `--format markdown` to either form (`pgrls explain
SEC023 --format markdown`, `pgrls explain --format markdown`) for a
paste-ready Markdown document — an `## SEC023 — …` heading + the body, or a
Markdown table of the catalog. `--format json` emits machine-readable rule
metadata (id, severity, title, a `fixable` flag, and — for a single rule —
the full reference body) for IDE / tooling integrations. All forms read only
pgrls's built-in rule catalog, so they need no database connection.

For canonical SQL fixes per rule, see [docs/RULES.md](docs/RULES.md). For per-rule
configuration options (allowlists, etc.), see each rule's section in [docs/RULES.md](docs/RULES.md); `pgrls.example.toml` shows the common knobs.

### Precision & false positives

A linter is only useful if you can trust it not to cry wolf. pgrls ships an
**adjudicated precision corpus** — small, self-contained schemas, each
labeled with exactly which rules should fire, including deliberately
adversarial *near-misses* (a `coalesce()`-wrapped auth check, an `IS NULL`
buried in a subquery, a predicate hidden in a `SubLink`) that look like
violations but are safe. The full rule set runs over every case against a
real Postgres; the result is published in
[`docs/PRECISION.md`](docs/PRECISION.md), and a CI job re-measures on every
push and fails if any rule fires where it shouldn't. Regenerate with
`python -m corpus.measure`; see [`corpus/README.md`](corpus/README.md) to
add cases.

For per-release changes, see [CHANGELOG.md](CHANGELOG.md).

## CI integration

pgrls is designed to live in your CI alongside any other linter. By
default it connects to a Postgres database with your schema applied,
introspects, and exits non-zero if any rule at or above
`fail_on` (default `warning`) fires. No database to point at? `pgrls lint
--migrations` builds an ephemeral one from your migration files (see
[Lint without a live database](#lint-without-a-live-database)).

### pre-commit

Two [pre-commit](https://pre-commit.com) hooks are published from this repo. `pgrls-lint-sql` analyzes raw DDL with **no database** — it runs at commit time with no Docker and no connection, so it's the usual choice — while `pgrls-lint` targets a live database (scope it to `pre-push`). Both reason over your *whole* schema, so point them at the source with `args` rather than relying on changed-file paths.

```yaml
# .pre-commit-config.yaml
repos:
  - repo: https://github.com/pgrls/pgrls
    rev: v0.56.0
    hooks:
      # Offline — lints raw DDL, no database. Repeat --sql-file for several
      # files (declare tables before the policies that reference them), or
      # use --snapshot for a committed `pgrls snapshot`.
      - id: pgrls-lint-sql
        args: ["--sql-file", "schema.sql", "--fail-on", "error"]
      # Live database — needs a reachable DB, so most teams use pre-push.
      # $DATABASE_URL is read from the environment (don't pass it in args —
      # pre-commit does not expand shell variables there).
      - id: pgrls-lint
        stages: [pre-push]
        args: ["--config", "pgrls.toml"]
```

### GitHub Actions

The quickest path is the published Action ([`pgrls/pgrls-action`](https://github.com/marketplace/actions/pgrls-postgres-rls-linter) on the GitHub Marketplace) — it installs `pgrls` from PyPI and runs `pgrls lint` against a reachable database:

```yaml
- uses: pgrls/pgrls-action@v1
  with:
    database-url: ${{ secrets.PGRLS_DATABASE_URL }}
    schemas: public
    fail-on: error
```

It exposes every flag `pgrls lint` does (`--format`, `--rule`, `--exclude-rule`, `--baseline`, `--output`, `--min-severity`, …); see the [Marketplace listing](https://github.com/marketplace/actions/pgrls-postgres-rls-linter) for the full input table.

Or run `pgrls` directly — useful when you want to spin up an ephemeral Postgres as a job service:

```yaml
# .github/workflows/pgrls.yml
name: pgrls
on: [push, pull_request]
jobs:
  lint:
    runs-on: ubuntu-latest
    services:
      postgres:
        image: postgres:16-alpine
        env:
          POSTGRES_USER: ci
          POSTGRES_PASSWORD: ci
          POSTGRES_DB: ci
        ports: ["5432:5432"]
        options: >-
          --health-cmd pg_isready
          --health-interval 10s
          --health-retries 5
    env:
      DATABASE_URL: postgres://ci:ci@localhost:5432/ci
    steps:
      - uses: actions/checkout@v4
      - uses: actions/setup-python@v5
        with:
          python-version: "3.11"
      - run: pip install pgrls
      - name: Apply schema
        run: psql "$DATABASE_URL" -v ON_ERROR_STOP=1 -f migrations/all.sql
      - name: Lint RLS
        run: pgrls lint --format sarif > pgrls.sarif
      - name: Upload SARIF for code scanning
        uses: github/codeql-action/upload-sarif@v3
        if: always()
        with:
          sarif_file: pgrls.sarif
```

The SARIF upload puts findings inline on the PR as code-scanning
alerts — no extra dashboard plumbing. Use `--format json` instead
of `--format sarif` if you want to pipe to `jq`, build your own
dashboard, or keep the report as a build artifact.

## Roadmap

- **More lint rules.** Continued expansion of the SEC / PERF / HYG / VIEW catalog. Polished error messages.
- ~~**TypeScript port of `pgrls.testing`**~~ — shipped as the [`pgrls-test`](https://www.npmjs.com/package/pgrls-test) npm package, versioned independently of the Python package (tagged `ts-v0.6.3`). Source: [`ts/`](ts/).
- **Go port** of `pgrls.testing` following the same Layer 1 protocol — versioned independently of the Python package as the `go/v0.7.x` sequence (Go module tag prefix `go/`, distinct from the Python package's tags). All seven steps (scaffold, Driver interface, pgx + lib/pq adapters, Client API, assertion helpers, conformance suite, CI + release) shipped through `go/v0.7.6`; see [`go/CHANGELOG.md`](go/CHANGELOG.md).
- ~~**SAT-based predicate implication checking.**~~ Z3-driven semantic predicate analysis landed in v0.4.x.
- ~~**Migration-as-input.**~~ `pgrls diff --apply migration.sql` shipped in v0.5.0; baseline cache + extension auto-detect in v0.5.1–v0.5.2.

## License

MIT — see `LICENSE`.
