Release History
Requirements

Introduction

Quick onboard

Deployment

Data Modeling

Connecting

Write data

Migration

Query

Operations and Maintenance

Performance Tuning

Troubleshooting

Reference Guide

SQL Reference

FAQ

Anonymizer

Anonymizer is a YMatrix extension designed to mask or replace personally identifiable information (PII) or commercially sensitive data stored in YMatrix databases. This capability employs a declarative anonymization approach, allowing users to define anonymization rules using YMatrix Data Definition Language (DDL) and apply anonymization policies directly within table definitions.

Note!
This feature is available only in the YMatrix Enterprise Edition. To try it out, click Request a Demo to schedule a consultation.

Prerequisites

Before using the Anonymizer feature, ensure that the extension has been installed in your database:

-- Install the anon extension
CREATE EXTENSION anon;
ALTER DATABASE demo SET session_preload_libraries = 'anon';

1. Usage Examples

1. Static Data Masking

Static data masking refers to the irreversible (or weakly reversible under strict controls) transformation of sensitive fields during data persistence or copying. This approach minimizes the risk of data leakage while preserving data usability for business analytics.

1.1 Example

  1. Create a table
demo=# CREATE TABLE customer (
    id SERIAL PRIMARY KEY,
    firstname TEXT,
    lastname TEXT,
    phone TEXT,
    birth DATE,
    postcode TEXT
);
CREATE TABLE
  1. Insert sample data
demo=# INSERT INTO customer VALUES 
(100, 'jane', 'conor', '18888888888', '1965-10-10', '1001'),
(101, 'marco', 'skywalker', NULL, '1951-10-09', '1004'),
(103, 'eralle', 'conor', '17777777777', '1965-07-10', '1003'),
(108, 'rika', 'Wang', '19999999999', '1981-10-09', '1004');
INSERT 0 4
  1. View original data before masking
demo=# SELECT * FROM customer;
 id  | firstname | lastname  |    phone    |   birth    | postcode
-----+-----------+-----------+-------------+------------+----------
 103 | eralle    | conor     | 17777777777 | 1965-07-10 | 1003
 101 | marco     | skywalker |             | 1951-10-09 | 1004
 100 | jane      | conor     | 18888888888 | 1965-10-10 | 1001
 108 | rika      | Wang      | 19999999999 | 1981-10-09 | 1004
(4 rows)
  1. Define masking rules
demo=# SECURITY LABEL FOR anon ON COLUMN customer.lastname 
IS 'MASKED WITH FUNCTION anon.dummy_last_name()';
SECURITY LABEL

demo=# SECURITY LABEL FOR anon ON COLUMN customer.phone 
IS 'MASKED WITH FUNCTION anon.partial(phone, 2, $$******$$, 2)';
  1. Apply masking rules permanently
  • Mask specific columns only
demo=# SELECT anon.anonymize_column('customer', 'phone');
 anonymize_column
------------------
 t
(1 row)

demo=# SELECT * FROM customer;
 id  | firstname | lastname  |   phone    |   birth    | postcode
-----+-----------+-----------+------------+------------+----------
 103 | eralle    | conor     | 17******77 | 1965-07-10 | 1003
 101 | marco     | skywalker |            | 1951-10-09 | 1004
 100 | jane      | conor     | 18******88 | 1965-10-10 | 1001
 108 | rika      | Wang      | 19******99 | 1981-10-09 | 1004
(4 rows)
  • Mask the entire table according to defined rules
demo=# SELECT anon.anonymize_table('customer');
 anonymize_table
-----------------
 t
(1 row)

demo=# SELECT * FROM customer;
 id  | firstname | lastname  |   phone    |   birth    | postcode
-----+-----------+-----------+------------+------------+----------
 103 | eralle    | Kertzmann | 17******77 | 1965-07-10 | 1003
 101 | marco     | McKenzie  |            | 1951-10-09 | 1004
 100 | jane      | Ward      | 18******88 | 1965-10-10 | 1001
 108 | rika      | Schulist  | 19******99 | 1981-10-09 | 1004
(4 rows)

2. Dynamic Data Masking

Dynamic data masking keeps data in its original form within the database and applies real-time masking or substitution only when query results are returned—based on the requester’s identity, role, or policy rules.

2.1 Data Access Policy Overview

  • CFO: Can view all customer records.
  • VIP Account Manager (vip_mgr): Can view all records except those marked as "super vip".
  • Regular Employee (bank_emp): Cannot view any sensitive customer information.

2.2 Example

  1. Create a table
mydb=# CREATE TABLE customers (
    id     INT PRIMARY KEY,
    status TEXT,          -- regular / vip / super vip
    pii    TEXT           -- sensitive field
) USING mars3;

mydb=# INSERT INTO customers VALUES 
(1, 'regular', 'Wang Wei'), 
(2, 'vip', 'Liu Han'), 
(3, 'super vip', 'Ma teng');
  1. Enable dynamic masking engine
mydb=# ALTER DATABASE mydb SET anon.transparent_dynamic_masking TO true;
  1. Ensure required roles exist; create them if missing
mydb=# DO $$
BEGIN
  IF NOT EXISTS (SELECT 1 FROM pg_roles WHERE rolname = 'cfo') THEN CREATE ROLE cfo LOGIN; END IF;
  IF NOT EXISTS (SELECT 1 FROM pg_roles WHERE rolname = 'vip_mgr') THEN CREATE ROLE vip_mgr LOGIN; END IF;
  IF NOT EXISTS (SELECT 1 FROM pg_roles WHERE rolname = 'bank_emp') THEN CREATE ROLE bank_emp LOGIN; END IF;
END$$;
DO
  1. Grant SELECT privileges on the table
mydb=# GRANT SELECT ON customers TO cfo, vip_mgr, bank_emp;
  1. Declare dynamic masking rules
mydb=# SECURITY LABEL FOR anon ON COLUMN customers.pii
IS 'MASKED WITH FUNCTION anon.ternary(
        (CURRENT_ROLE = ''bank_emp'')
        OR (CURRENT_ROLE = ''vip_mgr'' AND status = ''super vip''),
        ''****''::text,
        pii
    )';

mydb=# SECURITY LABEL FOR anon ON ROLE vip_mgr IS 'MASKED';
mydb=# SECURITY LABEL FOR anon ON ROLE bank_emp IS 'MASKED';
  1. Query data using different roles
  • As bank_emp (all PII masked)
mydb=# \c mydb bank_emp
You are now connected to database "mydb" as user "bank_emp".

mydb=> SELECT * FROM customers;
 id |  status   | pii
----+-----------+------
  1 | regular   | ****
  2 | vip       | ****
  3 | super vip | ****
(3 rows)
  • As cfo (full access)
mydb=> \c mydb cfo
You are now connected to database "mydb" as user "cfo".

mydb=> SELECT * FROM customers;
 id |  status   |    pii
----+-----------+------------
  1 | regular   | Wang Wei
  2 | vip       | Liu Han
  3 | super vip | Ma teng
(3 rows)
  • As vip_mgr (super vip PII masked)
mydb=> \c mydb vip_mgr
You are now connected to database "mydb" as user "vip_mgr".

mydb=> SELECT * FROM customers;
 id |  status   |   pii
----+-----------+----------
  1 | regular   | Wang Wei
  2 | vip       | Liu Han
  3 | super vip | ****
(3 rows)
← Previous
Audit
Next →
User Management