# safe-postgres-mcp

**The safe default for giving an AI agent your Postgres.** A zero-config, read-only PostgreSQL [MCP](https://modelcontextprotocol.io) server where *read-only* isn't a regex you hope holds — it's enforced by Postgres itself. Every agent-supplied query runs inside a `BEGIN TRANSACTION READ ONLY` with a statement timeout and a row cap, and is sent over the **extended wire protocol**, which cannot carry a second command. One `npx` command: your agent can explore a schema and run `SELECT`s, but it physically cannot write, cannot stack a second statement, and a runaway query is cancelled within the statement timeout (default 5s) so it can't hang your database.

[![CI](https://github.com/samuel-cabral/safe-postgres-mcp/actions/workflows/ci.yml/badge.svg)](https://github.com/samuel-cabral/safe-postgres-mcp/actions/workflows/ci.yml)
[![License: MIT](https://img.shields.io/badge/License-MIT-blue.svg)](./LICENSE)
![Node](https://img.shields.io/badge/node-%3E%3D20-3c873a.svg)
![TypeScript](https://img.shields.io/badge/TypeScript-strict-3178c6.svg)

---

## Why another Postgres MCP server?

Some "read-only" database tools enforce read-only by scanning the SQL *text* for scary keywords — a filter, and filters get bypassed. Others use the right primitive but stop too early: the original TypeScript reference Postgres MCP server (`@modelcontextprotocol/server-postgres`) wrapped each query in a `BEGIN TRANSACTION READ ONLY` and nothing more — no statement timeout, no row cap, no rejection of multi-statement input, and no bound on how much a single query could buffer into the agent’s context. It has since been **archived** in the official [modelcontextprotocol/servers-archived](https://github.com/modelcontextprotocol/servers-archived) repo with no maintained successor. Meanwhile popular DBA-oriented alternatives (e.g. Postgres MCP "Pro" / crystaldba) default to **unrestricted read/write** — read-only is an opt-in access-mode flag — and ship as a Python/Docker install that is friction for the Node/TS majority of MCP users. (Check each project's current docs; the ecosystem moves fast.)

This server inverts that. The guarantee doesn't live in a string matcher — it lives in the database engine.

**Defense in depth, from the outside in:**

| Layer | What it does | Is it the guarantee? |
|---|---|---|
| Keyword pre-check | Rejects obvious writes (`DELETE FROM …`) and stacked statements *before* a round-trip, with a clear error | No — it's fast-fail UX + a second line |
| `SET LOCAL statement_timeout` | Postgres cancels a runaway query instead of hanging your DB | Resource guardrail |
| Extended wire protocol | Agent SQL is sent as a prepared statement, which by construction carries exactly one command — a smuggled `; COMMIT` is refused by the server, not by a parser of ours | **Yes — for statement stacking** |
| `BEGIN TRANSACTION READ ONLY` | The engine rejects **any** write (`INSERT`/`UPDATE`/`DELETE`/DDL/…) at execution time | **Yes — this is the real guarantee** |
| Row cap + `ROLLBACK` | Truncates oversized result sets; never commits anything | Resource guardrail |

The keyword check is deliberately a *courtesy*, not the wall. Casing tricks, comment smuggling, or a data-modifying CTE that slips past the text analysis still hit a Postgres `READ ONLY` transaction and fail with error `25006`. Belt **and** suspenders — and the suspenders are bolted to the engine.

For your outermost layer, point `DATABASE_URL` at a dedicated least-privilege read-only role (see [`.env.example`](./.env.example)). This server is the second wall behind that role, not a replacement for it.

---

## 60-second quickstart

You need Node **>= 20** and a Postgres connection string. Two of the most common clients:

### Claude Code

```bash
claude mcp add --env DATABASE_URL=postgres://user:pass@host:5432/dbname \
  --transport stdio safe-postgres -- npx -y safe-postgres-mcp
```

> The order matters: keep another flag (`--transport stdio`) *between* `--env KEY=value` and the server name, otherwise the CLI parses the name as another `KEY=value` pair. Everything after `--` is handed to the server untouched.

Verify it's connected:

```bash
claude mcp list
claude mcp get safe-postgres
```

Add `--scope project` to share it with your team via a checked-in `.mcp.json`, or `--scope user` to enable it across all your projects.

### Claude Desktop

Open **Settings → Developer → Edit Config** (or edit the file directly):

- macOS: `~/Library/Application Support/Claude/claude_desktop_config.json`
- Windows: `%APPDATA%\Claude\claude_desktop_config.json`

```json
{
  "mcpServers": {
    "safe-postgres": {
      "command": "npx",
      "args": ["-y", "safe-postgres-mcp"],
      "env": {
        "DATABASE_URL": "postgres://user:pass@host:5432/dbname"
      }
    }
  }
}
```

Fully quit and reopen Claude Desktop to load it. If it doesn't appear, check `~/Library/Logs/Claude/mcp-server-safe-postgres.log` (the server logs all diagnostics to stderr; stdout is reserved for the JSON-RPC channel).

### Running from a local build

No npm publish required — build once and point any client at the absolute path:

```bash
git clone https://github.com/samuel-cabral/safe-postgres-mcp.git
cd safe-postgres-mcp && npm ci && npm run build
```

```bash
claude mcp add --env DATABASE_URL=postgres://user:pass@host:5432/dbname \
  --transport stdio safe-postgres -- node /abs/path/to/safe-postgres-mcp/build/index.js
```

The server fails fast: a missing/invalid `DATABASE_URL` or an unreachable database exits with an actionable message *before* the agent ever calls a tool.

---

## Tools

Five small, curated tools — a focused read-only toolset, deliberately not a 14-tool management suite. Every agent-supplied query runs inside a `READ ONLY` transaction, so the engine refuses a write regardless of what the SQL says; the three introspection tools issue only fixed, parameterized system-catalog `SELECT`s.

| Tool | Description | Parameters | Access |
|---|---|---|---|
| `query` | Run a single read-only SQL statement inside a `READ ONLY` transaction. A `LIMIT` is injected if you omit one; returns rows, field types, and a `truncated` flag. | `sql` *(string, required)* | Executes SQL (read-only tx) |
| `explain_query` | Return the query plan via `EXPLAIN (FORMAT JSON)` **without** running the query. `ANALYZE`/`ANALYSE` is rejected (it would execute the target), and the statement is sent as a prepared statement inside a `READ ONLY` transaction. Inspect cost/joins/index usage before paying for the query. | `sql` *(string, required)* | Plans the query; never executes it |
| `list_schemas` | List all non-system schemas with their owner. | — | Catalog read |
| `list_tables` | List tables, views, and materialized views in a schema with **approximate** row counts (from planner stats — fast, no full scan) and on-disk size. | `schema` *(string, default `public`)* | Catalog read |
| `describe_table` | Full description of one table/view: columns (type, nullability, default), primary key, foreign keys, and indexes. | `table` *(string, required)*, `schema` *(string, default `public`)* | Catalog read |

Introspection tools query the Postgres system catalogs with **parameterized** lookups — identifiers are never string-interpolated into SQL. Every tool returns both human-readable text and typed `structuredContent` matching its output schema, so an agent can consume either.

---

## Safety model

Each row is a thing an agent (or a hostile prompt steering one) could try, and the mechanism that stops it.

| Threat | Mitigation |
|---|---|
| Write / DDL — `INSERT`, `UPDATE`, `DELETE`, `DROP`, `TRUNCATE`, `GRANT`, … | Rejected by the `READ ONLY` transaction at execution time (Postgres `25006`); also fast-failed by the keyword pre-check |
| Stacked-query injection — a second, destructive statement smuggled after a semicolon | Agent SQL travels over the **extended** wire protocol (a prepared statement), which can hold only one command — Postgres itself answers a second one with `42601`. A literal/comment/dollar-quote-aware statement splitter rejects multi-statement input first, for a clearer error; the protocol is the part that cannot be talked around |
| Data-modifying CTE — `WITH x AS (DELETE … RETURNING *) SELECT …` | Dedicated hidden-write scan of CTE bodies, backstopped by the `READ ONLY` transaction |
| Comment / casing smuggling — `/* SELECT */ DELETE …` | Comments stripped (respecting string and dollar-quoted literals) before the head check; real enforcement is in the engine, not the text |
| Runaway query hanging the DB — `SELECT pg_sleep(3600)` | `statement_timeout` (per-transaction `SET LOCAL`, applied inside the tx, + pool-level default) cancels it; default **5s** |
| Oversized result set blowing up memory / agent context | The result is streamed through a **server-side cursor** that stops at `maxRows + 1` rows, so node-pg never buffers more than the cap — even if the query carries its own larger `LIMIT`. Excess is truncated and `truncated: true` is returned; default **500 rows** |
| Accidental persistence | Every transaction ends in `ROLLBACK` — the server never commits |
| Identifier injection via introspection | System-catalog lookups are parameterized; no identifier interpolation |
| Disabling a guardrail via a bad env var | Config is validated with hard ceilings (`MAX_ROWS` ≤ 10,000, `QUERY_TIMEOUT_MS` ≤ 120,000ms); garbage values refuse to start rather than silently weakening a limit |

**What this does not do:** it does not mask or redact PII in rows you are allowed to `SELECT`, and it does not substitute for database-level permissions. Grant the connecting role only what the agent should ever see; this server enforces *read-only* and *bounded*, not *authorized*.

---

## Configuration

All configuration is environment variables — that's the whole point of zero-config. See [`.env.example`](./.env.example).

| Variable | Required | Default | Max | Description |
|---|---|---|---|---|
| `DATABASE_URL` | **Yes** | — | — | Postgres connection string. Point it at a least-privilege read-only role. |
| `POSTGRES_URL` | — | — | — | Fallback used only when `DATABASE_URL` is unset. |
| `QUERY_TIMEOUT_MS` | No | `5000` | `120000` | Per-statement timeout in ms. A slow query is cancelled, not run forever. |
| `MAX_ROWS` | No | `500` | `10000` | Hard cap on rows returned by `query`. Excess rows are truncated and flagged. |

---

## When to use this vs. alternatives

- **Use this** when you want an agent to *safely read* a plain Postgres — RDS, Neon, Supabase-as-plain-PG, or self-hosted — with a `READ ONLY` guarantee enforced by the database, installed with one `npx` command and no YAML, no Docker, no Go binary.
- **Reach for a DBA-oriented tool** (index tuning, health checks, hypothetical indexes) when you're doing performance engineering rather than agent-safe reads — and you're comfortable running it write-enabled.
- **Reach for a cloud-vendor server** when you're fully inside that vendor's ecosystem (its auth, storage, and edge functions) and don't need neutral, portable Postgres access.

---

## Development

```bash
npm ci
npm run build       # tsc -> build/, chmod +x the bin
npm run typecheck   # strict TS, no emit
npm test            # vitest run
```

The suite runs **137 unit + MCP wiring tests** with zero external dependencies. Safety parsing (comment stripping, statement splitting, literal-aware CTE-write detection, LIMIT/FETCH row-cap logic, dollar-quote tags) is exercised directly, and the MCP layer is tested end-to-end over an in-memory client/server transport — rejection paths run fully without a live database, because the safety check fires before the connection pool is ever touched.

A further **24 integration tests** run against a real Postgres and are skipped automatically unless a DB is provided:

```bash
DATABASE_URL=postgres://user:pass@localhost:5432/db npm test
```

Among them is a block that tests the central claim **without going through the text filter at all**: it opens a `READ ONLY` transaction on a raw connection and hands Postgres a plain `CREATE`/`INSERT`/`UPDATE`/`DELETE`/`TRUNCATE`, asserting SQLSTATE `25006` each time. Those tests would still pass if the keyword layer had a hole — which is the point: they measure the engine, not the filter. A second block pins known injection payloads (a `$`-in-identifier fake dollar tag, an unterminated literal) as regressions.

The write attempts are refused by the engine before Postgres checks permissions, so a least-privilege read-only role — or a read-only replica — is still a perfectly safe target. CI (GitHub Actions) type-checks, builds, and tests on Node 20, 22, and 24 for every push and PR, **with a `postgres:16-alpine` service container**, so the integration suite runs in CI rather than skipping itself; a step fails the build if those tests ever report as skipped.

Built on the official [`@modelcontextprotocol/sdk`](https://github.com/modelcontextprotocol/typescript-sdk) (STDIO transport, protocol `2025-06-18`) and [`pg`](https://node-postgres.com/). Written in strict TypeScript.

---

## About

Built by **Samuel Cabral** — senior full-stack engineer (Node.js · TypeScript · NestJS · React · PostgreSQL). I build MCP servers and Claude Code / agent integrations, with a bias toward safety, tests, and tooling that a team can trust in production.

Available for MCP and Claude Code integration work.

- GitHub: [github.com/samuel-cabral](https://github.com/samuel-cabral)
- Email: [samuelcabral.mail@gmail.com](mailto:samuelcabral.mail@gmail.com)

Licensed under [MIT](./LICENSE).