PostgreSQL 18 with PostgreSQL Anonymizer on Ubuntu 24.04 on Azure User Guide

Overview

This guide covers the deployment and use of PostgreSQL 18 with PostgreSQL Anonymizer on Ubuntu 24.04 on Azure using cloudimg Azure Marketplace images. It pairs the PostgreSQL 18 relational database with PostgreSQL Anonymizer (anon), the extension from Dalibo that implements GDPR style data masking inside the database engine.

The idea is simple and powerful: you declare, in SQL, which columns hold personal data and how each should be disguised. PostgreSQL then enforces those rules itself. A role you mark as masked sees anonymized values; the table owner sees the real ones. Nothing is copied, exported, or duplicated into a second "safe" database, and the masking cannot be bypassed by writing a different query, because it is applied by the engine at query time.

The extension supports three complementary modes, all available on this image:

• Dynamic masking — masked roles see anonymized values in real time. The stored data is untouched. Ideal for giving analysts, contractors, or support staff access to production shaped data without exposing personal data.
• Static anonymization — anon.anonymize_table() and anon.anonymize_database() permanently rewrite the stored values. Ideal for producing a sanitized dump for a development or test environment.
• Synthetic data generation — the anon.fake_* and anon.dummy_* function families produce realistic substitute values (names, emails, cities, dates) so the anonymized data still looks and behaves like real data.

PostgreSQL 18 is installed from the official PostgreSQL PGDG repository. The anon extension is version 3.1.3, installed from the Dalibo Labs repository — which is upstream's own supported distribution channel for this extension. The extension is already created, initialised and preloaded, and the image ships a demo database with realistic personal data and a full set of masking rules already declared, so you can see the product working within a minute of first boot.

Security by design — no baked credential. Every role in the shipped image is password less by construction, and TLS is off, so the database is not reachable off box at all in the image itself. On first boot each VM mints a unique password for the postgres superuser and for the masked demo role, generates a unique self signed TLS server certificate, writes everything to the root only file /root/postgresql-anonymizer-credentials.txt, then enables TLS. Two VMs launched from this image never share a secret. A credential guard runs on every boot and refuses to leave the database serving if a published or example credential is ever actually in effect.

What is included:

• PostgreSQL 18 from the official PGDG repository, running under systemd as postgresql.service

• PostgreSQL Anonymizer 3.1.3 from the Dalibo Labs repository, preloaded via shared_preload_libraries and initialised so the synthetic data functions work immediately

• A demo database anondb with a customer table of realistic personal data, a complete set of masking rules, and a pre declared masked role called analyst

• A second table customer_static reserved for the static anonymization walkthrough, so the destructive demo never damages the dynamic one

• Per VM passwords for both roles and a per VM TLS certificate generated on first boot, written to a root only credentials file

• A boot time credential guard that fails closed if a published or example credential is in effect

• Unattended security upgrades left enabled so the appliance keeps receiving patches

Prerequisites

• Active Azure subscription, an SSH public key, and a VNet + subnet in the target region

• Subscription to this listing on Azure Marketplace

• A Network Security Group allowing 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 rich size such as Standard_E2s_v5 or larger.

Deploy the virtual machine

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

az vm create \
  --resource-group my-rg \
  --name pg-anon-1 \
  --image <this-marketplace-image> \
  --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 the first boot the VM mints its own passwords and TLS certificate. SSH in and read the root only credentials file:

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

You will see the postgres superuser password, the analyst masked role password, and ready to paste connection strings. The file is mode 0600, 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 this may show the VM's private address. When connecting from outside Azure, use the VM's public IP address from the portal or az vm list-ip-addresses.

Confirm the service is healthy

Check that PostgreSQL, the first boot unit and the credential guard are all in good order, and that the extension is loaded and initialised:

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 is the check that proves the synthetic data sets loaded — the loader only emits a notice rather than an error if they are missing, so this 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:

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 heart of the product. The analyst role is labelled MASKED, so PostgreSQL rewrites its queries to return anonymized values. The postgres owner is not masked and sees the real data. Both run exactly the same SQL against exactly the same table.

First, as the owner — 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 as the masked role — 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 are over TLS. You can verify that 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, 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 it 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(), anon.fake_city(), 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:

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 genuinely sanitized copy — for example to hand a dataset to a development team — static anonymization rewrites the values in place.

This is irreversible, so the image provides a dedicated customer_static table for the demonstration. Look at it 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 it, and read it back as that 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 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 carrying rules in the database at once, use anon.anonymize_database(). Take a backup first — there is no undo.

Security posture

• No baked credential. Every role ships with no password at all. Both role passwords are minted 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 password less through the local unix socket.

• A credential guard that fails closed. On every boot, postgresql-anonymizer-credguard.service proves the recorded per VM credentials genuinely authenticate, 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.

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

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. The credential guard verifies on every boot that the recorded password genuinely authenticates, and stops PostgreSQL if it does not — that check is what makes a stale or mismatched credential impossible to ignore. Never set a password to a value published in documentation: the guard actively tries a list of such values and refuses to serve if one of them works.

Operations

Service control and logs:

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

Back up the demo database:

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

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

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

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

Support

cloudimg images include 24/7 support. Raise an issue through the Azure Marketplace listing or contact cloudimg support with the VM name, 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.