PostgreSQL Anonymizer

Prerequisites

• An active Azure subscription, an SSH public key, and a VNet and subnet in the target region
• A subscription to this listing on Azure Marketplace
• A network security group that allows TCP 22 (administration) and TCP 5432 (the PostgreSQL wire protocol) from your client network. In production, restrict 5432 to your application subnet.
• Recommended virtual machine size: Standard_B2s (2 vCPU, 4 GB RAM) for development, evaluation and light workloads. For larger datasets and higher query rates, choose a memory-optimized size such as Standard_E2s_v5 or larger.

Deploy the virtual machine

Create the VM from the image, and open only SSH and the PostgreSQL port to your own network:

# Accept the Marketplace terms once per subscription (required before the first CLI deploy)
az vm image terms accept --urn sac1647283583153:postgresql-anonymizer:default:latest
az vm create \
  --resource-group my-rg \
  --name pg-anon-1 \
  --image sac1647283583153:postgresql-anonymizer:default:latest \
  --size Standard_B2s \
  --admin-username azureuser \
  --generate-ssh-keys \
  --public-ip-sku Standard
az vm open-port --resource-group my-rg --name pg-anon-1 --port 5432 --priority 900

Retrieve your per-VM credentials

On first boot, the VM creates its own passwords and TLS certificate. Connect over SSH and read the root-only credentials file:

sudo cat /root/postgresql-anonymizer-credentials.txt

The file contains the postgres superuser password, the analyst masked role password, and ready-to-paste connection strings. The file is mode 0600 and owned by root, and the values in it exist only on this VM.

The postgres.host line is filled in from the VM's own network metadata. Azure's instance metadata service returns an empty value for Standard SKU public IPs, so the line may show the VM's private address. When you connect from outside Azure, use the VM's public IP address from the portal or from az vm list-ip-addresses.

Confirm the service is healthy

Check that PostgreSQL and the credential guard are active, and that the extension is loaded and initialized:

systemctl is-active postgresql.service postgresql-anonymizer-credguard.service
sudo -u postgres psql -tAc "SELECT version();"
sudo -u postgres psql -d anondb -tAc "SELECT extname || ' ' || extversion FROM pg_extension WHERE extname='anon';"
sudo -u postgres psql -tAc "SHOW shared_preload_libraries;"
sudo -u postgres psql -d anondb -tAc "SELECT anon.is_initialized();"

anon.is_initialized() must return t. This check proves that the synthetic data sets loaded. The loader only emits a notice, not an error, if they are missing, so this check is the reliable signal.

Inspect the demo data and the masking rules

The anondb database ships a customer table holding realistic personal data, and a masking rule for every sensitive column. Look at the rules first:

sudo -u postgres psql -d anondb -c "SELECT attname AS column, masking_function FROM anon.pg_masking_rules WHERE attrelid = 'public.customer'::regclass ORDER BY attname;"

Each rule was declared with a SECURITY LABEL statement. For example, the email column is masked with a synthetic email generator, and the national identifier is replaced by a stable hash. Show the email rule with:

sudo -u postgres psql -d anondb -tAc "SELECT 'email -> ' || masking_function FROM anon.pg_masking_rules WHERE attrelid = 'public.customer'::regclass AND attname = 'email';"

Dynamic masking: the same query, two roles

This is the core of the product. The analyst role is labeled MASKED, so PostgreSQL rewrites its queries to return anonymized values. The postgres owner is not masked and sees the real data. Both roles run exactly the same SQL against exactly the same table.

First, query as the owner to see the real data:

PGPASSWORD='<POSTGRES_PASSWORD>' psql "host=127.0.0.1 port=5432 dbname=anondb user=postgres sslmode=require" -c "SELECT id, first_name, last_name, email, phone, city FROM customer ORDER BY id LIMIT 4;"

Now run the same query as the masked role to see the anonymized view:

PGPASSWORD='<ANALYST_PASSWORD>' psql "host=127.0.0.1 port=5432 dbname=anondb user=analyst sslmode=require" -c "SELECT id, first_name, last_name, email, phone, city FROM customer ORDER BY id LIMIT 4;"

The masked role sees plausible substitute names, email addresses on the reserved example.com and example.net domains, phone numbers with the subscriber digits starred out, and invented city names. The row count, the column types and the primary keys are unchanged, so application code and joins still behave normally.

Confirm that the stored data was never altered. The owner still sees the originals after the masked read:

PGPASSWORD='<POSTGRES_PASSWORD>' psql "host=127.0.0.1 port=5432 dbname=anondb user=postgres sslmode=require" -tAc "SELECT email FROM customer WHERE id = 1;"

Both connections use TLS. Verify this directly:

PGPASSWORD='<ANALYST_PASSWORD>' psql "host=127.0.0.1 port=5432 dbname=anondb user=analyst sslmode=require" -tAc "SELECT ssl FROM pg_stat_ssl WHERE pid = pg_backend_pid();"

Declare your own masking rules

Masking a column takes one statement. Mark a role as masked, and then label each column with the function that should replace it:

sudo -u postgres psql -d anondb -c "CREATE TABLE IF NOT EXISTS staff (id int PRIMARY KEY, name text, work_email text);"
sudo -u postgres psql -d anondb -c "INSERT INTO staff VALUES (1, 'Helen Ashworth', 'helen.ashworth@corp.example') ON CONFLICT (id) DO NOTHING;"
sudo -u postgres psql -d anondb -c "SECURITY LABEL FOR anon ON COLUMN staff.name IS 'MASKED WITH FUNCTION anon.fake_last_name()';"
sudo -u postgres psql -d anondb -c "SECURITY LABEL FOR anon ON COLUMN staff.work_email IS 'MASKED WITH FUNCTION anon.fake_email()';"

Grant the masked role access, and read the table back through that role to see the rules take effect:

sudo -u postgres psql -d anondb -c "GRANT SELECT ON staff TO analyst;" PGPASSWORD='<ANALYST_PASSWORD>' psql "host=127.0.0.1 port=5432 dbname=anondb user=analyst sslmode=require" -c "SELECT * FROM staff;"

Useful masking functions include anon.fake_first_name(), anon.fake_last_name(), anon.fake_email() and anon.fake_city(). Use anon.hash(column) for a stable pseudonym, anon.partial(column, 4, '*******', 0) to keep a prefix, and MASKED WITH VALUE NULL to blank a column entirely. The newer anon.dummy_* family is also available and is upstream's recommendation for new work.

To mark any role as masked, run:

sudo -u postgres psql -d anondb -c "SECURITY LABEL FOR anon ON ROLE analyst IS 'MASKED';"

Static anonymization: rewrite the data permanently

Dynamic masking leaves the stored data intact. When you need a sanitized copy, for example to hand a dataset to a development team, static anonymization rewrites the values in place.

Static anonymization is irreversible, so the image provides a dedicated customer_static table for the demonstration. Look at the table first, as the unmasked owner:

sudo -u postgres psql -d anondb -c "SELECT id, first_name, last_name, email, national_id FROM customer_static ORDER BY id LIMIT 4;"

Now anonymize the table, and read it back as the same unmasked owner:

sudo -u postgres psql -d anondb -tAc "SELECT anon.anonymize_table('public.customer_static');"
sudo -u postgres psql -d anondb -c "SELECT id, first_name, last_name, email, national_id FROM customer_static ORDER BY id LIMIT 4;"

The values have changed for everyone, including the owner, because the data on disk has been replaced. Confirm that no original addresses survive:

sudo -u postgres psql -d anondb -tAc "SELECT count(*) FROM customer_static WHERE email LIKE '%northgate-legal%' OR email LIKE '%brightlane-health%';"

To anonymize every table that carries rules in the database at once, use anon.anonymize_database(). Take a backup first, because there is no undo.

Security posture

No baked credential. Every role ships with no password at all. Both role passwords are generated on first boot from a cryptographic random source and are unique per VM.

No shared certificate. TLS is off in the image, and the certificate is generated per VM on first boot, so no two VMs share a server key.

TLS enforced for remote clients. pg_hba.conf accepts remote connections only through hostssl with scram-sha-256. There is no trust rule anywhere. On-box administration stays passwordless through the local Unix socket.

A credential guard that fails closed. On every boot, postgresql-anonymizer-credguard.service proves that the recorded per-VM credentials authenticate. It then attempts authentication with a list of published and example values. If any of them succeeds, the guard stops PostgreSQL rather than serve a database with a known credential.

Least privilege for analysts. The analyst role is MASKED and read-only. Give it to anyone who needs production-shaped data but must not see personal data.

You can rotate a password at any time. Generate a fresh random secret and record it in the credentials file in the same step, so that the credential guard continues to agree with the database:

NEWPW="$(openssl rand -base64 40 | tr -dc 'A-Za-z0-9' | cut -c1-28)" sudo -u postgres psql -c "ALTER ROLE analyst PASSWORD '${NEWPW}';"
sudo sed -i "s|^analyst.password=.*|analyst.password=${NEWPW}|" /root/postgresql-anonymizer-credentials.txt echo "analyst password rotated and recorded in the credentials file"

Always update /root/postgresql-anonymizer-credentials.txt when you rotate a password. The credential guard verifies on every boot that the recorded password authenticates, and it stops PostgreSQL if it does not. This check makes a stale or mismatched credential impossible to ignore. Never set a password to a value published in documentation. The guard tries a list of such values and refuses to serve if one of them works.

Operations

To check the service and read the first-boot log, run:

systemctl status postgresql.service --no-pager sudo journalctl -u postgresql-anonymizer-firstboot.service --no-pager | tail -20

To back up the demo database, run:

sudo -u postgres pg_dump -d anondb -f /tmp/anondb-backup.sql && ls -la /tmp/anondb-backup.sql

To export an anonymized dump, run the static anonymization first and then pg_dump. Alternatively, dump as a masked role so that the extension applies the rules during the export.

Third-party license attribution for the extension and its bundled synthetic data sets is shipped inside the image:

cat /usr/share/doc/postgresql-anonymizer-sac/ATTRIBUTION.txt

Support

SecureAnyCloud images include 24/7 support. Raise an issue through the Azure Marketplace listing, or contact SecureAnyCloud support with the VM name, the region and the output of systemctl status postgresql.service.

PostgreSQL Anonymizer is developed by Dalibo and distributed under The PostgreSQL License. Upstream documentation lives at postgresql-anonymizer.readthedocs.io.

Deploy on Azure

Launch PostgreSQL 18 with PostgreSQL Anonymizer on Ubuntu 24.04 on Azure with 24/7 support from SecureAnyCloud.

View this image

Need Help?

Our support team is available 24/7.

sales@secureanycloud.com