Fujitsu Enterprise Postgres Quick Start Guides

To configure Data Masking, you must update postgresql.conf file and set 'shared_preloaded_libraries' parameter to the value 'pgx_datamasking':

[fepuser@hostname ~]$ vi /database/inst1/postgresql.conf
shared_preload_libraries = 'pgx_datamasking'

Save the changes and restart the server to apply the configuration changes:

[fepuser@hostname ~]$ pg_ctl -D /database/inst1/ restart

Create the extension in the database where Data Masking will be used:

[fepuser@hostname ~]$ psql -d postgres
postgres=# CREATE EXTENSION pgx_datamasking;

Create a new table named employee:

postgres=# CREATE TABLE employee(id INT PRIMARY KEY NOT NULL,name VARCHAR(20) NOT NULL,
postgres-#                       age INT NOT NULL,salary REAL,credit_card TEXT);

Insert some data into it for testing purpose:

postgres=# INSERT INTO employee VALUES (1, 'EMP1', 42, 175269, '1234-2345-3456-4567');
postgres=# INSERT INTO employee VALUES (2, 'EMP2', 36, 119275, '8686-7575-6464-5353');
postgres=# INSERT INTO employee VALUES (3, 'EMP3', 42, 123216, '3453-3454-5343-3433');
postgres=# INSERT INTO employee VALUES (4, 'EMP4', 50, 159967, '2581-4703-6925-8147');

View the values before applying any masking policy to them:

postgres=# SELECT * FROM employee;

Full masking

postgres=# SELECT pgx_create_confidential_policy (
postgres-#         table_name := 'employee',
postgres-#        policy_name := 'employee_policy',
postgres-#        enable      := 't',
postgres-#        expression  := '1=1',
postgres-#        column_name := 'salary');
pgx_create_confidential_policy
--------------------------------
t
(1 row)

postgres=# SELECT * FROM employee;

Partial masking

postgres=# SELECT pgx_alter_confidential_policy (
postgres-#        table_name         := 'employee',
postgres-#        policy_name        := 'employee_policy',
postgres-#        action             :='ADD_COLUMN',
postgres-#        column_name        :='credit_card',
postgres-#        function_type      := 'PARTIAL',
postgres-#        function_parameters:= 'VVVVFVVVVFVVVVFVVVV, VVVV-VVVV-VVVV-VVVV, *, 0, 12');
pgx_alter_confidential_policy
--------------------------------
t
(1 row)

postgres=# SELECT * FROM employee;

Regular expression masking

postgres=# SELECT pgx_alter_confidential_policy (
postgres-#        table_name         := 'employee',
postgres-#        policy_name        := 'employee_policy',
postgres-#        action             := 'add_column',
postgres-#        column_name        := 'name',
postgres-#        function_type      := 'regexp',
postgres-#        regexp_pattern     := 'EMP',
postgres-#        regexp_replacement := '*',
postgres-#        regexp_flags       := 'g');
pgx_alter_confidential_policy
--------------------------------
t
(1 row)

postgres=# SELECT * FROM employee;

Here, we have create the new database user called 'test_masking' and assign the appropriate grants to select the employee table as below.

postgres=# Create user test_masking password 'fep123' login;
postgres=# Grant ALL ON employee TO test_masking ;

Create a full masking policy on 'salary' column:

postgres=# SELECT pgx_create_confidential_policy (
postgres-#        table_name  := 'employee',
postgres-#        policy_name := 'employee_policy',
postgres-#        enable      := 't',
postgres-#        column_name := 'salary',
postgres-#        expression  := 'current_user = ''test_masking''');

Verify the values in the employee tables:

postgres=# SELECT * FROM employee;
postgres=# \c postgres test_masking
postgres=> SELECT * FROM employee;

Delete existing masking policy:

postgres=# SELECT pgx_drop_confidential_policy (
postgres-#        table_name  := 'employee',
postgres-#        policy_name := 'employee_policy');
pgx_drop_confidential_policy
--------------------------------
t
(1 row)

Verify that unmasked data is visible after dropping the policy:

postgres=# SELECT * FROM employee;

Note: For VCI minimal 2 CPUs are required to show parallel processing and implementation of max_parallel_workers. Hence, please minimal 2 CPUs machine should be used.

To configure VCI, you must update postgresql.conf file and set 'shared_preloaded_libraries' and 'session_preload_libraries' parameters to the value 'vci, pg_prewarm' and a few other related parameters as below:

[fepuser@hostname ~]$ vi /database/inst1/postgresql.conf 
shared_preload_libraries = 'vci, pg_prewarm'
session_preload_libraries = 'vci, pg_prewarm'
shared_buffers = 100MB
reserve_buffer_ratio = 30
vci.control_max_workers = 8
vci.max_parallel_degree = 4
max_worker_processes = 18
full_page_writes = on
jit = off
max_wal_size = 2GB

Save the changes and restart the server to apply the configuration changes:

[fepuser@hostname ~]$ pg_ctl -D /database/inst1/ restart

Create extensions in the database where VCI will be used:

[fepuser@hostname ~]$ psql postgres 
postgres=# CREATE EXTENSION vci;
CREATE EXTENSION
postgres=# CREATE EXTENSION pg_prewarm;
CREATE EXTENSION

Create a new table with lots of random numbers:

postgres=# CREATE TABLE random_numbers AS SELECT numbers, md5(random()::text) FROM generate_series(1,10000000) numbers;
SELECT 10000000

Now, check the performance of sum() aggregate function before creating the VCI. Notice the execution time:

postgres=# EXPLAIN ANALYZE SELECT SUM(numbers) FROM random_numbers;

Create VCI on the target columns:

postgres=# CREATE INDEX random_numbers_vci ON random_numbers USING VCI (numbers) WITH (stable_buffer=true);
CREATE INDEX

Now, check the performance of sum() aggregate function after creating the VCI. Notice the difference in ‘Execution Time’ shown in the Query Plan:

postgres=# EXPLAIN ANALYZE SELECT SUM(numbers) FROM random_numbers;

Delete the created VCI:

postgres=# DROP INDEX random_numbers_vci;