5- Anonymized Replica

💡 With logical replication, you can maintain a replica that is always up to date and always anonymized. This feature was introduced in version 2.3 and became generally available in version 3.0.

For more details, check out the Replica Masking section of the documentation.

The Story

Pierre needs permanent access to the boutique data to run his analytics. With dynamic masking, he was given a masked view of the production database. But Paul would now prefer to keep Pierre away from the production server entirely.

The solution is a dedicated, anonymized replica: Pierre gets a read-only, always-fresh, fully masked copy of the data, and never touches production.

How it works

Learning Objective

In this section, we will learn:

• How to configure the publication on the source database
• How to configure the anonymized replica
• How to verify the anonymization in real time

A note on this setup

In production, the anonymized replica is a separate PostgreSQL server.

To keep this tutorial self-contained, the source and the replica are two databases of the same instance: boutique (the source) and boutique_replica (the anonymized replica). Logical replication works just as well between two databases of the same cluster.

This chapter creates cluster-wide objects (a replication role, a publication, a replication slot and a second database). If you want to replay it from scratch, clean them up first (see the Conclusion).

Prerequisites

Logical replication requires wal_level = logical. The default value is replica; raising it requires a restart of the instance (check it with SHOW wal_level;).

The anon extension must also be loaded in every session that declares masking rules. The simplest way to guarantee this is to preload it globally with shared_preload_libraries = 'anon'.

The source database

We use the company and supplier tables, filled with real data:

DROP TABLE IF EXISTS supplier CASCADE;
DROP TABLE IF EXISTS company CASCADE;

CREATE TABLE company (
    id INT PRIMARY KEY,
    name TEXT,
    vat_id TEXT UNIQUE
);

INSERT INTO company VALUES
(952,'Shadrach','FR62684255667'),
(194,E'Johnny\'s Shoe Store','CHE670945644'),
(346,'Capitol Records','GB663829617823');

CREATE TABLE supplier (
    id INT PRIMARY KEY,
    fk_company_id INT REFERENCES company(id),
    contact TEXT,
    phone TEXT,
    job_title TEXT
);

INSERT INTO supplier VALUES
(299,194,'Johnny Ryall','597-500-569','CEO'),
(157,346,'George Clinton','131-002-530','Sales manager');

SELECT * FROM company ORDER BY id;

id	name	vat_id
194	Johnny\'s Shoe Store	CHE670945644
346	Capitol Records	GB663829617823
952	Shadrach	FR62684255667

With replica masking, the source database needs no masking rules: the anonymization happens on the replica, as the data is received.

Publication side: the replication role

On the source, create a dedicated replication role:

CREATE ROLE anon_replicator LOGIN REPLICATION PASSWORD 'CHANGEME';

GRANT USAGE  ON SCHEMA public TO anon_replicator;
GRANT SELECT ON ALL TABLES IN SCHEMA public TO anon_replicator;

Publication side: create the publication

We only publish the tables that Pierre needs to read. The customer and payout tables are not published here.

CREATE PUBLICATION pub_boutique
  FOR TABLE company, supplier;

Check the publication:

SELECT pubname, schemaname, tablename FROM pg_publication_tables;

pubname	schemaname	tablename
pub_boutique	public	company
pub_boutique	public	supplier

When the replica is another database of the same cluster, the replication slot must be created beforehand:

SELECT slot_name
FROM pg_create_logical_replication_slot('boutique_slot','pgoutput');

slot_name
boutique_slot

Replica side: create the database

CREATE DATABASE boutique_replica OWNER paul;

The anon.replica_masking parameter allows the masking triggers to be installed on this database. It must be enabled before calling anon.start_replica_masking(), and therefore before the subscription is created:

ALTER DATABASE boutique_replica SET anon.replica_masking TO on;

💡 If you use a .pgpass file, add entries for the new database:

cat >> ~/.pgpass << EOL
*:*:boutique_replica:paul:CHANGEME
*:*:boutique_replica:pierre:CHANGEME
EOL

Replica side: initialize the extension and the tables

anon.init() is required on the replica: the faking and pseudonymization functions rely on the fake datasets it loads.

The tables must also exist before the subscription is created, since logical replication does not replicate DDL:

CREATE EXTENSION IF NOT EXISTS anon;
SELECT anon.init();

CREATE TABLE company (
    id INT PRIMARY KEY,
    name TEXT,
    vat_id TEXT UNIQUE
);

CREATE TABLE supplier (
    id INT PRIMARY KEY,
    fk_company_id INT REFERENCES company(id),
    contact TEXT,
    phone TEXT,
    job_title TEXT
);

Replica side: masking rules

The rules declared on the source only serve dynamic masking there: it is the replica that applies its own rules when the data is received. Declare rules similar to the ones of the Dynamic Masking tutorial:

SECURITY LABEL FOR anon ON COLUMN company.name
  IS 'MASKED WITH FUNCTION anon.pseudo_company(id)';

SECURITY LABEL FOR anon ON COLUMN company.vat_id
  IS 'MASKED WITH FUNCTION anon.random_string(10)';

SECURITY LABEL FOR anon ON COLUMN supplier.contact
  IS 'MASKED WITH VALUE $$CONFIDENTIAL$$';

SECURITY LABEL FOR anon ON COLUMN supplier.phone
  IS 'MASKED WITH FUNCTION anon.partial(phone,0,$$XX-XXX-XXX$$,0)';

Pseudonymizing company.name guarantees that Pierre always sees the same fake name for a given company, which lets him write consistent joins between company and supplier.

🚨 Declare all the masking rules before creating the subscription: a rule added later only masks the changes that arrive afterwards (see exercise E501).

Replica side: enable and subscribe

anon.start_replica_masking() installs, on each masked table, a trigger declared ENABLE REPLICA: it only fires during the replication apply process --- which is also why writes performed locally on the replica are not masked.

SELECT anon.start_replica_masking();

The subscription reuses the slot we created earlier, hence create_slot=false:

CREATE SUBSCRIPTION sub_boutique
  CONNECTION 'host=localhost port=65432 user=anon_replicator password=CHANGEME dbname=boutique'
  PUBLICATION pub_boutique
  WITH (slot_name='boutique_slot', create_slot=false);

Replication is asynchronous; we wait a moment for the initial copy to complete:

SELECT pg_sleep(3);

Check the subscription status:

SELECT subname, pid, received_lsn, latest_end_lsn
FROM pg_stat_subscription;

subname	pid	received_lsn	latest_end_lsn
sub_boutique	124	0/2737630	0/2737630

Verification

On the replica, the data is anonymized, even for Paul:

SELECT * FROM company ORDER BY id;

id	name	vat_id
194	Johnson PLC	7Amz57dWEa
346	Young-Carpenter	beEQFttDdh
952	Wilkinson LLC	y01TvEu6Ax

Unlike with dynamic masking, all users see the anonymized data on the replica: as long as the masking rules are declared before the data arrives, the original data is never stored there. (Exercise E501 shows what happens when a rule comes too late.)

For comparison, on the source the data is still real:

SELECT * FROM company ORDER BY id;

id	name	vat_id
194	Johnny\'s Shoe Store	CHE670945644
346	Capitol Records	GB663829617823
952	Shadrach	FR62684255667

A new row inserted on the source is replicated and anonymized automatically:

INSERT INTO company VALUES (501,'Acme Corp','US123456789');

SELECT pg_sleep(2);

SELECT * FROM company WHERE id = 501;

id	name	vat_id
501	Knight, Hurley and Clayton	VGr2HQ0av9

Beware of primary keys

🚨 Logical replication identifies rows by their replica identity --- by default, their primary key:

• With PostgreSQL Anonymizer, every replicated table must have a primary key (REPLICA IDENTITY FULL is not supported).
• Do not mask the columns of the primary key!
• Otherwise UPDATE and DELETE statements will no longer propagate.

Technical primary keys (sequences, UUID) are usually not personal data and do not need to be masked.

For more details on masking columns involved in referential integrity, see the Masking Foreign Keys section.

Exercises

As a reminder, unless stated otherwise, all commands are to be run as paul (the owner).

E501 - Add a masking rule after the fact

All our masking rules were declared before the subscription was created. Declare a new rule on supplier.job_title while the replication is running.

What happens to the rows that are already on the replica? How can you fix them?

E502 - A masked role on the replica?

Connect as Pierre and check what he sees on the replica.

Does Pierre need to be declared as a MASKED role on boutique_replica, like he was on the source? Why or why not?

E503 - Verify the anonymized replication

On the source, insert a new supplier; on the replica, check that it appears and is anonymized.

Solutions

S501

Declare the rule, then refresh the masking triggers with anon.refresh_replica_masking():

SECURITY LABEL FOR anon ON COLUMN supplier.job_title
  IS 'MASKED WITH VALUE $$CONFIDENTIAL$$';

SELECT anon.refresh_replica_masking();

🚨 The triggers only mask incoming rows: the suppliers that were replicated before the rule was declared still show their real job titles on the replica!

SELECT id, job_title FROM supplier ORDER BY id;

id	job_title
157	Sales manager
299	CEO

To fix the existing rows, apply the rules once with static masking (see the Static Masking tutorial). From then on, every incoming row is masked:

SELECT anon.anonymize_table('supplier');

SELECT id, job_title FROM supplier ORDER BY id;

id	job_title
157	CONFIDENTIAL
299	CONFIDENTIAL

This is why the masking rules should be declared before the subscription is created.

S502

Give Pierre read access to the replica database:

GRANT USAGE ON SCHEMA public TO pierre;
GRANT SELECT ON ALL TABLES IN SCHEMA public TO pierre;

💡 If Pierre already holds the cluster-wide pg_read_all_data privilege (granted in the Dynamic Masking tutorial), these grants are redundant. On a standalone replica server, they are required.

SELECT s.contact, s.phone, c.name
FROM supplier s
JOIN company c ON s.fk_company_id = c.id;

contact	phone	name
CONFIDENTIAL	XX-XXX-XXX	Johnson PLC
CONFIDENTIAL	XX-XXX-XXX	Young-Carpenter

There is no need to declare Pierre as a MASKED role on the replica: the anonymization happened at replication time, not at query time. The MASKED label that Pierre carries from the Dynamic Masking tutorial has no effect here, because anon.transparent_dynamic_masking is not enabled on boutique_replica.

S503

INSERT INTO supplier VALUES (42,501,'Wile E. Coyote','555-0100','Buyer');

SELECT pg_sleep(2);

SELECT s.contact, s.phone, s.job_title, c.name
FROM supplier s
JOIN company c ON s.fk_company_id = c.id
WHERE s.id = 42;

contact	phone	job_title	name
CONFIDENTIAL	XX-XXX-XXX	CONFIDENTIAL	Knight, Hurley and Clayton

The new supplier has been replicated and anonymized automatically: the contact and the job title are hidden (the latter by the rule added in E501), the phone is masked and the company name is the same pseudonym as in the previous sections.