postgres-mcp-server

An MCP server for safely querying PostgreSQL from an LLM. Read-only by default, multi-connection with named aliases, Postgres-session-enforced safety.

Quickstart

Create ~/.config/postgres-mcp/config.json:

{
  "connections": {
    "local": {
      "url_env": "LOCAL_DATABASE_URL",
      "mode": "read"
    }
  }
}

Add to your MCP client config (Claude Desktop, Cursor, Windsurf, Zed):

{
  "mcpServers": {
    "postgres": {
      "command": "npx",
      "args": ["-y", "@arieffian/postgres-mcp-server"],
      "env": { "LOCAL_DATABASE_URL": "postgres://user:pw@localhost/db" }
    }
  }
}

Restart the client and ask: "Ping the local Postgres and list its tables."

Modes

Every connection declares its mode statically in config. Escalation requires editing config and restarting.

Mode	SELECT	INSERT/UPDATE/DELETE	DDL	Notes
read	✓	✗	✗	Default. SET default_transaction_read_only = on at the session level.
write	✓	✓	✗	Writes must go through begin_transaction → execute → commit.
admin	✓	✓	✓	Same tx flow as write; DDL also permitted.

Tools

Meta

• ping, list_connections

SQL

• query — read-only SELECT via server-side cursor
• begin_transaction, commit, rollback — tx lifecycle
• execute — INSERT/UPDATE/DELETE/DDL inside an open tx

Schema introspection (Phase 2, new in 0.2.0)

• list_databases, list_schemas
• list_tables — includes regular, partitioned, and foreign tables (via kind field); row count is clamped to 0 for never-analyzed tables
• list_indexes, list_constraints — per-table catalog listings
• list_functions — excludes functions installed by extensions
• describe_table — composite: columns, PK, FKs, indexes, constraints in one call

Observability — ships in Phase 3.

Safety

Five layers — see docs/safety.md. Highlights:

• Read-only enforced by the Postgres session, not by parsing SQL — we do not trust our own parser.
• Statement timeout per connection (default 30s).
• Every SELECT wrapped in a server-side cursor; results capped by row count and byte size.
• Writes require an explicit transaction; no autocommit.
• Bound parameter values are never logged. Credentials in URLs are redacted.

Configuration

Discovery order (first hit wins):

1. --config <path> CLI flag
2. $POSTGRES_MCP_CONFIG env var
3. $XDG_CONFIG_HOME/postgres-mcp/config.json (fallback ~/.config/postgres-mcp/config.json)
4. ./postgres-mcp.config.json

Contributing

Requires Node ≥ 20. Local dev: npm install, npm test. Integration tests use testcontainers and need a working Docker daemon.

Publishing

Set NPM_TOKEN in the repo's GitHub Actions secrets. Changesets automatically opens a release PR on push to main; merging it publishes to npm with provenance.

License

MIT