Metadata-Version: 2.4
Name: sanitized-db-mcp
Version: 0.1.1
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'
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)

```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.

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