How to anonymize a PostgreSQL database without breaking it

Why random replacement breaks your data

Replacing every email with a random string looks safe, but it has three common side effects:

• The same person gets a different fake value in every table, so joins and lookups that use that value stop matching.
• Two different people can end up with the same fake value, which fails on a UNIQUE index.
• Every run produces different data, so a bug you saw yesterday cannot be reproduced today.

Use deterministic masking

Deterministic masking turns the same input into the same output every time, using a keyed hash (HMAC) built from a secret seed. [email protected] becomes the same fake address in users.email, in billing.contact and on tomorrow's run.

The seed is the key. A different seed gives different values, and without the seed the original cannot be recovered from the masked value. NULL stays NULL and an empty string stays empty.

Step 1: install and set the environment

Subveil is a single Go binary. Install it with Homebrew, or download a prebuilt binary from the documentation page.

$ brew install subveilhq/tap/subveil

The source, the target and the seed are read from environment variables, never from the config file. Use a read-only role for the source.

export SUBVEIL_SOURCE_DSN="postgresql://readonly@localhost:5432/app"export SUBVEIL_TARGET_DSN="postgresql://developer@localhost:5432/app_staging"export SUBVEIL_SEED="replace-with-a-long-secret"

Step 2: generate a config

subveil init reads the source schema and writes a subveil.yaml that proposes a transformer for the columns that look like personal data, such as emails, names and phone numbers. Treat it as a first draft and review every table.

$ subveil init --schemas public --out subveil.yaml

Step 3: pick a transformer for each column

• email, name, phone, address: realistic fake values that look like the real thing.
• uuid: a deterministic UUID, always safe on unique columns.
• redact: replaces every value with a constant such as [REDACTED]. Do not use it on a unique column.
• null: writes NULL. Quote it in YAML, because a bare null means no value.
• custom_sql: replace the value with a SQL expression evaluated on the source, which can use other columns of the same row.
• jsonb: mask only the listed paths inside a json or jsonb column.

version: 1source:  dsn_env: SUBVEIL_SOURCE_DSNtarget:  dsn_env: SUBVEIL_TARGET_DSNmasking:  seed_env: SUBVEIL_SEEDtables:  public.users:    columns:      email:        transformer: email        domain: example.test      full_name:        transformer: name      phone:        transformer: phone

Step 4: mask personal data inside JSON

Personal data often hides in a jsonb column. List the paths to mask and Subveil leaves the rest of the document alone. Paths that match nothing are skipped, because documents do not all have the same shape.

      profile:        transformer: jsonb        paths:          - path: "$.contact.email"            transformer: email          - path: "$..ssn"            transformer: redact

Step 5: keep unique columns unique

Subveil detects primary keys, unique constraints and unique indexes. On those columns email and uuid always include a hash token, and name, address and phone add a deterministic suffix, for example Ava Smith #3fa91c07. While a pull runs it also remembers the values it has produced, so two different inputs can never end up with the same masked value.

Check varchar(n) limits, because a suffix makes a value longer.

Step 6: check, then copy

plan shows the load order and any circular foreign keys without writing anything. --dry-run reports how many rows a pull would copy. The real pull loads the target in a single transaction, so it either completes or leaves nothing behind.

$ subveil plan --schemas public$ subveil pull --config subveil.yaml --dry-run$ subveil pull --config subveil.yaml

Step 7: verify the result

Do not trust a config you have not tested. Run a query on the target that looks for values that should no longer exist. For example, if you kept the default email domain example.com, this should return 0:

select count(*) from users where email not like '%@example.com';

Repeat it for every sensitive column, and run subveil init again after schema changes to catch new columns.

Frequently asked questions

Is deterministic masking reversible?

Not without your seed. Masked values come from a keyed hash and the original value is not stored. Keep the seed secret, because anyone who has it can test a guess against the mapping.

Does this change my production database?

No. Subveil reads the source through a read-only role inside a consistent snapshot and writes only to the target database.

Will joins still work after masking?

Yes for values that use the same transformer, because the same input always gives the same output. The exception is names in unique columns: they get a suffix, so a name masks differently there than in a non-unique column.

Which databases are supported?

PostgreSQL 14–18. MySQL and MariaDB are not supported yet.