Businesses that fully leverage technology rely on dozens of databases and applications to streamline their operations.
Between the growing regulations aimed at protecting consumer data and an increasing number of cybercriminals and cybercrime, ensuring the security of sensitive data is essential. Not properly securing sensitive data, can result in serious fines, data breaches, or loss of customer trust. All of these outcomes are far more expensive than implementing appropriate data security measures from the start.
Data masking offers an effective method of securing customer data by obscuring individual values.
This article will discuss Data Masking in PostgreSQL, particularly:
Data Masking
“masked with function anon.partial,” “masked with function anon.fake,” “masked with function anon.random,” familiar, right? These are common masking functions in the PostgreSQL anonymizer to mask original data types to anonymous dumps. But what is data masking? Why is there a need to mask the data?
Data masking ensures sensitive information is hidden from those who should not have access, protecting real data from unintentional and intentional threats. The goal is to secure the actual sensitive data but allow a variety of different teams within the business to make use of the data to optimize business operations.
To Read more:
Static Masking in Postgres
Like PostgreSQL database dynamic data masking, static masking creates proxies of the original data based on data masking rules.
In essence, the idea is to create tables and copy data into them in an anonymized form. However, you will need to update it continually, and it could endanger your database during extraction.
Even though static masking is regularly recommended when sharing data, the labor intensive process required per table and the need for anonymization creates a significant delay in any data transfers. Therefore, you may consider dynamic data masking, a safer and more efficient option for any application when sharing data across a multitude of data teams and third-party companies.
There are a couple of ways you can dynamically mask data within PostgreSQL such as by using views or triggers.
Masking Data in Postgres Using Views
Views get stored in query database objects. PostgreSQL views are virtual tables. Using a SELECT statement, views of logical tables represent data from one or more underlying tables. Notably, only materialized views store data tangibly.
A view may be particularly helpful in the following situations:
- Querying a view, which gets built on a sophisticated query, only requires a straightforward SELECT statement, which reduces the complexity of a query.
- Like a table, you may provide users access to a view that includes detailed data they are permitted to see.
- Despite changes in the columns of the underlying table, a view offers a continuous layer.
Creating PostgreSQL Views
The CREATE VIEW statement is used to create a view. The most fundamental form of the syntax for the CREATE VIEW statement is as follows:
CREATE VIEW view_name AS query;
The name of the view gets specified after the CREATE VIEW statement. Then, a query is added after the AS keyword. A query may consist of a basic SELECT statement or a more complex one, including joins.
For example, suppose there are four tables in a sample Postgres database:
- customer: Stores all customer data.
- film: Stores all film data.
- rating: Stores the ratings of films.
- genre: Stores the genre of films.
- director: Stores the director of films.
- address: Stores the address of customers.
- city: Stores the city of customers.
- country: Stores the country of customers.
If you want to get all of a customer’s information, you usually make a join statement like this:
SELECT cu.film_id AS id,
cu.film_name || ‘ ‘ || cu.film_year AS name,
a.rating,
genre.genre,
director.director,
CASE
WHEN cu.activebool THEN ‘active’
ELSE”
END AS notes,
cu.film_id AS fid
FROM film cu
INNER JOIN rating a USING (rating_id)
INNER JOIN genre USING (genre_id)
INNER JOIN director USING (director_id);
This query is pretty complicated. However, you can do the following to create a simpler view:
CREATE VIEW film_master AS
SELECT cu.film_id AS id,
cu.film_name || ‘ ‘ || cu.film_year AS name,
a.rating,
genre.genre,
director.director,
CASE
WHEN cu.activebool THEN ‘active’
ELSE”
END AS notes,
FROM film cu
INNER JOIN rating a USING (rating_id)
INNER JOIN genre USING (genre_id)
INNER JOIN director USING (director_id);
Furthermore, if you need to access all of the film data, you can execute the straightforward SELECT statement that follows to query it from the view:
SELECT
*
FROM
Film_master;
Using Views for Dynamic Data Masking
Using the views, you can return data dynamically masked, according to the user’s identity. For example, you can return a column value if the current_user is in a specific list (which can also be checked by a subquery to an entitlements table). Only if that user is in that list, they will get the value, and if they’re not, they will get a masked value.
Masking Data in Postgres by using Triggers
Specialized stored procedures called triggers are launched automatically in response to certain circumstances, for example Data Manipulation Language (DML).
In contrast to SQL Server triggers, PostgreSQL triggers must invoke a function. As a component of the trigger body, they do not support anonymous chunks of PL/pgSQL code. The user-supplied function has a trigger return type and is specified without an argument.
Creating PostgreSQL DML Triggers
Triggers in PostgreSQL can be activated either before or after a DML operation.
Notably, PostgreSQL triggers created on views can be executed instead of a DML command. In a DML statement, PostgreSQL triggers can run for each row that is affected by the DML statement or for each statement that runs only once.
Let’s say you want to make a trigger function that stores the run logic. We’d start with a create or replace function with a returns trigger. Then, we add an if function that triggers when an update occurs. From here, we update EMP by setting projectno=null where emp.projectno = old.projectno.
Now, we can develop the trigger. Developing the trigger uses the create trigger function that specifies to trigger after an update on projects. We can specify a trigger for each row.
Finally, we can delete a row from the projects table to test the trigger and confirm it works.
Dynamic Data Masking with Satori
Satori can help you secure your sensitive data by providing dynamic masking capabilities without requiring addition engineering resources. Satori’s dynamic masking does not require configuring any objects on the database, and can be automatically done based on the users’ identities and the types of data being accessed.
To learn more:
