postgres-mcp

A read-only PostgreSQL MCP server with a SQL security policy. It exposes a small set of read-only tools and validates every query against an allowlist of commands, a deny-list of keywords, an automatic row limit, and a per-statement timeout.

Read-only is enforced twice: by the SQL validator and by the connection itself, which is opened with default_transaction_read_only=on and a statement_timeout. A statement that slips past the validator still cannot write and cannot run forever.

Install

pip install -e ".[dev]"        # editable, with test deps
# or from a published index:
pip install postgres-mcp

Related MCP server: pgmcp

Configure

Copy config.example.yaml to config.yaml (gitignored) and fill it in. Any value may reference an environment variable as ${VAR} — keep secrets in the environment, never in a committed file.

postgres:
  host: "localhost"
  port: 5432
  dbname: "postgres"
  user: "${POSTGRES_MCP_USER}"
  password: "${POSTGRES_MCP_PASSWORD}"

security:
  query_timeout_ms: 30000
  max_rows_limit: 1000
  allowlist_commands: ["SELECT", "EXPLAIN", "SHOW", "WITH", "ANALYZE"]
  deny_keywords: ["DROP", "DELETE", "INSERT", "UPDATE", "ALTER",
                  "CREATE", "TRUNCATE", "GRANT", "REVOKE"]
  log_queries: true

Resolution order: $POSTGRES_MCP_CONFIG (explicit path), then ./config.yaml, then ./config.example.yaml in the working directory.

Cooperation with masstrade-agent

postgres-mcp can share its connection source with masstrade-agent: point it at the same ~/.wren/profiles.yml that masstrade-agent maintains, and both servers read the readonly credentials from MASSTRADE_RO_USER / MASSTRADE_RO_PASSWORD (process environment or ~/.wren/.env).

wren:
  profiles: true          # read ~/.wren/profiles.yml
  profile: null           # null = follow `active`; or a tenant SID (e.g. "APR")

With wren.profiles: true the postgres: block is ignored, and the select_profile tool is registered. The typical flow is:

1. masstrade-agent → select_database APR (upserts the APR profile).

2. postgres-mcp → select_profile APR (re-points at the same database).

3. postgres-mcp → query "SELECT ..." (read-only against APR).

When no config file is found at all and ~/.wren/profiles.yml exists, wren-profiles mode is enabled automatically.

No profile chosen yet is not a startup failure

A client may launch this server before select_database has picked a tenant. Exiting there is expensive: the client sees a closed connection and respawns the process on every reconnect attempt — a spawn loop that shows up as "failed initial connection … parking" in the client's log.

So a configuration that cannot yet produce a connection is a startable state, not an error. The server starts either way, and every tool call that needs a connection answers with the actionable message instead of a dead process:

{ "error": "No profile selected yet. Available: APR, EAC, EAL, EEE, ESK. …" }

The profiles file is re-read on each resolution, so select_database APR on masstrade-agent, a hand-edited active:, or select_profile APR all take effect without restarting this server. Profile names are matched case-insensitively, and the password never appears in any tool output.

The strict, fail-fast behaviour is still one keyword away — load_config() without allow_unresolved=True raises as before, which is what the tests and any caller that knows a connection must exist should use.

Run

postgres-mcp            # stdio, via the installed entry point
# or
python -m postgres_mcp

Tools

Security model

1. Allowlist — the first command must be on allowlist_commands.

2. Deny-list — any whole-word deny_keywords entry anywhere in the SQL rejects the statement.

3. Row limit — a LIMIT is appended to SELECT when the query has none (SHOW/EXPLAIN are left untouched, since they reject LIMIT).

4. Timeout — statement_timeout is set per connection from query_timeout_ms.

5. Query log — when log_queries is true, each query is logged (truncated) to stderr.

The validator is deliberately lightweight (string + regex, no SQL parser), so a denied keyword inside a string literal or quoted identifier can cause a false rejection — safe by design, but noted. The connection-level guards remain the authoritative boundary.

MCP client wiring

Point any MCP client at the stdio server. With Cursor or Hermes the entry is a server whose command is postgres-mcp (or python -m postgres_mcp); the user/password come from the environment of the process that launches it.

License

MIT — see LICENSE.