# grantguard

**Prove a Postgres role cannot perform the writes it is not allowed to perform.**

A prompt that says *"never change the owner or the due date"* is a request. A grant
that cannot express those columns is a fact. `grantguard` reads the facts out of
`pg_catalog`, compares them to a policy file you commit, and fails CI when they
disagree.

It is built for the case where something unattended holds a credential — an agent,
a cron job, a webhook handler — and the interesting question is not what it can do
but what it structurally *cannot*.

```
CRITICAL TABLE_WIDE_GRANT  public.tm_tasks UPDATE
         policy declares 2 column(s) (status, updated_at) but a TABLE-WIDE UPDATE
         grant is in force, so all 31 columns are writable. Any column-level grant
         on this table is decoration
         evidence: has_table_privilege('alis_dev_agent', 'public.tm_tasks', 'UPDATE') = true
         fix: revoke update on public.tm_tasks from alis_dev_agent;
              grant update (status, updated_at) on public.tm_tasks to alis_dev_agent
```

---

## Why this exists

In Postgres, a table-level `GRANT UPDATE ON t` applies to **every column**. A
column-level `GRANT UPDATE (a, b) ON t` applies to exactly those two. Hold both and
the table-level one wins — and the column list *stays in `pg_attribute.attacl`*,
where it goes on looking like a restriction that is no longer in force.

So this sequence, which looks entirely reasonable in a code review:

```sql
-- migration 1: it may move a status, nothing else
grant update (status, updated_at) on tm_tasks to agent;

-- migration 2, three days later: fix a 403 — "this only permits the verb"
grant update on tm_tasks to agent;
```

...silently widens one column to all of them. Reading the migrations in order
suggests the narrow grant still holds. Reading the catalogs says otherwise.

**This was found in production, not invented for a README.** An autonomous agent on
a multi-tenant platform I run was documented — in `CLAUDE.md`, in a registry, and in
the migration's own comment — as able to write *only* `status` on the work-queue
table. Measured against the live database:

| | before | after |
|---|---|---|
| columns the agent could `UPDATE` | **31** | **2** (`status`, `updated_at`) |
| `due_date` / `title` / `completed_at` / `assigned_to` | writable | denied |

*An owner, a due date, a status* are exactly the writes an unattended agent must
never make into a shared work system. Two of the three were open for three days,
and every document about the system said they were closed.

The distinction that catches it is cheap:

```sql
has_table_privilege(role, t, 'UPDATE')       -- true  => table-wide, every column
has_any_column_privilege(role, t, 'UPDATE')  -- true  => at least one column
```

Ask only the second and the widening is invisible. `grantguard` asks both, on every
verb, on every table, and then asks a policy file whether that is what you meant.

---

## Install

```bash
pip install grantguard              # Supabase backend, standard library only
pip install 'grantguard[postgres]'  # adds psycopg for --dsn
```

## Use it

**Look at what a role can actually write:**

```bash
grantguard describe --role agent --dsn "$DATABASE_URL"
```

```
role agent
  superuser=False  bypassrls=False  inherit=False  login=False

  public.tm_tasks
    SELECT  yes
    UPDATE  2 column(s): status, updated_at
    INSERT  10 column(s): assigned_to, created_by_name, description, due_date, …
    policy alis_dev_agent_update [UPDATE] to agent
      using      ((assigned_to = 'Anu Kama') AND (source = 'scope-agent'))
      with check ((assigned_to = 'Anu Kama') AND (source = 'scope-agent'))
```

**Generate a starter policy from live state** (the only way anyone adopts this on an
existing database — a blank file reports a hundred findings on the first run and
gets deleted):

```bash
grantguard init --role agent --out agents.yml
```

**Gate it:**

```bash
grantguard check --policy agents.yml --fail-on high
echo $?   # 0 clean, 1 findings at or above the threshold, 2 usage/connection error
```

---

## The policy file

```yaml
version: 1
schemas: [public]

roles:
  agent:
    description: Autonomous dev agent — moves its own task status, nothing else

    attributes:            # asserted, not assumed
      superuser: false
      bypassrls: false     # a BYPASSRLS role skips every policy on every table
      inherit: false

    tables:
      public.tm_tasks:
        select: true
        update: [status, updated_at]   # EXACTLY these columns
        insert: false
        delete: false
      public.tm_comments:
        select: true
        insert: true                   # `true` = table-wide is fine here

    never_writable:                    # must be impossible, not discouraged
      - public.tm_tasks.assigned_to    # who owns the work
      - public.tm_tasks.due_date       # when it is promised
      - public.tm_tasks.completed_at   # whether it is done

    allow_undeclared: false            # a table not listed above is a finding
```

Two deliberate choices:

- **A column list is a closed set, never a minimum.** Declaring
  `update: [status]` and finding a table-wide grant in force is the whole failure
  mode, so a list is always compared exactly.
- **`never_writable` is the inversion half.** Everything above it describes what
  should be allowed, and allowances drift open by accident. These name the writes
  that must be unreachable, so CI proves a negative instead of trusting that nobody
  widened a grant. It covers `INSERT` as well as `UPDATE` — writing a due date when
  *creating* a row is still writing a due date.

---

## What it checks

Twelve rules, ordered by the failure each one catches:

| Code | Severity | Catches |
|---|---|---|
| `ROLE_IGNORES_RLS` | critical<br>(advisory if declared) | `SUPERUSER`/`BYPASSRLS` — every policy on every table is skipped, so "the policy limits it to its own rows" is false |
| `NEVER_WRITABLE` | critical | a write declared impossible is reachable, and by which path |
| `TABLE_WIDE_GRANT` | critical | a column list was declared; a table-wide grant is in force |
| `ROLE_ATTR` | critical/high | an asserted role attribute does not match |
| `EXTRA_COLUMNS` | high | more columns writable than declared |
| `UNDECLARED_VERB` | high | a verb is granted that the policy says `false` |
| `UNDECLARED_TABLE` | high | privileges on a table the policy never mentions |
| `INHERITS_FROM_ROLE` | high | `INHERIT` + membership, so the audited table list is not the whole picture |
| `OWNER_BYPASSES_RLS` | high | the role owns the table, and an owner is exempt from its own policies unless RLS is `FORCE`d |
| `RLS_DISABLED` | high | row security off, so existing policies are inert |
| `MISSING_VERB` / `MISSING_COLUMNS` | medium | declared but not granted — a dead policy that fails as a flat 403 |
| `NO_POLICY` | medium | granted, RLS on, no policy names it: sees zero rows. Fails closed, reads as broken. Never reported for a role that bypasses RLS, where the claim would be false |
| `TRIGGER_ONLY` | advisory | a "cannot" that rests on a trigger rather than a privilege — drop the trigger and the write returns with no visible change to the grant |

Every finding carries the catalog fact it came from and a `fix:` you can paste.

**A declared `bypassrls: true` downgrades `ROLE_IGNORES_RLS` to advisory**, and this
matters more than it looks. `BYPASSRLS` is sometimes correct: a read-only reporting
role with `SELECT` and no policy does not get an error from an RLS-enabled table, it
gets **zero rows, silently** — so it can genuinely need `BYPASSRLS` to read at all
while holding no write anywhere. It stays critical when the file does not say so,
because the common case is nobody realising. But a gate that cannot be satisfied
gets waived with `--fail-on` and then ignored, taking every other check with it.
Saying it out loud in a reviewed policy is the difference between a decision and an
accident.


---

## In CI

```yaml
- run: pip install grantguard
- run: grantguard check --policy agents.yml --fail-on high
  env:
    DATABASE_URL: ${{ secrets.DATABASE_URL }}
```

This repo's own CI does both directions against a real Postgres 16: it builds a
role with a deliberately widened grant and asserts `grantguard` **exits 1**, then
narrows the grant and asserts it **exits 0**. A gate that only ever fails is
indistinguishable from a gate that is broken.

---

## MCP server

```bash
grantguard-mcp --dsn "$DATABASE_URL"
```

```json
{ "mcpServers": { "grantguard": {
    "command": "grantguard-mcp",
    "args": ["--supabase-ref", "abcdefghijklmno"],
    "env": { "SUPABASE_PAT": "sbp_..." } } } }
```

Four read-only tools:

- **`explain_write`** — *can this role write this column, and by what path?* Names
  whether the privilege comes from a table-wide or a column grant, whether RLS can
  constrain it, and which policies apply. This is the one worth having: it answers a
  specific forbidden write with a fact instead of a guess.
- **`describe_role`** — the whole effective write surface.
- **`check_policy`** — the CI gate, inline or from a path.
- **`list_roles`** — every role plus which ones ignore RLS entirely.

Every tool is read-only, because `grantguard` only ever runs `SELECT`. A model
holding this server cannot change a grant — it can only be told the truth about
one, then tell a human. That is the right shape for a governance tool.

The server speaks JSON-RPC over stdio **directly, with no SDK**. Two reasons: a
security CLI should not make `pip install` pull pydantic, anyio and httpx to answer
four questions about `pg_catalog`; and the official SDK's server entrypoint moved
between majors (`mcp.server.fastmcp` is gone in 2.x). The wire protocol is small
and stable, the framework around it is not.

---

## Backends

**`--dsn` / `DATABASE_URL`** — a direct libpq connection, opened read-only so the
session itself refuses writes.

**`--supabase-ref` / `SUPABASE_PROJECT_REF`** with a PAT in `SUPABASE_PAT` — runs
over the Supabase Management API's SQL endpoint. This exists because a managed
Postgres often is not reachable from where CI runs: no open port, no pooler
credentials in the runner. Standard library only.

Two things about that API that read as an auth failure and are not:

1. `api.supabase.com` is behind Cloudflare, which **403s python-urllib's default
   User-Agent.** Send a browser one or every call looks like a bad token.
2. It returns Postgres arrays as **literal strings** (`{a,b}`), where psycopg
   returns Python lists. `tuple("{agent}")` is seven characters, so a naive read
   makes every role comparison fail *while the run looks completely healthy*. The
   first version of this tool reported "no policy" on a table with three policies
   for exactly that reason; `introspect.pg_array` now normalises both shapes and is
   unit-tested against quoted commas, escapes and `NULL` elements.

---

## What it does not do

Being clear about this matters more than the feature list:

- **It does not read your RLS expressions and tell you whether they are correct.**
  It reports that a policy exists, its command, and its `USING`/`WITH CHECK` text.
  Deciding whether `assigned_to = current_setting('...')` is the right rule is
  yours. Proving a *column* is unreachable is decidable; proving a *predicate* is
  sound is not.
- **It is not a runtime control.** It is an audit and a CI gate. Nothing here stops
  a write at the moment it happens — the grant does that, which is the point.
- **A token that can run SQL can run anything.** The read-only guard in
  `backends.py` stops a bug in *this tool* from writing to your database. It is not
  a security boundary and is not offered as one.
- **It does not audit `pg_hba.conf`, network reachability, or secret handling.** It
  answers one question about privileges inside the database.

---

## Design notes

**It reads catalogs, never migrations.** A migration records what somebody intended.
The catalogs record what is in force. The entire problem is that those drift apart
quietly, so a tool that parses SQL files would confidently reproduce the mistake it
is supposed to catch.

**The check layer never touches a database.** `introspect.py` turns Postgres into
plain dataclasses; `check.py` turns dataclasses into findings. That split is why the
38-test suite runs in 0.05s with no container, and why every rule has a test named
for the failure it prevents rather than the function it calls.

**Severity means "how does this fail".** Widening access is critical or high;
failing closed is medium. `MISSING_VERB` breaks your feature and protects your data,
so it does not belong in the same bucket as a table-wide grant. Grading a
fail-closed bug as critical is how a gate gets a `--fail-on advisory` waiver added
and then ignored.

## Development

```bash
pip install -e '.[dev]'
python -m pytest -q      # 38 tests, no database required
```

MIT licensed.

## Further reading

**[Your prompt says it can't set a due date. Your grant says it can.](docs/your-grant-says-it-can.md)**
— the incident this tool came out of, the one query that would have caught it, and
the four other ways a "cannot" leaks in Postgres. Every SQL snippet in it runs
without installing anything.