postgres-mcp

PostgreSQL MCP Server enabling AI assistants to interact with PostgreSQL databases through the Model Context Protocol. Features Code Mode — a revolutionary approach that provides access to all 248 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.

248 Specialized Tools · 23 Resources · 20 AI-Powered Prompts

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

🎯 What Sets Us Apart

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):

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

🔗 Database Connection Scenarios

🛠️ 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 248 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:

Tool Groups (22 Available)

Syntax Reference

🌐 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:

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:

Utility Endpoints

🔐 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:

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

Aliases: PGHOST, PGPORT, PGUSER, PGPASSWORD, PGDATABASE are also supported (standard PostgreSQL client env vars).

Pool Tuning for IAM Auth: For cloud-managed databases with IAM authentication (e.g., AWS RDS, Google Cloud SQL), use --pool-max to control pool size.

CLI Reference

🤖 AI-Powered Prompts

Prompts provide step-by-step guidance for complex database tasks. Instead of figuring out which tools to use and in what order, simply invoke a prompt and follow its workflow — great for learning PostgreSQL best practices or automating repetitive DBA tasks.

This server includes 20 intelligent prompts for guided workflows:

📦 Resources

Resources give you instant snapshots of database state without writing queries. Perfect for quickly checking schema, health, or performance metrics — the AI can read these to understand your database context before suggesting changes.

This server provides 23 resources for structured data access:

🔧 Extension Support

Extension tools gracefully handle cases where extensions are not installed. Extension tool counts include create_extension helpers but exclude Code Mode; the Tool Groups table above adds +1 per group for Code Mode.

Contributing

Contributions are welcome! Please read our Contributing Guidelines before submitting a pull request.

Security

For security concerns, please see our Security Policy.

⚠️ Never commit credentials - Store secrets in environment variables

License

This project is licensed under the MIT License - see the LICENSE file for details.

Code of Conduct

Please read our Code of Conduct before participating in this project.