Enhancing Data Security in PostgreSQL: Using pgcrypto and Anonymizer Extensions

PostgreSQL is a widely-used and robust database management system, renowned for its advanced data handling capabilities. In the current era, where data security is crucial, PostgreSQL offers several extensions designed to enhance data security and privacy. This article delves into the use of the `pgcrypto` and `anon` extensions to implement encryption and data masking, thereby boosting data security.

Setting Up pgcrypto

The pgcrypto extension provides cryptographic functions for PostgreSQL, allowing for secure data encryption and hashing. Here’s a step-by-step guide to installing and enabling pgcrypto:

Check Available Extensions: First, verify if pgcrypto is available in your PostgreSQL installation:

SELECT * FROM pg_available_extensions WHERE name='pgcrypto';

--You should see an output similar to this:

name   | default_version | installed_version |         comment 
----------+-----------------+-------------------+------------------------- 
pgcrypto | 1.3             |                   | cryptographic functions

Create pgcrypto Extension: Enable pgcrypto in your database:

CREATE EXTENSION pgcrypto;

Verify Installation: Confirm that pgcrypto is installed:

SELECT * FROM pg_available_extensions WHERE name='pgcrypto';

-- The output should indicate that pgcrypto is installed:

name   | default_version | installed_version |         comment 
----------+-----------------+-------------------+------------------------- 
pgcrypto | 1.3             | 1.3               | cryptographic functions

Setting Up Anonymizer Extension

The anon extension in PostgreSQL helps mask sensitive data, ensuring privacy. Here’s how to set it up:

Install Required Dependencies: Install necessary packages before creating the anon extension:

dnf install ddlx_14-0.27-1PGDG.rhel9.noarch.rpm 
dnf install python3-faker-13.3.3-1.el9.noarch.rpm 
dnf install postgresql_anonymizer_14-1.1.0-1.rhel9.x86_64.rpm

Create and Configure Database: Create a new database and configure it for the anon extension:

CREATE DATABASE employee; 

ALTER DATABASE employee SET session_preload_libraries = 'anon';

Enable Anonymizer Extension: Connect to the new database and create the anon extension:

\c employee; 

CREATE EXTENSION anon CASCADE; 

SELECT anon.init();

Implementing Data Masking

Now, let’s create a sample table and apply data masking:

Create Sample Database and Table: Create a new database and a table to hold sample data:

\c employee;  

CREATE TABLE people (id INT,firstname VARCHAR(10),lastname VARCHAR(10),phone VARCHAR(15) );  

INSERT INTO people (id, firstname, lastname, phone) VALUES (1, 'Kemal', 'Oz', '9012345678');

Create Role and Grant Permissions: Create a role for data access and grant necessary permissions:

CREATE ROLE hr LOGIN; 

GRANT SELECT ON people TO hr;

Apply Data Masking: Enable dynamic masking and set up masking rules:

CREATE EXTENSION IF NOT EXISTS anon CASCADE; 

SELECT anon.start_dynamic_masking();  

Description: This command initializes dynamic data masking provided by the anon extension.

Purpose: To activate dynamic masking of data, which allows the system to apply masking rules dynamically as queries are executed.

SECURITY LABEL FOR anon ON ROLE hr IS 'MASKED';

• Description: This command assigns a security label to the hr role indicating that the role is subject to data masking rules.
• Purpose: To specify that the hr role should see masked data according to the rules defined in the anon extension.

SECURITY LABEL FOR anon ON COLUMN people.lastname IS 'MASKED WITH FUNCTION anon.fake_last_name()'; 

• Description: This command applies a security label to the lastname column of the people table, specifying that the data should be masked using the anon.fake_last_name() function.
• Purpose: To mask the lastname column data with a fake last name when accessed by roles with the MASKED label, ensuring that sensitive information is not exposed.

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

• Description: This command applies a security label to the phone column of the people table, specifying that the data should be masked using the anon.partial function. The anon.partial function reveals only the first two and last two digits of the phone number, masking the middle digits with asterisks (******).
• Purpose: To partially mask the phone column data, exposing only the first and last two digits, ensuring that the sensitive parts of the phone number are hidden from unauthorized users.

Verify Data Masking: Check the data masking in action:

SELECT * FROM people;

The output should show masked data:

id | firstname |  lastname  |   phone 
----+-----------+------------+------------  
1  | Kemal     | Wintheiser | 90******78

Conclusion

By leveraging the pgcrypto and anon extensions, PostgreSQL users can significantly enhance their data security measures. pgcrypto provides robust cryptographic functions, while anon ensures that sensitive data is masked effectively, protecting privacy and meeting compliance requirements. These tools are essential for any organization handling sensitive information, offering peace of mind in an increasingly data-centric world. For more detailed and technical articles like this, keep following our blog on Medium. If you have any questions or need further assistance, feel free to reach out in the comments below and directly.