Postgresql Ssh Mcp

npm version npm downloads license

A secure PostgreSQL MCP server with built-in SSH tunneling. Connect to databases through bastion hosts automatically — no manual ssh -L required.

Features

• Dual Transport — STDIO for Claude Desktop, Streamable HTTP for ChatGPT
• SSH Tunneling — Built-in tunnel with auto-reconnect and TOFU (trust on first use)
• Read-Only by Default — Safe for production; enable writes explicitly
• OAuth Support — Auth0 integration for secure ChatGPT connections
• Connection Pooling — Efficient resource management with configurable limits

---

Architecture

---

Quick Start

Claude Desktop (STDIO)

Add to your Claude Desktop config:

Platform	Config Location
macOS	~/Library/Application Support/Claude/claude_desktop_config.json
Windows	%APPDATA%/Claude/claude_desktop_config.json

{
  "mcpServers": {
    "postgres": {
      "command": "npx",
      "args": ["-y", "@zlash65/postgresql-ssh-mcp"],
      "env": {
        "DATABASE_URI": "postgresql://user:password@localhost:5432/mydb"
      }
    }
  }
}

ChatGPT (Streamable HTTP)

DATABASE_URI="postgresql://user:pass@localhost:5432/mydb" npx @zlash65/postgresql-ssh-mcp-http

Then configure ChatGPT to connect to https://your-subdomain.example.com/mcp.

Note: ChatGPT requires HTTPS. Use a tunnel (ngrok, Cloudflare Tunnel) for local testing.

---

Available Tools

Query Tools

Tool	Description
execute_query	Execute SQL with parameterized queries. Results capped by MAX_ROWS.
explain_query	Get EXPLAIN plans in text, JSON, YAML, or XML format. Supports ANALYZE.

Schema Tools

Tool	Description
list_schemas	List database schemas. Excludes system schemas by default.
list_tables	List tables with row counts and sizes. Optionally include views.
describe_table	Get columns, constraints, and indexes for a table.
list_databases	List all databases with owner, encoding, and size.

Admin Tools

Tool	Description
get_connection_status	Get pool stats, tunnel state, and connection info.
list_active_connections	Show active connections from pg_stat_activity.
list_long_running_queries	Find queries running longer than a threshold.
get_database_version	Get PostgreSQL server version.
get_database_size	Get database size and largest tables.
get_table_stats	Get vacuum/analyze stats and scan counts for a table.

---

Environment Variables

Database Connection

Variable	Required	Default	Description
DATABASE_URI	Yes*	—	Full connection string (e.g., postgresql://user:pass@host:5432/db)
DATABASE_HOST	Yes*	—	Database hostname
DATABASE_PORT	No	5432	Database port
DATABASE_NAME	Yes*	—	Database name
DATABASE_USER	Yes*	—	Database username
DATABASE_PASSWORD	Yes*	—	Database password

*Either DATABASE_URI or all individual connection variables are required.

SSL Configuration

Variable	Default	Description
DATABASE_SSL	Auto	true or false. Auto-enabled for non-localhost.
DATABASE_SSL_CA	—	Path to CA certificate bundle
DATABASE_SSL_REJECT_UNAUTHORIZED	true	Set false to allow self-signed certificates

SSH Tunnel

Variable	Required	Default	Description
SSH_ENABLED	No	false	Set true to enable SSH tunneling
SSH_HOST	Yes*	—	SSH server hostname
SSH_PORT	No	22	SSH server port
SSH_USER	Yes*	—	SSH username
SSH_PRIVATE_KEY_PATH	Yes**	—	Path to private key file
SSH_PRIVATE_KEY_PASSPHRASE	No	—	Passphrase for encrypted keys
SSH_PASSWORD	Yes**	—	SSH password (alternative to key)
SSH_STRICT_HOST_KEY	No	true	Verify host key against known_hosts
SSH_TRUST_ON_FIRST_USE	No	true	Auto-add unknown hosts (when strict is enabled)
SSH_KNOWN_HOSTS_PATH	No	~/.ssh/known_hosts	Custom known_hosts file
SSH_KEEPALIVE_INTERVAL	No	10000	Keepalive interval in milliseconds
SSH_MAX_RECONNECT_ATTEMPTS	No	5	Max reconnect attempts (-1 for unlimited)

*Required when SSH_ENABLED=true **Either SSH_PRIVATE_KEY_PATH or SSH_PASSWORD is required

Query Behavior

Variable	Default	Description
READ_ONLY	true	Block data modifications. Set false to allow writes.
MAX_ROWS	1000	Maximum rows returned per query
QUERY_TIMEOUT	30000	Query timeout in milliseconds
MAX_CONCURRENT_QUERIES	10	Maximum concurrent queries
POOL_DRAIN_TIMEOUT_MS	5000	Timeout for draining pool during reconnect

HTTP Server (Streamable HTTP only)

Variable	Default	Description
PORT	3000	HTTP server port
MCP_HOST	0.0.0.0	HTTP server bind address
MCP_AUTH_MODE	none	Authentication mode: none or oauth
MCP_STATELESS	true	Stateless mode (each request re-initializes)
MCP_SERVER_POOL_SIZE	4	Server instances for stateless mode
MCP_SESSION_TTL_MINUTES	30	Session TTL for stateful mode
MCP_SESSION_CLEANUP_INTERVAL_MS	300000	Session cleanup interval
MCP_ALLOWED_ORIGINS	—	Comma-separated allowed CORS origins (* for any)
MCP_ALLOWED_HOSTS	—	Comma-separated allowed Host headers

OAuth (Auth0)

Variable	Required	Description
AUTH0_DOMAIN	Yes*	Auth0 tenant domain (e.g., tenant.us.auth0.com)
AUTH0_AUDIENCE	Yes*	Auth0 API identifier / audience
MCP_RESOURCE_DOCUMENTATION	No	URL to API documentation (RFC 9728)

*Required when MCP_AUTH_MODE=oauth

---

Documentation

Guide	Description
STDIO Setup	Claude Desktop and local development
Streamable HTTP	HTTP server setup and configuration
Server Setup	Deploy to production with nginx and SSL
ChatGPT Setup	Complete Auth0 OAuth setup for ChatGPT

---

Docker

STDIO Server

docker build --target runtime -t postgresql-mcp .
docker run -e DATABASE_URI="postgresql://..." postgresql-mcp

HTTP Server

docker build --target runtime-http -t postgresql-mcp-http .
docker run -p 3000:3000 -e DATABASE_URI="postgresql://..." postgresql-mcp-http

---

Development

# Install dependencies
npm install

# Build
npm run build

# Run tests
npm test

# Type check
npm run typecheck

# Lint
npm run lint