pg-anonymizer

Export your PostgreSQL database anonymized. Replace all sensitive data thanks to faker. Output to a file that you can easily import with psql.

Usage

Run this command by giving a connexion string and an output file name (no need to install first thanks to npx):

npx pg-anonymizer postgres://user:[email protected]:1234/mydb -o dump.sql

☝️ This command requires pg_dump. It may already be installed as soon as PostgreSQL is installed.

Specify list of columns to anonymize

Use --list option with a comma separated list of column name:

npx pg-anonymizer postgres://localhost/mydb \
  --list=email,firstName,lastName,phone

Specifying another list via --list replace the default automatically anonymized values:

email,name,description,address,city,country,phone,comment,birthdate

Customize replacements

You can also choose which faker function you want to use to replace data (default is faker.random.word):

npx pg-anonymizer postgres://localhost/mydb \
  --list=firstName:faker.name.firstName,lastName:faker.name.lastName

👉 You don't need to specify faker function since the command will try to find correct function via column name.

You can use plain text too for static replacements:

npx pg-anonymizer postgres://localhost/mydb \
  --list=textcol:hello,jsoncol:{},intcol:12

Memory limit

Use -m to change pg_dump output memory limit (e.g: 512)

Locale (i18n)

Use -l to change the locale used by faker (default: en)

Import the anonymized file

The anonymized output file is plain SQL text, you can import it with psql.

psql -d mylocaldb < output.sql

Why

There are a bunch of competitors, still I failed to use them:

• postgresql_anonymizer may be hard to setup and may be cumbersome for simple usage. Still, I guess it's the best solution.
• pganonymize fails when it does not use public schema or columns have uppercase characters
• pganonymizer also fails with simple cases. Errors are not explicit and silent.