postgres-mcp

PostgreSQL MCP Server binding the Model Context Protocol to a secure PostgreSQL sandbox.

Features Code Mode — a revolutionary approach that provides access to all 278 tools through a secure, true V8 isolate (worker_threads), eliminating the massive token overhead of multi-step tool calls. Also includes schema introspection, migration tracking, smart tool filtering, deterministic error handling, connection pooling, HTTP/SSE Transport, OAuth 2.1 authentication, and extension support for citext, ltree, pgcrypto, pg_cron, pg_stat_kcache, pgvector, PostGIS, and HypoPG.

278 Specialized Tools · 24 Resources · 21 AI-Powered Prompts

Docker Hub • npm Package • MCP Registry • Wiki • Tool Reference • Changelog

🎯 What Sets Us Apart

Feature	Description
Code Mode (V8 Isolate)	Massive Token Savings: Execute complex, multi-step operations inside a secure, true V8 isolate (worker_threads). Stop burning tokens on back-and-forth tool calls and reduce your AI overhead by up to 90%.
Deterministic Error Handling	No more cryptic database errors causing AI hallucinations. We intercept and translate raw SQL exceptions into clear, actionable advice so your agent knows exactly how to recover without guessing.
278 Token-Optimized Tools	The largest PostgreSQL toolset on the MCP registry. Every query uses zero-cost token estimation and smart dataset truncation, ensuring agents always see the big picture without blowing their context windows.
OAuth 2.1 + Granular Control	Real enterprise security. Authenticate via OAuth 2.1 and control exactly who can read, write, or administer your database with precision scopes mapped down to the specific tool layer.
Audit Trails & Semantic Diffing	Total accountability. Track exactly what your AI is doing with detailed JSON logs, automatically snapshot schemas before mutations, and confidently review semantic row-by-row diffs before restoring data.
24 Resources & 21 Prompts	Instant database meta-awareness. Agents automatically read real-time health, performance, and replication metrics, and can invoke built-in prompt workflows for query tuning and schema design.
Introspection & Migrations	Prevent costly mistakes. Let your AI simulate the cascade impact of schema changes, safely order foreign-key updates, and track migration history automatically.
8 Extension Ecosystems	Ready for advanced workloads. First-class API support for pgvector (AI search), PostGIS (geospatial), pg_cron, pgcrypto, and more—all strictly typed and validated out of the box.
Smart Tool Filtering	Give your agent exactly what it needs without overflowing IDE limits. Dynamically compile your server with any combination of our 25 distinct tool groups.
Enterprise Infrastructure	Built for production. Blazing fast (millions of ops/sec), protected against SQL injection, features high-performance connection pooling, and supports both Streamable HTTP and Legacy SSE protocols simultaneously.

Suggested Rule (Add to AGENTS.md, GEMINI.md, etc)

MCP TOKEN MANAGEMENT:

• Token Visibility: When interacting with postgres-mcp, always monitor the _meta.tokenEstimate (or metrics.tokenEstimate in Code Mode) returned in tool responses.
• Audit Resource: Use the postgres://audit resource to review session-level token consumption and identify high-cost operations.
• Proactive Efficiency: If operations are consuming high token counts, prefer code mode and proactively use limit parameters.

🚀 Quick Start

Prerequisites

• PostgreSQL 12-18 (tested with PostgreSQL 18.1)
• Docker (recommended) or Node.js 24+ (LTS)

Docker (Recommended)

docker pull writenotenow/postgres-mcp:latest

Add to your ~/.cursor/mcp.json or Claude Desktop config:

{
  "mcpServers": {
    "postgres-mcp": {
      "command": "docker",
      "args": [
        "run",
        "--rm",
        "-i",
        "-e",
        "POSTGRES_HOST",
        "-e",
        "POSTGRES_PORT",
        "-e",
        "POSTGRES_USER",
        "-e",
        "POSTGRES_PASSWORD",
        "-e",
        "POSTGRES_DATABASE",
        "writenotenow/postgres-mcp:latest",
        "--tool-filter",
        "codemode",
        "--audit-log",
        "/tmp/postgres-logs/audit.jsonl"
      ],
      "env": {
        "POSTGRES_HOST": "host.docker.internal",
        "POSTGRES_PORT": "5432",
        "POSTGRES_USER": "your_username",
        "POSTGRES_PASSWORD": "your_password",
        "POSTGRES_DATABASE": "your_database"
      }
    }
  }
}

Note for Docker: Use host.docker.internal to connect to PostgreSQL running on your host machine.

📖 Full Docker guide: DOCKER_README.md · Docker Hub

npm

npm install -g @neverinfamous/postgres-mcp
postgres-mcp --transport stdio --postgres postgres://user:password@localhost:5432/database

From Source

git clone https://github.com/neverinfamous/postgres-mcp.git
cd postgres-mcp
npm install
npm run build
node dist/cli.js --transport stdio --postgres postgres://user:password@localhost:5432/database

Development

See From Source above for setup. After cloning:

npm run lint && npm run typecheck  # Run checks
npm run bench                      # Run performance benchmarks
node dist/cli.js info              # Test CLI
node dist/cli.js list-tools        # List available tools

Benchmarks

Run npm run bench to execute the performance benchmark suite (10 files, 93+ scenarios) powered by Vitest Bench. Use npm run bench:verbose for detailed table output.

Performance Highlights (Node.js 24, Windows 11):

Area	Benchmark	Throughput
Tool Dispatch	Map.get() single tool lookup	~6.9M ops/sec
WHERE Validation	Simple clause (combined regex fast-path)	~3.7M ops/sec
Identifier Sanitization	validateIdentifier()	~4.4M ops/sec
Auth — Token Extraction	extractBearerToken()	~2.7M ops/sec
Auth — Scope Checking	hasScope()	~5.3M ops/sec
Rate Limiting	Single IP check	~2.3M ops/sec
Logger	Filtered debug (no-op path)	~5.4M ops/sec
Schema Parsing	MigrationInitSchema.parse()	~2.1M ops/sec
Metadata Cache	Cache hit + miss pattern	~1.7M ops/sec
Sandbox Creation	CodeModeSandbox.create() cold start	~863 ops/sec

Full benchmark results and methodology are available on the Performance wiki page.

🔗 Database Connection Scenarios

Scenario	Host to Use	Example Connection String
PostgreSQL on host machine	localhost or host.docker.internal	postgres://user:pass@localhost:5432/db
PostgreSQL in Docker	Container name or network	postgres://user:pass@postgres-container:5432/db
Remote/Cloud PostgreSQL	Hostname or IP	postgres://user:pass@db.example.com:5432/db

Provider	Example Hostname
AWS RDS PostgreSQL	your-instance.xxxx.us-east-1.rds.amazonaws.com
Google Cloud SQL	project:region:instance (via Cloud SQL Proxy)
Azure PostgreSQL	your-server.postgres.database.azure.com
Supabase	db.xxxx.supabase.co
Neon	ep-xxx.us-east-1.aws.neon.tech

🛠️ Tool Filtering

[!IMPORTANT] All tool groups include Code Mode (pg_execute_code) by default. To exclude it, add -codemode to your filter: --tool-filter cron,pgcrypto,-codemode

💡 Code Mode (--tool-filter codemode) is the recommended configuration — it exposes pg_execute_code, a secure, true V8 isolate sandbox providing access to all 278 tools' worth of capability with up to 90% token savings. See Tool Filtering for alternatives.

• Requires admin OAuth scope — execution is logged for audit

📖 See Full Installation Guide →

What Can You Filter?

The --tool-filter argument accepts groups or tool names — mix and match freely:

Filter Pattern	Example	Description
Groups only	core,jsonb,transactions	Combine individual groups
Tool names	pg_read_query,pg_explain	Custom tool selection
Group + Tool	core,+pg_stat_statements	Extend a group
Group - Tool	core,-pg_drop_table	Remove specific tools

Tool Groups (25 Available)

Group	Tools	Description
codemode	1	Code Mode (sandboxed code execution) 🌟 Recommended
core	21	Read/write queries, tables, indexes, convenience/drop tools
transactions	9	BEGIN, COMMIT, ROLLBACK, savepoints, status
jsonb	21	JSONB manipulation, queries, and pretty-print
text	14	Full-text search, fuzzy matching
performance	25	EXPLAIN, query analysis, optimization, diagnostics, anomaly detection
admin	12	VACUUM, ANALYZE, REINDEX, insights
monitoring	12	Database sizes, connections, status
backup	13	pg_dump, COPY, restore, audit backups
schema	13	Schemas, views, sequences, functions, triggers
introspection	7	Dependency graphs, cascade simulation, schema analysis
migration	7	Schema migration tracking and management
partitioning	7	Native partition management
stats	20	Statistical analysis, window functions, outlier detection
vector	17	pgvector (AI/ML similarity search)
postgis	16	PostGIS (geospatial)
cron	9	pg_cron (job scheduling)
partman	11	pg_partman (auto-partitioning)
kcache	8	pg_stat_kcache (OS-level stats)
citext	7	citext (case-insensitive text)
ltree	9	ltree (hierarchical data)
pgcrypto	10	pgcrypto (encryption, UUIDs)
security	10	Security auditing, SSL, firewall, data masking, privilege analysis
roles	13	Role management, privileges, membership, RLS
docstore	10	JSONB document collections (NoSQL-style CRUD, indexing)

Syntax Reference

Prefix	Target	Example	Effect
(none)	Group	core	Whitelist Mode: Enable ONLY this group
(none)	Tool	pg_read_query	Whitelist Mode: Enable ONLY this tool
+	Group	+vector	Add tools from this group to current set
-	Group	-admin	Remove tools in this group from current set
+	Tool	+pg_explain	Add one specific tool
-	Tool	-pg_drop_table	Remove one specific tool

🌐 HTTP/SSE Transport (Remote Access)

For remote access, web-based clients, or HTTP-compatible MCP hosts, use the HTTP transport:

node dist/cli.js \
  --transport http \
  --port 3000 \
  --postgres "postgres://user:pass@localhost:5432/db"

Docker:

docker run --rm -p 3000:3000 \
  -e POSTGRES_URL=postgres://user:pass@host:5432/db \
  writenotenow/postgres-mcp:latest \
  --transport http --port 3000

The server supports two MCP transport protocols simultaneously, enabling both modern and legacy clients to connect:

Streamable HTTP (Recommended)

Modern protocol (MCP 2025-03-26) — single endpoint, session-based:

Method	Endpoint	Purpose
POST	/mcp	JSON-RPC requests (initialize, tools/list, etc.)
GET	/mcp	SSE stream for server notifications
DELETE	/mcp	Session termination

Sessions are managed via the Mcp-Session-Id header.

Stateless Mode

For serverless/stateless deployments where sessions are not needed:

node dist/cli.js --transport http --port 3000 --stateless --postgres "postgres://..."

In stateless mode: GET /mcp returns 405, DELETE /mcp returns 204, /sse and /messages return 404. Each POST /mcp creates a fresh transport.

Legacy SSE (Backward Compatibility)

Legacy protocol (MCP 2024-11-05) — for clients like Python mcp.client.sse:

Method	Endpoint	Purpose
GET	/sse	Opens SSE stream, returns /messages?sessionId=<id> endpoint
POST	/messages?sessionId=<id>	Send JSON-RPC messages to the session

Utility Endpoints

Method	Endpoint	Purpose
GET	/health	Health check (bypasses rate limiting, always available for monitoring)

🔐 Authentication

postgres-mcp supports two authentication mechanisms for HTTP transport:

Simple Bearer Token (--auth-token)

Lightweight authentication for development or single-tenant deployments:

node dist/cli.js --transport http --port 3000 --auth-token my-secret --postgres "postgres://..."

# Or via environment variable
export MCP_AUTH_TOKEN=my-secret
node dist/cli.js --transport http --port 3000 --postgres "postgres://..."

Clients must include Authorization: Bearer my-secret on all requests. /health and / are exempt. Unauthenticated requests receive 401 with WWW-Authenticate: Bearer headers per RFC 6750.

OAuth 2.1 (Enterprise)

Full OAuth 2.1 with RFC 9728/8414 compliance for production multi-tenant deployments:

node dist/cli.js \
  --transport http \
  --port 3000 \
  --postgres "postgres://user:pass@localhost:5432/db" \
  --oauth-enabled \
  --oauth-issuer http://localhost:8080/realms/postgres-mcp \
  --oauth-audience postgres-mcp-client

Additional flags: --oauth-jwks-uri <url> (auto-discovered if omitted), --oauth-clock-tolerance <seconds> (default: 60).

OAuth Scopes

Access control is managed through OAuth scopes:

Scope	Access Level
read	Read-only queries (SELECT, EXPLAIN)
write	Read + write operations
admin	Full administrative access
full	Grants all access
db:{name}	Access to specific database
schema:{name}	Access to specific schema
table:{schema}:{table}	Access to specific table

RFC Compliance

This implementation follows:

• RFC 9728 — OAuth 2.1 Protected Resource Metadata
• RFC 8414 — OAuth 2.1 Authorization Server Metadata
• RFC 7591 — OAuth 2.1 Dynamic Client Registration

The server exposes metadata at /.well-known/oauth-protected-resource.

Note for Keycloak users: Add an Audience mapper to your client (Client → Client scopes → dedicated scope → Add mapper → Audience) to include the correct aud claim in tokens.

[!NOTE] Per-tool scope enforcement: Scopes are enforced at the tool level — each tool group maps to a required scope (read, write, or admin). When OAuth is enabled, every tool invocation checks the calling token's scopes before execution. When OAuth is not configured, scope checks are skipped entirely.

[!WARNING] HTTP without authentication: When using --transport http without enabling OAuth or --auth-token, all clients have full unrestricted access. Always enable authentication for production HTTP deployments. See SECURITY.md for details.

Priority: When both --auth-token and --oauth-enabled are set, OAuth 2.1 takes precedence. If neither is configured, the server warns and runs without authentication.

🔧 Configuration

Environment Variables