Data Masking

Data Masking

Column and Dynamic Data Masking

Columns need two kinds of protection. Column privileges decide whether a role can read a column at all. Dynamic data masking shows the column to everyone but rewrites its values at query time unless the reader is entitled to the original. PostgreSQL 1,289 does the first with column-level GRANT and the second with a view; the open-source PostgreSQL Anonymizer extension adds declarative masking rules for larger estates.

4162-mask.sql: a column grant and a masking view for customer e-mailSQL

CREATE ROLE support NOLOGIN;
CREATE ROLE pii_readers NOLOGIN;
GRANT SELECT (customer_id, name, country) ON customers TO support;   -- column privileges
CREATE VIEW mart.customers_masked WITH (security_barrier) AS
SELECT customer_id, name, country,
       CASE WHEN pg_has_role(current_user, 'pii_readers', 'MEMBER') THEN email
            ELSE regexp_replace(email, '^(.).*@', '\1***@') END AS email
FROM customers;
GRANT USAGE ON SCHEMA mart TO support;
GRANT SELECT ON mart.customers_masked TO support, pii_readers;
SET ROLE support;
SELECT customer_id, name, country FROM customers WHERE customer_id = 1;
SELECT email FROM customers WHERE customer_id = 1;               -- not granted
SELECT customer_id, email FROM mart.customers_masked WHERE customer_id <= 2;
RESET ROLE;
GRANT pii_readers TO support;                                    -- now support may see PII
SET ROLE support;
SELECT customer_id, email FROM mart.customers_masked WHERE customer_id = 1;
RESET ROLE;
REVOKE pii_readers FROM support;

Output

...
           1 | Ada Reid | AU
ERROR:  permission denied for table customers
 customer_id |      email
-------------+------------------
           1 | a***@example.com
           2 | a***@example.com
...
           1 | ada.reid1@example.com

The view runs with its owner's privileges, so support reads e-mail through it without the column grant, and pg_has_role() decides per query whether to mask; security_barrier keeps a crafted function in a WHERE clause from seeing rows early. Granting pii_readers revealed the address; the script then revokes it.

MENU