Architecture:

The above image represents how authorized and Un-authorized people view sensitive data.

Data Masking:

Data masking, also known as data obfuscation or data anonymization, involves replacing sensitive data with fictional or modified values, rendering the data meaningless or less sensitive to unauthorized users. The goal is to protect sensitive information while allowing users to perform their duties or testing without exposing confidential data.

Data Redaction:

Data Redaction is used in the context of database security. It involves controlling access to sensitive data by showing authorized users the full, unredacted data while obscuring or replacing the sensitive parts for unauthorized users. Data redaction is often implemented as a security measure to prevent data breaches and ensure compliance with data privacy regulations.

Creating a table and adding data:

We created a table called tb_Customers to store customer information, including sensitive data like credit card numbers, phone numbers, and birthdates.

Then, create a pgcrypto extension if it does not exist in the database.

pgcrypto extension is used for cryptographic functions and is commonly used for tasks such as generating random numbers, hashing data, and performing encryption and decryption operations within the PostgreSQL database.

Data Redaction Function:

Next, we create a function called fn_redact_customersdata that redacts sensitive data based on the user’s privileges. The function accepts CreditCardNumber, Phone Number, and Birthdate as input parameters and returns redacted values.

In this function, we check the current user’s privileges. If the user is privileged_user2, they get full access to the sensitive data; otherwise, the data is redacted.

Role Creation and Privilege Assignment:

We create two roles, privileged_user2 and non_privileged_user2. The privileged_user2 role is granted select privileges on the tb_Customers table.

Role Switching and Data Retrieval:

Now, we simulate the role of nonprivileged users and demonstrate how data redaction works.

Here, you can observe that for the nonprivileged users, the data is redacted for credit card numbers, phone numbers, and birthdates.

Now, we simulate the role to privileged users.

Here, you can observe the privileged user accessing the full data.

Conclusion:

In this article, we have explored a practical implementation of data redaction in PostgreSQL. By creating a redaction function, defining roles, and granting privileges, we can protect sensitive customer data while allowing authorized users to access the full data. This approach provides a robust solution for securing sensitive information in your database and helps you meet regulatory and ethical data protection requirements.

References:

1. Basics of Data Masking – Baffle
2. www.enterprisedb.com
3. www.cybertec-postgresql.com
4. www.postgresql.org
5. www.datasunrise.com