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.
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.
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.
What postgresql data masking actually means here
The extension is called anon once it's installed, and the mental model is simple. You attach a masking rule to a column using a SECURITY LABEL. 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 lists six strategies, but those three cover everything I've needed.
A rule looks like this:
CREATE EXTENSION IF NOT EXISTS anon CASCADE;
SELECT anon.init();
SECURITY LABEL FOR anon ON COLUMN customers.full_name
IS 'MASKED WITH FUNCTION anon.fake_last_name()';
SECURITY LABEL FOR anon ON COLUMN customers.email
IS 'MASKED WITH FUNCTION anon.random_email()';
SECURITY LABEL FOR anon ON COLUMN customers.phone
IS 'MASKED WITH FUNCTION anon.partial(phone, 0, ''***-***-'', 4)';
The anon.init() 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() 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.
The reason I like the SECURITY LABEL 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 column, the review question is "where's the masking label?" and it's visible in the same migration.
Where my old script went wrong
My previous approach, the one I'm slightly embarrassed by, was a bash file that ran pg_dump, restored it into staging, then ran a psql -f mask.sql with a bunch of UPDATE statements. Three problems with that, and I hit all of them.
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.
Second, UPDATE customers SET email = 'user' || id || '@example.com' destroys the relationships you want to test against. Every customer had a distinct email, fine, but customer 42's email in the orders_archive table no longer matched customer 42 in customers. Join-heavy reports fell over on staging and nowhere else.
Third, it was slow. A 9 million row events table with a masked ip_address column took 40 minutes under my UPDATE approach because the fake-value function was called per row with no caching.
The extension's anonymous dump mode fixes the first problem outright. You create a role that is flagged as masked, and plain pg_dump 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 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.
The superuser barrier that broke my cron job
Before 3.2, I ran the dump as the postgres 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.
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.
The fix on my side was a dedicated masked role, which is what the anonymous dumps docs recommend anyway:
CREATE ROLE dump_anon LOGIN PASSWORD '...';
ALTER ROLE dump_anon SET anon.transparent_dynamic_masking = true;
SECURITY LABEL FOR anon ON ROLE dump_anon IS 'MASKED';
GRANT pg_read_all_data TO dump_anon;
And then the dump job becomes ordinary pg_dump, run as that role:
pg_dump app --user dump_anon \
--no-security-labels \
--exclude-extension=anon \
--format=custom \
--file=app_anon.dump
The --no-security-labels 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 needs pg_dump 17 or later; on older versions the docs suggest --extension plpgsql instead. There's an escape hatch, anon.nosuperuser = false, 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.
Seeded functions: same input, same fake, 40x faster
The headline feature in 3.2 is the replacement of anon.pseudo_* with anon.seeded_*. 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 matches customer 42's fake email in orders_archive, because both were derived from the same seed.
The old way:
SECURITY LABEL FOR anon ON COLUMN customers.email
IS 'MASKED WITH FUNCTION anon.pseudo_email(email)';
The new way, with a locale and a salt:
SECURITY LABEL FOR anon ON COLUMN customers.email
IS 'MASKED WITH FUNCTION anon.seeded_email(email, ''en_US'', ''staging-2026'')';
SECURITY LABEL FOR anon ON COLUMN customers.city
IS 'MASKED WITH FUNCTION anon.seeded_city(city, ''fr_FR'', ''staging-2026'')';
The pseudo_* 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 list all ten seeded functions and the signature is consistent: seed, locale, salt.
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.
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 last_name (two customers sharing a surname is realistic). It is not fine for email 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. Ugly, and I'm open to a better idea.
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 seeded_* 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.
Where this sits in a small team's setup
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 if you want the infrastructure side.
A systemd timer on the production host runs pg_dump nightly as the dump_anon 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.
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, dynamic masking will show up in your top statements and confuse you.
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 notes column, or a log table with request bodies. My support_tickets.body 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' because there's no realistic fake for free text, and the support features on staging get tested with seed data instead.
What to do this week
Run one query against your staging database:
SELECT email, phone FROM customers ORDER BY random() LIMIT 5;
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 pg_dump run as that role. It took me an afternoon, most of which was the unique-constraint problem above.
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.
I do this kind of database and infrastructure cleanup for clients as part of my freelance work, usually as the unglamorous first week of a bigger project. It's never the exciting part. It's the part that stops the shipped-order email from going to a real customer.
Originally published at abrarqasim.com. I write there about React, PHP, Rust, Go and the AI tooling around them.
Top comments (0)