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:

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

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

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:

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

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

Gate it:

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

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 FORCEd
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

- 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

grantguard-mcp --dsn "$DATABASE_URL"

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

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