Using “Row Level Security” to make large companies more secure

Hans-Jürgen Schönig

09.2019 / Category: How To / Tags: administration | security

Large companies and professional business have to make sure that data is kept secure. It is necessary to defend against internal, as well as external threats. PostgreSQL provides all the necessities a company needs to protect data and to ensure that people can only access what they are supposed to see. One way to protect data is “Row Level Security”, which has been around for a few years now. It can be used to reduce the scope of a user by removing rows from the result set automatically. Usually people apply simple policies to do that. But PostgreSQL Row Level Security (RLS) can do a lot more. You can actually control the way RLS behaves using configuration tables.

Table of Contents

Configuring access restrictions dynamically

Imagine you are working for a large cooperation. Your organization might change, people might move from one department to the other or your new people might join up as we speak. What you want is that your security policy always reflects the way your company really is. Let us take a look at a simple example:

CREATE TABLE t_company
(
     id            serial,
     department    text NOT NULL,
     manager       text NOT NULL
);

CREATE TABLE t_manager
(
     id      serial,
     person  text,
     manager text,
     UNIQUE (person, manager)
);

1

2

3

4

5

6

7

8

9

10

11

12

13

14

CREATE TABLE t_company

(

id serial,

department text NOT NULL,

manager text NOT NULL

);

CREATE TABLE t_manager

(

id serial,

person text,

manager text,

UNIQUE (person, manager)

);

I have created two tables. One will know, who is managing which department. The second table knows, who will report to him. The goal is to come up with a security policy, which ensures that somebody can only see own data or data from departments on lower levels. In many cases row level policies are hardcoded – in our case we want to be flexible and configure visibility given the data in the tables.

Let us populate the tables:

INSERT INTO t_manager (person, manager)
VALUES ('hans', NULL),
       ('paula', 'hans'),
       ('berta', 'hans'),
       ('manuel', 'paula'),
       ('mike', 'paula'),
       ('joe', 'berta'),
       ('jack', 'berta'),
       ('jane', 'berta')
;

1

2

3

4

5

6

7

8

9

10

INSERT INTO t_manager (person, manager)

VALUES ('hans', NULL),

('paula', 'hans'),

('berta', 'hans'),

('manuel', 'paula'),

('mike', 'paula'),

('joe', 'berta'),

('jack', 'berta'),

('jane', 'berta')

;

hierarchy

As you can see “hans” has no manager. “paula” will report directly to “hans”. “manuel” will report to “paula” and so on.

In the next step we can populate the company table:

INSERT INTO t_company (department, manager)
VALUES ('dep_1_1', 'joe'),
       ('dep_1_2', 'jane'),
       ('dep_1_3', 'jack'),
       ('dep_2_1', 'mike'),
       ('dep_2_2', 'manuel'),
       ('dep_1', 'berta'),
       ('dep_2', 'paula'),
       ('dep', 'hans')
;

1

2

3

4

5

6

7

8

9

10

INSERT INTO t_company (department, manager)

VALUES ('dep_1_1', 'joe'),

('dep_1_2', 'jane'),

('dep_1_3', 'jack'),

('dep_2_1', 'mike'),

('dep_2_2', 'manuel'),

('dep_1', 'berta'),

('dep_2', 'paula'),

('dep', 'hans')

;

For the sake of simplicity, I have named those departments in a way that they reflect the hierarchy in the company. The idea is to make the results easier to read and easier to understand. Of course, any other name will work just fine as well.

Defining row level policies in PostgreSQL

To enable row level security (RLS) you have to run ALTER TABLE … ENABLE ROW LEVEL SECURITY:

ALTER TABLE t_company ENABLE ROW LEVEL SECURITY;

1	ALTER TABLE t_company ENABLE ROW LEVEL SECURITY;

What is going to happen is that all non-superusers, or users who are marked as BYPASSRLS, won’t see any data anymore. By default, PostgreSQL is restrictive and you have to define a policy to configure the desired scope of users. The following policy uses a subselect to travers our organization:

CREATE POLICY my_fancy_policy
  ON t_company
  USING (manager IN ( WITH RECURSIVE t AS 
                        (
                           SELECT current_user AS person, NULL::text AS manager
                           FROM t_manager
                           WHERE manager = CURRENT_USER
                           UNION ALL
                           SELECT m.person, m.manager
                           FROM t_manager m
                           INNER JOIN t ON t.person = m.manager
                        )
                        SELECT person FROM t
                    )
        )
;

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

CREATE POLICY my_fancy_policy

ON t_company

USING (manager IN ( WITH RECURSIVE t AS

(

SELECT current_user AS person, NULL::text AS manager

FROM t_manager

WHERE manager = CURRENT_USER

UNION ALL

SELECT m.person, m.manager

FROM t_manager m

INNER JOIN t ON t.person = m.manager

)

SELECT person FROM t

)

)

;

What you can see here is that a policy can be pretty sophisticated. It is not just a simple expression but can even be a more complex subselect, which uses some configuration tables to decide on what to do.

PostgreSQL row level security in action

Let us create a role now:

CREATE ROLE paula LOGIN;
GRANT ALL ON t_company TO paula;
GRANT ALL ON t_manager TO paula;

1

2

3

CREATE ROLE paula LOGIN;

GRANT ALL ON t_company TO paula;

GRANT ALL ON t_manager TO paula;

paula is allowed to log in and read all data in t_company and t_manager. Being able to read the table in the first place is a hard requirement to make PostgreSQL even consider your row level policy.

Once this is done, we can set the role to paula and see what happens:

test=> SET ROLE paula;
SET
test=>; SELECT * FROM t_company;

id  | department | manager
----+------------+---------
4   | dep_2_1    | mike
5   | dep_2_2    | manuel
7   | dep_2      | paula
(3 rows)

1

2

3

4

5

6

7

8

9

10

test=> SET ROLE paula;

SET

test=>; SELECT * FROM t_company;

id | department | manager

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

4 | dep_2_1 | mike

5 | dep_2_2 | manuel

7 | dep_2 | paula

(3 rows)

As you can see paula is only able to see herself and the people in her department, which is exactly what we wanted to achieve.

Let us switch back to superuser now:

SET ROLE postgres;

We can try the same thing with a second user and we will again achieve the desired results:

1

2

3

SET ROLE postgres;

We can try the same thing with a second user and we will again achieve the desired results:

CREATE ROLE hans LOGIN;

GRANT ALL ON t_company TO hans;
GRANT ALL ON t_manager TO hans;

1

2

3

4

CREATE ROLE hans LOGIN;

GRANT ALL ON t_company TO hans;

GRANT ALL ON t_manager TO hans;

The output is as expected:

test=# SET role hans;
SET
test=> SELECT * FROM t_company;
id  | department | manager
----+------------+---------
1   | dep_1_1    | joe
2   | dep_1_2    | jane
3   | dep_1_3    | jack
4   | dep_2_1    | mike
5   | dep_2_2    | manuel
6   | dep_1      | berta
7   | dep_2      | paula
8   | dep        | hans
(8 rows)

1

2

3

4

5

6

7

8

9

10

11

12

13

14

test=# SET role hans;

SET

test=> SELECT * FROM t_company;

id | department | manager

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

1 | dep_1_1 | joe

2 | dep_1_2 | jane

3 | dep_1_3 | jack

4 | dep_2_1 | mike

5 | dep_2_2 | manuel

6 | dep_1 | berta

7 | dep_2 | paula

8 | dep | hans

(8 rows)

Row level security and performance

Keep in mind: A policy is basically a mandatory WHERE clause which is added to every query to ensure that the scope of a user is limited to the desired subset of data. The more expensive the policy is, the more impact it will have on performance. It is highly recommended to think twice and to make sure that your policies are reasonably efficient to maintain good database performance.

The performance impact of row level security in PostgreSQL (or any other SQL database) cannot easily be quantified because it depends on too many factors. However, keep in mind – there is no such thing as a free lunch.

If you want to learn more about Row Level Security check out my post about PostgreSQL security.

In order to receive regular updates on important changes in PostgreSQL, subscribe to our newsletter, or follow us on Facebook or LinkedIn.

3 responses to “Using “Row Level Security” to make large companies more secure”

Stefan Wolf says:

September 24, 2019 at 8:16 am

Good article for an excellent feature.

RLS is great and as you mentioned, "simple and transparent" implemended.
You'll see the additional WHERE/filter using EXPLAIN ANALYZE ...

Reply
Joost helberg says:

September 25, 2019 at 6:04 am

I've used rls for in company and public applications. works like a dream. avoids a lot of iffy security solutions in frameworks also.
Is anyone interested in creating a good connection pooling mechanism for rls?
read more on RLS and GDPR on https://gdprunplugged.eu/
But really, connection-pooling with RLS and end-user db-authentication needs a solution somehow. I'd be happy to design/write for such a thing.

Reply
Ivan Starkov says:

September 13, 2020 at 9:04 pm

One issue we see is huge performance drop using RLS. Looks like postgres builds very poor query plans when rls is used.
For example query with extracted RLS rules executes in 1ms, same query but using RLS cant take 30 seconds. Based on query plans it seems like postgresql in some cases doesnt use indexes for rls queries.

Reply

Leave a Reply Cancel reply

Stay tuned with our

NEWSLETTER

CYBERTEC PostgreSQL International GmbH
Römerstraße 19
2752 Wöllersdorf
Austria

+43 (0) 2622 93022-0
office@cybertec.at

Customer Support

Support Platform

Services

Support CYBERTEC Partner PostgreSQL Books

Company

STAY TUNED WITH OUR NEWSLETTER

Get the newest PostgreSQL Info & Tools

Data Protection Policy Terms and conditions Terms of Service Imprint

©

2024

CYBERTEC PostgreSQL International GmbH