PostgreSQL Data Masking Tricks

Hi,

SQL Server announced Dynamic Data Masking feature not long ago and I'm Postgresql DBA! PostgreSQL does not have like that feature yet. So, what if we need data masking on our database's tables?

If you need make strict your database tables and hide your column but not that all follow bellow function!

This function shows only the first and last two characters.

If you looking for some solution for your table's column and if it is the character you can create bellow function in your database and enjoy it!


CREATE OR REPLACE FUNCTION dba.pg_datamask_column(col character varying)
  RETURNS character varying AS
$BODY$
begin 
if (select length(col))<=2 then return col;
elsif (select length(col))=3 or (select length(col))=4 then return  (select replace(col,right(col,2),'xx'));
else return (select 
replace(substring(col from 1 for length(col)-2),
right(substring(col from 1 for length(col)-2),length(col)-4),'xxxx')||right(col,2));
end if;
end
$BODY$
LANGUAGE 'plpgsql'
  COST 100;


You will not be able to apply these changes on your main table but you can create a view which is included columns with dba.pg_datamask_column functions. So after revoking all privileges on the main table, you can grant select permission to a specific user. Finally, that user can see all main table's column and you sure that user can not see a specific column which has some data privacy.

Loves,