postgres-mcp

Last updated January 25, 2026

PostgreSQL MCP Server enabling AI assistants (AntiGravity, Claude, Cursor, etc.) to interact with PostgreSQL databases through the Model Context Protocol. Features 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.

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

Docker Hub • npm Package • MCP Registry

🚀 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

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

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`

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

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

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.

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

Filter Pattern	Example	Tools	Description
Shortcut only	`starter`	58	Use a predefined bundle
Groups only	`core,jsonb,transactions`	45	Combine individual groups
Shortcut + Group	`starter,+text`	69	Extend a shortcut
Shortcut - Tool	`starter,-pg_drop_table`	57	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)

Shortcut	Tools	Use Case	What's Included
`starter`	58	🌟 Recommended	Core, trans, JSONB, schema, codemode
`essential`	46	Minimal footprint	Core, trans, JSONB, codemode
`dev-power`	53	Power Developer	Core, trans, schema, stats, part, codemode
`ai-data`	59	AI Data Analyst	Core, JSONB, text, trans, codemode
`ai-vector`	47	AI/ML with pgvector	Core, vector, trans, part, codemode
`dba-monitor`	58	DBA Monitoring	Core, monitoring, perf, trans, codemode
`dba-manage`	57	DBA Management	Core, admin, backup, part, schema, codemode
`dba-stats`	56	DBA Stats/Security	Core, admin, monitoring, trans, stats, codemode
`geo`	42	Geospatial Workloads	Core, PostGIS, trans, codemode
`base-core`	58	Base Building Block	Core, JSONB, trans, schema, codemode
`base-ops`	51	Operations Block	Admin, monitoring, backup, part, stats, citext, codemode
`ext-ai`	24	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)

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`	15	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 (58 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 (47 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
`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	14 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.

🔥 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
✅ 203 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.

Name		Name	Last commit message	Last commit date
Latest commit History 299 Commits
.github		.github
releases		releases
src		src
.env.example		.env.example
.gitignore		.gitignore
.trivyignore		.trivyignore
CHANGELOG.md		CHANGELOG.md
CODE_OF_CONDUCT.md		CODE_OF_CONDUCT.md
CONTRIBUTING.md		CONTRIBUTING.md
DOCKER_README.md		DOCKER_README.md
Dockerfile		Dockerfile
LICENSE		LICENSE
README.md		README.md
SECURITY.md		SECURITY.md
eslint.config.js		eslint.config.js
package-lock.json		package-lock.json
package.json		package.json
server.json		server.json
tsconfig.json		tsconfig.json
vitest.config.ts		vitest.config.ts

License

neverinfamous/postgresql-mcp

Folders and files

Latest commit

History

Repository files navigation

postgres-mcp

🚀 Quick Start

Prerequisites

Installation

Development

⚡ MCP Client Configuration

Cursor IDE / Claude Desktop

Using Environment Variables (Recommended)

🔗 Database Connection Scenarios

Code Mode: Maximum Efficiency

Disabling Code Mode (Non-Admin Users)

Isolation Modes

Security

🛠️ Tool Filtering

What Can You Filter?

Shortcuts (Predefined Bundles)

Tool Groups (20 Available)

Quick Start: Recommended IDE Configuration

Option 1: Starter (58 Essential Tools)

Option 2: AI Vector (47 Tools + pgvector)

Syntax Reference

🔐 OAuth 2.1 Authentication

Configuration

OAuth Scopes

RFC Compliance

⚡ Performance Tuning

🤖 AI-Powered Prompts

📦 Resources

🔧 Extension Support

🔥 Core Capabilities

🏢 Enterprise Features

🏆 Why Choose postgres-mcp?

Contributing

Security

License

Code of Conduct

About

Topics

Resources

License

Code of conduct

Contributing

Security policy

Uh oh!

Stars

Watchers

Forks

Releases 3

Uh oh!

Contributors 2

Uh oh!

Languages