static_masking

Permanently remove sensitive data

Sometimes, it is useful to transform directly the original dataset. You can do that with different methods:

• Applying masking rules
• Shuffling a column
• Adding noise to a column

These methods will destroy the original data. Use with care.

Applying masking rules

You can permanently apply the masking rules of a database with anon.anonymize_database().

Let’s use a basic example :

CREATE TABLE customer(
  id SERIAL,
  full_name TEXT,
  birth DATE,
  employer TEXT,
  zipcode TEXT,
  fk_shop INTEGER
);

INSERT INTO customer
VALUES
(911,'Chuck Norris','1940-03-10','Texas Rangers', '75001',12),
(312,'David Hasselhoff','1952-07-17','Baywatch', '90001',423)
;

SELECT * FROM customer;

 id  |   full_name      |   birth    |    employer   | zipcode | fk_shop
-----+------------------+------------+---------------+---------+---------
 911 | Chuck Norris     | 1940-03-10 | Texas Rangers | 75001   | 12
 112 | David Hasselhoff | 1952-07-17 | Baywatch      | 90001   | 423

Step 1: Load the extension :

CREATE EXTENSION IF NOT EXISTS anon CASCADE;
SELECT anon.init();

Step 2: Declare the masking rules

SECURITY LABEL FOR anon ON COLUMN customer.full_name
IS 'MASKED WITH FUNCTION anon.dummy_name()';

SECURITY LABEL FOR anon ON COLUMN customer.employer
IS 'MASKED WITH FUNCTION anon.dummy_company_name()';

SECURITY LABEL FOR anon ON COLUMN customer.zipcode
IS 'MASKED WITH FUNCTION anon.random_zip()';

Step 3: Replace authentic data in the masked columns :

SELECT anon.anonymize_database();

SELECT * FROM customer;

 id  |  full_name  |   birth    |      employer           | zipcode | fk_shop
-----+-------------+------------+-------------------------+---------+---------
 911 | jesse Kosel | 1940-03-10 | Marigold Properties LLC | 62172   |      12
 312 | leolin Bose | 1952-07-17 | Inventure Inc           | 20026   |     423

You can also use anonymize_table() and anonymize_column() to remove data from a subset of the database :

SELECT anon.anonymize_table('customer');
SELECT anon.anonymize_column('customer','zipcode');

WARNING : Static masking is a slow process. The principle of static masking is to update all lines of all tables containing at least one masked column. This basically means that PostgreSQL will rewrite all the data on disk. Depending on the database size, the hardware and the instance config, it may be faster to export the anonymized data (See [Anonymous Dumps] ) and reload it into the database.

Disabling Static Masking

You may be scared that someone could accidentally run anon.anonymize_database() and wipe out all the data.

If so, you can disable this feature globally with:

ALTER SYSTEM SET anon.static_masking TO off

Or disable it for a single user :

ALTER ROLE bob SET anon.static_masking TO off;

Or disable it everyone except one user

ALTER DATABASE mydb SET anon.static_masking = FALSE;
ALTER ROLE daniel SET anon.static_masking = TRUE;

Static Masking and Multiple Masking Policies

When using multiple masking policies, you can simply add the policy name at the end of the static masking functions.

For instance, if you defined a masking policy named “rgpd”, you can apply it with

SELECT anon.anonymize_table('customer','rgpd');
SELECT anon.anonymize_column('customer','zipcode','rgpd');

By default, there’s a single masking policy named “anon”.

Parallel Static Masking

For large databases, static masking can be a time-consuming operation. To improve performance, the extension supports parallel static masking using PostgreSQL background workers.

Instead of using anon.anonymize_database(), you can use the parallel version:

SELECT anon.anonymize_database_parallel(4);

The parameter specifies the number of parallel workers to use. The function will:

1. Analyze the foreign key relationships between tables
2. Group tables to avoid constraint violations
3. Distribute the work across multiple background workers
4. Process tables in parallel where possible

The maximum number of background workers can be configured using the GUC parameter anon.max_bg_workers:

-- Set the maximum number of background workers (default: 4)
SET anon.max_bg_workers = 8;

-- Or configure it system-wide
ALTER SYSTEM SET anon.max_bg_workers = 8;
SELECT pg_reload_conf();

The anon.max_bg_workers parameter accepts values between 1 and 64. On servers with many CPUs, you may want to increase this value to improve performance. The value is capped by PostgreSQL’s guc: max_worker_processes (default: 8).

Important considerations:

• Foreign Keys: Tables with foreign key relationships are grouped together and processed sequentially to maintain referential integrity.
• Independent Tables: Tables without foreign key relationships can be processed in parallel.
• Resource Usage: More workers mean more CPU and I/O usage. Monitor your system resources when increasing this value.
• Worker Availability: The actual number of workers used may be limited by PostgreSQL’s max_worker_processes configuration.
• Logs: In this first version of parallel static masking, dynamic background workers are used. So there is no communication between main and background process. That’s why errors are logged into the PostgreSQL log file, but not in stdout. If anonymize_database_parallel response is False, then check pg logs.
• atomic function: The anon.anonymize_database_parallel() is NOT an atomic function. If a worker fails, the others will not rollback, leaving the database in an intermediate state: some tables will be anonymized, while others will remain unchanged. In such situation, check the logs to find which tables were not anonymized, and retry to apply the masking rules with anon.anonymize_table().

Faster masking by dropping indexes

!!! warning “Beta”

This option is still considered **beta** for now: please consider opening an
issue if you run into any bug.

Static masking keeps every secondary index up to date while it rewrites the masked rows, which is often the slowest part on a large, heavily-indexed table. When anon.static_masking.drop_indexes is enabled, anonymize_table() (and therefore anonymize_database()) drops a table’s plain secondary indexes before masking it and rebuilds them afterwards, which is usually much faster than maintaining them row by row:

SET anon.static_masking.drop_indexes = on;
SELECT anon.anonymize_database();

The parameter is off by default and only affects performance, never the masking result. Enable it for large tables carrying several secondary indexes; on a primary-key-only table there is nothing to drop and it makes no difference.

Important considerations:

• Atomicity: the drop, the masking and the rebuild run in the same transaction, so a failure restores the indexes along with the data.
• Indexes kept in place: unique, constraint (primary key, unique, exclusion), replica-identity, clustered, non-default-tablespace, invalid and partitioned indexes are never dropped, along with indexes carrying a custom per-column statistics target. A partitioned table’s indexes cannot be dropped individually, so the option does nothing for it directly. Only standalone indexes on the leaf partitions you mask benefit.
• Tablespace: a dropped index is rebuilt where it was, on the database’s default tablespace, so a non-empty default_tablespace never silently moves it (indexes on a non-default tablespace are kept, as noted above).
• Deferrable constraints: a table involved in a deferrable constraint, on either side, is skipped entirely with a NOTICE. Masking emits SET CONSTRAINTS ALL DEFERRED, which forbids CREATE INDEX on that table in the same transaction.
• Locking: this option raises the table lock for the masking from ROW EXCLUSIVE to ACCESS EXCLUSIVE, so it also blocks concurrent reads.
• Parallel masking: a session-level SET is not seen by the background workers of anonymize_database_parallel(); set it with ALTER DATABASE or ALTER SYSTEM for those. Those workers disable nested parallelism, so the index rebuild runs single-threaded there; keep max_worker_processes above the number of jobs. anonymize_column() is not affected.
• Statistics: rebuilding an index does not restore the statistics ANALYZE may have gathered on an expression index. Run ANALYZE after masking with this option (good practice anyway once the data has changed).

Shuffling

Shuffling mixes values within the same columns.

• anon.shuffle_column(shuffle_table, shuffle_column, primary_key) will rearrange all values in a given column. You need to provide a primary key of the table.

This is useful for foreign keys because referential integrity will be kept.

IMPORTANT: shuffle_column() is not a masking function because it works “vertically” : it will modify all the values of a column at once.

Adding noise to a column

There are also some functions that can add noise on an entire column:

• anon.add_noise_on_numeric_column(table, column, ratio) if ratio = 0.33, all values of the column will be randomly shifted with a ratio of +/- 33%

• anon.add_noise_on_datetime_column(table, column, interval) if interval = ‘2 days’, all values of the column will be randomly shifted by +/- 2 days

IMPORTANT : These noise functions are vulnerable to a form of repeat attack. See demo/noise_reduction_attack.sql for more details.