postgres-mcp

<!-- mcp-name: io.github.neverinfamous/postgres-mcp -->

Last Updated February 22, 2026

PostgreSQL MCP Server enabling AI assistants (AntiGravity, Claude, Cursor, etc.) to interact with PostgreSQL databases through the Model Context Protocol. Features deterministic error handling, connection pooling, HTTP/SSE Transport, OAuth 2.1 authentication, Code Mode, tool filtering, and extension support for citext, ltree, pgcrypto, pg_cron, pg_stat_kcache, pgvector, PostGIS, and HypoPG.

206 specialized tools · 20 resources · 19 AI-powered prompts

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

🎯 What Sets Us Apart

| Feature | Description | | -------------------------------- | --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- | | 206 Specialized Tools | The largest PostgreSQL tool collection for MCP — from core CRUD and native JSONB to pgvector, PostGIS, pg_cron, ltree, pgcrypto, and 8 extension ecosystems | | 20 Observability Resources | Real-time schema, performance metrics, connection pool status, replication lag, vacuum stats, lock contention, and extension diagnostics | | 19 AI-Powered Prompts | Guided workflows for query building, schema design, performance tuning, and extension setup | | Code Mode | Sandboxed JavaScript execution with 70-90% token reduction for multi-step operations | | OAuth 2.1 + Access Control | Enterprise-ready security with RFC 9728/8414 compliance, granular scopes (read, write, admin, full, db:*, table:*:*), and Keycloak integration | | Smart Tool Filtering | 20 tool groups + 15 shortcuts let you stay within IDE limits while exposing exactly what you need | | HTTP Streaming Transport | SSE-based streaming with /mcp, and /health endpoints for remote deployments | | High-Performance Pooling | Built-in connection pooling with health checks for efficient, concurrent database access | | 8 Extension Ecosystems | First-class support for pgvector, PostGIS, pg_cron, pg_partman, pg_stat_kcache, citext, ltree, and pgcrypto | | Deterministic Error Handling | Every tool returns structured {success, error} responses — no raw exceptions, no silent failures, no misleading messages. Agents get actionable context instead of cryptic PostgreSQL codes | | Production-Ready Security | SQL injection protection, parameterized queries, input validation, and sandboxed code execution | | Strict TypeScript | 100% type-safe codebase with 2376 tests and 85.37% coverage | | MCP 2025-11-25 Compliant | Full protocol support with tool safety hints, resource priorities, and progress notifications |

🚀 Quick Start

Prerequisites

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

Installation

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

---

Code Mode: Maximum Efficiency

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

[!TIP] Maximize Token Savings: For the best results, instruct your AI agent to prefer Code Mode over individual tool calls. Add a rule like this to your agent's prompt or system configuration:

"When using postgres-mcp, prefer pg_execute_code (Code Mode) for multi-step database operations to minimize token usage."

This ensures the agent batches operations into single calls instead of making many individual tool calls. See the Code Mode wiki for full API documentation.

[!NOTE] AntiGravity Users: Server instructions are automatically sent to MCP clients during initialization. However, AntiGravity does not currently support MCP server instructions. For optimal Code Mode usage in AntiGravity, manually provide the contents of src/constants/ServerInstructions.ts to the agent in your prompt or user rules.

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

| Mode | Isolation | When to Use | | -------- | ------------------ | ---------------------------- | | vm | Same process | Default, recommended | | worker | Separate V8 thread | Not recommended (incomplete) |

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

---

Development

Clone and install:

git clone https://github.com/neverinfamous/postgresql-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 59 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

| 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] AI IDEs like Cursor have tool limits. With 206 tools available, you MUST use tool filtering to stay within your IDE's limits. We recommend starter (59 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:

| Filter Pattern | Example | Tools | Description | | ---------------- | ------------------------- | ----- | ------------------------- | | Shortcut only | starter | 59 | Use a predefined bundle | | Groups only | core,jsonb,transactions | 47 | Combine individual groups | | Shortcut + Group | starter,+text | 72 | Extend a shortcut | | Shortcut - Tool | starter,-pg_drop_table | 58 | Remove specific tools |

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 counts include Code Mode (pg_execute_code) which is included in all presets by default.

| Shortcut | Tools | Use Case | What's Included | | -------------- | ------ | ------------------------ | -------------------------------------------------------- | | starter | 59 | 🌟 Recommended | Core, trans, JSONB, schema, codemode | | essential | 47 | Minimal footprint | Core, trans, JSONB, codemode | | dev-power | 54 | Power Developer | Core, trans, schema, stats, part, codemode | | ai-data | 60 | AI Data Analyst | Core, JSONB, text, trans, codemode | | ai-vector | 50 | AI/ML with pgvector | Core, vector, trans, part, codemode | | dba-monitor | 59 | DBA Monitoring | Core, monitoring, perf, trans, codemode | | dba-manage | 58 | DBA Management | Core, admin, backup, part, schema, codemode | | dba-stats | 57 | DBA Stats/Security | Core, admin, monitoring, trans, stats, codemode | | geo | 43 | Geospatial Workloads | Core, PostGIS, trans, codemode | | base-core | 59 | Base Building Block | Core, JSONB, trans, schema, codemode | | base-ops | 51 | Operations Block | Admin, monitoring, backup, part, stats, citext, codemode | | ext-ai | 26 | Extension: AI/Security | pgvector, pgcrypto, codemode | | ext-geo | 24 | Extension: Spatial | PostGIS, ltree, codemode | | ext-schedule | 19 | Extension: Scheduling | pg_cron, pg_partman, codemode | | ext-perf | 28 | Extension: Perf/Analysis | pg_stat_kcache, performance, codemode |

Tool Groups (20 Available)

Tool counts include Code Mode (pg_execute_code) which is added to all groups by default.

| Group | Tools | Description | | -------------- | ----- | ----------------------------------------------------------- | | core | 21 | Read/write queries, tables, indexes, convenience/drop tools | | transactions | 8 | BEGIN, COMMIT, ROLLBACK, savepoints | | jsonb | 20 | JSONB manipulation and queries | | text | 14 | Full-text search, fuzzy matching | | performance | 21 | EXPLAIN, query analysis, optimization | | admin | 11 | VACUUM, ANALYZE, REINDEX | | monitoring | 12 | Database sizes, connections, status | | backup | 10 | pg_dump, COPY, restore | | schema | 13 | Schemas, views, sequences, functions, triggers | | partitioning | 7 | Native partition management | | stats | 9 | Statistical analysis | | 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) | | codemode | 1 | Code Mode (sandboxed code execution) |

---

Quick Start: Recommended IDE Configuration

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

Option 1: Starter (59 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 (50 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

| Prefix | Target | Example | Effect | | -------- | -------- | ---------------- | --------------------------------------------- | | (none) | Shortcut | starter | Whitelist Mode: Enable ONLY this shortcut | | (none) | Group | core | Whitelist Mode: Enable ONLY this group | | + | 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 |

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 \
  --postgres "postgres://user:pass@localhost:5432/db" \
  --oauth-enabled \
  --oauth-issuer http://localhost:8080/realms/postgres-mcp \
  --oauth-audience postgres-mcp-client

Environment Variables (Required):

OAUTH_ENABLED=true
OAUTH_ISSUER=http://localhost:8080/realms/postgres-mcp
OAUTH_AUDIENCE=postgres-mcp-client

Environment Variables (Optional — auto-discovered from issuer):

OAUTH_JWKS_URI=http://localhost:8080/realms/postgres-mcp/protocol/openid-connect/certs
OAUTH_CLOCK_TOLERANCE=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.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.

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.

---

⚡ Performance Tuning

| Variable | Default | Description | | ----------------------- | ----------- | -------------------------------------------------- | | MCP_HOST | localhost | Server bind host (0.0.0.0 for containers) | | METADATA_CACHE_TTL_MS | 30000 | Cache TTL for schema metadata (milliseconds) | | LOG_LEVEL | info | Log verbosity: debug, info, warning, error |

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:

| Prompt | Description | Required Groups | Shortcut | | -------------------------- | -------------------------------------------------- | ----------------------------- | -------------- | | pg_query_builder | Construct queries with CTEs and window functions | core | starter | | pg_schema_design | Design schemas with constraints and indexes | core | starter | | pg_performance_analysis | Analyze queries with EXPLAIN and optimization | core, performance | dba-monitor | | pg_migration | Generate migration scripts with rollback support | core | starter | | pg_tool_index | Lazy hydration - compact index of all tools | — | any | | pg_quick_query | Quick SQL query guidance for common operations | core | starter | | pg_quick_schema | Quick reference for exploring database schema | core | starter | | pg_database_health_check | Comprehensive database health assessment | core, performance, monitoring | dba-monitor | | pg_backup_strategy | Enterprise backup planning with RTO/RPO | core, monitoring, backup | dba-manage | | pg_index_tuning | Index analysis and optimization workflow | core, performance | dba-monitor | | pg_extension_setup | Extension installation and configuration guide | core | starter | | pg_setup_pgvector | Complete pgvector setup for semantic search | core, vector | ai-vector | | pg_setup_postgis | Complete PostGIS setup for geospatial operations | core, postgis | geo | | pg_setup_pgcron | Complete pg_cron setup for job scheduling | core | ext-schedule | | pg_setup_partman | Complete pg_partman setup for partition management | core, partman | ext-schedule | | pg_setup_kcache | Complete pg_stat_kcache setup for OS monitoring | core, kcache | ext-perf | | pg_setup_citext | Complete citext setup for case-insensitive text | core, citext | base-ops | | pg_setup_ltree | Complete ltree setup for hierarchical data | core, ltree | ext-geo | | pg_setup_pgcrypto | Complete pgcrypto setup for cryptographic funcs | core, pgcrypto | ext-ai |

---

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

| Resource | URI | Description | | ------------ | ------------------------- | -------------------------------------------------- | | Schema | postgres://schema | Full database schema | | Tables | postgres://tables | Table listing with sizes | | Settings | postgres://settings | PostgreSQL configuration | | Statistics | postgres://stats | Database statistics with stale detection | | Activity | postgres://activity | Current connections | | Pool | postgres://pool | Connection pool status | | Capabilities | postgres://capabilities | Server version, extensions, tool categories | | Performance | postgres://performance | pg_stat_statements query metrics | | Health | postgres://health | Comprehensive database health status | | Extensions | postgres://extensions | Extension inventory with recommendations | | Indexes | postgres://indexes | Index usage with unused detection | | Replication | postgres://replication | Replication status and lag monitoring | | Vacuum | postgres://vacuum | Vacuum stats and wraparound warnings | | Locks | postgres://locks | Lock contention detection | | Cron | postgres://cron | pg_cron job status and execution history | | Partman | postgres://partman | pg_partman partition configuration and health | | Kcache | postgres://kcache | pg_stat_kcache CPU/I/O metrics summary | | Vector | postgres://vector | pgvector columns, indexes, and recommendations | | PostGIS | postgres://postgis | PostGIS spatial columns and index status | | Crypto | postgres://crypto | pgcrypto availability and security recommendations |

---

🔧 Extension Support

| Extension | Purpose | Tools | | -------------------- | ------------------------------ | -------------------------- | | pg_stat_statements | Query performance tracking | pg_stat_statements | | pg_trgm | Text similarity | pg_trigram_similarity | | fuzzystrmatch | Fuzzy matching | pg_fuzzy_match | | hypopg | Hypothetical indexes | pg_index_recommendations | | pgvector | Vector similarity search | 16 vector tools | | PostGIS | Geospatial operations | 15 postgis tools | | pg_cron | Job scheduling | 8 cron tools | | pg_partman | Automated partition management | 10 partman tools | | pg_stat_kcache | OS-level CPU/memory/I/O stats | 7 kcache tools | | citext | Case-insensitive text | 6 citext tools | | ltree | Hierarchical tree labels | 8 ltree tools | | pgcrypto | Hashing, encryption, UUIDs | 9 pgcrypto tools |

Extension tools gracefully handle cases where extensions are not installed. Extension tool counts include create_extension helpers, which are utility tools excluded from the published 206 count.

---

🔥 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

---

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.