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:

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 Node.js 20+; the Python package requires Python 3.10+.

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

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

Schema Exploration

Performance & Analysis

Database Info

MCP Prompts

Guided workflows that help Claude assist you effectively:

MCP Resources

Browsable database structure:

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