Seeding Preview Databases with Anonymized Production Snapshots

Synthetic seed data never has the customer with 4,000 orders, the name containing an apostrophe, or the row with a null someone swore could not happen. Production data has all of them — and personal data you cannot put in a preview environment. An anonymised snapshot gives you the shape without the liability.

When to use this pattern

  • Preview environments run against synthetic seeds and keep missing production-only edge cases.
  • You need realistic query shapes and row distributions for performance-sensitive work.
  • Regulation or policy forbids personal data outside production, which rules out a plain copy.

Prerequisites

The pipeline, and where it must not shortcut

Everything hinges on the anonymisation happening somewhere untrusted code can never reach, and on the output being verified before anyone can restore it.

Snapshot, Scrub, Verify, Publish A nightly job restores a production backup into an isolated scrub host with no application access, applies deterministic masking, runs verification assertions, and only then publishes a compressed artifact. Preview provisioning restores that artifact and never touches production. production nightly backup isolated scrub host no application access deterministic masking verification assertions verified artifact preview-seed.dump.gz preview pr-482 preview pr-509 preview pr-511 Previews restore the artifact only. Nothing downstream of the scrub host has a path to production. The verification step is a gate, not a report: a failing assertion must stop publication. Anonymising in place on a production replica is the one shortcut that turns a mistake into a breach.

Complete working example

#!/usr/bin/env bash
# scripts/build-preview-seed.sh — nightly, on the isolated scrub host.
set -euo pipefail
: "${MASK_KEY:?a stable secret; changing it re-randomises every value}"

DB=scrub
psql -c "DROP DATABASE IF EXISTS ${DB}" -c "CREATE DATABASE ${DB}"
pg_restore -d "$DB" --no-owner --no-privileges /backups/production-latest.dump

# --- Deterministic masking ------------------------------------------------------
# hmac(key, value) means the SAME email always maps to the SAME fake email, which
# preserves every join and uniqueness constraint that referenced it.
psql -d "$DB" <<'SQL'
CREATE EXTENSION IF NOT EXISTS pgcrypto;

CREATE OR REPLACE FUNCTION mask_email(v text) RETURNS text AS $$
  SELECT CASE WHEN v IS NULL THEN NULL ELSE
    encode(hmac(v, current_setting('app.mask_key'), 'sha256'), 'hex')
    || '@example.invalid' END;      -- .invalid can never receive mail
$$ LANGUAGE sql IMMUTABLE;

CREATE OR REPLACE FUNCTION mask_name(v text) RETURNS text AS $$
  SELECT CASE WHEN v IS NULL THEN NULL ELSE
    'User ' || substr(encode(hmac(v, current_setting('app.mask_key'), 'sha256'), 'hex'), 1, 8)
    END;
$$ LANGUAGE sql IMMUTABLE;
SQL

psql -d "$DB" -c "SET app.mask_key = '${MASK_KEY}';" <<'SQL'
BEGIN;
UPDATE users SET
  email      = mask_email(email),
  full_name  = mask_name(full_name),
  phone      = CASE WHEN phone IS NULL THEN NULL ELSE '+10000000000' END,
  password_hash = '$2b$12$invalidinvalidinvalidinvalidinvalidinvalidinvalidinv';
UPDATE orders          SET billing_email = mask_email(billing_email);
UPDATE support_tickets SET body = 'redacted for preview use';
-- Payment identifiers are removed rather than masked: a masked token is still a token.
UPDATE payment_methods SET provider_token = NULL, last_four = '0000';
DELETE FROM audit_log WHERE created_at < now() - interval '7 days';
COMMIT;
SQL

# --- Verification: this is a GATE, not a report ---------------------------------
fail=0
check() { local label="$1" sql="$2"
  n=$(psql -qtAX -d "$DB" -c "$sql"); [ "$n" = "0" ] || { echo "LEAK: $label ($n rows)"; fail=1; }; }

check "unmasked emails"   "SELECT count(*) FROM users WHERE email NOT LIKE '%@example.invalid'"
check "real phone digits" "SELECT count(*) FROM users WHERE phone IS NOT NULL AND phone <> '+10000000000'"
check "payment tokens"    "SELECT count(*) FROM payment_methods WHERE provider_token IS NOT NULL"
check "ticket bodies"     "SELECT count(*) FROM support_tickets WHERE body <> 'redacted for preview use'"
[ "$fail" = "0" ] || { echo "verification FAILED — not publishing"; exit 1; }

# --- Publish -------------------------------------------------------------------
pg_dump -d "$DB" -Fc --no-owner | gzip -9 > preview-seed.dump.gz
aws s3 cp preview-seed.dump.gz s3://preview-seeds/preview-seed-$(date +%F).dump.gz
aws s3 cp preview-seed.dump.gz s3://preview-seeds/preview-seed-latest.dump.gz
# In preview provisioning: restore the verified artifact, never production.
      - name: Seed preview database
        run: |
          aws s3 cp s3://preview-seeds/preview-seed-latest.dump.gz - \
            | gunzip | pg_restore -d "$PREVIEW_DATABASE_URL" --no-owner --clean --if-exists

Step-by-step walkthrough

Restore before masking, always into a scratch database. Anonymising against a production replica risks a mistyped WHERE clause writing to something that matters. The scrub host holds a disposable copy and has no route to the application.

Determinism is what preserves the data’s shape. hmac(value, key) maps one input to one output, so a user’s email in users and the same email in orders mask identically and the join survives. Random per-row substitution breaks every relationship and produces a database that looks realistic and behaves incoherently.

Deterministic Masking Preserves Joins; Random Masking Destroys Them With a keyed hash, one production email maps to one masked value in the users table and the same value in three order rows, so the join still resolves. With random replacement, each of the four rows gets a different value and the three orders no longer match any user. DETERMINISTIC — hmac(key, value) users.email → a3f9…@example.invalid orders #1 → a3f9… orders #2 → a3f9… orders #3 → a3f9… join resolves 3 orders, 1 user RANDOM PER ROW users.email → 7c2b… orders #1 → 91de… orders #2 → 4a07… orders #3 → ff31… 3 orphaned orders every user page renders empty The right-hand database is anonymous and useless: its bugs are artefacts of the masking, not of the code. Determinism also preserves unique constraints — random values can collide, and a restore then fails halfway.

Delete rather than mask where the value is the risk. A masked payment token is still a token-shaped string that someone will eventually try to use. Free-text fields — support tickets, notes, addresses — cannot be reliably masked at all, because personal data appears in prose. Replace their content wholesale.

Verification must block publication. The check function asserts, and a single failure prevents the dump from being written. A verification step that logs a warning and publishes anyway provides documentation of a leak rather than prevention of one.

Keep the mask key stable and secret. Stability means yesterday’s and today’s snapshots mask identically, so a preview restored from either behaves the same. Secrecy matters because a known key plus a masked value makes the original recoverable by brute force over a small domain — email addresses very much included.

Verification

# 1. Independent scan of the published artifact for anything that looks personal.
aws s3 cp s3://preview-seeds/preview-seed-latest.dump.gz - | gunzip \
  | pg_restore -f - \
  | grep -cE '[A-Za-z0-9._%+-]+@(?!example\.invalid)[A-Za-z0-9.-]+\.[A-Za-z]{2,}' -P    # → 0

# 2. Referential integrity survived the masking.
psql -d preview -qtAX -c "
  SELECT count(*) FROM orders o LEFT JOIN users u ON u.email = o.billing_email
  WHERE u.id IS NULL;"                                                                  # → 0

# 3. Restore time is acceptable for per-preview provisioning.
time (aws s3 cp s3://preview-seeds/preview-seed-latest.dump.gz - | gunzip \
      | pg_restore -d "$PREVIEW_DATABASE_URL" --no-owner --clean --if-exists)

Expected: zero on the first two, and a restore comfortably under a minute on the third. If the restore takes several minutes, subset the snapshot — the guidance in database mocking and seeding for ephemeral environments covers choosing a referentially consistent sample.

Keeping the column inventory from rotting

The hardest part of this pipeline is not the masking — it is knowing what to mask, in a schema that changes weekly. A column added in a migration is unmasked by default, and nothing about the pipeline notices; the snapshot simply starts carrying personal data.

The durable fix is to make the inventory part of the schema rather than a document. Tag sensitive columns with a Postgres comment or a naming convention, generate the masking statements from that metadata, and add a CI check that fails when a new column matching a sensitivity heuristic — anything named like an email, phone, address, name, or token — ships without a tag. That converts the failure mode from a silent leak into a red build on the pull request that introduced the column, which is the only point at which the person who knows what the column contains is still looking at it.

Where a Newly Added Sensitive Column Gets Caught A migration adds a column named recovery_email. Without schema-level tagging, it passes review, ships, and appears unmasked in the next nightly snapshot. With tagging plus a CI heuristic check, the pull request fails until the column is classified, and the masking statement is generated automatically. migration adds users.recovery_email no tagging tagged schema review passes — nobody thinks of the snapshot masking script unchanged unmasked email in every preview discovered months later, if at all CI heuristic: name matches "email" and carries no sensitivity comment pull request blocked until classified masking statement generated from the tag The check is caught at the only moment someone still knows what the column is for.

Common pitfalls

Masking with random values. Breaks joins and unique constraints, and produces “bugs” that exist only in the snapshot. Always derive from a keyed hash of the original.

Forgetting sequences and materialised views. A restored dump can leave sequences behind the maximum id, so the first insert in a preview collides. pg_restore handles this for logical dumps, but hand-rolled export scripts frequently do not.

Keeping snapshots forever. An anonymised dump is lower risk, not no risk. Expire them on the same lifecycle discipline as any other artifact, per setting artifact retention policies.

Subsetting rather than copying

A full anonymised copy is rarely the right artifact. It takes minutes to restore on every preview, costs storage proportional to production, and adds little signal over a well-chosen subset — because the value of production data is its shape, not its volume.

A referentially consistent subset — a few per cent of rows, chosen so that every foreign key still resolves — restores in seconds and reproduces the distributions that matter: the customer with thousands of orders, the record with an unexpected null, the name with an apostrophe. Those are what synthetic seeds miss, and none of them require the full table.

Building the subset is the only fiddly part. Start from a set of root records, follow foreign keys outward to collect everything they depend on, and stop. Most databases have a tool for this; where they do not, a hand-written traversal over the foreign-key graph is a day’s work and then runs unchanged for years.

The exception is performance work. If a preview exists to reproduce a query plan that only appears at production row counts, a subset genuinely will not do — but that is a specific, occasional need, better served by an on-demand full restore than by making every preview pay for it.

← Back to Database Mocking and Seeding for Ephemeral Environments