# PostgreSQL MCP Server

A production-ready **Model Context Protocol (MCP) server** for PostgreSQL, enabling AI assistants to securely interact with PostgreSQL databases.

## Features

- **30+ MCP tools** for database operations
- **Security guardrails** with SQL injection detection
- **Role-based access control** (Viewer, Analyst, Developer, DBA, Admin)
- **Read-only mode** for safe query execution
- **Transaction management** with begin/commit/rollback
- **AI-powered tools** for SQL generation, optimization, and analysis
- **Structured audit logging** for compliance
- **Async execution** with connection pooling

## Quick Start

### Prerequisites

- Python 3.12+
- PostgreSQL 14+
- pip

### Installation

```bash
# Clone the repository
git clone https://github.com/your-org/postgres-mcp.git
cd postgres-mcp

# Create virtual environment
python -m venv .venv
.venv\Scripts\activate   # Windows
# source .venv/bin/activate  # Linux/Mac

# Install dependencies
pip install -r requirements.txt

# Copy and configure environment
copy .env.example .env
# Edit .env with your database credentials
```

### Configure .env

```env
DATABASE_URL=postgresql+asyncpg://user:password@localhost:5432/your_db
DATABASE_HOST=localhost
DATABASE_PORT=5432
DATABASE_NAME=your_db
DATABASE_USER=your_user
DATABASE_PASSWORD=your_password
READ_ONLY_MODE=false
ENABLE_AI_SQL=true
ENABLE_AUDIT_LOG=true
```

### Run the Server

```bash
python -m app.server
```

### Run Tests

```bash
pytest -v
```

## MCP Client Configuration

### Claude Desktop

Add to `claude_desktop_config.json`:

```json
{
  "mcpServers": {
    "postgres": {
      "command": "python",
      "args": ["-m", "app.server"],
      "cwd": "/path/to/postgres-mcp",
      "env": {
        "DATABASE_URL": "postgresql+asyncpg://user:pass@localhost:5432/db"
      }
    }
  }
}
```

### Cursor / VS Code

```json
{
  "mcpServers": {
    "postgres": {
      "command": "python",
      "args": ["-m", "app.server"],
      "cwd": "/path/to/postgres-mcp"
    }
  }
}
```

## Available Tools

### Database Information
| Tool | Description |
|------|-------------|
| `list_databases` | List all accessible databases |
| `current_database` | Get current database name |
| `list_schemas` | List all schemas |
| `list_tables` | List tables in a schema |
| `list_views` | List views in a schema |
| `list_functions` | List functions in a schema |
| `list_indexes` | List indexes in a schema |
| `list_sequences` | List sequences in a schema |
| `list_extensions` | List installed extensions |

### Schema Inspection
| Tool | Description |
|------|-------------|
| `describe_table` | Full table description |
| `describe_column` | Column metadata |
| `foreign_keys` | Foreign key relationships |
| `primary_keys` | Primary key columns |
| `unique_constraints` | Unique constraints |
| `check_constraints` | Check constraints |
| `table_size` | Table size with indexes |
| `database_size` | Total database size |

### Query Execution
| Tool | Description |
|------|-------------|
| `execute_select` | Structured SELECT queries |
| `execute_insert` | Insert rows |
| `execute_update` | Update rows |
| `execute_delete` | Delete rows |
| `execute_query` | Raw SQL execution |

### Transactions
| Tool | Description |
|------|-------------|
| `begin_transaction` | Start a transaction |
| `commit_transaction` | Commit changes |
| `rollback_transaction` | Undo changes |

### Admin Tools
| Tool | Description |
|------|-------------|
| `active_connections` | List active connections |
| `running_queries` | List running queries |
| `locks` | List locks and deadlocks |
| `vacuum_status` | Vacuum and dead tuple info |
| `analyze_table` | Update table statistics |
| `server_version` | Get server version |
| `server_settings` | Get server settings |

### AI Tools
| Tool | Description |
|------|-------------|
| `generate_sql` | Generate SQL from description |
| `explain_sql` | Human-readable query explanation |
| `optimize_sql` | Query optimization suggestions |
| `validate_sql` | Validate without executing |
| `detect_sql_injection` | Security analysis |
| `estimate_query_cost` | Cost estimation |

## Security

### Guardrails

The server automatically blocks dangerous operations:
- `DROP DATABASE` / `DROP ROLE`
- `ALTER SYSTEM`
- `COPY ... PROGRAM`
- `TRUNCATE` (requires confirmation)
- `pg_sleep()`, `pg_read_file()`, `pg_write_file()`
- Multi-statement queries
- SQL injection patterns

### Read-Only Mode

When `READ_ONLY_MODE=true`, only `SELECT` and `EXPLAIN` are allowed.

### Role-Based Access

| Role | Access Level |
|------|-------------|
| Viewer | Read metadata only |
| Analyst | SELECT queries + AI tools |
| Developer | INSERT/UPDATE/DELETE + transactions |
| DBA | Admin tools (connections, settings) |
| Admin | Full access |

## Docker

```bash
# Build
docker build -t postgres-mcp .

# Run
docker run -p 8000:8000 \
  -e DATABASE_URL=postgresql+asyncpg://user:pass@host:5432/db \
  postgres-mcp
```

## Architecture

```
app/
├── server.py          # FastMCP server with all tool registrations
├── config.py          # Pydantic Settings configuration
├── database.py        # Async database connection manager
├── guards/
│   ├── sql_guard.py   # SQL validation and risk classification
│   └── permissions.py # Role-based access control
├── tools/
│   ├── schema_tools.py     # Database/schema inspection
│   ├── query_tools.py      # CRUD query execution
│   ├── transaction_tools.py # Transaction management
│   ├── admin_tools.py      # Monitoring and admin
│   └── ai_tools.py         # AI-powered SQL analysis
├── services/
│   └── audit.py       # Audit logging
├── models/
│   └── schemas.py     # Pydantic input/output models
└── utils/
    ├── logging.py     # Structured logging
    └── helpers.py     # Utilities (pagination, injection detection)
```

## Development

```bash
# Install dev dependencies
pip install -r requirements.txt

# Run linter
ruff check app/ tests/

# Run formatter
black app/ tests/

# Run type checker
mypy app/

# Run tests with coverage
pytest --cov=app --cov-report=term-missing
```

## Future Enhancements

- **pgvector integration** for vector similarity search
- **Semantic search** across database schemas
- **RAG workflows** for database documentation
- **Multi-database support** (MySQL, SQLite, SQL Server)
- **Streaming** for large result sets
- **Prepared statement caching**
- **Query result caching** with TTL
- **Webhook notifications** for schema changes
- **GraphQL endpoint** alongside MCP
- **Dashboard UI** for monitoring

## License

MIT