Using postgresql_anonymizer in Managed Service for PostgreSQL

The postgresql_anonymizer extension allows you to mask or replace personal data or commercial secrets in a PostgreSQL database.

The extension utilizes a declarative approach for anonymization: you can declare masking rules using DDL or set an anonymization strategy in the table definition.

Data anonymization methods:

• Static masking: Replaces confidential data with other data according to the masking rules. You will not be able to restore the original data.
• Generalization: Replaces a value (number or date) with a range containing this value.

Installing postresql_anonymizer in a PostgreSQL clusterInstalling postresql_anonymizer in a PostgreSQL cluster

To install postgresql_anonymizer in a PostgreSQL cluster:

1. Connect the shared library named anon to your cluster.

2. Add the anon extension to your database.

3. Assign the mdb_admin role to the owner of this database, if not assigned yet.

   You can get the owner's name with the list of databases in the cluster.

To learn more about the postgresql_anonymizer extension, see its official documentation.

Usage exampleUsage example

In our example, we are using static masking to replace data with other data according to the masking rules.

1. Connect to the database using psql.

2. Create a table named employees and populate it with data:

   CREATE TABLE employees (
     id SERIAL,
     name TEXT,
     company TEXT,
     code TEXT
   );
   
   INSERT INTO employees
   VALUES
   (111,'Maria Belova','Bank of Saratov','405-657'),
   (222,'Pavel Petrov','Head and Hands','601-245')
   ;
   

3. Check the result:

   SELECT * FROM employees;
   

   id  |       name       |    company      |   code
   ----+------------------+-----------------+-----------
   111 | Maria Belova     | Bank of Saratov |  405-657
   222 | Pavel Petrov     | Head and Hands  |  601-245
   

4. Declare the masking rules:

   SECURITY LABEL FOR anon ON COLUMN employees.company
   IS 'MASKED WITH FUNCTION anon.fake_company()';
   
   SECURITY LABEL FOR anon ON COLUMN employees.code
   IS 'MASKED WITH FUNCTION anon.random_zip()';
   

5. Replace the data in masked columns:

   SELECT anon.anonymize_database();
   

6. Make sure the data in the company and code columns has changed:

   id  |       name       |           company                |   code
   ----+------------------+----------------------------------+---------
   111 | Maria Belova     | Schneider, Phillips and Martinez |  82175
   222 | Pavel Petrov     | White, Hines and Ramos           |  49306