Using postgresql_anonymizer in Managed Service for PostgreSQL

The postgresql_anonymizer extension allows you to mask or replace personal and commercially sensitive data in a PostgreSQL database.

The extension uses a declarative approach to anonymization, allowing you to declare masking rules with DDL and set the anonymization strategy within the table definition.

Data anonymization methods:

• Static masking: Replaces sensitive information with other data according to the masking rules. This method makes it impossible to restore the original data.
• Generalization: Replaces a value, e.g., number or date, with a range that contains it.

Installing postresql_anonymizer in a PostgreSQL clusterInstalling postresql_anonymizer in a PostgreSQL cluster

To install postgresql_anonymizer in a PostgreSQL cluster:

1. Load the shared library anon into your cluster.

2. Enable the anon extension in your database.

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

   You can get the owner's name from the cluster’s database list.

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

Usage exampleUsage example

In the following example, we will use static masking to substitute data according to the masking rules.

1. Connect to the database via 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. Define 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. Verify that 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