# PostgreSQL MCP Server

A production-ready [Model Context Protocol](https://modelcontextprotocol.io) 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](https://docs.astral.sh/uv/) package manager
- PostgreSQL 14+

## Installation

```bash
# 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

```bash
# 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`:

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

```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"
      }
    }
  }
}
```

### OpenCode

In `opencode.json`:

```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`):

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

```python
# 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

```bash
# 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