postgres-safe-mcp

A PostgreSQL MCP server that automatically detects and obfuscates PII in query results before they reach the AI. Connect Claude (or any MCP client) to your database without exposing sensitive data.

How it works

1. Connect — Point the server at any PostgreSQL database (local or remote)

2. Schema check — The agent calls describe_schema first to learn the actual table and column names, avoiding guesswork and failed queries

3. Auto-detect — On startup, the server runs instant heuristic detection on all column names to classify PII (emails, names, phones, SSNs, etc.). No data sampling required — column name patterns catch the vast majority of PII fields

4. Mask by default — Query results are automatically masked before the AI sees them:

   PII masked: first_name (MASKED: PERSON), last_name (MASKED: PERSON), email (MASKED: EMAIL_ADDRESS)
   3 rows
   
   | id   | first_name | last_name | email         | created_at |
   |------|------------|-----------|---------------|------------|
   | 5022 | S**        | D****     | s***@g***.com | 2026-03-06 |
   | 5021 | S**        | D****     | s***@g***.com | 2026-03-03 |
   | 5020 | l***       | c***      | l***@t***.com | 2026-02-24 |

5. Reveal when needed — The AI can selectively unmask specific columns or PII types when the user explicitly asks to see real data

Masking and unmasking

Default behavior: everything masked

Every query runs through the redaction engine before results reach the AI. PII columns are detected by name pattern and masked automatically. The AI sees partial values like J****** instead of Jessica — enough structure to reason about the data without exposing real PII.

How the AI decides what to reveal

The query tool accepts two optional parameters for selective unmasking:

• reveal_columns — Unmask specific columns by name (e.g. ["email", "first_name"])

• reveal_types — Unmask all columns of a PII type (e.g. ["EMAIL_ADDRESS"])

The AI is guided by these rules in the tool description:

Secret columns can never be revealed

Columns matching patterns like encrypted_password, otp_secret_key, reset_password_token are classified as SECRET. These are always fully redacted ([REDACTED]) and cannot be unmasked even if reveal_columns or reveal_types is used. There's no legitimate reason for an AI agent to see raw password hashes or auth tokens.

Manual overrides

You can force specific masking behavior per column in your config:

column_rules:
  # Force a column to be treated as PII even if the name doesn't match patterns
  - table: users
    column: custom_id_field
    pii_type: US_SSN
    masking_style: partial

  # Explicitly mark a column as NOT PII (skip masking)
  - table: users
    column: display_name    # public-facing, not sensitive
    pii_type: none
    masking_style: none

Or at runtime via the configure_masking tool (in-memory, not persisted).

Human in the loop

In Claude Code, every tool call is shown to the user before execution. When the AI uses reveal_columns, you see exactly which columns are being unmasked and can approve or deny the request. This creates a natural checkpoint — the AI proposes what to reveal, you decide whether to allow it.

PII detection

Detection uses a two-layer approach:

• Column name heuristics (fast, no NLP) — Pattern matching on column names handles common patterns like email, first_name, phone, plus prefixed variants like bus_email, rep_phone_number, pref_first_name, former_last_name

• Presidio NLP analysis (on first access) — Samples ~100 rows and runs Microsoft Presidio to detect PII in column values, catching columns with non-obvious names

Detected PII types include: email addresses, phone numbers, names, physical addresses, SSNs, tax IDs, credit cards, IP addresses, dates of birth, financial account numbers, geolocation, and more.

Secret columns (encrypted passwords, tokens, OTP secrets) are always fully redacted and cannot be revealed.

Free text columns (message bodies, notes, descriptions) get value-level Presidio scanning since PII is embedded in prose.

Masking styles

Installation

Requires Python 3.12+ and uv.

git clone <repo-url>
cd postgres-safe-mcp
uv sync

On first run, the spaCy NLP model (en_core_web_lg, ~560MB) will be downloaded automatically.

Usage

With Claude Code

Add to your .mcp.json (project-level or ~/.claude/.mcp.json for global):

{
  "mcpServers": {
    "postgres-safe": {
      "command": "uv",
      "args": [
        "run",
        "--directory", "/path/to/postgres-safe-mcp",
        "python", "-m", "postgres_safe_mcp",
        "-c", "postgresql://user:pass@localhost:5432/mydb"
      ]
    }
  }
}

With a config file

uv run python -m postgres_safe_mcp --config config.yaml

CLI options

Connection string precedence: CLI arg > DATABASE_URL env var > config file. Read-only precedence: CLI flag > config file > default (true).

MCP Tools

query

Execute a SQL query with automatic PII redaction. Write queries (INSERT, UPDATE, DELETE) are blocked in read-only mode (default) and allowed when configured with read_only: false.

describe_schema

List tables and columns with their types and PII detection status.

explain_query

Show the PostgreSQL execution plan for a query.

configure_masking

Override masking rules at runtime (in-memory, not persisted).

list_masking_rules

Show all active PII classifications and masking rules.

Configuration

See config.example.yaml for a full example.

connection_string: "postgresql://user:pass@localhost:5432/mydb"
default_masking_style: "partial"
auto_detect: true
read_only: true                     # false to allow INSERT/UPDATE/DELETE
sample_size: 100
max_rows: 1000
allowed_schemas:
  - public

# Manual overrides take precedence over auto-detection
column_rules:
  - table: users
    column: email
    pii_type: EMAIL_ADDRESS
    masking_style: partial
  - table: users
    column: internal_id
    pii_type: none          # explicitly mark as NOT PII
    masking_style: none

Development

# Install with dev dependencies
uv sync --all-extras

# Run tests
uv run pytest

# Run tests with verbose output
uv run pytest -v