Welcome to Paul's Boutique !

This is a 4 hours workshop that demonstrates various anonymization techniques using the PostgreSQL Anonymizer extension.

The Story

Paul's boutique has a lot of customers. Paul asks his friend Pierre, a Data Scientist, to make some statistics about his clients : average age, etc...

Pierre wants a direct access to the database in order to write SQL queries.

Jack is an employee of Paul. He's in charge of relationship with the various suppliers of the shop.

Paul respects his suppliers privacy. He needs to hide the personal information to Pierre, but Jack needs read and write access the real data.

Objectives

Using the simple example above, we will learn:

• How to write masking rules
• The difference between static and dynamic masking
• Implementing advanced masking techniques

About GDPR

This tutorial does not go into the details of the GPDR act and the general concepts of anonymization.

For more information about it, please refer to the talk below:

• Anonymisation, Au-delà du RGPD (Video / French)
• Anonymization, Beyond GDPR (PDF / english)

Requirements

In order to make this workshop, you will need:

• A Linux VM ( preferably Debian 12 bookworm or Ubuntu 24.04)
• A PostgreSQL instance ( preferably PostgreSQL 17 )
• The PostgreSQL Anonymizer (anon) extension, installed and initialized by a superuser
• A database named "boutique" owned by a superuser called "paul"
• A role "pierre" and a role "jack", both allowed to connect to the database "boutique"

ANON_IMG=registry.gitlab.com/dalibo/postgresql_anonymizer:stable
docker pull $ANON_IMG

And you can then launch it with:

docker run --name anon_tuto --detach -e POSTGRES_PASSWORD=x $ANON_IMG
docker exec -it anon_tuto psql -U postgres

!!! tip Check out the INSTALL section in the documentation to learn how to install the extension in your PostgreSQL instance.

The Roles

We will with 3 different users:

CREATE ROLE paul LOGIN SUPERUSER PASSWORD 'CHANGEME';


CREATE ROLE pierre LOGIN PASSWORD 'CHANGEME';


CREATE ROLE jack LOGIN PASSWORD 'CHANGEME';

GRANT pg_read_all_data TO jack;

GRANT pg_write_all_data TO jack;

Unless stated otherwise, all commands must be executed with the role paul.

!!! Tip Setup a .pgpass file to simplify the connections !

cat > ~/.pgpass << EOL
*:*:boutique:paul:CHANGEME
*:*:boutique:pierre:CHANGEME
*:*:boutique:jack:CHANGEME
EOL
chmod 0600 ~/.pgpass

The Sample database

We will work on a database called "boutique":

CREATE DATABASE boutique OWNER paul;

We need to activate the anon library inside that database:

ALTER DATABASE boutique
SET session_preload_libraries = 'anon';