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
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:
|
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:
|
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') ; |
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:
|
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.
To enable row level security (RLS) you have to run ALTER TABLE … 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:
|
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.
Let us create a role now:
|
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:
|
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:
|
1 2 3 |
SET ROLE postgres; We can try the same thing with a second user and we will again achieve the desired results: |
|
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:
|
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) |
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.
You need to load content from reCAPTCHA to submit the form. Please note that doing so will share data with third-party providers.
More InformationYou are currently viewing a placeholder content from Facebook. To access the actual content, click the button below. Please note that doing so will share data with third-party providers.
More InformationYou are currently viewing a placeholder content from X. To access the actual content, click the button below. Please note that doing so will share data with third-party providers.
More Information
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 ...
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.
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.