# 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/SaadRiaz99/postgres-mcp-server.git
cd postgres-mcp-server

# 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=true
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-server",
      "env": {
        "DATABASE_HOST": "localhost",
        "DATABASE_PORT": "5432",
        "DATABASE_NAME": "your_db",
        "DATABASE_USER": "your_user",
        "DATABASE_PASSWORD": "your_password",
        "READ_ONLY_MODE": "true"
      }
    }
  }
}
```

### Cursor / VS Code

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

## Fork and use it

1. Click **Fork** on [this repository](https://github.com/SaadRiaz99/postgres-mcp-server/fork).
2. Clone your fork (`git clone https://github.com/YOUR_USERNAME/postgres-mcp-server.git`).
3. Follow Installation above. On Linux/macOS use `cp .env.example .env` instead of `copy`.
4. Set `DATABASE_HOST`, `DATABASE_PORT`, `DATABASE_NAME`, `DATABASE_USER`, and
   `DATABASE_PASSWORD` in `.env`. The asyncpg pool uses these separate fields;
   changing `DATABASE_URL` alone does not configure that pool.
5. Use a dedicated PostgreSQL role with only the privileges you need. Start with
   read-only database grants and `READ_ONLY_MODE=true`. Existing write/admin tools
   remain exposed; the application flag and role metadata are not a substitute
   for database-level permissions or authenticated authorization.
6. Configure your MCP client to launch the virtual environment's Python with
   `-m app.server` and this repository as its working directory. If the client
   does not support `cwd`, launch through a script that changes directory first.
7. Try: “Find columns containing email in public”, “Show index usage”, or
   “Which sessions are blocking queries?”

These six new inspection tools do not require a paid model API key. An AI client
may have its own model costs. Keep `.env` private and use a test database first.

### New inspection and diagnostic tools

| Tool | Inputs | Purpose |
|------|--------|---------|
| `search_columns` | `search`, `schema`, `limit` | Literal case-insensitive column-name search |
| `list_triggers` | `table_name`, `schema`, `limit` | Visible trigger events and actions |
| `table_privileges` | `table_name`, `schema`, `limit` | Visible table grants |
| `table_statistics` | `schema`, `limit` | Estimated rows, scans and maintenance timestamps |
| `index_statistics` | `schema`, `limit` | Index scans, bytes, uniqueness and validity |
| `blocking_queries` | `limit` | Blocked and blocking process IDs in this database |

`schema` defaults to `public`; `limit` defaults to 100 and accepts 1–1000.
Results include `success`, `data`, `count`, `limit`, and `duration_ms`.
Invalid input and database errors return structured error objects. No new tool
modifies data or cancels sessions; schema/search/table values are bound parameters.
Results are capped (not paginated); narrow the schema or table to reduce results.

Catalog visibility depends on database grants. Empty results can mean no matching
objects or insufficient visibility. Multi-event triggers appear once per event.
Table grants are not a complete effective-permission audit. Statistics are
estimates/counters since collection/reset; zero index scans alone do **not** mean
an index should be removed. Session details can be null for restricted roles.
`query_age_seconds` is query age, not exact lock-wait duration.

References: [PostgreSQL statistics](https://www.postgresql.org/docs/14/monitoring-stats.html)
and [blocking PID functions](https://www.postgresql.org/docs/14/functions-info.html).

Run the focused tests with:

```bash
python -m pytest tests/test_inspection_tools.py --no-cov
```

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