pgrls 0.5.49

Static analyzer for Postgres Row-Level Security — 36 lint rules across security, performance, and hygiene; 10 mechanically auto-fixable; semantic policy-diff command for CI gating; pytest plugin for RLS isolation tests.

pgrls

▶ 23-second demo · Rule reference · CHANGELOG · PyPI

Static analyzer for Postgres Row-Level Security. Finds the auth-bug shapes and predicate logic flaws eyeball-review misses; 10 of 36 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).

Beta — actively maintained. 36 lint rules, 10 mechanically auto-fixable, semantic policy-diff command, pytest plugin for RLS isolation tests. Tested on PostgreSQL 15, 16, 17. Stable JSON / SARIF schema for CI integrations. The CHANGELOG records every release; current build is shown by the PyPI badge above.

• Lint & fix — pgrls lint checks a live database against all thirty-six rules and reports findings as text, JSON, SARIF, or Markdown for CI. pgrls fix auto-remediates the mechanically-fixable rules (SEC001, SEC002, SEC006, SEC019, SEC020, PERF001, PERF003, 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.
• 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. Optional Z3-based predicate analysis (pip install pgrls[diff-z3]), 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 for fast re-runs.
• TypeScript port — pgrls-test on npm implements the same RLS-testing contract for JS/TS — both pg and postgres.js driver adapters, vitest-friendly. See ts/ in this repo.

Install

pip install pgrls

Requires Python 3.11+ and Postgres 15+. pgrls is tested in CI against PostgreSQL 15–17 (see .github/workflows/test.yml for the matrix).

Real-world bugs pgrls catches

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

CREATE POLICY tenant_read ON public.documents
    FOR SELECT TO authenticated
    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) 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 contains a top-level
         `auth_func() IS NULL` disjunct in its USING clause. For anonymous
         connections that disjunct evaluates to 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.

Browse the full rule catalogue in AGENTS.md for the other 35 — missing WITH CHECK, BYPASSRLS roles, per-row auth-function evaluation, search-path attacks, view-mediated RLS bypasses, and more.

Usage

Point pgrls at any Postgres database:

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

Or pass the URL directly:

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

Limit the scan to specific schemas:

pgrls lint --schemas public,tenant

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

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 # PR comments / rendered CI reports

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:

pgrls lint --rule SEC001 --rule SEC003

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 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):

{
  "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, "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.

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.

# 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.

# 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;), SEC006 (emits ALTER POLICY … WITH CHECK (…) mirroring the policy's USING), SEC019 (emits ALTER POLICY … USING (…) / WITH CHECK (…) adding the missing_ok = true second argument to one-argument current_setting() calls), SEC020 (emits ALTER POLICY … WITH CHECK (…) replacing a constant-true WITH CHECK with the policy's USING), PERF001 (rewrites unwrapped auth calls as (SELECT auth.uid()) and emits ALTER POLICY … USING (…);), PERF003 (emits CREATE INDEX ON … (…); for a policy-predicate column with no leading-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.

Configuration

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

[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"

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.

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. The TypeScript port ships as 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/ in this repo. The Go port is shipping in stages at go/ (module github.com/pgrls/pgrls/go); step 1 (scaffold + ProtocolVersion constant + error types) shipped in v0.7.0, with steps 2–7 (Driver interface, pgx + lib/pq adapters, Client API, assertion helpers, conformance suite, release tag) tracked in go/CHANGELOG.md.

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.

# 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 — the same Violation shape is reused.

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.

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

See AGENTS.md for the full classification table and AST pattern documentation.

Rules

pgrls lint ships these rules:

ID	Severity	Catches
SEC001	error	Tables in scanned schemas with RLS disabled
SEC002	error	Tables with RLS enabled but FORCE ROW LEVEL SECURITY off
SEC003	error	Permissive policies granted to PUBLIC
SEC004	error	Inverted auth check (Lovable CVE pattern) in USING
SEC005	warning	Policy expression has no own-column reference
SEC006	error	INSERT/UPDATE/ALL policies with no WITH CHECK
SEC007	info	All policies on a table are permissive (no RESTRICTIVE floor)
SEC008	warning	Policy USING clause is constant true
SEC009	warning	RLS enabled but no policies defined (silent deny-all)
SEC010	warning	Policy USING/WITH CHECK clause is constant false (deny-all anti-pattern)
SEC011	warning	Policy expression has an OR true branch (debug bypass left in)
SEC012	warning	Table has only RESTRICTIVE policies (silent deny-all — needs at least one PERMISSIVE)
SEC013	warning	Trigger on RLS-protected table can bypass policies (triggers fire as table owner)
SEC014	warning	SECURITY DEFINER function bypasses caller's RLS (audit every SECDEF function)
SEC015	warning	SECURITY DEFINER function exposed to pg_temp search-path shadowing
SEC016	warning	Role with the BYPASSRLS attribute bypasses every RLS policy
SEC017	warning	Function with the LEAKPROOF attribute is evaluated below the RLS barrier
SEC018	warning	Policy compares a column against current_user / session_user (no isolation under a shared pool role)
SEC019	info	Policy calls current_setting() without the missing_ok argument (raises on an unset GUC)
SEC020	warning	Policy WITH CHECK is constant true while USING restricts (writes accept rows reads never would)
SEC021	info	Policy compares an identity column against a hardcoded literal (e.g. tenant_id = 1)
SEC022	info	RLS-enabled table whose policies are all FOR SELECT — no write-side policy, so INSERT/UPDATE/DELETE are denied
SEC023	warning	Policy granted to a role carrying BYPASSRLS — the role skips the policy entirely, so its TO clause is inert
SEC024	info	Policy calls current_setting() with an unqualified parameter name (a dropped prefix the application cannot SET)
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	warning	Policy predicate uses LIKE / ILIKE / SIMILAR TO / POSIX regex against an auth-context value (a wildcard-shape GUC matches every row)
PERF001	warning	Auth function called per-row in policy USING (unwrapped)
PERF002	warning	Policy expression uses a VOLATILE function (random(), clock_timestamp(), …)
PERF003	warning	Policy predicate column without a leading-column index (sequential scan on every query)
HYG001	error	Policies referencing columns that don't exist on the table
HYG002	warning	Policy named like a placeholder (todo, fixme, tmp, …)
HYG003	info	Policy is an exact duplicate of another policy on the same table
VIEW001	error	View over RLS-protected table without WITH (security_invoker = true)
VIEW002	warning	View over RLS-protected table without WITH (security_barrier = true)
VIEW003	warning	Materialized view over RLS-protected table (RLS not honored at query time)
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. Both forms read only pgrls's built-in rule catalog, so they need no database connection.

For canonical SQL fixes per rule, see AGENTS.md. For per-rule configuration options (allowlists, etc.), see pgrls.example.toml.

For per-release changes, see CHANGELOG.md.

CI integration

pgrls is designed to live in your CI alongside any other linter. It needs a Postgres database with your schema applied; it then connects, introspects, and exits non-zero if any rule at or above fail_on (default warning) fires.

pre-commit

# .pre-commit-config.yaml
repos:
  - repo: https://github.com/pgrls/pgrls
    rev: v0.5.7
    hooks:
      - id: pgrls-lint
        # pgrls hits a real database, so most teams scope this to
        # `pre-push` rather than every commit.
        stages: [pre-push]
        args:
          - --database-url=$DATABASE_URL
          - --config=pgrls.toml

GitHub Actions

# .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 — landed in v0.6.0 as pgrls-test. Source: ts/.
• Go port of pgrls.testing following the same Layer 1 protocol — step 1 (scaffold + protocol-version constant + error types) landed in v0.7.0; subsequent steps (Driver interface, pgx + lib/pq adapters, Client API, assertion helpers, conformance suite) tracked in 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.