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, 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

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):

{
  "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:

{
  "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

{
  "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

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:

{ "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 and its query executor.