How to Anonymization and Data Masking for PostgreSQL in Ubuntu 22.04 LTS Server

In this video, I will show you how to remove or mask Personally Identifiable Information or PII, and other sensitive data from a PostgreSQL database.

PLEASE SUBSCRIBE :)
PLEASE HIT LIKE IF IT HELPED :)

This is sometimes required when you want a copy of production data to your development environment. We don't want sensitive data in our development environment.
The solution is to anonymize or mask the data in our development environment.

We will use the postgresql-anonymizer extension of PostgreSQL which is available in postgres repo.

This tutorial assume you already have the latest PostgreSQL installed on Ubuntu server. If you don't, see the follow the link below for installing Ubuntu server and PostgreSQL.

LINKS:

COMMANDS:
1. Installation
sudo apt -y install make gcc postgresql-server-dev-14
cd postgresql_anonymizer/
make extension PG_CONFIG=/usr/lib/postgresql/14/bin/pg_config
sudo make install PG_CONFIG=/usr/lib/postgresql/14/bin/pg_config

sudo su - postgres
psql
ALTER SYSTEM SET shared_preload_libraries = 'anon';

sudo su - postgres
psql
CREATE EXTENSION anon CASCADE;

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

\dt

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;

SELECT * FROM customer;

select * from customer;

IS 'MASKED WITH VALUE $$CONFIDENTIAL$$';

select * from customer;

3. Dynamic Masking
CREATE TABLE people ( id TEXT, firstname TEXT, lastname TEXT, phone TEXT);
INSERT INTO people VALUES ('T1','Sarah', 'Conor','0609110911');
SELECT * FROM people;

CREATE USER lazy WITH PASSWORD 'Password1';

SECURITY LABEL FOR anon ON ROLE lazy
IS 'MASKED';

select * from people;

postgres postgres lazy

local all lazy md5

sudo su - postgres
psql
\c - lazy
SELECT * FROM people;

\c - postgres
SELECT * FROM people;

Chapters:
00:00 Intro
00:48 Install postgresql_anonymizer Extension
01:36 Load the Extension
02:36 Static Masking
05:45 Dynamic Masking
08:45 Outro

Please enjoy the video and if you have any questions, leave a comment down below.

Remember to Like, Share and Subscribe if you enjoyed the video!

#postgresql
#database
#ubuntu
#thelazysysadmin

[brendishyjorz]

Thank you for this excellent tutorial, how can change again my primary server like my maste node and my secondary server like my standby after promote secondary to read-write?

[matheusFernandes-yzzx]

🎯 Key points for quick navigation:

00:00 *🛡️ Installing PostgreSQL Anonymizer Extension*
- Installing the PostgreSQL anonymizer extension from source.
01:54 *🔒 Static Masking for Anonymization*
- Static masking permanently removes sensitive data by replacing it with artificial information.
- It's essential to use static masking carefully to avoid data loss.
- Examples of static masking on data columns in a table.
05:09 *🎭 Dynamic Masking with PostgreSQL*
- Dynamic masking hides data in specific rows by marking them as masked.
- It allows certain users to access the original data while masking it for others.
- Example of dynamic masking for a specific column in a table.

Made with HARPA AI

[kulveerkapoor]

when i changed to user and then used select * from people; getting an error permission denied for table people

[Sheri]

6:45 Hello! What command I have to use to get mask phone_number only with one side (for example:
I put but i have error(, very poor info in Internet.

[venkataramakotibandaru]

How to install Postgre SQL Anonymizer on Amazon RDS?

[uncoub]

hi, i have a problem, help me please

[one_helsingmc_borrov]

when i put next command in terminal:
$ make extension
i got next error:
make: *** No rule to make target 'extension'. Stop.
does anyone know how to solve it ?

[uncoub]

ERROR: schema "mask" does not exist

[KalyanChakravarthy-oxsg]

Hi

Super simplified the process for newbies

Can you help me with my below problem

We have few fields which are I below pattern which needs masking


In the above columns I need data before @needs to be masked. Is it possible

Pls guide and thanks in advance