# safe-postgres-mcp

An MCP server that lets a model explore and query PostgreSQL, built so that **it cannot modify
anything** — not by convention, but because three independent layers each have to fail before a
write becomes possible.

Runs locally over stdio (Claude Desktop today), with the transport isolated in a single file so an
HTTP transport can be added later for a ChatGPT connector.

## The three layers

**1. Startup privilege audit.** Before a single tool is registered, the server interrogates the
connected role. If the role is too powerful, the process exits non-zero and the tools never exist.
There is no degraded mode.

**2. Lexical guard.** Every statement must be a single read-only command. Comments and literals are
stripped before analysis, so a semicolon hidden inside `'a;b'` is not a statement separator and a
`DROP` hidden behind a `--` comment is still caught. Queries also travel over the extended query
protocol (`queryMode: 'extended'`), under which the backend refuses to parse more than one command —
a protocol-level defence that does not depend on our lexer being correct.

> Passing `values: []` is **not** enough to get that protection. `pg`'s `requiresPreparation()`
> returns `values.length > 0`, so an empty array falls back to the simple query protocol, which
> accepts `SELECT 1; SELECT 2`. `queryMode` must be set explicitly — see `extended()` in `src/db.ts`.

**3. Read-only transaction.** Every query runs inside `BEGIN TRANSACTION READ ONLY` with a statement
timeout, and is **always rolled back** — including on success. The session additionally sets
`default_transaction_read_only=on`.

### Why layer 1 is not redundant

A read-only transaction is strong but, in PostgreSQL's own words, "does not prevent all writes to
disk". It reliably blocks `INSERT`/`UPDATE`/`DELETE`, all DDL, and those statements nested inside
functions, all with SQLSTATE `25006`. It does **not** block:

| Escape | Why it works | Required privilege |
|---|---|---|
| `dblink` / `postgres_fdw` | Opens a *separate* connection with its own read-write transaction | `EXECUTE` on the extension |
| `COPY (SELECT …) TO PROGRAM` | Runs a shell command; never writes to a table | superuser or `pg_execute_server_program` |
| `plpython3u`, `plperlu` | Arbitrary filesystem and network I/O inside a `SELECT` | `USAGE` on the language |
| `pg_terminate_backend()` | Not a write, so read-only does not apply | `pg_signal_backend` |

Every one of those needs a privilege the audit refuses. **The audit is what turns the read-only
transaction from a strong default into a guarantee.**

### What the audit checks

Always fatal — these let a query escape the read-only transaction:

- `SUPERUSER`, `BYPASSRLS`, `REPLICATION`
- membership in `pg_execute_server_program`, `pg_write_server_files`, `pg_read_server_files`
- a reachable `dblink`, `postgres_fdw`, `file_fdw`, or untrusted procedural language

Fatal by default, downgraded to a warning by `ALLOW_WRITABLE_ROLE=true` — the read-only transaction
does block these, so overriding is defensible:

- `INSERT` / `UPDATE` / `DELETE` / `TRUNCATE` on any reachable table
- `CREATE` on any reachable schema or database
- `CREATEDB`, `CREATEROLE`

Privileges are resolved with `has_table_privilege()` and friends, which account for inheritance
through role membership and through `PUBLIC` — something scanning `information_schema` misses.

## Setup

```bash
npm install
```

### Create a read-only role

```sql
CREATE ROLE mcp_reader LOGIN PASSWORD 'choose-a-strong-password';
GRANT USAGE ON SCHEMA tenant_acme TO mcp_reader;
GRANT SELECT ON ALL TABLES IN SCHEMA tenant_acme TO mcp_reader;
ALTER DEFAULT PRIVILEGES IN SCHEMA tenant_acme GRANT SELECT ON TABLES TO mcp_reader;

-- PostgreSQL 14 and older grant CREATE on the public schema to PUBLIC by default:
REVOKE CREATE ON SCHEMA public FROM PUBLIC;
```

If you skip this, the server tells you exactly which privilege blocked it and prints this script
scoped to your schema.

### Claude Desktop (Windows + WSL)

Dependencies are installed under WSL, so `node_modules` holds Linux binaries (`@esbuild/linux-x64`).
Claude Desktop runs on Windows and would use `node.exe`, which cannot load them — so the config
invokes the server *through* WSL rather than directly.

The config file lives in different places depending on how Claude Desktop was installed:

| Install | Path |
|---|---|
| Regular installer | `%APPDATA%\Claude\claude_desktop_config.json` |
| Microsoft Store (MSIX) | `%LOCALAPPDATA%\Packages\Claude_<id>\LocalCache\Roaming\Claude\claude_desktop_config.json` |

MSIX packages virtualise `%APPDATA%`, so the Store build never creates `%APPDATA%\Claude`. On this
machine the real path is:

```
C:\Users\lucas\AppData\Local\Packages\Claude_pzs8sxrjxfjjc\LocalCache\Roaming\Claude\
```

That file also holds the app's own preferences, so add `mcpServers` to it — do not overwrite it.

```json
{
  "mcpServers": {
    "postgres-safe": {
      "command": "wsl.exe",
      "args": [
        "-d", "Ubuntu", "--",
        "bash", "-lc",
        "cd /mnt/c/Users/lucas/Desktop/mcpYt && exec npx tsx src/index.ts"
      ],
      "env": {
        "DATABASE_URL": "postgres://mcp_reader:password@host:5432/mydb",
        "WSLENV": "DATABASE_URL/u:PG_SCHEMA/u:ALLOW_WRITABLE_ROLE/u:MAX_ROWS/u:STATEMENT_TIMEOUT_MS/u"
      }
    }
  }
}
```

`WSLENV` is what carries the variables across the Windows/Linux boundary — the `/u` suffix means
"forward this from Win32 into WSL". Keeping the URL in the `env` block rather than inline in the
shell command means passwords containing quotes or `$` need no escaping.

If you would rather run entirely on Windows, run `npm install` from a Windows shell so npm fetches
`@esbuild/win32-x64`, then use `"command": "npx"` with a Windows path. Note that the two installs
overwrite each other in the same `node_modules`, so pick one.

The connection string lives in the client config, so it never enters the conversation.

### Claude Code

`.mcp.json` in the project root already configures the server for Claude Code running inside WSL —
no `wsl.exe` wrapper needed. Fill in `DATABASE_URL` and it is picked up on the next session.

## Configuration

| Variable | Default | Meaning |
|---|---|---|
| `DATABASE_URL` | *(required)* | Connection string |
| `PG_SCHEMA` | auto-detected | Tenant schema. Required only when the role can reach more than one |
| `ALLOW_WRITABLE_ROLE` | `false` | Downgrade the write-grant checks to warnings |
| `MAX_ROWS` | `200` | Row cap per query |
| `STATEMENT_TIMEOUT_MS` | `10000` | Per-query timeout |

### Multi-tenant scoping

The server anchors to exactly one schema and every tool operates inside it, so unqualified table
names resolve there. The role's `USAGE` grants are the source of truth: if it can reach exactly one
non-system schema, that schema is detected automatically; if it can reach several, `PG_SCHEMA`
picks between them. The tenant boundary is enforced by PostgreSQL's grants, not by parsing SQL for
cross-schema references.

## Tools

| Tool | Purpose |
|---|---|
| `query` | Run one read-only statement |
| `list_tables` | Tables, views and matviews with estimated rows, size and comments |
| `describe_table` | Columns, types, defaults, PK, outbound and inbound FKs, constraints, indexes |
| `list_relationships` | The schema's whole foreign-key graph, for writing correct JOINs |
| `explain_query` | Execution plan, never `ANALYZE` |
| `get_database_info` | Connection details and which safety checks passed |

## Tests

```bash
npm run test:unit          # lexical guard, no database needed
TEST_ADMIN_URL=postgres://owner:pw@host:5432/db npm run test:integration
```

The integration suite creates a throwaway `mcp_test` schema with a read-only role and a writable
role, then drops both. Its load-bearing test issues an `INSERT` with a role that genuinely holds
`INSERT`, bypassing the lexical guard entirely, and asserts PostgreSQL rejects it with `25006` —
proving layer 3 works on its own rather than assuming it.