postgres-mcp

Last updated December 23, 2025

A PostgreSQL MCP Server that enables AI assistants (Claude, Cursor, etc.) to interact with PostgreSQL databases through the Model Context Protocol. Provides 202 specialized tools, 20 resources, and 19 AI-powered prompts and includes OAuth 2.1 authentication, code mode, connection pooling, tool filtering, plus support for citext, ltree, pgcrypto, pg_cron, pg_stat_kcache, pgvector, PostGIS, HypoPG, and advanced PostgreSQL features.

✅ Under Development - 202 tools, 20 resources, and 19 prompts.

🚀 Quick Start

Prerequisites

• Node.js 18+
• PostgreSQL 12-18 (tested with PostgreSQL 18.1)
• npm or yarn

Installation

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

# Clone and install
git clone https://github.com/neverinfamous/postgres-mcp.git
cd postgres-mcp
npm install

# Build
npm run build

# Run checks
npm run lint && npm run typecheck

# Test CLI
node dist/cli.js info
node dist/cli.js list-tools

⚡ MCP Client Configuration

Cursor IDE / Claude Desktop

{
  "mcpServers": {
    "postgres-mcp": {
      "command": "node",
      "args": [
        "C:/path/to/postgres-mcp/dist/cli.js",
        "--postgres", "postgres://user:password@localhost:5432/database",
        "--tool-filter", "starter"
      ]
    }
  }
}

[!TIP] The starter shortcut provides 58 tools including Code Mode for token-efficient operations. All presets include Code Mode by default. See Tool Filtering to customize.

Using Environment Variables (Recommended)

{
  "mcpServers": {
    "postgres-mcp": {
      "command": "node",
      "args": [
        "C:/path/to/postgres-mcp/dist/cli.js",
        "--tool-filter", "starter"
      ],
      "env": {
        "POSTGRES_HOST": "localhost",
        "POSTGRES_PORT": "5432",
        "POSTGRES_USER": "your_user",
        "POSTGRES_PASSWORD": "your_password",
        "POSTGRES_DATABASE": "your_database"
      }
    }
  }
}

🔗 Database Connection Scenarios

Code Mode: Maximum Efficiency

Code Mode (pg_execute_code) dramatically reduces token usage (70–90%) and is included by default in all presets.

Disabling Code Mode (Non-Admin Users)

If you don't have admin access or prefer individual tool calls, exclude codemode:

{
  "args": ["--tool-filter", "starter,-codemode"]
}

Isolation Modes

The vm mode is fully functional and is the default. No configuration needed.

Security

• Requires admin OAuth scope
• Blocked: require(), process, eval(), filesystem
• Rate limited: 60 executions/minute

📖 Full documentation: docs/CODE_MODE.md

🛠️ Tool Filtering

[!IMPORTANT] AI IDEs like Cursor have tool limits. With 203 tools available, you MUST use tool filtering to stay within your IDE's limits. We recommend starter (58 tools) as a starting point. Code Mode is included in all presets by default for 70-90% token savings on multi-step operations.

What Can You Filter?

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

All shortcuts and tool groups include Code Mode (pg_execute_code) by default for token-efficient operations. To exclude it, add -codemode to your filter: --tool-filter cron,pgcrypto,-codemode

Shortcuts (Predefined Bundles)

Tool Groups (20 Available)

Quick Start: Recommended IDE Configuration

Add one of these configurations to your IDE's MCP settings file:

Option 1: Starter (49 Essential Tools)

Best for: General PostgreSQL database work - CRUD operations, JSONB, schema management.

{
  "mcpServers": {
    "postgres-mcp": {
      "command": "node",
      "args": [
        "/path/to/postgres-mcp/dist/cli.js",
        "--transport",
        "stdio",
        "--tool-filter",
        "starter"
      ],
      "env": {
        "POSTGRES_HOST": "localhost",
        "POSTGRES_PORT": "5432",
        "POSTGRES_USER": "your_username",
        "POSTGRES_PASSWORD": "your_password",
        "POSTGRES_DATABASE": "your_database"
      }
    }
  }
}

Option 2: AI Vector (46 Tools + pgvector)

Best for: AI/ML workloads with semantic search and vector similarity.

⚠️ Prerequisites: Requires pgvector extension installed in your PostgreSQL database.

{
  "mcpServers": {
    "postgres-mcp-ai": {
      "command": "node",
      "args": [
        "/path/to/postgres-mcp/dist/cli.js",
        "--transport",
        "stdio",
        "--tool-filter",
        "ai-vector"
      ],
      "env": {
        "POSTGRES_HOST": "localhost",
        "POSTGRES_PORT": "5432",
        "POSTGRES_USER": "your_username",
        "POSTGRES_PASSWORD": "your_password",
        "POSTGRES_DATABASE": "your_database"
      }
    }
  }
}

Customization Notes:

• Replace /path/to/postgres-mcp/ with your actual installation path
• Update credentials (your_username, your_password, etc.) with your PostgreSQL credentials
• For Windows: Use forward slashes in paths (e.g., C:/postgres-mcp/dist/cli.js) or escape backslashes (C:\\postgres-mcp\\dist\\cli.js)
• Extension tools gracefully handle cases where extensions are not installed

Syntax Reference

Legacy Syntax (still supported): If you start with a negative filter (e.g., -base,-extensions), it assumes you want to start with all tools enabled and then subtract.

🔐 OAuth 2.1 Authentication

When using HTTP/SSE transport, oauth 2.1 authentication can protect your MCP endpoints.

Configuration

CLI Options:

node dist/cli.js \
  --transport http \
  --port 3000 \
  --oauth-enabled \
  --oauth-issuer http://localhost:8080/realms/db-mcp \
  --oauth-audience postgres-mcp

Environment Variables:

# Required
OAUTH_ENABLED=true
OAUTH_ISSUER=http://localhost:8080/realms/db-mcp
OAUTH_AUDIENCE=postgres-mcp

# Optional (auto-discovered from issuer)
OAUTH_JWKS_URI=http://localhost:8080/realms/db-mcp/protocol/openid-connect/certs
OAUTH_CLOCK_TOLERANCE=60

OAuth Scopes

Access control is managed through OAuth scopes:

RFC Compliance

This implementation follows:

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

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

⚡ Performance Tuning

Tip: Lower METADATA_CACHE_TTL_MS for development (e.g., 5000), or increase it for production with stable schemas (e.g., 300000 = 5 min).

🤖 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 19 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 20 resources for structured data access:

🔧 Extension Support

Extension tools gracefully handle cases where extensions are not installed.

🏷️ Tool Annotations

All 199 tools include Tool Annotations (MCP SDK 1.25+), providing UX hints to MCP clients about tool behavior:

🔥 Core Capabilities

• 📊 Full SQL Support - Execute any PostgreSQL query with parameter binding
• 🔍 JSONB Operations - Native JSONB functions and path queries
• 🔐 Connection Pooling - Efficient connection management with health checks
• 🎛️ Tool Filtering - Control which operations are exposed
• ⚡ Performance Tools - EXPLAIN ANALYZE, buffer analysis, index hints
• 🗺️ PostGIS Support - Geospatial queries and spatial indexes
• 🧠 pgvector Support - AI/ML vector similarity search

🏢 Enterprise Features

• 🔐 OAuth 2.1 Authentication - RFC 9728/8414 compliant
• 🛡️ Tool Filtering - Control which database operations are exposed
• 📈 Monitoring - Process lists, replication lag, cache hit ratios

🏆 Why Choose postgres-mcp?

✅ TypeScript Native - Full type safety with strict mode
✅ 199 Specialized Tools - Comprehensive PostgreSQL coverage
✅ Tool Annotations - UX hints for read-only, destructive, and idempotent operations
✅ Connection Pooling - Efficient PostgreSQL connection management
✅ Extension Support - pgvector, PostGIS, pg_stat_statements, pg_cron
✅ Tool Filtering - Stay within AI IDE tool limits
✅ Modern Architecture - Built on MCP SDK 1.25+

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.