Content
# 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
