MCP Server for PostgreSQL

PostgreSQL MCP Server

MCP server for PostgreSQL database operations. Works with Claude Code, Claude Desktop, Cursor, and any MCP-compatible client.

Language Versions

This repository contains both TypeScript and Python implementations:

Version	Directory	Status	MCP protocol	Installation
TypeScript	/typescript	✅ Recommended (Smithery)	2026-07-28 (SDK v2)	npm install -g @javiagui/postgresql-mcp
Python	/python	✅ Stable	2026-07-28 (SDK v2)	pipx install postgresql-mcp

Note: The TypeScript version is used for Smithery deployments. Both versions provide identical functionality.

Protocol compatibility: both servers speak the 2026-07-28 revision and are dual-era — clients that still open with the 2025-era initialize handshake are served exactly as before, so no client needs upgrading. The TypeScript package requires patched Node.js 22.23.4 or 24.22.1; the Python package requires Python 3.11+ (CI uses patched 3.11.17 and 3.12.15).

Features

• Query Execution: Execute SQL queries with read-only protection by default
• Schema Exploration: List schemas, tables, views, and functions
• Table Analysis: Describe structure, indexes, constraints, and statistics
• Performance Tools: EXPLAIN queries and analyze table health
• Security First: SQL injection prevention, credential protection, read-only by default
• MCP Prompts: Guided workflows for exploration, query building, and documentation
• MCP Resources: Browsable database structure as markdown

Quick Start

TypeScript (Recommended for Smithery)

# Install globally
npm install -g @javiagui/postgresql-mcp

# Or run directly with npx
npx @javiagui/postgresql-mcp

Python

# Install
pipx install postgresql-mcp

# Configure Claude Code
claude mcp add postgres -s user \
  -e POSTGRES_HOST=localhost \
  -e POSTGRES_USER=your_user \
  -e POSTGRES_PASSWORD=your_password \
  -e POSTGRES_DB=your_database \
  -- postgresql-mcp

Full Installation Guide - Includes database permissions setup, remote connections, and troubleshooting.

Configuration

Environment Variables

Variable	Required	Default	Description
POSTGRES_HOST		localhost	Database host
POSTGRES_PORT		5432	Database port
POSTGRES_USER	✅		Database user
POSTGRES_PASSWORD	✅		Database password
POSTGRES_DB	✅		Database name
POSTGRES_SSLMODE		prefer	SSL mode
ALLOW_WRITE_OPERATIONS		false	Enable INSERT/UPDATE/DELETE
QUERY_TIMEOUT		30	Query timeout (seconds)
MAX_ROWS		1000	Maximum rows returned

Multiple databases

Most real work touches more than one database. Instead of editing this config and restarting your client every time, declare several connections and pick one per call by alias:

POSTGRES_CONNECTIONS='[
  {"alias":"local","host":"localhost","user":"me","password":"…","database":"app","allowWrite":true},
  {"alias":"staging","url":"postgresql://reader:…@staging.example.com:5432/app"},
  {"alias":"analytics","url":"postgresql://reader:…@warehouse:6543/metrics?sslmode=require","default":true}
]'

• list_databases returns the configured aliases with host, database name, write permission and which is the default. Credentials are never returned.
• Every other tool takes an optional database argument naming an alias. Omit it and you get the default connection — so an existing single-database setup keeps working with no changes at all.
• allowWrite is per connection, falling back to ALLOW_WRITE_OPERATIONS. A production replica stays read-only while a local database allows writes.
• default: true marks the connection used when database is omitted; otherwise it is the first one declared. At most one connection may be marked default.
• Each connection accepts either discrete fields (host, port, user, password, database, sslmode) or a url DSN — and discrete fields override the DSN, so you can reuse a URL and change one part of it.
• One pool per alias, opened lazily: a configured but unused database never opens a socket.

POSTGRES_CONNECTIONS and the plain POSTGRES_* variables are mutually exclusive. When the first is set the others are ignored, so there is never a question about which one won.

Credentials only ever come from the environment. The database argument names an alias; there is deliberately no way to pass a host, user, password or connection string as a tool argument, because that would put secrets into the conversation. A test enforces this.

Claude Code CLI

# TypeScript version
claude mcp add postgres -s user \
  -e POSTGRES_HOST=localhost \
  -e POSTGRES_USER=your_user \
  -e POSTGRES_PASSWORD=your_password \
  -e POSTGRES_DB=your_database \
  -- npx @javiagui/postgresql-mcp

# Python version
claude mcp add postgres -s user \
  -e POSTGRES_HOST=localhost \
  -e POSTGRES_USER=your_user \
  -e POSTGRES_PASSWORD=your_password \
  -e POSTGRES_DB=your_database \
  -- postgresql-mcp

Cursor IDE

Add to ~/.cursor/mcp.json:

{
  "mcpServers": {
    "postgres": {
      "command": "npx",
      "args": ["@javiagui/postgresql-mcp"],
      "env": {
        "POSTGRES_HOST": "localhost",
        "POSTGRES_PORT": "5432",
        "POSTGRES_USER": "your_user",
        "POSTGRES_PASSWORD": "your_password",
        "POSTGRES_DB": "your_database"
      }
    }
  }
}

Available Tools (15 total)

Every tool below except list_databases accepts an optional database argument naming a configured connection — see Multiple databases.

Query Execution

Tool	Description
query	Execute read-only SQL queries against the database
execute	Execute write operations (INSERT/UPDATE/DELETE) when enabled
explain_query	Get EXPLAIN plan for query optimization

Schema Exploration

Tool	Description
list_schemas	List all schemas in the database
list_tables	List tables in a specific schema
describe_table	Get table structure (columns, types, constraints)
list_views	List views in a schema
describe_view	Get view definition and columns
list_functions	List functions and procedures

Performance & Analysis

Tool	Description
table_stats	Get table statistics (row count, size, bloat)
list_indexes	List indexes for a table
list_constraints	List constraints (PK, FK, UNIQUE, CHECK)

Database Info

Tool	Description
get_database_info	Get database version and connection info
search_columns	Search for columns by name across all tables
list_databases	List the configured connections by alias, with host, database, write permission and which is the default. Never returns credentials.

MCP Prompts

Guided workflows that help Claude assist you effectively:

Prompt	Description
explore_database	Comprehensive database exploration and overview
query_builder	Help building efficient queries for a table
performance_analysis	Analyze table performance and suggest optimizations
data_dictionary	Generate documentation for a schema

MCP Resources

Browsable database structure:

Resource URI	Description
postgres://schemas	List all schemas
postgres://schemas/{schema}/tables	Tables in a schema
postgres://schemas/{schema}/tables/{table}	Table details
postgres://database	Database connection info

Example Usage

Once configured, ask Claude to:

Schema Exploration:

• "List all tables in the public schema"
• "Describe the users table structure"
• "What views are available?"

Querying:

• "Show me 10 rows from the orders table"
• "Find all customers who placed orders last week"
• "Count records grouped by status"

Performance Analysis:

• "What indexes exist on the orders table?"
• "Analyze the performance of the users table"
• "Explain this query: SELECT * FROM orders WHERE created_at > '2024-01-01'"

Documentation:

• "Generate a data dictionary for this database"
• "What columns contain 'email' in their name?"

Security

This MCP server implements multiple security layers:

Read-Only by Default

Write operations (INSERT, UPDATE, DELETE) are blocked unless explicitly enabled via ALLOW_WRITE_OPERATIONS=true.

SQL Injection Prevention

• All queries are validated before execution
• Dangerous operations (DROP DATABASE, etc.) are always blocked
• Multiple statements are not allowed
• SQL comments are blocked

Credential Protection

• Passwords stored using secure string types
• Credentials never appear in logs or error messages

Query Limits

• Results limited by MAX_ROWS (default: 1000)
• Query timeout configurable via QUERY_TIMEOUT

Development

TypeScript

cd typescript
npm install
npm run build
npm run dev  # Watch mode

Python

cd python
uv sync
uv run pytest -v --cov=postgres_mcp

Running Tests

# Python unit tests (no database required)
cd python
uv run pytest tests/test_security.py tests/test_settings.py -v

# Integration tests (requires PostgreSQL)
docker-compose up -d
uv run pytest tests/test_integration.py -v

Troubleshooting

Connection Issues

# Verify PostgreSQL is running
pg_isready -h localhost -p 5432

# Test connection with psql
psql -h localhost -U your_user -d your_database

Permission Denied

Ensure your database user has SELECT permissions:

GRANT SELECT ON ALL TABLES IN SCHEMA public TO your_user;

MCP Server Not Connecting

# Check server status
claude mcp get postgres

# Test server directly
postgresql-mcp  # Should wait for MCP messages

Links

• PyPI Package
• npm Package
• Installation Guide
• GitHub Repository

Author

Built by Javier Aguilar - AI Agent Architect specializing in multi-agent orchestration and MCP development.

License

MIT