psql-mcp-server

An MCP server that wraps the PostgreSQL psql CLI, giving MCP clients (Claude, Codex, etc.) access to the full backslash-command surface of psql — schema inspection, queries, EXPLAIN plans, COPY, roles, extensions and more — behind a small, safety-gated tool set.

Every operation shells out to the real psql binary; this project is a process wrapper, not a driver. Read-only access is the default: writes, local file I/O, and shell escapes are all disabled unless explicitly enabled.

Prerequisites

• Node.js 20+
• The PostgreSQL client tools (psql) installed locally. On macOS: brew install libpq (and set PSQL_MCP_PSQL_PATH if it isn't on PATH).
• psql 18+ is recommended: it supports \restrict, which this server uses to disable all backslash commands while running SQL you pass to run_query / run_statement, closing off \! shell-escape injection at the source. On psql < 18 the same protection is enforced by scanning the SQL text for embedded backslash commands before it is sent.

Quick install (recommended)

npm install @web4w3/psql-mcp-server

No cloning or build step needed — the package ships pre-compiled. Wire it into your MCP client's mcp.json:

{
  "mcpServers": {
    "psql": {
      "command": "npx",
      "args": ["-y", "@web4w3/psql-mcp-server"],
      "env": {
        "DATABASE_URL": "postgres://user:password@host:5432/dbname"
      }
    }
  }
}

See mcp.json.example for a full multi-connection setup.

Setup (from source)

1. Install dependencies: npm install
2. Build: npm run build
3. Wire it into your MCP client's mcp.json — see mcp.json.example for a full multi-connection setup, or the minimal single-database example below.

{
  "mcpServers": {
    "psql": {
      "command": "node",
      "args": ["/absolute/path/to/psql-mcp-server/dist/index.js"],
      "env": {
        "DATABASE_URL": "postgres://user:password@host:5432/dbname"
      }
    }
  }
}

Configuration

All configuration is read from the env block of your mcp.json.

| Variable | Purpose | |---|---| | DATABASE_URL | Single connection, simplest setup. | | PSQL_MCP_CONNECTIONS | JSON registry of named connections (see below). Takes priority over DATABASE_URL. | | PSQL_MCP_DEFAULT_CONNECTION | Which registry entry tools use when connection is omitted. Defaults to the first entry. | | PSQL_MCP_ALLOW_WRITES | Enable INSERT/UPDATE/DELETE/DDL. Default false. | | PSQL_MCP_ALLOW_FILE_IO | Enable \copy, \i, \o, \e, large objects, etc. Default false. | | PSQL_MCP_ALLOW_SHELL | Enable \! shell escape. Default false. | | PSQL_MCP_STATEMENT_TIMEOUT_MS | Postgres statement_timeout applied to every session. Default 30000. | | PSQL_MCP_COMMAND_TIMEOUT_MS | Wall-clock cap on the psql child process. Default 60000. | | PSQL_MCP_MAX_ROWS | Row cap on returned result sets. Default 1000. | | PSQL_MCP_MAX_OUTPUT_BYTES | Byte cap on psql's stdout. Default 1000000. | | PSQL_MCP_PSQL_PATH | Path to the psql binary if not on PATH. |

Multiple connections

"PSQL_MCP_CONNECTIONS": "{\"dev\":{\"url\":\"postgres://...\",\"allowWrites\":true},\"prod\":{\"host\":\"prod.internal\",\"database\":\"appdb\",\"user\":\"app_ro\",\"passwordCommand\":\"vault kv get -field=password secret/appdb/ro\",\"sslmode\":\"verify-full\",\"allowWrites\":false}}"

Each connection accepts url or host/port/database/user, plus password, passwordCommand, passwordFile, sslmode, description, and per-connection overrides allowWrites / allowFileIo / allowShell / statementTimeoutMs. A per-connection override always wins over the global flag — this is how you keep prod hard-read-only even if writes are enabled globally for dev.

Credentials

Anything placed in mcp.json is stored in plaintext on disk. In order of preference:

1. passwordCommand — shells out to a secret manager (Vault, 1Password, security find-generic-password, ...) and reads the password from stdout. The secret never touches mcp.json.
2. passwordFile — a libpq ~/.pgpass-style file (chmod 600).
3. password — inline in mcp.json. Simplest, least safe.

Credentials are always passed to psql via environment variables (PGPASSWORD, PGPASSFILE), never via argv, since process arguments are visible to any local user via ps.

Safety model

Every backslash command is classified by risk before it runs:

| Risk | Examples | Gate | |---|---|---| | safe | \d, \dt, \df, \l, \du, \conninfo, \pset, ... | always allowed | | writes | \gexec, \password, EXPLAIN ANALYZE | allowWrites | | fileIo | \copy, \i, \o, \e, \lo_import, \w | allowFileIo | | shell | \! | allowShell | | session | \c, \q, \cd, \setenv, \unrestrict | never allowed (would corrupt the server's connection/session model) |

Plain SQL sent to run_query/run_statement is protected too:

• The session runs with default_transaction_read_only=on for run_query and explain_query (without analyze), so PostgreSQL itself — not a regex — rejects any write attempt.
• On psql 18+, SQL is prefixed with \restrict <random-key-per-call>, which disables every backslash command until a matching \unrestrict. A \! smuggled inside a string or comment cannot execute, because it can't guess the key.
• On psql < 18, the SQL text is lexically scanned for embedded backslash commands (respecting quotes, comments and dollar-quoting) and rejected if it contains anything not on the safe list.
• run_query and explain_query require exactly one top-level statement — a semicolon-separated second statement could otherwise execute outside of EXPLAIN, or corrupt structured (CSV/JSON) parsing. run_statement accepts a multi-statement batch and renders it with psql's own aligned output, which correctly separates each statement's result.

Enabling a capability is global by default but can be scoped per connection, so you can allow writes on dev while keeping prod permanently read-only regardless of the global flag.

Tools

| Tool | Wraps | Notes | |---|---|---| | list_connections | — | Lists configured connections and their capabilities. Passwords redacted. | | server_info | \conninfo, version() | Connectivity + version check. | | run_query | plain SQL | Read-only. Table/JSON/CSV/expanded output. | | run_statement | plain SQL | Requires allowWrites. Optional single-transaction wrapping. | | explain_query | EXPLAIN | analyze:true requires allowWrites (it executes the query). | | list_databases | \l+ | | | list_schemas | \dn+ | | | list_objects | \dt \dv \dm \di \ds \dE \d | Filter by kind + name pattern. | | describe_object | \d+ NAME | Columns, indexes, constraints, triggers, privileges. | | list_functions | \df family | Functions, aggregates, procedures, triggers, window functions. | | show_definition | \sf+ \sv+ | Function/view source. | | list_roles_privileges | \du \dp \ddp \drg \drds | | | list_types_extensions | \dT \dD \dx \do \dC \dO \dc \db \dA \dy \dF* \dR* \dX \dl \dconfig \dL \de* \dP \da \dd | One tool, what selects the listing. | | copy_data | \copy | Requires allowFileIo (and allowWrites when importing). | | psql_help | --help=*, \h | Offline reference; no capability required. | | psql_meta | any backslash command | Escape hatch for everything above plus \crosstabview, \watch, \errverbose, \bind, pipeline-mode commands, etc. Same risk classification applies. |

Together, the dedicated tools plus psql_meta reach every documented psql 18 backslash command.

Development

npm run dev          # run against $DATABASE_URL with tsx, no build step
npm run typecheck     # tsc --noEmit
npm run build         # compile to dist/
npm run test:integration   # end-to-end tests against a real PostgreSQL server

The integration tests need a reachable database (TEST_DATABASE_URL, default postgres://postgres:[email protected]:55433/testdb). A throwaway instance:

docker run -d --rm --name psqlmcp-test -e POSTGRES_PASSWORD=testpw \
  -e POSTGRES_DB=testdb -p 55433:5432 postgres:17

They drive the server exactly as an MCP client would — over the stdio JSON-RPC transport — and assert both functional coverage (every tool against a seeded schema) and safety enforcement (every gated capability is confirmed blocked by default and confirmed to work once enabled).