Anonymization & Data Masking for Postgres

PostgreSQL Anonymizer is an extension to mask or replace personally identifiable information (PII) or commercially sensitive data from a Postgres database.

The project has a declarative approach of anonymization. This means you can declare the masking rules using the PostgreSQL Data Definition Language (DDL) and specify your anonymization policy inside the table definition itself.

The main goal of this extension is to offer anonymization by design. We firmly believe that data masking rules should be written by the people who develop the application because they have the best knowledge of how the data model works. Therefore masking rules must be implemented directly inside the database schema.

Once the masking rules are defined, you can apply them using 5 different masking methods :

• Anonymous Dumps : Simply export the masked data into an SQL file
• Static Masking : Remove the PII according to the rules
• Dynamic Masking : Hide PII only for the masked users
• Masking Views : Build dedicated views for the masked users
• Masking Data Wrappers : Apply masking rules on external data

Each method has its pros and cons. Different masking methods may be used in different contexts. In any case, masking the data directly inside the PostgreSQL instance without using an external tool is crucial to limit the exposure and the risks of data leak.

In addition, various Masking Functions are available : randomization, faking, partial scrambling, shuffling, noise or even your own custom function!

Finally, the extension offers a panel of detection functions that will try to guess which columns need to be anonymized.

Quick Start

Step 0. Launch docker image of the project

ANON_IMG=registry.gitlab.com/dalibo/postgresql_anonymizer
docker run --name anon_quickstart --detach -e POSTGRES_PASSWORD=x $ANON_IMG
docker exec -it anon_quickstart psql -U postgres

Step 1. Create a database and load the extension in it

CREATE DATABASE demo;
ALTER DATABASE demo SET session_preload_libraries = 'anon'

\connect demo
You are now connected to database "demo" as user "postgres".

Step 2. Create a table

CREATE TABLE people AS
    SELECT  153478       AS id,
            'Sarah'      AS firstname,
            'Conor'      AS lastname,
            '0609110911' AS phone
;

SELECT * FROM people;
   id   | firstname | lastname |   phone
--------+-----------+----------+------------
 153478 | Sarah     | Conor    | 0609110911

Step 3. Create the extension and activate the masking engine

CREATE EXTENSION anon;
ALTER DATABASE demo SET anon.transparent_dynamic_masking TO true;

Step 4. Declare a masked user

CREATE ROLE skynet LOGIN;

SECURITY LABEL FOR anon ON ROLE skynet IS 'MASKED';

GRANT pg_read_all_data to skynet;

Step 5. Declare the masking rules

SECURITY LABEL FOR anon ON COLUMN people.lastname
  IS 'MASKED WITH FUNCTION anon.dummy_last_name()';

SECURITY LABEL FOR anon ON COLUMN people.phone
  IS 'MASKED WITH FUNCTION anon.partial(phone,2,$$******$$,2)';

Step 6. Connect with the masked user

\connect - skynet
You are now connected to database "demo" as user "skynet"

SELECT * FROM people;
   id   | firstname | lastname  |   phone
--------+-----------+-----------+------------
 153478 | Sarah     | Stranahan | 06******11

Success Stories

With PostgreSQL Anonymizer we integrate, from the design of the database, the principle that outside production the data must be anonymized. Thus we can reinforce the GDPR rules, without affecting the quality of the tests during version upgrades for example.

— Thierry Aimé, Office of Architecture and Standards in the French Public Finances Directorate General (DGFiP)

---

Thanks to PostgreSQL Anonymizer we were able to define complex masking rules in order to implement full pseudonymization of our databases without losing functionality. Testing on realistic data while guaranteeing the confidentiality of patient data is a key point to improve the robustness of our functionalities and the quality of our customer service.

— Julien Biaggi, Product Owner at bioMérieux

---

I just discovered your postgresql_anonymizer extension and used it at my company for anonymizing our user for local development. Nice work!

— Max Metcalfe

If this extension is useful to you, please let us know !

Support

We need your feedback and ideas ! Let us know what you think of this tool, how it fits your needs and what features are missing.

You can either open an issue or send a message at contact@dalibo.com.