Confession: for about two years, my “staging database” for one client project was a nightly pg_dump of production with the passwords column nulled out. That was the whole anonymisation strategy. Real names, real emails, real phone numbers, sitting on a staging box that three contractors and one intern had credentials for. I knew it was bad. I kept telling myself I’d fix it after the next release.<\/p>\n
What finally made me fix it wasn’t a compliance audit. It was a support ticket. A customer got a “your order has shipped” email from staging, because someone ran a queue worker against the staging copy and the email column was real. That’s the day I installed PostgreSQL Anonymizer, and it’s been in every Postgres project I’ve touched since.<\/p>\n
Version 3.2 came out on September 4th, and it changes two things I care about. The pseudonymisation functions got a rewrite (40x faster, per Dalibo), and there’s a new security barrier that stops superusers from running masking at all. The second one broke my script on the first run, which is why I’m writing this post instead of the one I had planned.<\/p>\n
The extension is called A rule looks like this:<\/p>\n The The reason I like the My previous approach, the one I’m slightly embarrassed by, was a bash file that ran First, the real data touched the staging disk before masking ran. If the mask step failed halfway (it did, twice, both times on a foreign key I hadn’t accounted for), staging sat there with production data until someone noticed.<\/p>\n Second, Third, it was slow. A 9 million row The extension’s anonymous dump mode fixes the first problem outright. You create a role that is flagged as masked, and plain Before 3.2, I ran the dump as the That’s intentional. 3.2 introduces a barrier where the extension refuses to run any masking function on behalf of a superuser. Dalibo’s stated reason is least privilege, and it sits next to three CVEs fixed in the same release (CVE-2026-19633, CVE-2026-19634, CVE-2026-83534), all of which are privilege escalation paths. Two of them let a user gain superuser under the right conditions, and the announcement says the risk is “very high” on PostgreSQL 14 and on instances upgraded from 14 or earlier. If you’re on that version, upgrade the extension first and read this post second.<\/p>\n The fix on my side was a dedicated masked role, which is what the anonymous dumps docs<\/a> recommend anyway:<\/p>\n And then the dump job becomes ordinary The The headline feature in 3.2 is the replacement of The old way:<\/p>\n The new way, with a locale and a salt:<\/p>\n The Two things to know before you trust this. The salt matters. Without one, anybody who can guess the fake dataset and the hashing method can, in principle, reverse a pseudonym by brute force over likely inputs. Dalibo says this plainly in the docs section titled “Pseudonymization IS NOT Anonymization”, and I’d rather quote their caution than pretend it’s a solved problem. A salt stored outside the database (in your secrets manager, not in the masking rule text if that rule is in version control) closes most of that gap for a staging use case.<\/p>\n The second is collisions. The default fake dataset is 1000 values per category. If you have 50,000 distinct last names and seed them into a pool of 1000, you get collisions by construction. That’s fine for On speed: I don’t have a rigorous benchmark to give you, and I’m suspicious of “40x” claims in general. What I can say is that the 9 million row events table that used to take 40 minutes under my UPDATE script now dumps in under 4 with If you’re one developer or a team of three with a Hetzner box and a Postgres container, here’s the shape of what I run now. I wrote up the box itself in what this blog actually runs on<\/a> if you want the infrastructure side.<\/p>\n A systemd timer on the production host runs Dynamic masking for live analyst access is the mode I haven’t adopted and probably won’t. Technically the dump role above uses the same machinery (results are rewritten on the fly for roles flagged as masked), but it runs once a night. Handing an analyst a masked role on production means every query they run pays the masking cost, and I’d rather they hit the nightly anonymised copy. If you’re using pg_stat_statements to chase slow queries the way I described in four queries before adding an index<\/a>, dynamic masking will show up in your top statements and confuse you.<\/p>\n One more honest limitation. The extension masks columns you label. It does nothing about PII that ends up in a JSONB blob, a free-text Run one query against your staging database:<\/p>\n If you recognise any of those people, that’s the whole argument. Install the extension on the production replica (Debian and RPM packages, a Docker image, and an Ansible role all exist), write labels for the five most obvious columns, create a non-superuser dump role, and switch your staging refresh to a If your Postgres is on 14 or was upgraded from 14, upgrade the extension to 3.2 today regardless of anything else in this post. The CVEs are the part of the announcement that doesn’t wait for a convenient sprint.<\/p>\nanon<\/code> once it’s installed, and the mental model is simple. You attach a masking rule to a column using a SECURITY LABEL<\/code>. Then you pick how that rule gets applied: permanently rewrite the table (static masking), rewrite on the fly for certain roles (dynamic masking), or apply it while producing a dump (anonymous dumps). The release announcement<\/a> lists six strategies, but those three cover everything I’ve needed.<\/p>\nCREATE EXTENSION IF NOT EXISTS anon CASCADE;\nSELECT anon.init();\n\nSECURITY LABEL FOR anon ON COLUMN customers.full_name\n IS 'MASKED WITH FUNCTION anon.fake_last_name()';\n\nSECURITY LABEL FOR anon ON COLUMN customers.email\n IS 'MASKED WITH FUNCTION anon.random_email()';\n\nSECURITY LABEL FOR anon ON COLUMN customers.phone\n IS 'MASKED WITH FUNCTION anon.partial(phone, 0, ''***-***-'', 4)';\n<\/code><\/pre>\nanon.init()<\/code> call loads a small fake dataset (about 1000 values per category, English only by default) that the faking functions draw from. If you skip it, fake_last_name()<\/code> errors out and the message doesn’t tell you why. I lost twenty minutes to that in 2024 and I still forget it on fresh installs.<\/p>\nSECURITY LABEL<\/code> approach over a hand-written UPDATE script is that the rules live in the database schema. They travel with the schema dump. When a colleague adds a date_of_birth<\/code> column, the review question is “where’s the masking label?” and it’s visible in the same migration.<\/p>\nWhere my old script went wrong<\/h2>\n
pg_dump<\/code>, restored it into staging, then ran a psql -f mask.sql<\/code> with a bunch of UPDATE statements. Three problems with that, and I hit all of them.<\/p>\nUPDATE customers SET email = 'user' || id || '@example.com'<\/code> destroys the relationships you want to test against. Every customer had a distinct email, fine, but customer 42’s email in the orders_archive<\/code> table no longer matched customer 42 in customers<\/code>. Join-heavy reports fell over on staging and nowhere else.<\/p>\nevents<\/code> table with a masked ip_address<\/code> column took 40 minutes under my UPDATE approach because the fake-value function was called per row with no caching.<\/p>\npg_dump<\/code> run as that role sees only masked values, so the rules are applied while the dump is being written. Production data never lands on the target machine. (If you remember pg_dump_anon.sh<\/code> from older versions, it’s deprecated now; the masked-role approach replaced it.) I now do this from a dedicated role on the production replica, which brings me to the 3.2 change that bit me.<\/p>\nThe superuser barrier that broke my cron job<\/h2>\n
postgres<\/code> superuser because that’s what my Ansible role had always done and nobody had questioned it. After upgrading, the job failed with a permissions error even though the role could obviously do anything.<\/p>\nCREATE ROLE dump_anon LOGIN PASSWORD '...';\nALTER ROLE dump_anon SET anon.transparent_dynamic_masking = true;\nSECURITY LABEL FOR anon ON ROLE dump_anon IS 'MASKED';\nGRANT pg_read_all_data TO dump_anon;\n<\/code><\/pre>\npg_dump<\/code>, run as that role:<\/p>\npg_dump app --user dump_anon \\\n --no-security-labels \\\n --exclude-extension=anon \\\n --format=custom \\\n --file=app_anon.dump\n<\/code><\/pre>\n--no-security-labels<\/code> flag matters more than it looks. It strips the masking rules out of the dump, so whoever restores staging can’t read your masking policy and reason backwards from it. --exclude-extension<\/code> needs pg_dump 17 or later; on older versions the docs suggest --extension plpgsql<\/code> instead. There’s an escape hatch, anon.nosuperuser = false<\/code>, that restores the old behaviour. I’d rather not. If a piece of software tells me my cron job has been running with more privilege than it needs for two years, the right response is to fix the cron job, not disable the warning.<\/p>\nSeeded functions: same input, same fake, 40x faster<\/h2>\n
anon.pseudo_*<\/code> with anon.seeded_*<\/code>. Pseudonymisation here means the fake value is deterministic. Feed the same real email in, get the same fake email out, every time. That’s what makes joins survive masking: customer 42’s fake email in customers<\/code> matches customer 42’s fake email in orders_archive<\/code>, because both were derived from the same seed.<\/p>\nSECURITY LABEL FOR anon ON COLUMN customers.email\n IS 'MASKED WITH FUNCTION anon.pseudo_email(email)';\n<\/code><\/pre>\nSECURITY LABEL FOR anon ON COLUMN customers.email\n IS 'MASKED WITH FUNCTION anon.seeded_email(email, ''en_US'', ''staging-2026'')';\n\nSECURITY LABEL FOR anon ON COLUMN customers.city\n IS 'MASKED WITH FUNCTION anon.seeded_city(city, ''fr_FR'', ''staging-2026'')';\n<\/code><\/pre>\npseudo_*<\/code> functions still exist but are deprecated, so migrate now while it’s a find-and-replace and not a 2am outage. The masking functions docs<\/a> list all ten seeded functions and the signature is consistent: seed, locale, salt.<\/p>\nlast_name<\/code> (two customers sharing a surname is realistic). It is not fine for email<\/code> if your schema has a unique constraint on it, which mine did. The seeded email function builds from name components so the effective pool is larger, but I still hit two unique-violation errors on a 400k row table. My fix was to append the pseudonymised id: anon.seeded_email(email, 'en_US', 'salt') || '.' || id<\/code>. Ugly, and I’m open to a better idea.<\/p>\nseeded_*<\/code> rules applied through the masked role. Some of that is the dump path itself and not the functions. I’m still not sure how much.<\/p>\nWhere this sits in a small team’s setup<\/h2>\n
pg_dump<\/code> nightly as the dump_anon<\/code> role, writes to a local file, and rsyncs it to the staging host. A second timer on staging drops and recreates the database from that file. The masking rules live in a migration file in the app repo, applied by the same migration runner as everything else, so a new PII column without a label fails code review rather than leaking.<\/p>\nnotes<\/code> column, or a log table with request bodies. My support_tickets.body<\/code> column had customer phone numbers in it for months after I thought staging was clean. I now mask that column with a plain MASKED WITH VALUE 'redacted'<\/code> because there’s no realistic fake for free text, and the support features on staging get tested with seed data instead.<\/p>\nWhat to do this week<\/h2>\n
SELECT email, phone FROM customers ORDER BY random() LIMIT 5;\n<\/code><\/pre>\npg_dump<\/code> run as that role. It took me an afternoon, most of which was the unique-constraint problem above.<\/p>\n