pg_gdpr_audit — Find Personal Data in Your PostgreSQL Database — Blog

What Does This Tool Do?

pg_gdpr_audit searches all schemas in the database for columns that may contain personally identifiable information (PII). It detects common patterns like email, phone, date_of_birth, first_name and classifies them by sensitivity level. The result is a complete report of all potentially GDPR-relevant columns — including whether they are encrypted or masked.

How Is It Called?

Claude CLI (Natural Language)

● ● ● Claude CLI — GDPR Audit

claude> Are there unprotected personal data in my database?

▶ Calling tools...
→ pg_connect { "profile": "production-db" }
✓ Connected to production-db (PostgreSQL 16.x)

→ pg_gdpr_audit {}

GDPR audit complete. 3 schemas scanned, 26 PII columns found:

⚠ ecommerce.customers — 6 PII columns
   email           (text)   — Level 3 — AES-256 required
   first_name      (text)   — Level 3 — AES-256 required
   last_name       (text)   — Level 3 — AES-256 required
   phone           (text)   — Level 3 — AES-256 required
   shipping_address (jsonb) — Level 2 — access logging
   billing_address (jsonb) — Level 2 — access logging

⚠ healthcare.patients — 5 PII columns
   first_name      (text)   — Level 3 — AES-256 required
   last_name       (text)   — Level 3 — AES-256 required
   email           (text)   — Level 3 — AES-256 required
   phone_number    (text)   — Level 3 — AES-256 required
   date_of_birth   (date)   — Level 3 — AES-256 required

Recommendation: 26 PII columns require immediate action (9x Level 3, 2x Level 2).

MCP Protocol (Programmatic)

● ● ● MCP Protocol — Connection

// Programmatic invocation via MCP SDK
const result = await client.callTool("pg_connect", { "profile": "production-db" });

// Response:
{ "status": "connected", "version": "PostgreSQL 16.x" }

● ● ● MCP Protocol — GDPR Audit

// Programmatic invocation via MCP SDK
const result = await client.callTool("pg_gdpr_audit", {});

// Response:
{
"pii_columns_found": 26,
"pii_columns": [
    {
      "schema": "ecommerce",
      "table": "customers",
      "column": "email",
      "data_type": "text",
      "pii_type": "email",
      "pii_level": 3,
      "recommendation": "Requires AES-256 encryption"
    },
    {
      "schema": "ecommerce",
      "table": "customers",
      "column": "first_name",
      "data_type": "text",
      "pii_type": "name",
      "pii_level": 3,
      "recommendation": "Requires AES-256 encryption"
    },
    {
      "schema": "healthcare",
      "table": "patients",
      "column": "email",
      "data_type": "text",
      "pii_type": "email",
      "pii_level": 3,
      "recommendation": "Requires AES-256 encryption"
    },
    {
      "schema": "healthcare",
      "table": "patients",
      "column": "date_of_birth",
      "data_type": "date",
      "pii_type": "dob",
      "pii_level": 3,
      "recommendation": "Requires AES-256 encryption"
    }
]
}

What Happens Under the Hood?

The tool searches information_schema.columns with pattern matching on known PII column names:

-- PII column detection via information_schema.columns
SELECT table_schema, table_name, column_name, data_type FROM information_schema.columns WHERE table_schema NOT IN ('pg_catalog', 'information_schema') AND ( column_name ILIKE '%email%' OR column_name ILIKE '%phone%' OR column_name ILIKE '%birth%' OR column_name ILIKE '%first_name%' OR column_name ILIKE '%last_name%' OR column_name ILIKE '%address%' OR column_name ILIKE '%ssn%' OR column_name ILIKE '%passport%' OR column_name ILIKE '%national_id%' ) ORDER BY table_schema, table_name, column_name;

Additionally, the tool checks whether PII columns are encrypted. It detects encrypted columns through column comments (e.g., COMMENT ON COLUMN ... IS 'encrypted:aes256') or special data types like bytea for columns that would normally be varchar.

Output in Detail

pii_columns_found — Total number of detected PII columns
schema / table / column — Full path to the PII column
data_type — PostgreSQL data type of the column
pii_type — Classification: email, name, phone, dob, address
pii_level — Sensitivity level: 1 (low), 2 (medium), 3 (high)
recommendation — Recommended protection measure (e.g., AES-256, access logging)

Pro Tip: The tool detects PII based on column names — not content. Columns like metadata or notes that might contain PII in JSON fields are only detected if a compliance policy file with custom patterns is configured.

What Does the Test Suite Validate?

The test suite validates pg_gdpr_audit against a PostgreSQL instance with a healthcare.patients schema containing typical PII columns (date_of_birth, email, phone_number):

● ● ● vitest run — Testsuite

✓ pg_gdpr_audit > detects PII columns in healthcare schema
✓ pg_gdpr_audit > counts unprotected columns correctly
✓ pg_gdpr_audit > returns schemas_scanned count

Test Files 1 passed (1)
Tests 3 passed (3)
Duration 198ms

Test Data Scenario

The seed database contains:

healthcare.patients — Patients with date_of_birth, email, phone_number, first_name, last_name (all unencrypted)
app.users — Users with encrypted email field (bytea + comment)
perf.transactions — Financial data without PII columns (control group)

Tests validate that:

PII columns in the healthcare schema are correctly detected
The unprotected column count (unprotected_count) is accurate
The scanned schema count (schemas_scanned) is correctly returned

Summary

information_schema

Data Source

Output Fields

Tests

read-only

Type

pg_gdpr_audit turns the manual process of searching database schemas for PII into an automated, repeatable check. Ideal as a regular compliance scan or before a GDPR audit.

Combined with pg_permission_audit (→ who has access to PII?), it forms the core of the /pg-comply workflow skill.

pg_gdpr_audit — Find Personal Data in Your PostgreSQL Database