postgres-mcp-hardened

# postgres-mcp-hardened

> ### 🚧 Version 0.1.9 — a security release, and how it was found
>
> Published: binaries for five platforms with checksums, Sigstore signatures and build provenance;
> `.mcpb` bundles for one-click install; an image on `ghcr.io` for amd64 and arm64; a package on npm;
> and an entry in the official MCP registry.
>
> **0.1.8 closes six bypasses that were present in 0.1.7, and none of them were found by us.** They
> came from four independent reviewers reading a draft article about this project. Two days of our
> own adversarial work across every axis we could think of had come back mostly clean the day before.
> Passing the tests you thought to write is not the same as looking.
>
> The one that matters most needs **no privileges at all**: with a column redacted, a join on it
> through `USING` answered whether a given value was present, which is a complete equality oracle
> against the least-privilege reader this project tells you to configure. The others: a substring
> comparison that let a remote database pass as loopback and skip TLS; two routes to an oracle over
> the structure of a schema the caller was refused; `X-Forwarded-For` read from the wrong end, so a
> header the client writes reset both rate limits; a memory bound that doubled as a rate-limit reset;
> and the cost guard failing open when it could not read a plan. Each was reproduced against a
> running server before being fixed, and each is in [`CHANGELOG.md`](CHANGELOG.md) with the query.
>
> One thing an existing unit test had been doing since it was written: asserting the vulnerable
> behaviour. It was green for exactly as long as the hole existed.
>
> A resource limit is documented and **not** solved, in [`THREAT_MODEL.md`](THREAT_MODEL.md): 49
> bytes of SQL make PostgreSQL fold a constant into 5.9 GB of backend memory during planning, and a
> five second `statement_timeout` does not stop it. 0.1.8 refuses the obvious shapes; the general
> problem is upstream of anything this server can do.
>
> **0.1.9 exists because the 0.1.8 fix had a one-word bypass**: `chr(120)` instead of `'x'` produced
> the same gigabyte plan, because the size estimate could not read a function call and gave up, and
> giving up meant allowing. It was caught within the hour by running the *published* build through
> `npx` from a clean container rather than trusting the local one. The rule now is the one PostgreSQL
> uses to decide whether to fold at all: is the expression constant.
>
> Everything here is 0.1.x because nobody outside this project has run it against their own data.

**The official Postgres MCP server was deprecated in 2024 and still gets 437k downloads a month. Its entire defence is one database-level read-only transaction — and that alone does not stop every write. This is a maintained Rust replacement with defence in depth.**

A drop-in [Model Context Protocol](https://modelcontextprotocol.io) server that lets an AI agent query PostgreSQL — **read-only, enforced at the database level**, with real SQL validation, timeouts, cost limits, OAuth 2.1, and an audit trail. Speaks **Streamable HTTP** and stdio, and negotiates the MCP revision: `2026-07-28` (current, and the default since upstream released it on 2026-08-03), `2025-11-25`, and `2025-06-18` — what most shipping clients still speak today. A client asks for what it knows; it is not negotiated down.

## Try to break it — one command, no database

The read-only guard has an offline mode. Hand it a statement and it says what it decided: no
database, no configuration, nothing installed permanently.

```sh
npx postgres-mcp-hardened --validate "/* comment */ DROP TABLE users"
# REJECT: non-read-only statement: Drop

npx postgres-mcp-hardened --validate "SELECT 1; DROP TABLE users"
# REJECT: multiple statements are forbidden

npx postgres-mcp-hardened --validate "WITH d AS (DELETE FROM t RETURNING *) SELECT * FROM d"
# REJECT: non-read-only statement: non-read-only query (CTE / SELECT INTO / FOR UPDATE)

npx postgres-mcp-hardened --validate "SELECT * FROM orders WHERE id = 1"
# ALLOW
```

**If something that writes comes back `ALLOW`, that is the most valuable thing anyone can send us.**
It needs no working exploit and no write-up — one line of SQL and "this should not be allowed" is a
complete report. Anything that gets past the guard goes through [`SECURITY.md`](SECURITY.md);
everything else is an ordinary issue, and the bar for opening one is *this looks wrong to me*, not
*I am certain*.

The fuzzer is deterministic and prints its seed, so whatever it finds reproduces on a machine that
has never seen yours — a million mutations take about a minute:

```sh
npx postgres-mcp-hardened --fuzz 1000000
# fuzz: 1000000 iterations, seed 1592594996, slowest validation 8 ms
# RESULT: 0 invariant violations
```

For the whole thing against a real database, `docker compose -f examples/docker-compose.yml up -d`
brings up PostgreSQL with sample data and the server in front of it, connecting as a role that holds
`SELECT` and nothing else.

Every bypass found so far lives in the `MUST_REJECT` corpus in `src/validate.rs` and runs on every
commit, recorded with what it cost rather than tidied away. Yours would join them.

## Why

`@modelcontextprotocol/server-postgres` is **deprecated on npm** (last publish December 2024) and
still sees **475,790 downloads in the 30 days to 9 August 2026**. Credit where it is due: its approach is not naive — it
wraps each query in `BEGIN TRANSACTION READ ONLY` and always `ROLLBACK`s, which is a real defence
and one this server now adopts as well.

The problem is that it is the *only* defence, and it is not complete:

- **A read-only transaction does not block every write, and a rollback does not undo everything it
  lets through.** Two separate facts, and the second is the one that matters.

  `gin_clean_pending_list()` runs inside `SET TRANSACTION READ ONLY` and its work **survives the
  rollback**: an index with 25 pending pages has 0 after the transaction is rolled back.
  `pg_backup_start()` puts the session into backup state, survives `DISCARD ALL`, and with the
  default `fast => false` waits for a spread checkpoint while forcing `full_page_writes` on, which
  is a real cost on a busy server. `pg_import_system_collations()` also executes without raising
  `SQLSTATE 25006`, but be careful how much weight you put on it: **that one IS undone by a
  rollback**, so against a server that always rolls back it is a curiosity rather than a bypass.

  Reproduce it, but read the two preconditions first, because without them you will see a zero or an
  error and conclude we made this up. All three need superuser or ownership of the object. And the
  import only restores collations that are *missing*, so something has to be removed first:

  ```sql
  -- as superuser, and note these are three separate transactions: a statement that errors
  -- inside a block aborts the whole block, so they cannot be run as one.
  DELETE FROM pg_collation WHERE oid IN (SELECT oid FROM pg_collation ORDER BY oid DESC LIMIT 200);

  BEGIN READ ONLY;
    DELETE FROM pg_collation WHERE collname LIKE 'zu%';  -- ERROR: cannot execute DELETE ...
  ROLLBACK;

  BEGIN READ ONLY;
    SELECT pg_import_system_collations('pg_catalog');    -- 200, no error
  COMMIT;                                                -- and now the rows are there
  ```

  Both are writes, both are inside a read-only transaction, and one is refused while the other is
  not. That asymmetry is why this server does not treat the transaction as its only defence. It is
  also why the *role* matters more than any of this: every example above needs privileges a
  least-privilege reader does not have, and this server refuses to start as a network listener when
  the role it was given can write. What it cannot control is which connection string somebody pastes
  into a client config, and the usual answer is whichever one they already had.

- **No statement timeout, no cost guard, no row limit** — one query can run until the server gives up.
- **No authentication, no audit trail, no handling of prompt injection** through returned row data.
- One source file of 143 lines, unmaintained since December 2024, no test suite.

This server keeps the rollback, adds AST validation in front of it, and adds the operational layers
the original never had.

## `postgres-mcp-hardened` vs the archived original

| | archived `server-postgres` | **postgres-mcp-hardened** |
|---|---|---|
| Read-only enforcement | `BEGIN TRANSACTION READ ONLY` + `ROLLBACK` — one layer, and PostgreSQL lets some writes through it | **AST validation (sqlparser)** *plus* the same read-only transaction and rollback, *plus* a denylist for functions that write despite it |
| Multi-statement / `DROP` via CTE | reaches the database and is stopped only by the transaction | rejected by the parser, before it reaches the database |
| Statement timeout | none | `statement_timeout` + `idle_in_transaction_session_timeout` enforced |
| Runaway / expensive queries | run unbounded | **`EXPLAIN` cost guard** rejects them before execution |
| Prompt injection via row data | raw output | wrapped `trusted="false"` + delimiter escaping |
| Error messages | leak schema (`relation X does not exist`) | structured, non-leaking, actionable |
| Auth | none | **OAuth 2.1** (RS256 JWT, scope + audience + issuer) |
| Audit | none | tamper-evident hash-chained log |
| Schema as MCP resources | ✅ | ✅ — plus comments, primary and foreign keys |
| Tests / CI | none | unit + end-to-end suites against live PostgreSQL, a deterministic fuzz harness, conformance driven by the official MCP SDK, clippy + `cargo audit` + container build on every push |
| Transport | stdio / deprecated SSE | **Streamable HTTP** + stdio |
| Maintained | ❌ deprecated since 2024 | ✅ |

## Install

Five ways in, in the order most people want them.

**One click**, for a client that accepts `.mcpb` bundles: download
`postgres-mcp-hardened-<y