postgres extension postgresql_anonymizer - ghdrako/doc_snipets GitHub Wiki

• https://postgresql-anonymizer.readthedocs.io/en/stable/
• https://postgresql-anonymizer.readthedocs.io/en/latest/dynamic_masking/
• https://postgresql-anonymizer.readthedocs.io/en/latest/masking_functions/
• https://access.crunchydata.com/documentation/postgresql-anonymizer/1.3.1/

PostgreSQL Anonymizer works by creating a new schema and building a "mask" over the original data using views. These views are identical to the original tables but replace sensitive data with fake data. The masked user is only granted access to the new schema and not the original one, so they can only see the obscured data​.

However, there are several limitations and issues with the dynamic masking feature:

• It only works with one schema at a time. If you need to mask data in multiple schemas, you would have to use the anon.anonymize() function or the anon.dump() function which work fine with multiple schemas​
• It can be slow with some queries, especially if you try to join 2 tables on a masked key using hashing or pseudonymization​
• It can cause issues with graphic tools like DBeaver or pgAdmin, which might directly query the original table instead of being "redirected" by the masking engine to the masked view. To view the masked data with a graphic tool, you might need to either run a SQL query like SELECT * FROM table_name or navigate to the masked view in the database navigator​

Static masking:

• 'anon.anonymize_database()' - permanently remove the PII from a database - destroy the original data You can also use anonymize_table() and anonymize_column() to remove data from a subset of the database.

Dynamic masking

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

Declare a masked user

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

Anonymous Dumps

• https://postgresql-anonymizer.readthedocs.io/en/latest/anonymous_dumps/

pg_dump_anon.sh -h localhost -p 5432 -U bob bob_db > dump.sql

or experimental

pg_dump foo \
        --user dump_anon \
        --no-security-labels \
        --extension pgcatalog.plpgsql \
        --file=foo_anonymized.sql

Install as extension in instance

ALTER DATABASE foo SET session_preload_libraries = 'anon';
CREATE EXTENSION anon CASCADE;
SELECT anon.init();

Install as docker

docker pull registry.gitlab.com/dalibo/postgresql_anonymizer:stable
docker run -d -e POSTGRES_PASSWORD=x -p 6543:5432 registry.gitlab.com/dalibo/postgresql_anonymizer
export PGPASSWORD=x
psql --host=localhost --port=6543 --user=postgres
# SELECT anon.partial_email('[email protected]');
     partial_email
-----------------------
 da******@gm******.com
(1 row)

Install in CloudSQL for Postgres

• https://cloud.google.com/sql/docs/postgres/extensions#postgresql_anonymizer

cloudsql.enable_anon flag to on