PostgreSQL Anonymizer 18

Option A: AWS Marketplace console

• Open the SecureAnyCloud Hardened Adminer & Database Admin and Backup Tools AMIs listing on AWS Marketplace. Choose View purchase options, then Subscribe. One subscription covers every version in the listing.
• When the subscription is active, choose Launch your software.
• Choose One-click launch from AWS Marketplace, or Launch from EC2 Console for the full launch wizard. In Version, choose PostgreSQL Anonymizer 18 on Ubuntu 24.04, then choose your Region.
• Pick an instance type and your key pair. m5.large is recommended, and the software charge is $0.04 per vCPU per hour.
• Set the security group. The listing's default group allows SSH (22) only, so restrict it to your own IP address. Step 8 adds the PostgreSQL rule.
• Choose your VPC and subnet, leave the metadata defaults (the image already requires IMDSv2), then choose Launch.

Option B: AWS CLI

Run this on your own workstation. Replace the AMI ID with the one shown for your Region on the Marketplace launch page. Replace the VPC, subnet, key pair and CIDR with your own:

SG=$(aws ec2 create-security-group --group-name postgresql-anonymizer \
  --description "PostgreSQL Anonymizer" --vpc-id vpc-0123456789abcdef0 \
  --query GroupId --output text)
aws ec2 authorize-security-group-ingress --group-id "$SG" --protocol tcp --port 22 --cidr <your-mgmt-cidr>
aws ec2 run-instances \
  --image-id ami-0123456789abcdef0 \
  --instance-type m5.large \
  --key-name my-key-pair \
  --security-group-ids "$SG" \
  --subnet-id subnet-0123456789abcdef0 \
  --tag-specifications 'ResourceType=instance,Tags=[{Key=Name,Value=postgresql-anonymizer}]'

You do not need --metadata-options, because the AMI already makes IMDSv2 required.

Connect as ubuntu with the key pair you chose:

ssh -i ~/.ssh/my-key-pair.pem ubuntu@<public-ip>

First boot finishes within a minute of launch. It creates this instance's TLS certificate, anonymization salt and passwords, builds the demo database and then writes the credentials file.

Show the connection details. The command leaves out the two password lines. Read them with sudo cat /root/postgresql-anonymizer-credentials.txt and keep them in your secrets manager.

sudo grep -vE '^(POSTGRES_PASSWORD|ANALYST_PASSWORD)=' /root/postgresql-anonymizer-credentials.txt

# PostgreSQL Anonymizer 3.3.2 + PostgreSQL 18 - generated on first boot by postgresql-anonymizer-firstboot.service.
# These values are unique to this instance. Store them somewhere safe. POSTGRES_HOST=172.31.89.177 POSTGRES_PORT=5432 POSTGRES_USER=postgres POSTGRES_SSLMODE=require ANON_DATABASE=anondb ANALYST_USER=analyst TLS_CERTIFICATE=/etc/postgresql/18/main/tls/server.crt TLS_CERTIFICATE_SHA256=<certificate-sha256-fingerprint> ANON_SALT_FILE=/etc/postgresql/18/main/conf.d/30-sac-anon-salt.conf
# The analyst role is MASKED: it reads anonymized values from anondb; the postgres role reads the real ones.
# Static anonymization (rewrites data in place):
sudo -u postgres psql -d anondb -c "SET ROLE data_steward; SELECT anon.anonymize_table('customer_static');"
# PostgreSQL listens on 5432 on every interface but admits network clients over TLS (scram-sha-256) only.
# The security group opens SSH 22 only: open 5432 to your application subnets yourself.
# Self-test:
sudo /usr/local/sbin/postgresql-anonymizer-selftest.sh
# User guide: https://secureanycloud.com/stacks/postgresql-anonymizer-18/

POSTGRES_PASSWORD is the postgres superuser's password for network logins. ANALYST_PASSWORD is the password of the masked analyst role. On the instance itself, sudo -u postgres psql logs in without a password.

psql --version sudo -u postgres
psql -d anondb -tAc "SELECT extname || ' ' || extversion FROM pg_extension WHERE extname = 'anon';" systemctl is-active postgresql@18-main.service postgresql-anonymizer-egress-guard.service
sudo ss -Hltn

psql (PostgreSQL) 18.6 (Ubuntu 18.6-1.pgdg24.04+2) anon 3.3.2 active active LISTEN 0 4096 127.0.0.53%lo:53 0.0.0.0:* LISTEN 0 200 0.0.0.0:5432 0.0.0.0:* LISTEN 0 4096 0.0.0.0:22 0.0.0.0:* LISTEN 0 4096 127.0.0.54:53 0.0.0.0:* LISTEN 0 200 [::]:5432 [::]:* LISTEN 0 4096 [::]:22 [::]:*

PostgreSQL listens on 5432 and SSH on 22. Port 53 on 127.0.0.53 and 127.0.0.54 is the operating system's local DNS stub.

The customer table in anondb holds six rows of realistic but invented personal data. Every personal column carries a masking rule declared with SECURITY LABEL FOR anon. The extension lists the rules in anon.pg_masking_rules:

sudo -u postgres psql -d anondb -c "SELECT attname AS column, coalesce(masking_function, 'VALUE ' || masking_value) AS rule FROM anon.pg_masking_rules WHERE attrelid = 'public.customer'::regclass ORDER BY attnum;"

column | rule -------------+---------------------------------------- first_name | anon.fake_first_name() last_name | anon.fake_last_name() email | anon.fake_email() phone | anon.partial(phone, 4, $$*******$$, 0) city | anon.fake_city() national_id | anon.hash(national_id) balance | VALUE NULL (7 rows)

Names, email addresses and cities are replaced with fake values. The phone number keeps its first four characters. The national identifier becomes a salted SHA-256 pseudonym, and the same input always gives the same pseudonym on this instance, so joins on it still work. The balance is hidden.

Run the same SELECT over TLS as the owner and as the masked analyst role. The commands read the passwords from the credentials file at run time, so they never appear on screen. First, run it as the owner, postgres:

H="$(sudo sed -n 's/^POSTGRES_HOST=//p' /root/postgresql-anonymizer-credentials.txt)" export PGPASSWORD="$(sudo sed -n 's/^POSTGRES_PASSWORD=//p' /root/postgresql-anonymizer-credentials.txt)" psql "host=$H port=5432 dbname=anondb user=postgres sslmode=require" \
  -c "SELECT id, first_name, last_name, email, phone, city, national_id, balance FROM customer ORDER BY id LIMIT 4;" unset PGPASSWORD

id | first_name | last_name | email | phone | city | national_id | balance ----+------------+-----------+-----------------------------------------+-----------------+------------+-------------+---------- 1 | Sarah | Whitfield | sarah.whitfield@northgate-legal.example | +44 7700 900412 | Manchester | QQ123456C | 4820.55 2 | Daniel | Okafor | d.okafor@brightlane-health.example | +44 7700 900198 | Leeds | QQ998877A | 12750.00 3 | Priya | Raman | priya.raman@meridian-finance.example | +44 7700 900733 | Bristol | QQ445566B | 310.20 4 | Tomasz | Nowak | tomasz.nowak@harbourpoint.example | +44 7700 900021 | Glasgow | QQ112233D | 8905.75 (4 rows)

Now run it as the masked role, analyst. The query shortens the pseudonym column to 16 characters to fit the screen:

H="$(sudo sed -n 's/^POSTGRES_HOST=//p' /root/postgresql-anonymizer-credentials.txt)" export PGPASSWORD="$(sudo sed -n 's/^ANALYST_PASSWORD=//p' /root/postgresql-anonymizer-credentials.txt)" psql "host=$H port=5432 dbname=anondb user=analyst sslmode=require" \
  -c "SELECT id, first_name, last_name, email, phone, city, left(national_id, 16) AS national_id, balance FROM customer ORDER BY id LIMIT 4;" unset PGPASSWORD

id | first_name | last_name | email | phone | city | national_id | balance ----+------------+-----------+----------------------------+-------------+-----------------+------------------+--------- 1 | George | Norman | leslie90@example.org | +44 ******* | Robinsonton | 794d9ce209125276 | 2 | John | Munoz | amberwilliams@example.com | +44 ******* | South Jesustown | 7dd450636fb96d3a | 3 | Shaun | Rowe | michelle99@example.com | +44 ******* | New Connietown | 0962740060d7e830 | 4 | Marie | Garcia | carolhernandez@example.net | +44 ******* | Port Tylerstad | ecf962ecff00ccf6 | (4 rows)

The fake names, email addresses and cities change on every query, but the pseudonyms do not. The stored rows are unchanged, as the owner's query above still shows. The masked role cannot write to the table and cannot read the salt:

H="$(sudo sed -n 's/^POSTGRES_HOST=//p' /root/postgresql-anonymizer-credentials.txt)" export PGPASSWORD="$(sudo sed -n 's/^ANALYST_PASSWORD=//p' /root/postgresql-anonymizer-credentials.txt)" psql "host=$H port=5432 dbname=anondb user=analyst sslmode=require" -c "UPDATE customer SET balance = 0 WHERE id = 1;" 2>&1 || true psql "host=$H port=5432 dbname=anondb user=analyst sslmode=require" -c "SHOW anon.salt;" 2>&1 || true unset PGPASSWORD

ERROR: Anon: role is masked ERROR: permission denied to examine "anon.salt" DETAIL: Only roles with privileges of the "pg_read_all_settings" role may examine this parameter.

pg_hba.conf rejects a connection without TLS before any password is checked, and a wrong password is refused:

H="$(sudo sed -n 's/^POSTGRES_HOST=//p' /root/postgresql-anonymizer-credentials.txt)" for mode in disable require; do OUT="$(PGPASSWORD=<set-at-first-boot> psql "host=$H port=5432 dbname=anondb user=analyst sslmode=$mode" -c 'SELECT 1' 2>&1 || true)" echo "${OUT#*failed: }" done

FATAL: pg_hba.conf rejects connection for host "172.31.89.177", user "analyst", database "anondb", no encryption FATAL: password authentication failed for user "analyst"

Masking a column takes one statement. This example creates a staff table, labels its columns and lets the masked role read it. The function anon.noise adds up to 10% random noise to the salary.

sudo -u postgres psql -d anondb <<'SQL' CREATE TABLE staff (id int PRIMARY KEY, full_name text, work_email text, salary numeric(10,2)); INSERT INTO staff VALUES (1, 'Helen Ashworth', 'helen.ashworth@corp.example', 61500.00), (2, 'Marcus Oyelaran', 'marcus.oyelaran@corp.example', 58250.00); SECURITY LABEL FOR anon ON COLUMN staff.full_name IS 'MASKED WITH FUNCTION anon.fake_last_name()'; SECURITY LABEL FOR anon ON COLUMN staff.work_email IS 'MASKED WITH FUNCTION anon.fake_email()'; SECURITY LABEL FOR anon ON COLUMN staff.salary IS 'MASKED WITH FUNCTION anon.noise(salary, 0.1)'; GRANT SELECT ON staff TO analyst; SQL

CREATE TABLE INSERT 0 2 SECURITY LABEL SECURITY LABEL SECURITY LABEL GRANT

The demo's grants cover only the tables that existed on first boot. Every table you add to anondb later needs its own GRANT SELECT ON <table> TO analyst;, as above. Run it after the table's masking labels are in place.

Read it back as the masked role:

H="$(sudo sed -n 's/^POSTGRES_HOST=//p' /root/postgresql-anonymizer-credentials.txt)" export PGPASSWORD="$(sudo sed -n 's/^ANALYST_PASSWORD=//p' /root/postgresql-anonymizer-credentials.txt)" psql "host=$H port=5432 dbname=anondb user=analyst sslmode=require" -c "SELECT * FROM staff ORDER BY id;" unset PGPASSWORD

id | full_name | work_email | salary ----+-----------+----------------------+---------- 1 | Bowers | xbenitez@example.org | 64485.61 2 | Holmes | qadkins@example.com | 56540.43 (2 rows)

To mask another role, label it: SECURITY LABEL FOR anon ON ROLE <role> IS 'MASKED';. A masked role also needs read access to the tables it queries. The demo grants analyst USAGE and SELECT on the public schema of anondb only. It also grants SELECT on the extension's fake-data tables (anon.first_name, anon.email and the others), which the fake_*() functions read.

Your own databases

Masking is enabled per database, not for the whole server. It is on in anondb only. A database you create or restore has no masking until you switch it on there with ALTER DATABASE <db> SET anon.transparent_dynamic_masking TO true and declare the security labels for its personal columns. Until then, any role that can read its tables reads the real values.

For that reason, the masked analyst role has no access to any database except anondb. It is not a member of pg_read_all_data or any other built-in role, and it cannot connect to postgres or template1. PostgreSQL lets every role connect to a database you create, but analyst cannot read any of its tables until you grant access. The image never grants that for you. Check it from the instance:

H="$(sudo sed -n 's/^POSTGRES_HOST=//p' /root/postgresql-anonymizer-credentials.txt)" export PGPASSWORD="$(sudo sed -n 's/^ANALYST_PASSWORD=//p' /root/postgresql-anonymizer-credentials.txt)" OUT="$(psql "host=$H port=5432 dbname=postgres user=analyst sslmode=require" -c "SELECT 1;" 2>&1 || true)" echo "${OUT#*failed: }" psql "host=$H port=5432 dbname=anondb user=analyst sslmode=require" -tAc "SELECT pg_has_role('pg_read_all_data', 'MEMBER');" unset PGPASSWORD

FATAL: permission denied for database "postgres" DETAIL: User does not have CONNECT privilege. f

To give a masked role access to one of your own databases, work as postgres inside that database and follow this order. Switch masking on and label the columns first. Grant the masked role access only after that:

CREATE EXTENSION IF NOT EXISTS anon; ALTER DATABASE mydb SET anon.transparent_dynamic_masking TO true; SECURITY LABEL FOR anon ON COLUMN public.patient.full_name IS 'MASKED WITH FUNCTION anon.fake_last_name()'; SECURITY LABEL FOR anon ON COLUMN public.patient.email IS 'MASKED WITH FUNCTION anon.fake_email()'; -- only now, with masking on and every personal column labelled: GRANT CONNECT ON DATABASE mydb TO analyst; GRANT USAGE ON SCHEMA public TO analyst; GRANT SELECT ON ALL TABLES IN SCHEMA public TO analyst; -- the fake_*() functions read the extension's own synthetic datasets in the anon schema: GRANT SELECT ON anon.address, anon.city, anon.company, anon.country, anon.email, anon.first_name, anon.iban, anon.last_name, anon.lorem_ipsum, anon.postcode, anon.siret, anon.street_name TO analyst;

ALTER DATABASE ... SET applies to new sessions, so reconnect before you test. Do not grant a masked role pg_read_all_data, because it reads every table in every database, including the ones where masking is off.

PostgreSQL gives PUBLIC the CONNECT privilege on every database you create. For each new database, either label its columns and enable masking there, or run REVOKE CONNECT ON DATABASE mydb FROM PUBLIC; straight after CREATE DATABASE. If a dump you restore grants SELECT to PUBLIC, switch masking on and label the columns before you restore it.

Static anonymization replaces the stored values for everyone, including the owner, and it cannot be undone. The demo provides customer_static, a copy of the customer data with the same rules, owned by the non-login role data_steward. Look at the table first:

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

id | first_name | last_name | email | national_id | balance ----+------------+-----------+-----------------------------------------+-------------+---------- 1 | Sarah | Whitfield | sarah.whitfield@northgate-legal.example | QQ123456C | 4820.55 2 | Daniel | Okafor | d.okafor@brightlane-health.example | QQ998877A | 12750.00 3 | Priya | Raman | priya.raman@meridian-finance.example | QQ445566B | 310.20 (3 rows)

Because anon.nosuperuser is on, the superuser is refused, so the table's owner runs the anonymization instead:

sudo -u postgres psql -d anondb -tAc "SELECT anon.anonymize_table('public.customer_static');" 2>&1 || true
sudo -u postgres psql -d anondb -tAc "SET ROLE data_steward; SELECT anon.anonymize_table('public.customer_static');"

ERROR: Anon: Static and dynamic masking cannot be used with a superuser if anon.nosuperuser is set to true. SET t

Read the table back, and confirm that no original identifier or address is left:

sudo -u postgres psql -d anondb -c "SELECT id, first_name, last_name, email, left(national_id, 16) AS national_id, balance FROM customer_static ORDER BY id LIMIT 3;"
sudo -u postgres psql -d anondb -tAc "SELECT count(*) FROM customer_static WHERE national_id LIKE 'QQ%' OR email LIKE '%northgate-legal%';"

id | first_name | last_name | email | national_id | balance ----+------------+-----------+-----------------------------+------------------+--------- 1 | Trevor | Mcgee | jacquelinewalsh@example.org | 794d9ce209125276 | 2 | Veronica | Lawrence | kennedyrachel@example.net | 7dd450636fb96d3a | 3 | Jennifer | Baird | aguilarjustin@example.org | 0962740060d7e830 | (3 rows) 0

anon.anonymize_database() anonymizes every labelled table in a database at once. anon.anonymize_schema_parallel() spreads the work over background workers, which run as the role that called it. Take a backup first, because there is no undo.

The security group opens SSH only. Allow 5432 from the subnets your applications and analysts connect from, and never from 0.0.0.0/0. Run this on your workstation:

aws ec2 authorize-security-group-ingress --group-id <your-sg-id> \
  --protocol tcp --port 5432 --cidr <your-subnet-cidr>

Clients connect with sslmode=require, or with verify-full and the certificate from TLS_CERTIFICATE, whose fingerprint is in the credentials file.

PostgreSQL itself needs outbound connections only if you use features such as postgres_fdw or dblink. To allow one, add the address or CIDR to /etc/sac/postgresql-anonymizer-egress-allow.conf and run sudo systemctl reload postgresql-anonymizer-egress-guard. PostgreSQL keeps running.

The shipped self-test checks the whole instance in one run and prints ANON_SELFTEST_OK on success:

sudo /usr/local/sbin/postgresql-anonymizer-selftest.sh

== PostgreSQL Anonymizer self-test == OK: credentials file 0600 root OK: PostgreSQL listens on 0.0.0.0:5432 and [::]:5432 OK: postgres logs in over TLS (anon 3.3.2); a wrong password and plaintext are refused OK: analyst reads 6 MASKED rows (fake names, '+44 *******', SHA-256 national ids, NULL balances); the owner reads the real rows OK: analyst cannot write, cannot read anon.salt; a wrong analyst password is refused OK: analyst reaches anondb only: no pg_read_all_data, no CONNECT on database postgres ANON_SELFTEST_OK

Confirm these controls: the PostgreSQL units carry the metadata block, only postgres and analyst have passwords, the salt file is readable by postgres only and is not stored in the database, the masking safeguards are on, and the egress backstop is loaded.

systemctl show -p IPAddressDeny postgresql@18-main.service postgresql-anonymizer-firstboot.service
sudo -u postgres psql -tAc "SELECT rolname, rolsuper, rolcanlogin, rolpassword IS NOT NULL AS has_password FROM pg_authid WHERE rolname IN ('postgres','analyst','data_steward') ORDER BY 1;"
sudo stat -c '%a %U:%G %n' /etc/postgresql/18/main/conf.d/30-sac-anon-salt.conf
sudo -u postgres psql -tAc "SELECT count(*) FROM pg_db_role_setting WHERE setconfig::text LIKE '%anon.salt%';"
sudo -u postgres psql -tAc "SHOW anon.nosuperuser;" -c "SHOW anon.restrict_to_trusted_schemas;"
sudo nft list chain inet sac_anon_egress output | grep -c sac-backstop

IPAddressDeny=168.63.129.16/32 fd00:ec2::254/128 169.254.169.254/32 IPAddressDeny=168.63.129.16/32 fd00:ec2::254/128 169.254.169.254/32 analyst|f|t|t data_steward|f|f|f postgres|t|t|t 600 postgres:postgres /etc/postgresql/18/main/conf.d/30-sac-anon-salt.conf 0 on on 6