dynamic_masking

Hide sensitive data from a “masked” user

You can hide some data from a role by declaring this role as a “MASKED” one. Other roles will still access the original data.

Example:

CREATE TABLE people ( id TEXT, firstname TEXT, lastname TEXT, phone TEXT);
INSERT INTO people VALUES ('T1','Sarah', 'Conor','0609110911');
SELECT * FROM people;

=# SELECT * FROM people;
 id | firstname | lastname |   phone
----+----------+----------+------------
 T1 | Sarah    | Conor    | 0609110911
(1 row)

Step 1 : Activate the dynamic masking engine

=# CREATE EXTENSION IF NOT EXISTS anon CASCADE;
=# SELECT anon.start_dynamic_masking();

Step 2 : Declare a masked user

=# CREATE ROLE skynet LOGIN;
=# SECURITY LABEL FOR anon ON ROLE skynet
-# IS 'MASKED';

Step 3 : Declare the masking rules

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

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

Step 4 : Connect with the masked user

=# \c - skynet
=> SELECT * FROM people;
 id | firstname | lastname  |   phone
----+----------+-----------+------------
 T1 | Sarah    | Stranahan | 06******11
(1 row)

How to change the type of a masked column

When dynamic masking is activated, you are not allowed to change the datatype of a column if there’s a mask upon it.

To modify a masked column, you need to switch of temporarily the masking engine like this:

BEGIN;
SELECT anon.stop_dynamic_masking();
ALTER TABLE people ALTER COLUMN phone TYPE VARCHAR(255);
SELECT anon.start_dynamic_masking();
COMMIT;

How to drop a masked table

The dynamic masking engine will build masking views upon the masked tables. This means that it is not possible to drop a masked table directly. You will get an error like this :

# DROP TABLE people;
psql: ERROR:  cannot drop table people because other objects depend on it
DETAIL:  view mask.company depends on table people

To effectively remove the table, it is necessary to add the CASCADE option, so that the masking view will be dropped too:

DROP TABLE people CASCADE;

How to unmask a role

Simply remove the security label like this:

SECURITY LABEL FOR anon ON ROLE bob IS NULL;

To unmask all masked roles at once you can type:

SELECT anon.remove_masks_for_all_roles();

Limitations

Listing the tables

Due to how the dynamic masking engine works, when a masked role will try to display the tables in psql with the \dt command, then psql will not show any tables.

This is because the search_path of the masked role is rigged.

You can try adding explicit schema you want to search, for instance:

\dt *.*
\dt public.*

Only one schema

The dynamic masking system only works with one schema (by default public). When you start the masking engine with start_dynamic_masking(), you can specify the schema that will be masked with:

ALTER DATABASE foo SET anon.sourceschema TO 'sales';

Then open a new session to the database and type:

SELECT start_dynamic_masking();

However static masking with anon.anonymize()and anonymous export with anon.dump() will work fine with multiple schemas.

Performances

Dynamic Masking is known to be very slow with some queries, especially if you try to join 2 tables on a masked key using hashing or pseudonymization.

Graphic Tools

When you are using a masked role with a graphic interface such as DBeaver or pgAdmin, the “data” panel may produce the following error when trying to display the content of a masked table called foo:

SQL Error [42501]: ERROR: permission denied for table foo

This is because most of these tools will directly query the public.foo table instead of being “redirected” by the masking engine toward the mask.foo view.

In order the view the masked data with a graphic tool, you can either:

1- Open the SQL query panel and type SELECT * FROM foo

2- Navigate to Database > Schemas > mask > Views > foo