Anonymizing Production Snapshots for Local Use

A bug only reproduces with production-shaped data, so someone restores last night's backup onto their laptop — with 2.3 million real customer emails, addresses and order histories — and a week later a local test run sends a password-reset email to a real customer through a misconfigured SMTP setting. Synthetic seed data is safe but too tidy to reproduce many bugs; raw snapshots reproduce them and create a data-protection incident. This page produces a subset of production that keeps the shapes, distributions and relationships while replacing personal data deterministically, and verifies the result before anyone downloads it. It is part of database seeding and fixture parity.

The process runs inside the production security boundary; only the masked, verified output ever reaches a laptop.

Diagnostic

Find personal data in the schema and check whether existing local copies contain real values:

#!/usr/bin/env bash
set -euo pipefail
psql "$LOCAL_DATABASE_URL" -Atc "
  select table_name || '.' || column_name from information_schema.columns
  where table_schema = 'public' and column_name ~* '(email|phone|name|address|birth|ip|iban|tax|ssn)'
  order by 1" | head -12
psql "$LOCAL_DATABASE_URL" -Atc "select count(*) from customers where email !~ '@example\.(test|com)$'"

Expected bad output:

customers.email
customers.full_name
customers.phone
addresses.line1
addresses.postal_code
orders.shipping_name
events.ip_address
2318842

Seven columns hold personal data, and every one of 2.3 million local customer rows has a real email address.

Root cause

Production data is copied locally because it is the fastest way to get realistic volumes, edge cases and relationships, and because nothing makes the safe path easier than the unsafe one. Personal data is spread wider than the obvious columns: denormalised copies (a shipping_name on every order), JSON blobs, free-text notes and audit logs all contain it, so masking a handful of columns by hand misses some. Random masking breaks relationships and makes bugs irreproducible — the same customer gets different fake emails in two tables. And once a raw snapshot is on a laptop, it spreads to backups, screenshots and test fixtures. A workable process needs three properties: subsetting (a small, representative slice), deterministic masking (the same input always maps to the same fake value, so joins still work), and verification (a scan that proves no known personal pattern survived).

Resolution

1. Declare masking rules per column in a reviewed file, including the non-obvious places:

tables:
  customers:
    email: "email"
    full_name: "name"
    phone: "phone"
  addresses:
    line1: "street"
    postal_code: "keep_prefix:2"
  orders:
    shipping_name: "name"
  events:
    ip_address: "ipv4"
    metadata: "json_drop:[customer_email,customer_phone]"
  support_notes:
    body: "redact"
subset:
  root: customers
  where: "created_at > now() - interval '180 days'"
  percent: 5

2. Mask deterministically in the database with a keyed hash, so the same real value always becomes the same fake value and joins survive:

CREATE EXTENSION IF NOT EXISTS pgcrypto;
CREATE OR REPLACE FUNCTION mask_email(v text, salt text) RETURNS text
LANGUAGE sql IMMUTABLE AS $$
  SELECT 'user_' || substr(encode(hmac(lower(v), salt, 'sha256'), 'hex'), 1, 12) || '@example.test'
$$;
CREATE OR REPLACE FUNCTION mask_name(v text, salt text) RETURNS text
LANGUAGE sql IMMUTABLE AS $$
  SELECT 'Customer ' || upper(substr(encode(hmac(v, salt, 'sha256'), 'hex'), 1, 6))
$$;
UPDATE customers SET email = mask_email(email, current_setting('mask.salt')),
                     full_name = mask_name(full_name, current_setting('mask.salt')),
                     phone = '+1555' || lpad((abs(hashtext(phone)) % 10000000)::text, 7, '0');
UPDATE orders SET shipping_name = mask_name(shipping_name, current_setting('mask.salt'));
UPDATE support_notes SET body = '[redacted]';

The salt lives in the production secret store and is never shipped with the snapshot, so masked values cannot be reversed by hashing guesses.

3. Run the pipeline inside the production boundary on a restored copy, subset first to keep it small, then mask, verify and export:

#!/usr/bin/env bash
set -euo pipefail
pg_restore --no-owner -d "$SCRATCH_DB" /backups/latest.dump
psql "$SCRATCH_DB" -v ON_ERROR_STOP=1 -c "SET mask.salt = '$MASK_SALT'" -f masking/subset.sql -f masking/mask.sql
./masking/verify.sh "$SCRATCH_DB"
pg_dump --no-owner -Fc "$SCRATCH_DB" -f /exports/shop-masked-$(date +%F).dump

4. Verify before export — fail the pipeline if any known personal pattern remains:

#!/usr/bin/env bash
set -euo pipefail
db="$1"
leaks=$(psql "$db" -Atc "
  select 'customers.email', count(*) from customers where email !~ '^user_[0-9a-f]{12}@example\.test$'
  union all select 'orders.shipping_name', count(*) from orders o join customers c on c.id = o.customer_id where o.shipping_name !~ '^Customer [0-9A-F]{6}$'
  union all select 'events.metadata', count(*) from events where metadata::text ~* '@[a-z0-9-]+\.(com|net|org|de)'" | awk -F'|' '$2 > 0')
[ -z "$leaks" ] && echo "verification passed" || { echo "PII remains:"; echo "$leaks"; exit 1; }

5. Give developers one command to fetch and restore the latest verified export:

#!/usr/bin/env bash
set -euo pipefail
aws s3 cp "s3://acme-dev-snapshots/shop-masked-latest.dump" /tmp/shop.dump
docker compose exec -T db dropdb -U postgres --if-exists shop
docker compose exec -T db createdb -U postgres shop
docker compose exec -T db pg_restore -U postgres --no-owner -d shop < /tmp/shop.dump
rm -f /tmp/shop.dump

Expected output

$ ./masking/verify.sh "$SCRATCH_DB"
verification passed
$ psql "$LOCAL_DATABASE_URL" -Atc "select email, full_name from customers limit 2"
[email protected]|Customer 8C21F0
[email protected]|Customer 1D7B4A
$ psql "$LOCAL_DATABASE_URL" -Atc "select count(*) from customers"
115942

The local database has about 116,000 customers — 5% of the recent ones — with realistic relationships and volumes, and every personal field is replaced consistently across tables.

The same real customer maps to the same fake email and name everywhere, so a bug report that references an order can be followed through customers, addresses and events in the masked copy exactly as in production. Because the mapping depends on a secret salt, nobody can reverse it by hashing a list of known emails.

Prevention

1. Fail the pipeline on new unmasked columns. Compare the schema's PII-looking columns with the masking rules on every run and stop if a new column is not covered.

2. Point local SMTP at a capture server so even a masked address cannot receive mail; see capturing outbound email locally with Mailpit.

3. Expire local copies. Name exports by date and have the doctor script warn when the local copy is older than a month, so stale snapshots are refreshed rather than shared around.

Platform caveats

Apple Silicon (ARM64): pg_restore from the Postgres container image matches the server version; avoid using a host pg_restore of a different major version, which fails on newer dump formats.

Large databases: subsetting must preserve referential integrity; tools such as Jailer or custom subset SQL that follows foreign keys from a root table avoid orphaned rows.

Regulated data: masking reduces risk but may not satisfy every regulation for every field; involve the data protection officer before distributing any production-derived data.

Rollback

Drop the local database and reseed from synthetic fixtures:

#!/usr/bin/env bash
set -euo pipefail
docker compose exec -T db dropdb -U postgres --if-exists shop
docker compose exec -T db createdb -U postgres shop
make db:migrate db:seed

Frequently Asked Questions

Related

• Make database seeds deterministic across machines
• Reset local database state between test runs
• Scan CI logs for accidentally printed secrets