Pre-defined data masking functions

pg_columnmask extension provides built-in utility functions written in C language (for faster execution) which can be used as masking expression for pg_columnmask policies.

mask_text

A function to mask text data with configurable visibility options.

Arguments

Parameter	Datatype	Description
input	TEXT	The original text string to be masked
mask_char	CHAR(1)	Character used for masking (default: 'X')
visible_prefix	INT	Number of characters at the beginning of input text that will remain unmasked (default: 0)
visible_suffix	INT	Number of characters at the end of input text that will remain unmasked (default: 0)
use_hash_mask	BOOLEAN	If TRUE, uses a hash-based masking instead of mask_char (default: FALSE)

Example of using different masking options

Mask the entire input string with the default 'X' character

postgres=> SELECT pgcolumnmask.mask_text('Hello World');
  mask_text  
-------------
 XXXXXXXXXXX

Use the mask_char argument to mask text input using a different character

postgres=> SELECT pgcolumnmask.mask_text('Hello World', '*');
  mask_text  
-------------
 ***********

Use visible_prefix and visible_suffix parameters to control how many characters remain unmasked at the start and end of the text

postgres=> SELECT pgcolumnmask.mask_text('Hello World', '*', 5, 1);
  mask_text  
-------------
 Hello*****d

When use_hash_mask is true the input string is masked using random characters mask_char argument is ignored but visible_prefix and visible_suffix are still honored

postgres=> SELECT pgcolumnmask.mask_text('Hello World', '*', 2, 2, true);
  mask_text  
-------------
 Hex36dOHild

mask_timestamp

Parameter	Datatype	Description
ts_to_mask	TIMESTAMP	The original timestamp to be masked
mask_part	TEXT	Specifies which part of the timestamp to mask (default: 'all') Valid values: 'year', 'month', 'day', 'hour', 'minute', 'second', 'all'
mask_value	TIMESTAMP	The timestamp value to use for masking (default: '1900-01-01 00:00:00')

Example of using mask_timestamps

These examples demonstrate complete timestamp masking to a default value, partial masking of specific timestamp components (year only), and masking with a custom replacement value.

Completely mask input value to the default timestamp

postgres=> SELECT pgcolumnmask.mask_timestamp('2023-06-15 14:30:00');
   mask_timestamp    
---------------------
 1900-01-01 00:00:00

To mask only one part of the timestamp from example only the year

postgres=> SELECT pgcolumnmask.mask_timestamp('2023-06-15 14:30:00', 'year');
   mask_timestamp    
---------------------
 1900-06-15 14:30:00

To change the masked value for timestamp use the mask_value argument

postgres=> SELECT pgcolumnmask.mask_timestamp('2023-06-15 14:30:00', 'all', '2012-12-12 12:12:12');
   mask_timestamp    
---------------------
 2012-12-12 12:12:12

mask_timestamp

A function to mask email addresses while preserving email structure.

Parameter	Datatype	Description
input	TEXT	The original email address to be masked
mask_char	CHAR(1)	Character used for masking (default: 'X')
mask_local	BOOLEAN	If TRUE, masks the local part of email (before @) (default: TRUE)
mask_domain	BOOLEAN	If TRUE, masks the domain part of email (after @) (default: TRUE)

Example of using mask_email

These examples demonstrate complete email masking, custom mask characters, and selective masking of either the local part or domain part of the email address.

Complete masking

postgres=> SELECT pgcolumnmask.mask_email('[e-mail]');
    mask_email    
------------------
 [e-mail]

Use mask_char to change the character used for masking

postgres=> SELECT pgcolumnmask.mask_email('[e-mail]', '*');
    mask_email    
------------------
 ****@*******.com

Use mask_local and mask_domain to control masking on local and domain

postgres=> SELECT pgcolumnmask.mask_email('[e-mail]', '*', true, false);
    mask_email    
------------------
 ****@example.com

postgres=> SELECT pgcolumnmask.mask_email('[e-mail]', '*', false, true);
    mask_email    
------------------
 user@*******.com