Dynamic Data masking in Azure PostgreSQL – Flexible Server for migrated Oracle workloads

  • Thread starter Thread starter VenkatMR
  • Start date Start date
V

VenkatMR

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.



VenkatMR_1-1727296505218.png



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.

VenkatMR_2-1727296546734.png



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.
    Code: 
    INSERT INTO people_new VALUES ('E1',David, 'Miller','0609110911');
INSERT INTO people_new VALUES ('E2','Robert', 'Bruce','0708910911');

  3. SELECT * FROM people_new;

VenkatMR_3-1727296823626.png

  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

VenkatMR_4-1727297182574.png



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



VenkatMR_5-1727297341755.png

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;











VenkatMR_6-1727297655775.png

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.

Continue reading...
 
Back
Top