# postgres-mcp-query-tool

An MCP server that gives an AI agent **scoped, safe access to your Postgres databases**. Configure as many named connections as you like — each with its own access mode, row cap, and timeout — so the same agent can have full read/write on `local` and strict **read-only** on `prod` without ever confusing the two.

> Ask *"query prod for the latest user join time"* and the agent sees that `prod` is read-only, routes a `SELECT` through the read tool, and physically cannot write to it.

Inspired by [PlanetScale's MCP server query executor](https://github.com/planetscale/mcp-server/blob/main/src/lib/query-executor.ts), reworked for plain Postgres with per-connection access control.

## Features

- **Multiple named connections** — define `local`, `prod`, `staging`, etc. in one config.
- **Per-connection access mode** — `read_only` or `read_write`. Read-only connections refuse every write.
- **Defense-in-depth read-only enforcement** — two independent layers:
  1. A SQL validator rejects writes up front with a clear message.
  2. Reads run inside a `BEGIN READ ONLY` transaction, so **Postgres itself** rejects writes even if the validator is ever fooled. (For a third layer, point the connection at a Postgres role with no write grants.)
- **Hard row caps** — per-connection `max_rows`. Exceeding it is a clean error, never a silent truncation.
- **Configurable timeouts** — per-connection `timeout_seconds` (default 30s) enforced via `statement_timeout`, plus a client-side backstop that cancels a wedged query. Long queries never hang.
- **Two-phase confirmation for destructive writes** — DDL (`CREATE`/`DROP`/`ALTER`/…) and `DELETE` are refused until the agent re-issues them with `confirm_destructive: true` after you approve the exact statement.
- **Dangerous statements blocked outright** — `TRUNCATE`, and `DELETE`/`UPDATE` with no `WHERE` or an always-true `WHERE` (e.g. `WHERE 1=1`).
- **Credentials stay secret** — `list_connections` never reveals URLs; the server never logs SQL or rows.
- **Inline or file config** — pass config as a file path **or** drop the whole JSON straight into the MCP definition. No separate file required.

## Tools

| Tool | Purpose |
|------|---------|
| `list_connections` | Lists each connection's `name`, `access`, `max_rows`, `timeout_seconds`. No URLs or credentials. The agent calls this first to learn which connections accept writes. |
| `execute_read_query` | Runs a read-only query (`SELECT`, `WITH`, `SHOW`, `EXPLAIN`, `VALUES`, `TABLE`) inside a `READ ONLY` transaction. Streams up to `max_rows + 1` via a server-side cursor; the extra row triggers `row_limit_exceeded`. |
| `execute_write_query` | Runs a write against a `read_write` connection. Refused on `read_only`. Enforces the destructive-statement and confirmation rules above. |

## Install

```bash
npm install
npm run build
```

## Configure

Each connection takes:

| Field | Required | Notes |
|-------|----------|-------|
| `url` *or* `url_env` | one of | A literal Postgres URL, or the name of an env var holding it. Use `url_env` for production secrets. |
| `access` | yes | `"read_only"` or `"read_write"`. |
| `max_rows` | no | Hard cap on returned rows. Default `1000`. |
| `timeout_seconds` | no | Per-query timeout. Default `30`. |
| `default_schema` | no | Schema (or comma-separated list) set as the `search_path` for every query, so unqualified table names resolve without a prefix — e.g. `"public"`. Each name must be a plain SQL identifier. |

Example (`config.example.json`):

```json
{
  "connections": {
    "local": {
      "url": "postgres://postgres:postgres@localhost:5432/postgres",
      "access": "read_write",
      "max_rows": 10000,
      "timeout_seconds": 30
    },
    "prod": {
      "url_env": "PROD_DB_URL",
      "access": "read_only",
      "max_rows": 1000,
      "timeout_seconds": 30,
      "default_schema": "public"
    }
  }
}
```

## Wire into your MCP client

Add to your MCP client config (e.g. `~/.claude.json` under `mcpServers`). There are two ways to supply the config.

### Option A — inline config (no separate file)

Drop the whole config into the `POSTGRES_MCP_CONFIG` env var. Nothing else on disk:

```json
{
  "mcpServers": {
    "postgres-query": {
      "command": "node",
      "args": ["/absolute/path/to/postgres-mcp-query-tool/dist/index.js"],
      "env": {
        "PROD_DB_URL": "postgres://reader:secret@prod-host:5432/app",
        "POSTGRES_MCP_CONFIG": "{\"connections\":{\"local\":{\"url\":\"postgres://postgres:postgres@localhost:5432/postgres\",\"access\":\"read_write\",\"max_rows\":10000},\"prod\":{\"url_env\":\"PROD_DB_URL\",\"access\":\"read_only\",\"max_rows\":1000}}}"
      }
    }
  }
}
```

You can also pass it on the command line with `--config-json '<json>'`.

### Option B — config file

```json
{
  "mcpServers": {
    "postgres-query": {
      "command": "node",
      "args": [
        "/absolute/path/to/postgres-mcp-query-tool/dist/index.js",
        "--config",
        "/absolute/path/to/config.json"
      ],
      "env": {
        "PROD_DB_URL": "postgres://reader:secret@prod-host:5432/app"
      }
    }
  }
}
```

**Config source precedence** (first match wins): `--config-json` → `POSTGRES_MCP_CONFIG` env var → `--config <path>` → `./config.json`.

## Run standalone

```bash
node dist/index.js --config /absolute/path/to/config.json
# or
POSTGRES_MCP_CONFIG='{"connections":{...}}' node dist/index.js
```

## Error shape

Tool errors come back with `isError: true` and a JSON body:

```json
{ "error": "row_limit_exceeded", "message": "...", "details": { "max_rows": 1000, "connection": "prod" } }
```

Codes: `row_limit_exceeded`, `query_timeout`, `validation_failed`, `needs_confirmation`, `read_only_connection`, `unknown_connection`, `postgres_error`.

## Security notes

- **Keep production credentials out of the config file.** Use `url_env` (or the inline `POSTGRES_MCP_CONFIG` env var) so secrets live in the client's environment, not in a checked-in file.
- **Read-only enforcement is real defense-in-depth.** Even if the validator is bypassed, `BEGIN READ ONLY` makes Postgres reject the write. For the strongest guarantee, also connect with a Postgres role that has no write privileges.
- **The server never logs SQL or returned rows** — only startup/shutdown diagnostics on stderr.

## Acknowledgements

Inspired by [PlanetScale's MCP server](https://github.com/planetscale/mcp-server) and its [query executor](https://github.com/planetscale/mcp-server/blob/main/src/lib/query-executor.ts).