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:

Scenario	What the AI does
"Show me the last 5 registrations"	Keeps everything masked — browsing doesn't need real data
"How many users signed up last month?"	Aggregate query, no PII in results
"What is the email for registration 5015?"	Reveals email — user explicitly asked for it
"Show me John's full name"	Reveals first_name, last_name — user asked to see the value
"Are there duplicate registrations?"	Keeps masked — duplicates are detectable from masked patterns

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

Style	Example	Description
partial (default)	j***@e***.com	Shows enough structure to be useful, hides the sensitive parts
full	[EMAIL ADDRESS]	Complete replacement with a type label
pseudonymize	user_a3f2@masked.invalid	Deterministic fake values — same input always produces the same output, preserving relationships across queries
none	john@example.com	No masking (for columns you've explicitly marked as safe)

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

Flag	Description
-c, --connection-string	PostgreSQL connection string (highest priority)
--config	Path to YAML config file
--read-only	Only allow SELECT queries (default)
--read-write	Allow INSERT, UPDATE, DELETE, and other write queries

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.

Parameter	Type	Description
sql	string	SQL query (write queries require read-only mode to be disabled)
params	dict	Query parameters for parameterized queries
reveal_columns	list[string]	Column names to show unmasked
reveal_types	list[string]	PII entity types to show unmasked (e.g. EMAIL_ADDRESS, PERSON)

describe_schema

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

Parameter	Type	Description
table	string	Table name (omit for all tables)
show_pii	bool	Show PII detection status (default: true)

explain_query

Show the PostgreSQL execution plan for a query.

configure_masking

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

Parameter	Type	Description
table	string	Table name
column	string	Column name
masking_style	string	partial, full, pseudonymize, or none
pii_type	string	PII entity type override

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