Implementing Oracle Data Masking capabilities in Azure PostgreSQL- Flexible Server

Introduction

Dynamic data masking is essential when sensitive information, such as PII, needs to be protected while sharing data with third parties or during the transfer of data from production to lower environments. By replacing confidential data with fictitious or altered data, this technique ensures that developers, vendors, or external partners cannot access real PII, enhancing security and privacy.

This blog offers guidelines for customers transitioning from Oracle to Azure PostgreSQL – Flexible Server who wish to mask their PII or PCI data in their lower environments. It details how to enable dynamic masking for a user or role in Azure PostgreSQL Flexible Server using the ANON (preview) extension.

Enable the Server Level Parameters

To enable the server level parameter, navigate the azure portal left panel and search for ‘Server Parameters’ under settings section.

Search for “azure. Extensions” and in the value section click the checkbox for PGCRYPTO and ANON and select SAVE.

Once the above is completed search for “shared_preload_libraries” and in the value section click the checkbox for ANON and then click SAVE.

The above step would prompt the restart of the server.

Enable Dynamic Data masking

Once the server is restarted login the database either by using PgAdmin or through psql.

Here is an example that how to enable the Dynamic Masking:

1. Create a table.

   CREATE TABLE people_new ( id TEXT, firstname TEXT, lastname TEXT, phone TEXT);

2. Insert some new records.

   INSERT INTO people_new VALUES ('E1',David, 'Miller','0609110911');
   INSERT INTO people_new VALUES ('E2','Robert', 'Bruce','0708910911');
   

3. SELECT * FROM people_new;

1. Issue the following statement to initialize the dynamic masking

   SELECT anon.start_dynamic_masking();

2. Create a user/role for the masked user

   CREATE USER masked_user WITH PASSWORD 'masked_user';

3. Assign the anon masking for the user/role

   SECURITY LABEL FOR anon on ROLE masked_user IS 'MASKED';

4. Create the dynamic masking for the phone column in the table people_new

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

5. Grant all the permission for the table people_new to ‘masked_user’

   GRANT ALL ON TABLE people_new to masked_user;

6. Run the select command as POSTGRES user and you could see the following results with no masking in phone column

     10. Login as ‘masked_user’ and execute SELECT * FROM people_new; you could find the following result as phone column is masked. 

Disable Dynamic Masking

In order to disable the dynamic masking, use the following steps.

1. Issue the stop dynamic masking command as POSTGRES user.

SELECT anon.stop_dynamic_masking();

      2. Login as ‘masked_user’ and check the values for people_new table

SELECT * FROM people_new;

You can see now the columns are unmasked.

     3. To remove the roles assigned and the masked function, issue the following command.

SELECT anon.remove_masks_for_all_roles();

This would completely remove the functions created and the role assigned to the user ‘masked_user’

Feedback and suggestions

If you have feedback or suggestions for improving this data migration asset, please send an email to Database Platform Engineering Team.