# pgguard-mcp

An MCP server that gives an AI agent **read** access to a Postgres database — and makes "read" mean something.

The first objection to connecting an agent to a production database is not whether it can be done. It is "I do not want an AI to have write access to my database." Most MCP Postgres servers answer that with a flag. This one answers it as the whole product: there is no write path at all, and what can be read is decided by a policy file you can review in a PR.

It runs out of the box against a bundled demo dataset (real Postgres via PGlite, no Docker, no database to install), and against your own Postgres with one environment variable.

```
npx pgguard-mcp
```

## Enforcement: two layers, and only one of them is the boundary

```
your SQL
   │
   ▼
Layer 2 — policy engine (this server)          policy + UX
   │  parses with the real PostgreSQL grammar (libpg-query 17)
   │  rejects: non-SELECT statements, multi-statement, SELECT INTO,
   │  write CTEs, FOR UPDATE, denylisted functions, tables/columns
   │  outside the policy; injects the row cap
   ▼
Layer 1 — Postgres itself                      the real boundary
   │  BEGIN READ ONLY … ROLLBACK
   │  SET LOCAL statement_timeout
   │  optional SET LOCAL ROLE / request.jwt.claims (RLS path)
   ▼
rows, capped and audited
```

Layer 2 is what gives you column-level granularity and error messages a model can act on. **Layer 1 is what stops an attacker.** An `INSERT` that somehow slipped past the parser still fails at the database with `cannot execute INSERT in a read-only transaction`, and the transaction is rolled back, never committed. A server whose only protection is a SQL parser is not a security boundary — that is why this one has both, and why the tests verify each layer independently.

## Install

Claude Desktop (`claude_desktop_config.json`):

```json
{
  "mcpServers": {
    "pgguard": {
      "command": "npx",
      "args": ["-y", "pgguard-mcp"]
    }
  }
}
```

Claude Code:

```bash
claude mcp add pgguard -- npx -y pgguard-mcp
```

With no configuration at all, the server starts against the demo dataset: a small synthetic SaaS schema (~300 customers, ~2,000 orders over 90 days) where `orders` and `order_items` are fully visible, `customers` hides `email` and `stripe_customer_id`, and `users` and `audit_events` are not visible at all. Those hidden tables are the demonstration material — ask for them and watch what happens.

## Point it at your database

```json
{
  "mcpServers": {
    "pgguard": {
      "command": "npx",
      "args": ["-y", "pgguard-mcp"],
      "env": {
        "PGGUARD_DATABASE_URL": "postgres://agent_ro:secret@db.internal:5432/app",
        "PGGUARD_POLICY": "/etc/pgguard/policy.json"
      }
    }
  }
}
```

Generate a starting policy from the live schema (it emits explicit column lists, so the reviewer sees every column the agent will get — delete the ones it should not):

```bash
npx pgguard-mcp init-policy "$PGGUARD_DATABASE_URL" --out pgguard.policy.json
```

Prefer `--out` over shell redirection: PowerShell 5.1 writes UTF-16LE for `>`, and a UTF-16 policy file fails `JSON.parse` at startup. Without `--out` the policy goes to stdout.

## The policy file

Deny-by-default: anything not listed is invisible and unqueryable.

```json
{
  "version": 1,
  "maxRows": 200,
  "statementTimeoutMs": 5000,
  "tables": {
    "public.orders": { "columns": "*" },
    "public.order_items": { "columns": "*" },
    "public.customers": { "columns": ["id", "name", "city", "created_at"] }
  },
  "denyFunctions": []
}
```

Two consequences are enforced in code, not just documented:

- `"columns": "*"` includes **future** columns: anything an `ALTER TABLE` adds later becomes visible without a policy change. `init-policy` emits explicit lists so this is a deliberate choice. Prefer explicit lists for sensitive tables.
- `SELECT *` against a column-restricted table is **rejected**, with a message naming the allowed columns. The server never silently rewrites your query into a different column list — a result that differs from what you asked for is more dangerous than a refusal.

`denyFunctions` extends a built-in list (`pg_read_file`, `pg_ls_dir`, `dblink`, `lo_import`, `lo_export`, `pg_sleep`, `pg_terminate_backend`, `set_config`, and relatives). The built-ins always apply; the file can only add names.

An invalid policy fails startup with a message naming the offending field. It never falls back to a default.

## What a refusal looks like

Real output, demo dataset:

```
> run_query: SELECT * FROM users

table public.users is not in the access policy; run describe_access to see what is visible
```

```
> run_query: SELECT email FROM customers

column email is not in the access policy for public.customers; allowed columns for public.customers: id, name, city, created_at
```

```
> run_query: WITH x AS (INSERT INTO orders ... RETURNING *) SELECT * FROM x

WITH x AS (...) does not contain a SELECT; write-capable CTEs (INSERT/UPDATE/DELETE ... RETURNING) are not accepted
```

The full hostile-input suite — DML/DDL, `SELECT INTO`, write CTEs, multi-statement, comment smuggling, allowlist violations, CTE shadowing, system catalogs, denylisted functions, `COPY TO PROGRAM`, locking clauses, resource exhaustion — lives in [`tests/policy.attacks.test.ts`](https://github.com/Azzaraell/pgguard-mcp/blob/main/tests/policy.attacks.test.ts). Every case must be denied **and** name the rule that denied it. The suite also contains the queries that must pass (joins, aggregates, window functions, read-only CTEs), so the gate is proven to be a filter, not a reject-everything wall.

The suite grows adversarially. Before release, the bundled review subagent (`.claude/agents/sql-gate-bypass-hunter.md`) was turned loose on the gate and found five real bypasses, each reproduced against the built code: schema-qualified 3-part column references (`public.customers.email`), whole-row field selection (`(alias).email`, `(alias).*`), correlated references to hidden outer-scope columns (`SELECT (SELECT c.email) FROM customers c`), named `WINDOW` clauses ordering by hidden columns, and the `*_to_xml*` function family (`query_to_xml` executes its string argument as SQL). All five are closed and are now permanent regression cases in the suite, next to a `UNION` smuggling path found the same way during the build. This is the intended workflow: the subagent's job is to keep finding what the gate misses, and the suite is where those findings go to die.

## Tools

Six tools, and no write tool — not even behind a flag. This server does not have a write path to disable; that is a design decision, not a missing feature. The count is deliberately small: the value of this server is in the enforcement path, not in tool count.

| Tool | Purpose |
|---|---|
| `describe_access` | The effective policy: visible tables and columns, row cap, timeout, whether a role or RLS claims are active. The agent can say "I am not allowed to see that column" instead of failing mysteriously. Start here. |
| `list_tables` | Allowlisted tables with estimated row counts and comments |
| `describe_table` | Columns (only those the policy exposes), types, nullability, PK/FK, indexes |
| `sample_rows` | Structured reads: one table, simple filters, a limit. The no-SQL path for simple questions |
| `run_query` | A single read-only `SELECT` through the full gate: parse, policy check, row-cap injection, read-only transaction |
| `explain_query` | `EXPLAIN` without executing. `ANALYZE` is not offered — it would run the query |

Schema introspection is filtered by the same policy as queries: the model never learns that `users.password_hash` exists.

## Audit log

One JSONL line per tool call, **including denials** — a recorded refusal is what makes this an audit log rather than a query log. Defaults to stderr; set `PGGUARD_AUDIT_LOG` to write to a file.

```json
{"ts":"2026-07-29T08:28:00.258Z","tool":"run_query","sql":"SELECT * FROM users","paramCount":0,"decision":"deny","rule":"table-not-allowed","durationMs":0}
```

Fields: timestamp, tool, normalized SQL, parameter count, decision (`allow`/`deny`), the rule that triggered a denial, rows returned, duration, whether the response was trimmed. Parameter values and result rows are never logged — an audit log that leaks the data it guards defeats its own purpose.

## Deployment: the database side

The server enforces its own layers, but the strongest deployment also restricts the role it connects as. Create a read-only role and grant only what the policy allows:

```sql
CREATE ROLE agent_ro LOGIN PASSWORD '...';
GRANT CONNECT ON DATABASE app TO agent_ro;
GRANT USAGE ON SCHEMA public TO agent_ro;
GRANT SELECT ON public.orders, public.order_items TO agent_ro;
GRANT SELECT (id, name, city, created_at) ON public.customers TO agent_ro;
ALTER ROLE agent_ro SET default_transaction_read_only = on;
```

With `PGGUARD_ROLE` set, the server runs `SET LOCAL ROLE` inside each query transaction, so grants and row-level security policies apply even when the connecting user is broader. With `PGGUARD_CLAIMS` set, `request.jwt.claims` is set per transaction — the standard Supabase RLS path. Column-level `GRANT SELECT (col, ...)` means even a total failure of the server's policy engine still cannot read hidden columns.

## Environment variables

| Variable | Default | Purpose |
|---|---|---|
| `PGGUARD_DATABASE_URL` | empty → demo mode | Postgres connection string |
| `PGGUARD_POLICY` | bundled demo policy | Path to the policy file |
| `PGGUARD_ROLE` | empty | `SET LOCAL ROLE` per transaction |
| `PGGUARD_CLAIMS` | empty | JSON for `request.jwt.claims` (Supabase RLS) |
| `PGGUARD_AUDIT_LOG` | stderr | Path for the JSONL audit log |
| `PGGUARD_MAX_RESPONSE_BYTES` | `100000` | Response size cap per tool call |

The variables are prefixed on purpose: `DATABASE_URL` is too common a name for a process that inherits its environment from an MCP client.

## Stated limitations

- **stdio only.** No HTTP/SSE transport. One database per process; run two processes for two databases.
- **In demo mode (PGlite), `statement_timeout` does not interrupt a running query.** PGlite is single-threaded WASM with no interrupt mechanism — verified against PGlite 0.5.4 on 2026-07-29. The read-only transaction, roles, grants, and RLS all work in demo mode; the timeout is set but will not cut a CPU-bound query. Against a real Postgres server over `PGGUARD_DATABASE_URL`, `statement_timeout` is standard server behavior and does fire.
- **The parser is layer two.** Column checks are conservative by design: an unqualified column is rejected if any column-restricted table in scope does not allow it (qualify the column to fix), and qualified references the gate cannot resolve are left to the database. The read-only transaction and role grants are what these checks defer to.
- **Quoted mixed-case identifiers are out of scope.** Policy keys are matched case-insensitively, the way unquoted SQL identifiers fold. A table created as `"Orders"` with quotes needs a policy written to match, and is a reason to prefer lowercase DDL.
- **No write path, no query cache, no multi-tenancy.** Each is a different product, not a roadmap item.

## Context

As of 2026-07-29, the official reference server `@modelcontextprotocol/server-postgres` is marked deprecated on npm, and the high-download alternative (`@supabase/mcp-server-supabase`, ~119k weekly downloads) is oriented toward project management — migrations, logs, branches — rather than policy-gated read access for agents. This server exists in that gap: deny-by-default policy, column-level allowlists, an audit log that records refusals, and a public attack suite as the evidence.

## Development

```bash
npm install
npm run build              # tsc
npm test                   # vitest: attack suite + tool integration over PGlite, no Docker
npm run generate:seed      # regenerate seed/demo.sql deterministically
```

`seed/generate.ts` uses a fixed seed; regeneration must produce byte-identical output. If it does not, the generator picked up nondeterminism — fix the generator, never hand-edit the SQL.

The `.claude/` setup is part of the repo's workflow: an adversarial-review subagent (`agents/sql-gate-bypass-hunter.md`) that tries to sneak reads or writes past the gate and turns successes into failing attack-suite cases, a skill (`skills/run-attack-suite/`) that runs the suite and checks its coverage, and two hooks wired in `settings.json` — `hooks/no-stdout-log.sh` keeps stray `console.log` out of the stdio server, and `hooks/no-null-bytes.sh` blocks `git commit` when a staged text file contains null bytes (a guard against UTF-16 output from PowerShell redirects, which once published an empty README to npm on a sibling project). The `.claude/` hooks only see commits made by the agent, so the null-byte check also exists as a real git hook in `githooks/pre-commit`; enable it per clone with `git config core.hooksPath githooks`.

## License

MIT — see [LICENSE](LICENSE).