Demo app to show what PostgreSQL anonymizer can do for us
Tested with the following tools:
- ruby 3.4.5
- node 16.20.2
- yarn 1.22.22
- PostgreSQL 17
git clone https://github.com/FerPerales/anon_app.git
cd anon_app
bundle install
yarn install
rails db:create
rails db:migrate
rails db:seed
rails serverGo to localhost:3000/users to see the original users data
git clone https://gitlab.com/dalibo/postgresql_anonymizer.git
Note: instructions provided for Mac. See oficial PostgreSQL docs to install in a different OS
make extension
make installpsql anon_app_development -c "ALTER DATABASE anon_app_development SET session_preload_libraries = 'anon'"
psql anon_app_development -c "CREATE EXTENSION IF NOT EXISTS anon CASCADE;"psql anon_app_developmentInside your database console, run this to define our masking rules:
SELECT anon.init();
SECURITY LABEL FOR anon ON COLUMN users.first_name
IS 'MASKED WITH FUNCTION anon.dummy_first_name()';
SECURITY LABEL FOR anon ON COLUMN users.last_name
IS 'MASKED WITH FUNCTION anon.dummy_last_name()';
SECURITY LABEL FOR anon ON COLUMN users.street_line1
IS 'MASKED WITH VALUE $$CONFIDENTIAL$$';
SECURITY LABEL FOR anon ON COLUMN users.street_line2
IS 'MASKED WITH VALUE $$CONFIDENTIAL$$';
SECURITY LABEL FOR anon ON COLUMN users.zipcode
IS 'MASKED WITH FUNCTION anon.random_zip()';
SECURITY LABEL FOR anon ON COLUMN users.email
IS 'MASKED WITH FUNCTION anon.partial_email(users.email)';
ALTER DATABASE anon_app_development SET anon.transparent_dynamic_masking TO true;
CREATE ROLE restricted_user LOGIN;
SECURITY LABEL FOR anon ON ROLE restricted_user is 'MASKED';
GRANT pg_read_all_data to restricted_user;
-- If you are running PostgreSQL 13 or 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.
CREATE ROLE anon_dumper LOGIN PASSWORD 'password';
ALTER ROLE anon_dumper SET anon.transparent_dynamic_masking TO true;
SECURITY LABEL FOR anon ON ROLE anon_dumper IS 'MASKED';
GRANT pg_read_all_data to anon_dumper;pg_dump anon_app_development --no-security-labels --exclude-extension="anon" --file="anon_app_development.sql"
pg_dump anon_app_development --user anon_dumper --no-security-labels --exclude-extension="anon" --file="anon_app_development_anonymized.sql"SELECT anon.anonymize_database();Check localhost:300 again and you will see all data anonymized