Guide: PostgreSQL Secuirty

Postgres Data Masking

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:

Last updated on

September 28, 2022

The information provided in this article and elsewhere on this website is meant purely for educational discussion and contains only general information about legal, commercial and other matters. It is not legal advice and should not be treated as such. Information on this website may not constitute the most up-to-date legal or other information. The information in this article is provided ‚Äúas is‚ÄĚ without any representations or warranties, express or implied. We make no representations or warranties in relation to the information in this article and all liability with respect to actions taken or not taken based on the contents of this article are hereby expressly disclaimed. You must not rely on the information in this article as an alternative to legal advice from your attorney or other professional legal services provider. If you have any specific questions about any legal matter you should consult your attorney or other professional legal services provider. This article may contain links to other third-party websites. Such links are only for the convenience of the reader, user or browser; we do not recommend or endorse the contents of any third-party sites.