Metadata-Version: 2.4
Name: supabase-rls-check
Version: 0.6.1
Summary: Consent-gated, read-only RLS, policy, and anonymous exposure checks for Supabase projects.
Author: Trung Thanh Tran
License: MIT
Project-URL: Homepage, https://github.com/Tthanh2k6/supabase-rls-check
Project-URL: Source, https://github.com/Tthanh2k6/supabase-rls-check
Project-URL: Changelog, https://github.com/Tthanh2k6/supabase-rls-check/blob/main/CHANGELOG.md
Project-URL: Agent skill, https://www.agensi.io/skills/supabase-rls-review
Project-URL: MCP server (Glama), https://glama.ai/mcp/servers/Tthanh2k6/supabase-rls-check
Keywords: supabase,postgresql,row-level-security,rls,postgrest,security,audit,mcp
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: Topic :: Database
Classifier: Topic :: Security
Requires-Python: >=3.11
Description-Content-Type: text/markdown
License-File: LICENSE
Requires-Dist: requests<3,>=2.31
Provides-Extra: mcp
Requires-Dist: mcp>=1.2; extra == "mcp"
Provides-Extra: dev
Requires-Dist: pytest<10,>=7; extra == "dev"
Dynamic: license-file

# supabase-rls-check

<!-- mcp-name: io.github.Tthanh2k6/supabase-rls-check -->

[![PyPI](https://img.shields.io/pypi/v/supabase-rls-check)](https://pypi.org/project/supabase-rls-check/) [![Glama MCP server](https://glama.ai/mcp/servers/Tthanh2k6/supabase-rls-check/badges/score.svg)](https://glama.ai/mcp/servers/Tthanh2k6/supabase-rls-check)

Consent-gated, read-only configuration and anonymous-exposure checks for Supabase projects (PostgreSQL 15+, Python 3.11+). No database password or `service_role` key required.

## Why this exists

Supabase applications intentionally ship a public `anon` JWT or `sb_publishable_` key inside browser JavaScript bundles so client code can talk directly to PostgREST (`/rest/v1/`) and Storage. Because every visitor can read that key from DevTools, access control depends directly on PostgreSQL Row Level Security (RLS), policy expressions, table/function grants, view options, and Storage bucket visibility—along with never bundling a `service_role` token in frontend code.

During rapid prototyping, AI-assisted schema generation, or manual migrations, it is easy to leave a table in `public` without RLS, keep a temporary `USING (true)` policy, or forget that PostgreSQL grants `EXECUTE` to `PUBLIC` on new `SECURITY DEFINER` functions exposed at `/rest/v1/rpc/*`. `supabase-rls-check` helps project owners review those patterns safely without modifying data or saving table rows.

## Installation

Requires Python 3.11 or newer.

```bash
pipx install supabase-rls-check      # or: uvx supabase-rls-check --help
```

Optional Model Context Protocol (MCP) server support (`mcp>=1.2`):

```bash
pipx install "supabase-rls-check[mcp]"
```

From source for development:

```bash
pip install -e ".[dev]"
```

## Three-step usage

### 1. Export catalog metadata with `check.sql`

Print the bundled read-only SQL query (or open [`src/supabase_rls_check/sql/check.sql`](src/supabase_rls_check/sql/check.sql)):

```bash
supabase-rls-check sql --print-sql > check.sql
```

Paste `check.sql` into the **Supabase SQL Editor**, adjust the sensitive-table name pattern if needed, run the `SELECT`, and export the single result set as CSV or JSON (`rule,schema_name,object_name,policy_name,basis`). Only catalog metadata is queried—never application rows.

Validate and normalize the export:

```bash
supabase-rls-check sql --from export.csv --out sql_results.json --i-own-this-project
```

### 2. Optionally verify anonymous read exposure (`probe`)

Check what an unauthenticated caller can actually read with your public `anon` JWT or `sb_publishable_` key, and optionally inspect your frontend origin for leaked `service_role` JWTs:

```bash
supabase-rls-check probe \
  --url https://YOUR_PROJECT.supabase.co \
  --anon-key YOUR_ANON_OR_PUBLISHABLE_KEY \
  --site https://your-app.example \
  --out probe_results.json \
  --i-own-this-project
```

- Pass the project HTTPS origin to `--url` (not `/rest/v1/`).
- Only `anon` JWTs and `sb_publishable_` keys are accepted; `service_role` and `authenticated` tokens are rejected before any request is sent.
- If OpenAPI schema discovery is disabled on PostgREST, pass known table/view names explicitly with `--tables todos profiles`.

### 3. Generate the standalone report and dry-run SQL template

```bash
supabase-rls-check report \
  --from sql_results.json probe_results.json \
  --out-dir review_out \
  --project "My Supabase App" \
  --i-own-this-project
```

This writes three files to `review_out/`:

- `report.html` — standalone, offline-ready HTML report (A4 landscape print stylesheet for **Print -> Save as PDF**)
- `report.md` — Markdown summary suitable for issues or pull requests
- `fix.sql` — commented review template wrapped in `BEGIN; ... ROLLBACK;` (never executed by this tool; replace `user_id` with your real ownership column and test on staging first)

Add `--lang vi` to generate Vietnamese report text.

### Scan your app for secrets in public env variables

Scan a local project directory (read-only, local files only, no network) for `service_role` JWTs, `sb_secret_` keys, and live API secrets exposed through client-bundled environment variables (`EXPO_PUBLIC_`, `NEXT_PUBLIC_`, `VITE_`, `REACT_APP_`, `PUBLIC_`, `NUXT_PUBLIC_`) or hardcoded in client source files:

```bash
supabase-rls-check scan-env . --out envscan.json
supabase-rls-check report \
  --from sql_results.json envscan.json \
  --out-dir review_out \
  --project "My Supabase App" \
  --i-own-this-project
```

- Inspects `.env`, `.env.*`, `app.json`, `app.config.*`, `eas.json`, AI coding agent and MCP configuration files (`.claude/settings.json`, `.claude/settings.local.json`, `.cursor/mcp.json`, `.mcp.json`, `.vscode/mcp.json`), and client source files (`*.js`, `*.jsx`, `*.ts`, `*.tsx`, `*.mjs`, `*.cjs`, `*.vue`, `*.svelte`) anywhere in the tree, which covers common React Native / Expo layouts such as `src/`, `app/`, `components/`, and `screens/`. It skips `node_modules`, `.git`, virtualenvs, build outputs (`dist`, `build`, `web-build`, `android/app/build`, `ios/build`, `ios/Pods`), binary files, and files over 2 MB.
- Emits `public_env_service_role` (**Critical**), `public_env_secret_key` (**High**), `service_role_in_client_code` and `db_url_in_client_code` (**Critical**; both look for `service_role` JWTs, `sb_secret_` keys, and Postgres URLs with a password in client-bundled files, excluding `supabase/functions/`, `server/`, `api/`, `scripts/`, and `backend/`), and `agent_config_secret` (**Critical**, reporting file path and JSON key path when valid JSON, or falling back to text search on invalid JSON, for `sb_secret_` keys, `service_role` JWTs, and `postgres(ql)?://user:password@` URLs).
- The `anon` JWT and `sb_publishable_` keys are expected in a client app and are never reported. The scan is pattern-based on single lines: obfuscated, split, concatenated, or encoded keys can be missed.
- Requires no consent flag because it only reads local files, and always masks secret values (`first 6 chars + … + last 4`).

### Explaining a rule and optional MCP mode

Inspect the rationale, caveats, and remediation example for any rule without touching a target project:

```bash
supabase-rls-check explain rls_disabled
supabase-rls-check explain definer_anon_exec
```

If installed with `supabase-rls-check[mcp]`, start the local stdio MCP server:

```bash
supabase-rls-check mcp
```

The MCP server exposes only four local, read-only tools: `analyze_sql_export(path)`, `explain_rule(rule)`, `scan_public_env(path)`, and `diff_results(old_path, new_path)`. Network probing is intentionally excluded from MCP so AI clients never handle API keys or send HTTP requests through this tool.

## Run it in CI (GitHub Action)

The repository is also a composite GitHub Action. It runs the same read-only `check.sql` with `psql` (inside a `default_transaction_read_only` session), writes `report.md`, `report.html` and `fix.sql`, adds a summary table to the job page, and fails the job when a finding is at or above `fail-on`.

Unlike the three-step flow above, this needs a Postgres connection string. Keep it in a repository secret and point it at a staging project or a read-only role.

```yaml
name: RLS check
on: [pull_request]
jobs:
  rls:
    runs-on: ubuntu-latest
    steps:
      - uses: Tthanh2k6/supabase-rls-check@main
        with:
          database-url: ${{ secrets.STAGING_DATABASE_URL }}
          i-own-this-project: "true"
          fail-on: High        # Critical, High, Medium, Low or none
      - uses: actions/upload-artifact@v4
        if: always()
        with:
          name: rls-report
          path: rls-report/
```

Outputs: `findings` (total) and `failing` (at or above `fail-on`). The action refuses to run unless `i-own-this-project` is `"true"`.

## Catch drift between runs

Compare two local CSV or JSON result files (read-only, local files only, no network) to see what is **new** and what was **resolved** between runs, and optionally exit with code `1` only when a new finding appears at or above `--fail-on-new` (`critical`, `high`, `medium`, or `low`):

```bash
supabase-rls-check diff \
  --old baseline_results.json \
  --new sql_results.json \
  --out diff.json \
  --fail-on-new high
```

Each finding is keyed by `(rule, schema_name, object_name, policy_name, basis)`, where `basis` explains why a heuristic rule fired (for example `"table name matches pattern 'payment'"` vs `"table comment tag 'pii'"` on `sensitive_anon_policy`, the column name on `secret_column_exposed`, or the argument name(s) on `definer_trusts_id_arg`). Adding a `pii` table comment between runs therefore shows up as a **new** finding (and the previous pattern-based finding on that policy, if any, as **resolved**) rather than a separate "changed" bucket. Older JSON files without a `basis` key remain compatible and default to `""`. The same applies when `basis` changes for a table: a baseline exported before 0.6.1 lists an extension-owned table with an empty `basis`, so the first diff against it shows that table as **new** (with `owned by extension <name>`) and the old entry as **resolved**, and `--fail-on-new high` exits `1` once. Re-export the baseline with 0.6.1 to avoid this.

Run `check.sql` on a schedule and on pull requests against your baseline (for example `.rls-baseline.json` committed in the repository or downloaded from the previous workflow artifact) so schema drift outside a pull request triggers an alert:

```yaml
name: RLS drift check
on:
  pull_request:
  schedule:
    - cron: "0 6 * * *"
jobs:
  drift:
    runs-on: ubuntu-latest
    steps:
      - uses: actions/checkout@v4
      - uses: actions/setup-python@v5
        with:
          python-version: "3.12"
      - name: Run check.sql and diff against baseline
        env:
          DATABASE_URL: ${{ secrets.STAGING_DATABASE_URL }}
        run: |
          python -m pip install supabase-rls-check
          supabase-rls-check sql --print-sql > check.sql
          PGOPTIONS='-c default_transaction_read_only=on' \
            psql "$DATABASE_URL" -v ON_ERROR_STOP=1 --csv -f check.sql > current.csv
          supabase-rls-check sql --from current.csv --out current.json --i-own-this-project
          supabase-rls-check diff \
            --old .rls-baseline.json \
            --new current.json \
            --out diff.json \
            --fail-on-new high
      - uses: actions/upload-artifact@v4
        if: always()
        with:
          name: rls-diff
          path: |
            current.json
            diff.json
```

## Sample report and offline demo

![Sample HTML report preview](docs/demo-report.png)

Try the offline fictional dataset ([`demo/todo_ai.csv`](demo/todo_ai.csv) -> [`demo/report.md`](demo/report.md) and [`demo/fix.sql`](demo/fix.sql)):

```bash
supabase-rls-check report --from demo/todo_ai.csv --out-dir demo --project "todo-ai (fictional demo)" --i-own-this-project
supabase-rls-check report --from demo/todo_ai.csv --out-dir demo/vi --project "todo-ai (dữ liệu giả)" --lang vi --i-own-this-project
```

## Seventeen checks: SQL catalog rules

`check.sql` inspects PostgreSQL 15+ system catalogs and `storage.buckets` in a single read-only CTE query (and `scan-env` adds local file checks):

| Rule ID | Severity | Meaning |
| --- | --- | --- |
| `rls_disabled` | High | Table or partitioned table in `public` has Row Level Security disabled (`relrowsecurity = false`). When the table belongs to an extension (for example PostGIS `spatial_ref_sys`), the exposure is still reported but `basis` reads `owned by extension <name>`, and `fix.sql` carries a comment recommending moving the extension out of `public` into an `extensions` schema instead of `ENABLE ROW LEVEL SECURITY`, which fails on Supabase with "must be owner" (see [supabase/supabase discussion #52767](https://github.com/supabase/supabase/discussions/52767)). Detection reads `pg_depend` (`deptype = 'e'`) and only covers tables that are direct members of an extension; some extensions are not relocatable, so check before moving one. |
| `policy_true` | High | Policy in `public` has an unconditional `USING (true)` or `WITH CHECK (true)` expression. |
| `policy_true_writable` | Critical | Permissive `UPDATE`, `DELETE`, or `ALL` policy in `public` for `anon`, `authenticated`, or `public` has an unconditional `USING (true)` expression on a table where `anon` or `authenticated` holds `UPDATE` or `DELETE` privileges, allowing anyone with that role to modify or delete every row. |
| `sensitive_anon_policy` | High | Policy granted to `anon` or `public` on a table matching common sensitive names (`user`, `profile`, `order`, `payment`, `invoice`, `customer`, `patient`, `secret`, `token`, `document`) or tagged with a `pii` table comment (`basis` records the matched pattern or `'pii'` comment tag). |
| `definer_anon_exec` | High | Non-trigger `SECURITY DEFINER` function in `public` is executable by `anon` (callable via `/rest/v1/rpc/*`). |
| `definer_search_path` | High | `SECURITY DEFINER` function in `public` has no fixed `search_path` set on the function definition. |
| `view_not_invoker` | High | View in `public` does not enable `security_invoker`, so base-table queries run with view-owner privileges. |
| `public_bucket` | Medium | Supabase Storage bucket in `storage.buckets` is marked `public = true`. |
| `public_bucket_listable` | Medium | Public Storage bucket in `storage.buckets` also has an `anon` or `public` `SELECT` (or `ALL`) policy on `storage.objects` matching that bucket id or `true`, letting anyone list every file name in the bucket through the API. |
| `public_extension` | Low | PostgreSQL extension is installed directly in `public` rather than a dedicated `extensions` schema. |
| `policy_user_metadata` | High | Policy in `public` reads user-editable `user_metadata` from `auth.jwt()` or `request.jwt.claims`. Use server-managed `app_metadata` or a protected authorization table. |
| `policy_auth_per_row` | Low | Policy on an RLS-enabled table in `public` calls `auth.uid()`, `auth.jwt()`, `auth.role()`, or `current_setting()` without a sub-select. Performance finding: use `(select auth.uid())` for row-independent calls. |
| `auth_users_exposed` | Critical | Public view directly depends on `auth.users`, allows `anon` or `authenticated` to SELECT, and is materialized or lacks `security_invoker`. Drop it or harden a regular view and revoke access; publish only needed profile columns from a separate table. |
| `matview_api_exposed` | High | Materialized view or foreign table in `public` allows `anon` or `authenticated` to `SELECT`. Row Level Security does not apply to materialized views, so every row is readable through the Data API. Revoke `SELECT` from `anon`/`authenticated` and serve the data through a regular view with `security_invoker = true`, or move it to a schema the API does not expose. |
| `policy_any_authenticated` | Medium | Policy in `public` only checks that the caller is signed in (`auth.role() = 'authenticated'` or `auth.uid() IS NOT NULL`), giving every signed-in account the same access. Tie the row to `(select auth.uid()) = user_id` or a membership table, or document intentional shared access in a table comment. |
| `secret_column_exposed` | Medium | Table in `public` has a column matching or ending with `api_key`, `apikey`, `secret`, `client_secret`, `access_token`, `refresh_token`, `private_key`, or `password` (`basis` = column name) where `anon` or `authenticated` holds table or column `SELECT`. RLS can limit rows to the owner, but the client still receives the plaintext secret; move secrets to Supabase Vault, a private schema, or a server-only Edge Function, or `REVOKE SELECT (col) ON public.t FROM anon, authenticated`. |
| `definer_trusts_id_arg` | Medium | `SECURITY DEFINER` function in `public` executable by `anon` or `authenticated` takes an argument ending with `_id` or equal to `uid`, `user`, `org`, or `tenant` (`basis` = argument name(s)) and its `prosrc` does not reference `auth.uid()` or `auth.jwt()`. Review needed: misses functions that delegate caller checks to a helper function and may flag functions that validate the ID against a membership table. |
| `db_url_in_client_code` | Critical | Local `scan-env` check: a `postgres(ql)?://user:password@` connection string in a client-bundled file (React Native / Expo source, `app.json`, `app.config.*`, `eas.json`). Placeholders such as `${DB_PASSWORD}` are ignored. |
| `agent_config_secret` | Critical | Local `scan-env` check: AI coding agent or MCP config file (`.claude/settings.json`, `.claude/settings.local.json`, `.cursor/mcp.json`, `.mcp.json`, `.vscode/mcp.json`) contains an `sb_secret_` key, a `service_role` JWT, or a `postgres(ql)?://user:password@` connection string. |

The nine newer catalog rules (`policy_true_writable`, `policy_user_metadata`, `policy_auth_per_row`, `auth_users_exposed`, `matview_api_exposed`, `public_bucket_listable`, `policy_any_authenticated`, `secret_column_exposed`, and `definer_trusts_id_arg`) add only commented guidance to `fix.sql`; they never generate automatic DDL. You can also tag tables with `COMMENT ON TABLE public.x IS 'pii'` so `sensitive_anon_policy` does not depend on table naming (idea from a reader comment on the dev.to article). Overlap is intentional: `policy_true` keeps firing for the same policy as `policy_true_writable`, a materialized view over `auth.users` produces both `auth_users_exposed` and `matview_api_exposed` (same as regular views over `auth.users` overlap with `view_not_invoker`), and listable public buckets produce both `public_bucket` and `public_bucket_listable`. Policy expression checks are heuristics over PostgreSQL's deparsed SQL. The authentication performance check recognizes directly SELECT-wrapped calls; complex subqueries need manual review. The Auth exposure check follows direct dependencies and returns no findings when `auth.users` is absent.

### Additional checks from `probe`

When `probe` is run, two runtime checks complement the SQL catalog export:

- `anon_readable` (**High**) — PostgREST returned visible rows for an unauthenticated `GET`/`HEAD` request using the `anon` or publishable key (records column names and visible row count only).
- `service_role_exposed` (**Critical**) — A JWT whose payload decodes to `role = "service_role"` was found in frontend HTML or a directly linked same-origin script.

Tip: pass the anon key through the `SUPABASE_ANON_KEY` environment variable instead of `--anon-key` to keep it out of shell history.

## How it is tested

- **Unit tests** (`pytest`): consent flag enforcement, GET/HEAD-only probing with no redirects, response size limits, row values and keys never written to disk, `service_role` JWT detection, local `scan-env` secret detection (including agent/MCP config files and React Native / Expo sources and config) with masked values, `diff` drift comparison with `basis` failure-injection and severity gating, HTML/SQL escaping in reports, identifier quoting in `fix.sql`, English and Vietnamese output, all 17 catalog rules in reports/explanations, comment-only fixes for the newer rules, and the MCP tool surface (no `probe` exposed).
- **Catalog query on a real PostgreSQL 15** (GitHub Actions): `tests/fixtures/seed_pg.sql` plants issues for all 17 rules next to safe controls. CI compares all 32 expected findings (including `basis` values) exactly, including overlapping view/matview, writable `USING (true)` policy, and bucket findings, and rejects extra or missing rows. Cases cover both JWT sources, all four authentication helpers, mixed wrapped/unwrapped calls, signed-in-only policies, writable vs read-only `USING (true)` policies, materialized views, public bucket listing policies, `pii` table comments, an extension-owned table (via `ALTER EXTENSION ... ADD TABLE`), column-level `SELECT` revocations on secret columns, `SECURITY DEFINER` ID-argument checks, role grants, and safe `app_metadata`/sub-select/invoker controls. The query runs with read-only transactions, including separate cases without an `auth` schema or `auth.users` table.
- **`diff` end to end on a real PostgreSQL 15** (GitHub Actions job `diff-e2e`, also runnable locally with `scripts/e2e_diff.py` when `psql` is available): applies a small fixture schema, exports and builds a JSON report, reruns with nothing changed (`diff --fail-on-new high` exits `0`), adds `COMMENT ON TABLE ... IS 'pii'`, reruns, and asserts that `diff --fail-on-new high` exits non-zero with the escalated `sensitive_anon_policy` finding.
- **Abuse cases checked by hand**: running any command without `--i-own-this-project` exits before touching the network; passing a `service_role` key to `probe` is refused; the MCP server cannot reach the network.

## Ethical boundaries

- **Authorization required**: Only scan projects you own or have explicit **written authorization** to test, including any `--site` frontend domain. Commands that process project exports or targets require `--i-own-this-project` or `--have-written-consent`.
- **Strictly read-only**: `probe` sends `GET` and `HEAD` requests only—never `POST`, `PUT`, `PATCH`, `DELETE`, or RPC invocations. HTTP redirects are not followed, `trust_env` is disabled, responses are capped at 5 MB, and timeouts are fixed at 20 seconds.
- **No customer row storage**: `probe` requests at most `limit=1` row per relation in memory, extracts column names, discards row values immediately, and strips JWT patterns (`[REDACTED]`) from metadata and error messages.

## Limitations (not a penetration test)

This tool is a configuration and anonymous-read review aid, **not a penetration test** and **not a security certification**:

- Configuration findings are review signals, not proof of exploitability; public buckets or anonymous `SELECT` policies are sometimes intentional.
- An empty finding list, HTTP `200 []`, or `401`/`403` on a single request does not prove overall safety.
- Does **not** test authenticated cross-user / multi-tenant isolation, `INSERT`/`UPDATE`/`DELETE` permissions, SQL function bodies, Edge Functions, custom Auth hooks, or complex `storage.objects` policy combinations.
- `--site` inspects only the entry HTML document and up to 20 directly linked same-origin HTTPS `<script src>` files. Dynamically imported chunks, source maps, split/obfuscated tokens, and non-JWT secret formats (`sb_secret_*`) are outside its coverage, and JWT signatures are not verified.
- `fix.sql` is a starting template ending in `ROLLBACK;`. Adding a new permissive policy does not override existing broad permissive policies—always export existing policies/grants and test two-user isolation on staging before committing changes.

## Use it from an AI coding agent

- **Agent skill** (Claude Code, Cursor, Codex and other SKILL.md agents): [Supabase RLS Review on Agensi](https://www.agensi.io/skills/supabase-rls-review) walks the agent through the review, policy fixes and a two-user isolation test on staging.
- **MCP server listing**: [supabase-rls-check on Glama](https://glama.ai/mcp/servers/Tthanh2k6/supabase-rls-check).

## Need a human review?

Need a manual review of RLS policies, `SECURITY DEFINER` function bodies, and two-user staging isolation? Book a fixed-scope review (from $69, report plus a ROLLBACK-wrapped fix script):

- Upwork: https://www.upwork.com/services/product/2119987807643160130
- Contra: https://contra.com/s/sFUXWqOZ-supabase-security-check-for-lovable-bolt-and-v0-apps

## License

Code is released under the [MIT License](LICENSE) (Copyright (c) 2026 Trung Thanh Tran). Bundled Bricolage Grotesque report fonts in `assets/` are licensed under the SIL Open Font License 1.1 ([`assets/BricolageGrotesque-OFL.txt`](assets/BricolageGrotesque-OFL.txt)).
