PostgreSQL MCP Server

A production-ready Model Context Protocol server for PostgreSQL database operations. Enables AI assistants to securely interact with PostgreSQL databases through a standardized interface.

Features

• 11 MCP Tools - list_tables, describe_table, execute_select, execute_query, list_schemas, list_views, explain_query, database_info, health_check, connection_status, metrics
• Security First - SQL injection detection, blocked dangerous commands, read-only mode, rate limiting
• Async Everywhere - asyncpg connection pooling with automatic reconnect and retry
• Schema Caching - TTL-based caching for metadata queries
• Structured Logging - structlog with JSON or console output
• Metrics - query counts, execution times, security violations
• Type Safe - Pydantic v2 models, strict mypy typing
• Production Ready - graceful shutdown, signal handling, connection health checks

Prerequisites

• Python 3.12+
• uv package manager
• PostgreSQL 14+

Installation

# Clone and enter the project
cd postgres-mcp-server

# Create environment and install dependencies
uv sync

# Configure environment
cp .env.example .env
# Edit .env with your PostgreSQL connection details

Configuration

Variable	Default	Description
POSTGRES_HOST	localhost	PostgreSQL host
POSTGRES_PORT	5432	PostgreSQL port
POSTGRES_DATABASE	postgres	Database name
POSTGRES_USER	postgres	Database user
POSTGRES_PASSWORD		Database password
POSTGRES_MIN_CONNECTIONS	1	Min pool connections
POSTGRES_MAX_CONNECTIONS	10	Max pool connections
READ_ONLY	true	Restrict to SELECT only
ALLOW_WRITE	false	Enable write operations
MAX_ROWS	1000	Max rows returned per query
QUERY_TIMEOUT	30	Query timeout in seconds
RATE_LIMIT	60	Max requests per window
RATE_WINDOW	60	Rate limit window in seconds
LOG_LEVEL	INFO	Log level (DEBUG, INFO, WARNING, ERROR)
LOG_JSON	false	Output logs as JSON
CACHE_TTL	300	Schema cache TTL in seconds
RETRY_MAX_ATTEMPTS	3	Max connection retries
METRICS_ENABLED	true	Enable metrics collection

Running the Server

# Start with stdio transport (default for MCP)
uv run python -m src.server

# Or directly
uv run src/server.py

Connecting with MCP Clients

Claude Desktop

Add to your claude_desktop_config.json:

{
  "mcpServers": {
    "postgres": {
      "command": "uv",
      "args": [
        "--directory",
        "/path/to/postgres-mcp-server",
        "run",
        "src/server.py"
      ],
      "env": {
        "POSTGRES_HOST": "localhost",
        "POSTGRES_PORT": "5432",
        "POSTGRES_DATABASE": "yourdb",
        "POSTGRES_USER": "youruser",
        "POSTGRES_PASSWORD": "yourpassword"
      }
    }
  }
}

Cursor

In Cursor settings, add a new MCP server:

{
  "mcpServers": {
    "postgres": {
      "command": "uv",
      "args": [
        "--directory",
        "/path/to/postgres-mcp-server",
        "run",
        "src/server.py"
      ],
      "env": {
        "POSTGRES_HOST": "localhost",
        "POSTGRES_PORT": "5432",
        "POSTGRES_DATABASE": "yourdb",
        "POSTGRES_USER": "youruser",
        "POSTGRES_PASSWORD": "yourpassword"
      }
    }
  }
}

OpenCode

In opencode.json:

{
  "mcpServers": {
    "postgres": {
      "command": "uv",
      "args": [
        "--directory",
        "/path/to/postgres-mcp-server",
        "run",
        "src/server.py"
      ],
      "env": {
        "POSTGRES_HOST": "localhost",
        "POSTGRES_PORT": "5432",
        "POSTGRES_DATABASE": "yourdb",
        "POSTGRES_USER": "youruser",
        "POSTGRES_PASSWORD": "yourpassword"
      }
    }
  }
}

VS Code MCP Extension

In VS Code settings (settings.json):

{
  "mcp.server": {
    "postgres": {
      "command": "uv",
      "args": [
        "--directory",
        "/path/to/postgres-mcp-server",
        "run",
        "src/server.py"
      ],
      "env": {
        "POSTGRES_HOST": "localhost",
        "POSTGRES_PORT": "5432",
        "POSTGRES_DATABASE": "yourdb",
        "POSTGRES_USER": "youruser",
        "POSTGRES_PASSWORD": "yourpassword"
      }
    }
  }
}

Available Tools

Tool	Description
list_tables	List all tables, optionally filtered by schema
describe_table	Get table schema with columns, types, constraints, indexes
execute_select	Run SELECT queries with parameters and pagination
execute_query	Run INSERT/UPDATE/DELETE (requires write mode)
list_schemas	List all database schemas
list_views	List all views, optionally filtered by schema
explain_query	Get PostgreSQL execution plan for a query
database_info	Get database version, encoding, timezone
health_check	Check database connection health
connection_status	Get connection pool status
metrics	Get server metrics

Example Usage

# List all tables
result = await list_tables()

# Describe a table
result = await describe_table(schema="public", table="users")

# Execute a SELECT query
result = await execute_select(
    query="SELECT * FROM users WHERE active = $1",
    params=[True],
    limit=50
)

# Get database info
result = await database_info()

# Explain a query
result = await explain_query("SELECT * FROM users JOIN orders ON users.id = orders.user_id")

# Check health
result = await health_check()

# Get metrics
result = await metrics()

Security

• Blocked commands: DROP, ALTER, TRUNCATE, CREATE DATABASE, DROP DATABASE, COPY, VACUUM, GRANT, REVOKE, CREATE EXTENSION
• SQL injection detection: Pattern-based detection of common injection vectors
• Rate limiting: Configurable request throttling
• Query timeout: Automatic cancellation of long-running queries
• Row limits: Maximum rows returned per query
• Read-only mode: Restrict all write operations

Project Structure

postgres-mcp-server/
├── src/
│   ├── __init__.py
│   ├── server.py      # FastMCP server entry point
│   ├── config.py      # Environment configuration
│   ├── database.py    # asyncpg connection pool & queries
│   ├── tools.py       # MCP tool implementations
│   ├── security.py    # SQL validation & security
│   ├── models.py      # Pydantic data models
│   ├── utils.py       # Utility functions
│   └── logger.py      # structlog setup
├── tests/
│   ├── __init__.py
│   ├── test_security.py
│   ├── test_models.py
│   ├── test_database.py
│   └── test_utils.py
├── .env.example
├── pyproject.toml
└── README.md

Development

# Install dev dependencies
uv sync --dev

# Run tests
uv run pytest

# Run linting
uv run ruff check src/

# Run type checking
uv run mypy src/

# Run formatting check
uv run black --check src/

Troubleshooting

Connection refused

Ensure PostgreSQL is running and accessible from the server host.

Authentication failed

Verify POSTGRES_USER and POSTGRES_PASSWORD in .env.

Timeout errors

Increase QUERY_TIMEOUT or optimize your queries.

Security violations

Queries containing blocked commands or suspicious patterns are rejected. Use execute_select for SELECT queries.

Pool exhaustion

Increase POSTGRES_MAX_CONNECTIONS if you see pool acquisition timeouts.

License

MIT