Implementing Row-level Security in PostgreSQL

[box type=”note” align=”” class=”” width=””]The following excerpt is taken from the book Mastering PostgreSQL 9.6, authored by Hans-Jürgen Schönig. The book gives a comprehensive primer on different features and capabilities of PostgreSQL 9.6, and how you can leverage them efficiently to administer and manage your PostgreSQL database.[/box]

In this article, we discuss the concept of row-level security and how effectively it can be implemented in PostgreSQL using a interesting example. Having the row-level security feature enables allows you to store data for multiple users in a single database and table. At the same time it sets restrictions on the row–level access, based on a particular user’s role or identity.

What is Row-level Security?

In usual cases, a table is always shown as a whole. When the table contains 1 million rows, it is possible to retrieve 1 million rows from it. If somebody had the rights to read a table, it was all about the entire table. In many cases, this is not enough. Often it is desirable that a user is not allowed to see all the rows.

Consider the following real-world example: an accountant is doing accounting work for many people. The table containing tax rates should really be visible to everybody as everybody has to pay the same rates. However, when it comes to the actual transactions, you might want to ensure that everybody is only allowed to see his or her own transactions. Person A should not be allowed to see person B’s data. In addition to that, it might also make sense that the boss of a division is allowed to see all the data in his part of the company.

Row-level security has been designed to do exactly this and enables you to build multi-tenant systems in a fast and simple way. The way to configure those permissions is to come up with policies. The CREATE POLICY command is here to provide you with a means to write those rules:

test=# h CREATE POLICY

Command: CREATE POLICY

Description: define a new row level security policy for a table

Syntax:

CREATE POLICY name ON table_name

[ FOR { ALL | SELECT | INSERT | UPDATE | DELETE } ]

[ TO { role_name | PUBLIC | CURRENT_USER | SESSION_USER } [, ...] ]

[ USING ( using_expression ) ]

[ WITH CHECK ( check_expression ) ]

To show you how a policy can be written, I will first log in as superuser and create a table containing a couple of entries:

test=# CREATE TABLE t_person (gender text, name text);

CREATE TABLE

test=# INSERT INTO t_person VALUES

('male', 'joe'), ('male', 'paul'), ('female', 'sarah'), (NULL, 'R2-

D2');

INSERT 0 4

Then access is granted to the joe role:

test=# GRANT ALL ON t_person TO joe;

GRANT

So far, everything is pretty normal and the joe role will be able to actually read the entire table as there is no RLS in place. But what happens if row-level security is enabled for the table?

test=# ALTER TABLE t_person ENABLE ROW LEVEL SECURITY;

ALTER TABLE

There is a deny all default policy in place, so the joe role will actually get an empty table:

test=> SELECT * FROM t_person;

gender | name

--------+------

(0 rows)

Actually, the default policy makes a lot of sense as users are forced to explicitly set permissions. Now that the table is under row-level security control, policies can be written (as superuser):

test=# CREATE POLICY joe_pol_1

ON t_person

FOR SELECT TO joe

USING (gender = 'male');

CREATE POLICY

Logging in as the joe role and selecting all the data, will return just two rows:

test=> SELECT * FROM t_person;

gender | name

--------+------

male | joe

male | paul

(2 rows)

Let us inspect the policy I have just created in a more detailed way. The first thing you see is that a policy actually has a name. It is also connected to a table and allows for certain operations (in this case, the SELECT clause). Then comes the USING clause. It basically defines what the joe
role will be allowed to see. The USING clause is therefore a mandatory filter attached to every query to only select the rows our user is supposed to see.

Now suppose that, for some reason, it has been decided that the joe role is also allowed to see robots. There are two choices to achieve our goal. The first option is to simply use the ALTER POLICY clause to change the existing policy:

test=> h ALTER POLICY

Command: ALTER POLICY

Description: change the definition of a row level security policy

Syntax:

ALTER POLICY name ON table_name RENAME TO new_name

ALTER POLICY name ON table_name

[ TO { role_name | PUBLIC | CURRENT_USER | SESSION_USER } [, ...] ]

[ USING ( using_expression ) ]

[ WITH CHECK ( check_expression ) ]

The second option is to create a second policy as shown in the next example:

test=# CREATE POLICY joe_pol_2

ON t_person

FOR SELECT TO joe

USING (gender IS NULL);

CREATE POLICY

The beauty is that those policies are simply connected using an OR condition.Therefore, PostgreSQL will now return three rows instead of two:

test=> SELECT * FROM t_person;

gender | name

--------+-------

male | joe

male | paul

| R2-D2

(3 rows)

The R2-D2 role is now also included in the result as it matches the second policy. To show you how PostgreSQL runs the query, I have decided to include an execution plan of the query:

test=> explain SELECT * FROM t_person;

QUERY PLAN

----------------------------------------------------------

Seq Scan on t_person (cost=0.00..21.00 rows=9 width=64)

Filter: ((gender IS NULL) OR (gender = 'male'::text))

(2 rows)

As you can see, both the USING clauses have been added as mandatory filters to the query.

You might have noticed in the syntax definition that there are two types of clauses:

• USING: This clause filters rows that already exist. This is relevant to SELECT and UPDATE clauses, and so on.
• CHECK: This clause filters new rows that are about to be created; so they are relevant to INSERT
   and UPDATE clauses, and so on.

Here is what happens if we try to insert a row:

test=> INSERT INTO t_person VALUES ('male', 'kaarel');

ERROR: new row violates row-level security policy for table "t_person"

As there is no policy for the INSERT clause, the statement will naturally error out. Here is the policy to allow insertions:

test=# CREATE POLICY joe_pol_3

ON t_person

FOR INSERT TO joe

WITH CHECK (gender IN ('male', 'female'));

CREATE POLICY

The joe role is allowed to add males and females to the table, which is shown in the next listing:

test=> INSERT INTO t_person VALUES ('female', 'maria');

INSERT 0 1

However, there is also a catch; consider the following example:

test=> INSERT INTO t_person VALUES ('female', 'maria') RETURNING *;

ERROR: new row violates row-level security policy for table "t_person"

Remember, there is only a policy to select males. The trouble here is that the statement will return a woman, which is not allowed because joe role is under a male only policy. Only for men, will the
RETURNING * clause actually work:

test=> INSERT INTO t_person VALUES ('male', 'max') RETURNING *;

gender | name

--------+------

male | max

(1 row)

INSERT 0 1

If you don’t want this behavior, you have to write a policy that actually contains a proper USING
clause.

If you liked our post, make sure to check out our book Mastering PostgreSQL 9.6 – a comprehensive PostgreSQL guide covering all database administration and maintenance aspects.