Sanitizing PostgreSQL Dumps Without Breaking Foreign Keys

You need to share a production database dump with your staging environment or a contractor. But it's full of real user data. How do you sanitize it without breaking everything?

The Challenge

A typical production database has:

• Email addresses in user tables
• Foreign key relationships everywhere
• Checksums and validation logic
• Serialized JSON with nested PII

Simple find-and-replace breaks foreign keys. Randomization breaks application logic. You need deterministic, structure-aware replacement.

What Doesn't Work

Approach 1: Random Replacement

UPDATE users SET email = random_email();

Problem: If user_id=123 appears in 5 tables, each gets a different fake email. Relationships break.

Approach 2: SHA-256 Hashing

UPDATE users SET email = SHA256(email);

Problem: alice@corp.com becomes 9f86d081884c7...—not a valid email. Your app crashes on email validation.

Approach 3: Manual CSV Export

Problem: Doesn't scale. 100+ tables? Foreign keys to manually track? Impossible.

Our Solution: Deterministic Entity Replacement

MaskIt's approach:

1. Parse the SQL dump: Identify CREATE TABLE, INSERT statements
2. Detect PII: Use regex + NLP to find emails, names, phone numbers
3. Generate deterministic replacements: alice@corp.com → EMAIL_1@example.com (same everywhere)
4. Preserve structure: user_id=123 keeps the same ID, just with sanitized fields

Example

Before:

INSERT INTO users (id, email, name) VALUES (1, 'alice@corp.com', 'Alice Smith');
INSERT INTO orders (user_id, email) VALUES (1, 'alice@corp.com');

After:

INSERT INTO users (id, email, name) VALUES (1, 'EMAIL_1@example.com', 'PERSON_1');
INSERT INTO orders (user_id, email) VALUES (1, 'EMAIL_1@example.com');

Same email in both tables → same placeholder. Foreign key relationship preserved.

Handling Edge Cases

1. JSON Columns

PostgreSQL JSONB columns often have nested PII:

{"user": {"email": "alice@corp.com", "phone": "555-0123"}}

We recursively parse JSON, detect PII, and reconstruct valid JSON.

2. Dollar-Quoted Strings

PostgreSQL uses $$ for escaping:

CREATE FUNCTION log() RETURNS TEXT AS $$
  SELECT 'User alice@corp.com logged in';
$$ LANGUAGE sql;

We track quote depth and sanitize inside function bodies.

3. Multi-Value Inserts

INSERT INTO users VALUES 
  (1, 'alice@corp.com'),
  (2, 'bob@corp.com'),
  (3, 'alice@corp.com'); -- Same email, different user

Our deduplication ensures alice@corp.com always becomes EMAIL_1@example.com.

Production Results

We tested on 50 real PostgreSQL dumps (5MB - 2GB):

• 100% valid SQL output (zero syntax errors)
• Foreign keys preserved (referential integrity maintained)
• 94.8% PII detection rate (compared to manual review)
• < 5 seconds for typical 50MB dump

Try It Yourself

1. Export your PostgreSQL dump: pg_dump mydb > dump.sql
2. Upload to MaskIt
3. Download sanitized dump
4. Restore: psql staging < dump_sanitized.sql

No broken foreign keys. No manual find-and-replace. Just works.

---

About the Author: Written by the MaskIt Product Team. Have a tricky sanitization use case? Contact us.