Row-level Security Policy in PostgreSQL

In this article, we take a look at different ways to set row-level privileges in PostgreSQL. Before we proceed, let’s look at privileges in the context of database systems.

What are privileges?

Privileges a way of granting rights to a specific group of people to allow them to perform a specific task such as access data in a table or even delete a table.

For instance the following commands allows users jones, steve, and davies to access all data in a table.

GRANT SELECT ON malware_info TO jones;

The output shows that user jones has access to other users’ data. In this case there is no isolation of users’ data and data privacy is violated.

So how do we prevent someone from accessing a specific set of data belonging to another user? We can not rely on table privileges to isolate data in a table.

Difference between table privileges and row level security policy

It is possible to use privileges to limit what actions roles/users can undertake against tables in a database but privileges in PostgreSQL do not apply to specific rows.

We can rely on row-level policies to restrict access to data to certain users.

So if there is a table that contains a set of data that should not be accessed by one or more users, we can create a row security policy for that table.

Note: Grant of privileges to roles/users in PostgreSQL is beyond the scope of this article. If you want detailed information on how to create roles and grant privileges in PostgreSQL, check the documentation.

How to set row-level policies in PostgreSQL

In PostgreSQL, we can use the CREATE POLICY command to create row-level policies if there is a row policy available, and the ALTER POLICY command to alter existing row-level policies to suit our needs.

Let’s assume we have built a web-based service that polls the signature of various malware from different sources.

Users are allowed to submit malware information from different sources. Each user can access his/her submitted data.

So in this case, we expect users to view and modify their data.

Let’s start by creating a database and a table for the malware service using the CREATE DATABASE and CREATE TABLE commands.

CREATE DATABASE malware_source WITH OWNER = 'mike'; 

We can connect to the malware_source database by using the command below.

c malware_source mike

Then the following command creates a table with the following data types:

CREATE TABLE malware_info (
    username VARCHAR(100) PRIMARY KEY NOT NULL,
    malware_name VARCHAR(100) NOT NULL UNIQUE,
    polled_source VARCHAR(100) NOT NULL,
    sig_type TEXT NOT NULL,
    active_market BOOLEAN NOT NULL
);

The INSERT command can be used to insert data into the malware_info table as shown below:

INSERT INTO malware_info VALUES ('davies', 'Fancy Lazarus', 'cywarenews.com', 'Ransomware', 'true');

You can execute the query below to check if you have correctly inserted the data into the malware_info table.

SELECT * FROM malware_info;

username  | malware_name  | polled_source  | sig_type   | active_market
----------+---------------+----------------+------------+---------------
davies    | Fancy Lazarus | cywarenews.com | Ransomware | t
jones     | Facefish      | cywarenews.com | Rootkit    | t
steve     | Avaddon       | cywarenews.com | Ransomware | t
(3 rows)

Now the table contains information submitted by three different users.

Now it’s time to use the CREATE POLICY command to create and enable a new row security policy for the malware _info table.

Even before we create a row-level policy for the malware_info table, we need to enable it to ensure that the policy we create for the table becomes effective.

The command below enables row-level policy for the malware_info table:

ALTER TABLE malware_info ENABLE ROW LEVEL SECURITY;

We can execute the command below to check if the malware_info table is enabled for row-level security:

SELECT relname, relrowsecurity FROM pg_class WHERE oid = 'malware_info'::regclass;
   relname    | relrowsecurity 
--------------+----------------
 malware_info | t
(1 row)

Next we create a row-level policy to allow each user to view his/her data.

CREATE POLICY malware_info_rls
    ON malware_info
    FOR ALL TO PUBLIC
    USING username = current_user
;

This policy ensures that connected user or let’s say the current user has the right to access, modify or even delete data not belonging to another user.

Instead of applying the row-level policy to all users, you can also apply it to specific roles with specific commands such as INSERT or UPDATE.

So the main purpose of row-level security policy is to isolate access to a user’s data from other users.

Testing row level policy

Let’s check whether the row level policy we have implemented for the malware_info table works as expected.

Use the meta-command c to connect to the database as the admin user:

c malware_source mike;

Now let try to access the data stored in the malware_source table as the superuser:

SELECT * FROM malware_info;

As you can see the admin user can retrieve data from all users.

malware_source=> select * from malware_info ;
username  | malware_name  | polled_source  | sig_type   | active_market
----------+---------------+----------------+------------+---------------
davies    | Fancy Lazarus | cywarenews.com | Ransomware | t
jones     | Facefish      | cywarenews.com | Rootkit    | t
steve     | Avaddon       | cywarenews.com | Ransomware | t
(3 rows)

If another user tries to execute the same command, the server displays data submitted by the user as shown below:

username  | malware_name  | polled_source  | sig_type   | active_market
----------+---------------+----------------+------------+---------------
davies    | Fancy Lazarus | cywarenews.com | Ransomware | t
(1 row)

So it means our row level policy is effective. You can check the documentation for additional details such as altering row policies and so on.

Note: It does not happen often but if you want include the table owner or the superuser in the row level policy, use the following command:

 ALTER TABLE malware_info FORCE ROW LEVEL SECURITY;

How often do we need row level policies ?

It is not necessary to always apply or enforce row-level security policy to tables in the PostgreSQL cluster. If there is no need to segregate data or prevent a user from viewing another user’s data, then a row-level policy is not an option in this case.

But in a larger organization where data privacy is a necessity, it is common to experience tables with enforced row level policies to prevent members in a department from accessing another department’s data.

So in a nutshell, a row-level security policy is just another way of isolating data and preventing some organisation members to access data they should not be able to access.

To get an expert review of your PostgreSQL security, consider our PostgreSQL Health Checks.

Michael Aboagye

Did you like this article?