# PostgreSQL Model Context Protocol (MCP) Server

A secure, robust Model Context Protocol (MCP) server written in Python that allows AI models to safely read, write, update, and delete records from a PostgreSQL database.

## Project Overview

### Purpose & Problem Solved
AI models lack direct access to databases, which hinders their ability to build reports, analyze database structure, and manage records on demand. Traditional integrations can expose databases to major security risks, such as SQL injection, resource exhaustion, or accidental truncation.

This PostgreSQL MCP Server acts as a secure, sandboxed gateway for AI agents. It implements:
- **Strict parameterized query enforcement** to eliminate SQL injection risks.
- **Multi-tier security verification**:
  - **Transactional Dry-Runs (Small/Medium operations)**: Runs write operations inside a PostgreSQL `BEGIN` transaction, captures a sample preview of affected records, and automatically issues a `ROLLBACK` so zero disk changes persist until explicitly confirmed.
  - **Disposable Environment Testing (High-Risk/Large-Scale operations)**: Tests high-risk DDL (`DROP TABLE`, `TRUNCATE`) or large-scale modifications (> 1,000 rows) inside an isolated temporary schema (`_mcp_disposable_...`), inspects structural outcomes, and automatically drops the disposable environment.
- **Row limits** preventing memory exhaustion from accidental massive selects.
- **Automated pre-deletion backups** (CSV and database dumps) with built-in age and count retention policies.
- **Error feedback loops** and **Explain Plan integrations** to allow self-correcting query generation.

### Tech Stack
- **FastMCP**: High-level SDK for creating MCP tools, resources, and prompts.
- **asyncpg**: High-performance, asynchronous PostgreSQL client library.
- **sqlglot**: SQL parser and transpiler used to inspect ASTs and validate query safety.
- **python-dotenv**: Configuration management via `.env` files.

---

## Installation & Configuration

### Prerequisites
- Python 3.10+
- PostgreSQL database instance
- `pg_dump` (Optional, fallback Python backup generator will be used if missing)

### Installation
1. Clone the repository and navigate to the project directory:
   ```bash
   cd "Postgres MCP"
   ```
2. Create and activate a virtual environment:
   ```bash
   python3 -m venv venv
   source venv/bin/activate
   ```
3. Install the dependencies:
   ```bash
   pip install -r requirements.txt
   ```

### Configuration
Create a `.env` file in the root of the workspace directory. The server loads parameters using both standard PostgreSQL keys and user-friendly aliases:

```ini
# Database Connection (Uses URL or individual variables)
DATABASE_URL=postgresql://user:password@host:5432/dbname

# Fallback individual configuration parameters
DB_HOST=localhost
DB_PORT=5432
DB_USER=postgres
DB_PASSWORD=your_password
DB_NAME=postgres

# SSL / TLS Settings ('disable', 'prefer', 'require', 'verify-ca', 'verify-full')
PGSSLMODE=prefer

# Row limit guardrail for SELECT queries
ROW_LIMIT=500

# Large Scale Operation Threshold (triggers Disposable Environment testing)
LARGE_SCALE_THRESHOLD=1000

# Backup Retention Configurations
BACKUP_DIR=./backups
MAX_BACKUPS=10
MAX_BACKUP_AGE_DAYS=7.0
DB_BACKUP_THRESHOLD_BYTES=10485760 # 10MB
```

### MCP Client Integration (mcp.json)
To register the server with an MCP client (such as Claude Desktop or other AI interfaces), add the server setup details to your client configuration file. 

Use absolute paths for the virtual environment's python interpreter and the `mcp_server.py` script:

```json
{
  "mcpServers": {
    "postgres-mcp": {
      "command": ".../Postgres MCP/venv/bin/python",
      "args": [
        ".../Postgres MCP/mcp_server.py"
      ],
      "env": {
        "DB_HOST": "192.168.1.1",
        "DB_PORT": "5432",
        "DB_USER": "postgres",
        "DB_PASSWORD": "<PASSWORD>",
        "DB_NAME": "job_searcher"
      }
    }
  }
}
```

*Note: Passing environment variables inside the `"env"` key in the client configuration is a secure way to supply database credentials at runtime.*

---

## Usage Examples

### Running the Server
Run the main script to start the server over stdio transport:
```bash
python mcp_server.py
```

### Context Injection (Resources)
The server exposes the database schema to the AI via:
- **Resource URI**: `postgres://schema`
  - *Returns*: A complete Markdown outline of all tables, comments, column types, nullability, defaults, check constraints, indexes, and foreign keys.

### Tools Exposed to the AI

#### 1. `get_schema_info`
Lists all database tables and schema-wide foreign key relationships.

#### 2. `get_table_details`
Retrieves column schemas, CHECK constraints, indexes, and descriptions for a specific table.
- **Parameters**:
  - `table_name` (string)

#### 3. `execute_read_query`
Executes a read-only query. Limits results to `ROW_LIMIT` (default 500) and returns results in a structured JSON format.
- **Parameters**:
  - `query` (string): e.g., `"SELECT name, email FROM users WHERE age > $1"`
  - `parameters` (list, optional): e.g., `[25]`
  - `explain` (boolean, optional): Prepend `EXPLAIN ANALYZE` to the query.

#### 4. `execute_write_query`
Executes write queries (`INSERT`, `UPDATE`, `DELETE`). Requires explicit confirmation.
- **Parameters**:
  - `query` (string): e.g., `"INSERT INTO users (name, email) VALUES ($1, $2)"`
  - `parameters` (list, optional): e.g., `["Alice", "alice@example.com"]`
  - `confirm` (boolean): Default `False`. If `False`, the query is not run and a `pending_approval` message is returned.

#### 5. `delete_records`
Deletes rows from a table. Backs up matching records to a timestamped CSV before performing the delete. Rotates backups based on retention settings, and runs a full database backup if the CSV size exceeds the database backup threshold.
- **Parameters**:
  - `table_name` (string)
  - `where_clause` (string): e.g., `"id = $1"` (Must not be empty or evaluate to `1=1`)
  - `parameters` (list, optional): e.g., `[104]`
  - `confirm` (boolean): Default `False`.

#### 6. `export_to_csv`
Executes a SELECT query and exports the structured results directly to a CSV file in the backups directory.
- **Parameters**:
  - `query` (string)
  - `parameters` (list, optional)
  - `filename` (string, optional)

#### 7. `backup_database`
Runs a full database schema and data backup immediately.

---

## Contribution Guidelines

We welcome contributions to enhance security features, support additional dialects, or improve parsing performance.

### Project Layout
- [config.py](file:///Users/dbearsong/Documents/projects/python/Postgres%20MCP/config.py): Configuration parser.
- [safety.py](file:///Users/dbearsong/Documents/projects/python/Postgres%20MCP/safety.py): Query AST checks and literal validation.
- [database.py](file:///Users/dbearsong/Documents/projects/python/Postgres%20MCP/database.py): Connection pool and database queries.
- [backup.py](file:///Users/dbearsong/Documents/projects/python/Postgres%20MCP/backup.py): Pre-deletion backups and retention rotation.
- [mcp_server.py](file:///Users/dbearsong/Documents/projects/python/Postgres%20MCP/mcp_server.py): FastMCP API registration.
- [test_server.py](file:///Users/dbearsong/Documents/projects/python/Postgres%20MCP/test_server.py): Integration test suite.

### Testing
To run the integration and safety test suite, ensure your `.env` connection values are set and run:
```bash
python test_server.py
```
*Note: The test suite sets up temporary tables (`test_mcp_users`, `test_mcp_logs`), executes query tools, attempts SQL injection/destructive actions, validates backups, and cleans up the tables automatically upon completion.*