Metadata-Version: 2.4
Name: sanitized-db-mcp
Version: 0.2.0
Summary: MCP server that rewrites SQL to prevent PII/PHI exposure in AI agent queries
Project-URL: Homepage, https://github.com/ruminaider/sanitized-db-mcp
Project-URL: Repository, https://github.com/ruminaider/sanitized-db-mcp
Project-URL: Issues, https://github.com/ruminaider/sanitized-db-mcp/issues
License-Expression: MIT
License-File: LICENSE
Keywords: agent,llm,mcp,phi,pii,postgresql,sanitization,sql
Classifier: Development Status :: 4 - Beta
Classifier: License :: OSI Approved :: MIT License
Classifier: Programming Language :: Python :: 3.11
Classifier: Programming Language :: Python :: 3.12
Classifier: Topic :: Database
Classifier: Topic :: Security
Requires-Python: >=3.11
Requires-Dist: httpx>=0.27
Requires-Dist: mcp>=1.0.0
Requires-Dist: pglast>=6.0
Requires-Dist: psycopg[binary]>=3.1
Requires-Dist: pyyaml>=6.0
Provides-Extra: dev
Requires-Dist: pytest-asyncio>=0.23; extra == 'dev'
Requires-Dist: pytest>=8.0; extra == 'dev'
Provides-Extra: sse
Requires-Dist: sse-starlette>=1.6; extra == 'sse'
Requires-Dist: starlette<1.0,>=0.36; extra == 'sse'
Requires-Dist: uvicorn>=0.27; extra == 'sse'
Description-Content-Type: text/markdown

# Sanitized DB MCP Server

[![PyPI](https://img.shields.io/pypi/v/sanitized-db-mcp)](https://pypi.org/project/sanitized-db-mcp/)
[![Docker](https://img.shields.io/badge/ghcr.io-sanitized--db--mcp-blue)](https://ghcr.io/ruminaider/sanitized-db-mcp)
[![Tests](https://github.com/ruminaider/sanitized-db-mcp/actions/workflows/test.yml/badge.svg)](https://github.com/ruminaider/sanitized-db-mcp/actions/workflows/test.yml)

An MCP server that rewrites SQL queries at the AST level to prevent PII/PHI exposure.

## Why This Exists

AI agents write SQL. They also hallucinate column names, ignore access controls, and cheerfully SELECT * from tables full of personal data. This server sits between the agent and your PostgreSQL database, rewriting every query so hidden columns return type-preserving placeholders instead of real values. The agent gets useful results; your users keep their privacy.

## Quick Start

### 1. Create an allowlist

Generate a scaffold from your database schema:

```bash
# Install the CLI (skip if using uvx or Docker below)
pip install sanitized-db-mcp

sanitized-db-mcp generate-allowlist --database-url postgresql://user:pass@host:5432/mydb > allowlist.yaml
```

Edit the YAML to expose only the columns agents should see (see [Generating an Allowlist](#generating-an-allowlist) below).

### 2. Run the server

Pick one of the four methods below and add the config to your `.mcp.json`.

#### Method 1: uvx (recommended — zero install)

`uvx` runs the package in an isolated environment with no permanent installation and no dependency conflicts. Unlike pip, there is nothing to install or manage. Unlike Docker, there are no volume mounts or path mappings to configure.

```json
{
  "sanitized-db": {
    "type": "stdio",
    "command": "uvx",
    "args": ["sanitized-db-mcp"],
    "env": {
      "ALLOWLIST_PATH": "./allowlist.yaml",
      "DATABASE_URL": "postgresql://user:pass@host:5432/mydb"
    }
  }
}
```

No install needed. `uvx` downloads and runs the package in an isolated environment.

#### Method 2: pip install

```bash
pip install sanitized-db-mcp
```

```json
{
  "sanitized-db": {
    "type": "stdio",
    "command": "python3",
    "args": ["-m", "sanitized_db_mcp.server"],
    "env": {
      "ALLOWLIST_PATH": "./allowlist.yaml",
      "DATABASE_URL": "postgresql://user:pass@host:5432/mydb"
    }
  }
}
```

#### Method 3: Docker (pre-built image)

```json
{
  "sanitized-db": {
    "type": "stdio",
    "command": "docker",
    "args": [
      "run", "-i", "--rm",
      "-e", "ALLOWLIST_PATH=/app/allowlist.yaml",
      "-e", "DATABASE_URL",
      "-v", "./allowlist.yaml:/app/allowlist.yaml:ro",
      "ghcr.io/ruminaider/sanitized-db-mcp:latest"
    ],
    "env": {
      "DATABASE_URL": "postgresql://user:pass@host:5432/mydb"
    }
  }
}
```

#### Method 4: Docker (build from source)

```bash
docker build -t sanitized-db-mcp:local .
```

```json
{
  "sanitized-db": {
    "type": "stdio",
    "command": "docker",
    "args": [
      "run", "-i", "--rm",
      "-e", "ALLOWLIST_PATH=/app/allowlist.yaml",
      "-e", "DATABASE_URL",
      "-v", "./allowlist.yaml:/app/allowlist.yaml:ro",
      "sanitized-db-mcp:local"
    ],
    "env": {
      "DATABASE_URL": "postgresql://user:pass@host:5432/mydb"
    }
  }
}
```

### 3. Query through the agent

The server exposes a single MCP tool (`query`) that accepts raw SQL and returns sanitized results.

## Generating an Allowlist

The CLI tool connects to your database, reads the schema, and produces a YAML file where **nothing is visible by default**. You opt columns in by uncommenting them.

```bash
sanitized-db-mcp generate-allowlist --database-url postgresql://user:pass@host:5432/mydb > allowlist.yaml
```

Add `--deny-pii` to flag columns that look like PII (email, name, phone, address, etc.):

```bash
sanitized-db-mcp generate-allowlist --database-url postgresql://... --deny-pii > allowlist.yaml
```

Example output:

```yaml
# Generated by: sanitized-db-mcp generate-allowlist --deny-pii
#
# HOW TO USE:
# - Columns under "columns:" are VISIBLE to agents (currently empty)
# - Commented lines show available columns — uncomment to make visible
# - Lines marked "# PII" were flagged as likely PII/PHI — review carefully
# - After editing, restart the MCP server to apply changes

tables:
  users:
    columns: {}
    # Available columns (uncomment to make visible):
    #   id: {type: integer, placeholder: 0}
    #   is_active: {type: boolean, placeholder: false}
    #   email: {type: varchar, placeholder: '[REDACTED]'}  # PII
    #   password: {type: varchar, placeholder: '[REDACTED]'}  # PII
```

Uncomment the columns you want agents to see. Leave everything else hidden.

## Allowlist YAML Format

```yaml
tables:
  <table_name>:
    columns:
      <column_name>: {type: <pg_type>, placeholder: <sql_literal>}
allowed_functions:
  - FUNCTION_NAME
```

- **Listed columns are VISIBLE.** Unlisted columns are hidden and replaced with type-preserving placeholders.
- `type`: base PostgreSQL type (`integer`, `varchar`, `boolean`, `timestamp`, `uuid`, `jsonb`, etc.).
- `placeholder`: SQL literal that replaces hidden column values. Must be type-compatible.
- `allowed_functions`: SQL functions agents can call. All others are rejected.

## Configuration

| Variable | Required | Default | Description |
|---|---|---|---|
| `ALLOWLIST_PATH` | Yes | -- | Path to `allowlist.yaml` |
| `MCP_SERVER_NAME` | No | `sanitized-db` | MCP server name (affects tool name: `mcp__<name>__query`) |
| `DATABASE_URL` | If not using Render | -- | PostgreSQL connection string |
| `RENDER_POSTGRES_ID` | If using Render | -- | Render Postgres instance ID |
| `RENDER_API_KEY` | If using Render | -- | Render API bearer token |

The server prefers Render API credentials when both are set. For local development, `DATABASE_URL` is sufficient.

## Framework Integration

- **Django**: Use a `visible()` field decorator to mark safe fields, then generate the allowlist from model metadata.
- **Rails / Other**: Use the CLI tool to scaffold, then curate manually. Or build your own generator that outputs the same YAML format.
- **Custom generators**: The YAML format is the contract. Any tool that produces conformant YAML works.

## SSE Transport (Remote Deployment)

For shared deployments (e.g., Render.com), the server supports SSE transport over HTTP.

### Installation

```bash
pip install 'sanitized-db-mcp[sse]'
```

### Configuration

| Variable | Required | Default | Description |
|---|---|---|---|
| `MCP_TRANSPORT` | No | `stdio` | Transport mode: `stdio` or `sse` |
| `PORT` | No | `8000` | HTTP port (Render sets this automatically) |
| `MCP_API_KEY` | Recommended | — | Bearer token for HTTP authentication |
| `MCP_MAX_CONNECTIONS` | No | unlimited | Max concurrent connections (uvicorn `limit_concurrency`) |
| `MCP_SESSION_TIMEOUT` | No | unlimited | Max SSE session duration in seconds |

### Running

```bash
export MCP_TRANSPORT=sse
export MCP_API_KEY=your-secret-key
export ALLOWLIST_PATH=./allowlist.yaml
export DATABASE_URL=postgresql://...
python -m sanitized_db_mcp.server
```

### Docker

```bash
docker run -e MCP_TRANSPORT=sse -e MCP_API_KEY=secret -e ALLOWLIST_PATH=/app/allowlist.yaml \
  -e DATABASE_URL=postgresql://... -p 8000:8000 sanitized-db-mcp
```

### Claude Code Client Configuration

In your MCP client config, point to the SSE endpoint:

```json
{
  "mcpServers": {
    "sanitized-db": {
      "type": "sse",
      "url": "https://your-service.onrender.com/sse",
      "headers": {
        "Authorization": "Bearer your-secret-key"
      }
    }
  }
}
```

### Endpoints

- `GET /sse` — SSE connection (MCP session)
- `POST /messages/` — Client-to-server messages
- `GET /health` — Health check (no auth required)

### Deployment Guidance

**Connection limits:** Set `MCP_MAX_CONNECTIONS` to prevent resource exhaustion under attack or misconfiguration. A reasonable value is 2-5x your expected concurrent clients (e.g., 100 when expecting 10-20 clients). New connections beyond the limit receive HTTP 503. Note: each SSE session AND each `/messages/` POST from that session count as separate concurrent connections, so do not set this too low.

**Session timeout:** Set `MCP_SESSION_TIMEOUT` (seconds) to close abandoned SSE sessions. 28800 (8 hours) is a reasonable starting point. Clients reconnect automatically after timeout. Without this, abandoned sessions consume resources indefinitely since uvicorn's `timeout_keep_alive` does not apply to active SSE streams.

**CORS:** CORS headers are intentionally omitted. The server's clients (Claude Code, MCP CLI tools) are non-browser applications that ignore CORS. The native browser `EventSource` API cannot send `Authorization` headers, so browsers cannot authenticate even if they attempt cross-origin connections. If browser-based MCP clients become a supported consumer, add Starlette's `CORSMiddleware`.

**Audit logging:** Every query is logged as structured JSON with client IP, request ID, session ID, and user agent for HIPAA compliance. Behind a reverse proxy (Render, nginx), client IP is extracted from `X-Forwarded-For`. Configure your log aggregator for 6-year retention per HIPAA requirements.

## How the Sanitizer Works

The server exposes a single MCP tool (`query`) that accepts raw SQL and returns sanitized results. Every query passes through an 11-step pipeline:

```
Agent sends SQL
      |
      v
1.  Parse (pglast) ───── syntax error? → QuerySyntaxError
      |
      v
2.  Statement type ───── not SELECT? → StatementTypeError
      |                   SELECT INTO? → StatementTypeError
      |                   FOR UPDATE/SHARE? → StatementTypeError
      v
3.  Table validation ─── system catalog? → SystemCatalogError
      |                   not in allowlist? → RestrictedColumnError
      |                   TABLESAMPLE? → unwrap, validate inner table
      v
4.  Function check ───── always-blocked? → DisallowedFunctionError
      |                   not in allowlist? → DisallowedFunctionError
      |                   FILTER (WHERE ...)? → walk with WHERE rules
      |                   inline OVER clause? → walk with WHERE rules
      v
5.  WHERE/JOIN check ─── hidden column? → RestrictedColumnError
      v
6.  Clause check ──────── ORDER BY / GROUP BY / DISTINCT ON / WINDOW
      |                    hidden column? → RestrictedColumnError
      v
7.  Subquery check ───── hidden column in subquery/CTE SELECT? → RestrictedColumnError
      v
8.  Rewrite SELECT ───── hidden columns → type-preserving placeholders
      |                   SELECT * → visible columns + redaction marker
      v
9.  Serialize AST ────── rewritten SQL string
      v
10. Execute ───────────── read-only, 5s timeout, SSL
      v
11. Audit log ─────────── structured JSON (original, rewritten, outcome)
```

## Security Model

All 200 tests pass with 0 xfails. The pen test suite covers 21 attack categories:

| Category | Tests | Defense |
|---|---|---|
| ORDER BY / GROUP BY / DISTINCT ON | 13 | sortClause, groupClause, distinctClause walked with WHERE rules |
| Window function attacks | 6 | Named WINDOW and inline OVER walked with WHERE rules |
| Aggregate FILTER clause | 4 | agg_filter walked with WHERE rules |
| CTE attacks | 6 | CTE SELECT targets validated; unused CTEs with hidden columns rejected |
| LATERAL join attacks | 3 | Correlated subquery WHERE clauses validated |
| Schema/identifier tricks | 8 | Quoted identifiers, unicode escapes, pg_temp schema handled |
| Composite type / row attacks | 3 | Field selection rejected; ROW() with hidden columns redacted |
| ARRAY attacks | 3 | ARRAY subquery, ARRAY_AGG, ARRAY[] with hidden columns caught |
| JSONB operator attacks | 5 | ->, ->>, #>, @>, ? operators on hidden columns caught |
| Type cast attacks | 4 | Chained casts on hidden columns redacted; casts in WHERE rejected |
| FROM clause variants | 3 | TABLESAMPLE unwrapped, VALUES and generate_series handled |
| Locking clauses | 3 | FOR UPDATE, FOR SHARE rejected at sanitizer level |
| Subquery nesting | 5 | Triple-nested, correlated, NOT EXISTS, ANY all validated |
| Statement type / multi-statement | 5 | SELECT INTO rejected; null byte, comment, dollar quoting tested |
| Ambiguous column resolution | 3 | Unqualified columns conservatively resolved |
| Encoding edge cases | 4 | Cyrillic homoglyphs, unicode escapes, semicolons in aliases |
| Timing / side-channel | 4 | pg_sleep, amplification, cross-join, recursive CTE blocked |
| Error-based extraction | 4 | Generic error messages; no column/table names leaked |
| Connection security | 5 | SSL, timeout, read-only, autocommit, no connection strings in errors |
| Allowlist integrity | 4 | Case-insensitive lookup, unknown columns default to hidden |
| Audit logging | 4 | All outcomes covered, HIPAA fields present, finally-block guarantee |

## Running Tests

```bash
cd sanitized-db-mcp

# Full test suite (200 tests across 3 suites)
python -m pytest sanitized_db_mcp/tests/ -v

# Individual suites
python -m pytest sanitized_db_mcp/tests/test_sanitizer.py -v    # Core rewriting (37 tests)
python -m pytest sanitized_db_mcp/tests/test_bypass.py -v       # Bypass resistance (64 tests)
python -m pytest sanitized_db_mcp/tests/test_pentest.py -v      # Pen test (99 tests)
python -m pytest sanitized_db_mcp/tests/test_allowlist.py -v    # Allowlist loader
```

## Key Files

| File | Purpose |
|---|---|
| `server.py` | MCP server entry point, `query(sql)` tool |
| `sanitizer.py` | AST-level SQL rewriting engine |
| `allowlist.py` | In-memory allowlist representation |
| `connection.py` | Render API + static connection management |
| `errors.py` | Sanitized error classes (no schema leakage) |
| `audit.py` | HIPAA-compliant query audit logging |
