dynamic_masking

Hide sensitive data from a “masked” user

You can hide some data from a role by declaring this role as “MASKED”.

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;
=# ALTER DATABASE foo SET anon.transparent_dynamic_masking TO true;

Step 2 : Declare the masking rules

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

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

Step 3 : Declare a masked user with read access

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

GRANT pg_read_all_data to skynet;

NOTE: If you want a more fine-grained access policy you can grant access more precisely, for instance:

GRANT USAGE ON SCHEMA public TO skynet;
GRANT SELECT ON ALL TABLES IN SCHEMA public TO skynet;
-- etc.

Step 4 : Connect with the masked user

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

Principles

• Masked roles should not be allowed to insert, update or delete data.
• You can mask table in multiple schemas.
• Generated columns are respected.
• You can apply Row Security Policies aka RLS to a masked role.
• A masking rule may break data integrity. For instance, you can mask a column having a UNIQUE constraint with the value NULL. This is up to you to decide whether or not the mask users need data integrity.

Generated columns

Generated columns are recomputed from the masked values of their source columns by default. To return a generated column’s original stored value instead, label it as NOT MASKED in the active masking policy:

SECURITY LABEL FOR anon ON COLUMN users.email_hash IS 'NOT MASKED';

This keeps email_hash unchanged while email remains dynamically masked. Removing the label restores recomputation from masked values.

Limitations

Masked roles are not allowed to :

• use EXPLAIN
• define SECURITY LABELS
• use cached plans prepared by another role (see below)
• read views defined as SECURITY INVOKER

How to unmask a role

Simply remove the security label like this:

SECURITY LABEL FOR anon ON ROLE bob IS NULL;

Transparent Dynamic Masking and Prepared Statements

Transparent dynamic masking works by rewriting a query when PostgreSQL analyzes it. Prepared statements (PREPARE), PL/pgSQL functions and other cached plans are analyzed once and then reused, so the masking decision must be re-evaluated whenever it could change.

To guarantee this, the extension marks every query analyzed on the current role, in the same way PostgreSQL does for Row Level Security.

As a result, a cached plan is analyzed again when:

• the current role changes (for instance with SET ROLE), so a plan prepared by an unmasked role is never reused by a masked role;
• a masking rule is declared, changed or removed, or a role is masked or unmasked with SECURITY LABEL, so plans prepared before the change are rebuilt with the new rules.

These steps ensure that prepared statements and PL/pgSQL functions behave the same way as regular queries when run by a masked user.

The trade-off is that a new execution plan must be regenerated, which may be less performant, each time a cached plan is reused by a different role in the session or the masking rules change. The impact is usually small, but applications that frequently switch roles and rely heavily on prepared statements may notice it.