Row Level Security (RLS) is one of the key features in PostgreSQL. It can be used to dramatically improve security and help to protect data in all cases. However, there are a couple of corner cases which most people are not aware of. So if you are running PostgreSQL and you happen to use RLS in a high-security environment, this might be the most important piece of text about database security you have ever read.

Row-Level-Security in PostgreSQL: Corner cases

To prepare for my examples let me create some data first. The following code is executed as superuser:

CREATE USER bob NOSUPERUSER;
CREATE USER alice NOSUPERUSER;

CREATE TABLE t_service (service_type text, service text);
INSERT INTO t_service VALUES 
	('open_source', 'PostgreSQL consulting'),
	('open_source', 'PostgreSQL training'),
	('open_source', 'PostgreSQL 24x7 support'),
	('closed_source', 'Oracle tuning'),
	('closed_source', 'Oracle license management'),
	('closed_source', 'IBM DB2 training');

GRANT ALL ON SCHEMA PUBLIC TO bob, alice;
GRANT ALL ON TABLE t_service TO bob, alice;

For the sake of simplicity there are only three users: postgres, bob, and alice. The t_service table contains six different services. Some are related to PostgreSQL and some to Oracle. The goal is to ensure that bob is only allowed to see Open Source stuff while alice is mostly an Oracle girl.

While hacking up the example, we want to see who we are and which chunks of code are executed as which user at all times. Therefore I have written a debug function which just throws out a message and returns true:

CREATE FUNCTION debug_me(text) RETURNS boolean AS
$$
BEGIN
	RAISE NOTICE 'called as session_user=%, current_user=% for "%" ', 
		session_user, current_user, $1;
	RETURN true;
END;
$$ LANGUAGE 'plpgsql';

GRANT ALL ON FUNCTION debug_me TO bob, alice;

Now that the infrastructure is in place, RLS can be enabled for this table:

ALTER TABLE t_service ENABLE ROW LEVEL SECURITY;

The superuser is not able to see all the data. Normal users are not allowed to see anything. To them, the table will appear to be empty.

Using CREATE POLICY in PostgreSQL

Of course, people want to see data. In order to expose data to people, policies have to be created. In my example there will be two policies:

CREATE POLICY bob_pol ON t_service
	FOR SELECT
	TO bob
	USING (debug_me(service) AND service_type = 'open_source');

CREATE POLICY alice_pol ON t_service
	FOR SELECT
	TO alice
	USING (debug_me(service) AND service_type = 'closed_source');

What we see here is that bob is really the Open Source guy while alice is more on the Oracle side. I added the debug_me function to the policy so that you can see which users are active.

Let us set the current role to bob and run a simple SELECT statement:

test=# SET ROLE bob;
SET
test=> SELECT * FROM t_service;
psql: NOTICE:  called as session_user=hs, current_user=bob for "PostgreSQL consulting" 
psql: NOTICE:  called as session_user=hs, current_user=bob for "PostgreSQL training" 
psql: NOTICE:  called as session_user=hs, current_user=bob for "PostgreSQL 24x7 support" 
 service_type |         service         
--------------+-------------------------
 open_source  | PostgreSQL consulting
 open_source  | PostgreSQL training
 open_source  | PostgreSQL 24x7 support
(3 rows)

The policy does exactly what you would expect for bob. The same thing is true for alice:

test=> SET ROLE alice;
SET
test=> SELECT * FROM t_service;
psql: NOTICE:  called as session_user=hs, current_user=alice for "Oracle tuning" 
psql: NOTICE:  called as session_user=hs, current_user=alice for "Oracle license management" 
psql: NOTICE:  called as session_user=hs, current_user=alice for "IBM DB2 training" 
 service_type  |          service          
---------------+---------------------------
 closed_source | Oracle tuning
 closed_source | Oracle license management
 closed_source | IBM DB2 training
(3 rows)

PostgreSQL Row-Level-Security and views

As a PostgreSQL consultant and PostgreSQL support company there is one specific question which keeps coming to us again and again: What happens if RLS (Row Level Security) is used in combination with views? This kind of question is not as easy to answer as some people might think. Expect some corner cases which require a little bit of thinking to get stuff right.

To show how things work, I will switch back to user “postgres” and create two identical views:

test=> SET ROLE postgres;
SET 
test=# CREATE VIEW v1 AS SELECT *, session_user, current_user 
	FROM t_service;
CREATE VIEW
test=# CREATE VIEW v2 AS SELECT *, session_user, current_user 
	FROM t_service;
CREATE VIEW 
test=# GRANT SELECT ON v1 TO bob, alice;
GRANT
test=# ALTER VIEW v2 OWNER TO alice;
ALTER VIEW
test=# GRANT SELECT ON v2 TO bob;
GRANT

SELECT permissions will be granted to both views, but there is one more difference: alice will be the owner of v2. v1 is owned by the postgres user. This tiny difference makes a major difference later on, as you will see. To sum it up: v1 will be owned by postgres, v2 is owned by our commercial database lady alice, and everybody is allowed to read those views.

Let us see what happens:

test=# SET ROLE bob;
SET
test=> SELECT * FROM v1;
 service_type  |          service          | session_user | current_user 
---------------+---------------------------+--------------+--------------
 open_source   | PostgreSQL consulting     | hs           | bob
 open_source   | PostgreSQL training       | hs           | bob
 open_source   | PostgreSQL 24x7 support   | hs           | bob
 closed_source | Oracle tuning             | hs           | bob
 closed_source | Oracle license management | hs           | bob
 closed_source | IBM DB2 training          | hs           | bob
(6 rows)

Ooops! What is going on here? “bob” is allowed to see all the data. There is a reason for that: The view is owned by “postgres”. That means that the row level policy on t_service will not be taken into account. The RLS policies (Row Level Security) have been defined for bob and alice. However, in this case they are not taken into consideration, because the view is owned by the superuser, and the superuser has given us SELECT permissions on this view so we can see all that data. That is important: Imagine some sort of aggregation (e.g. SELECT sum(turnover) FROM sales). A user might see the aggregate but not the raw data. In that case, skipping the policy is perfectly fine.

The situation is quite different in the case of v2:

test=> SELECT * FROM v2;
psql: NOTICE:  called as session_user=hs, current_user=bob for "Oracle tuning" 
psql: NOTICE:  called as session_user=hs, current_user=bob for "Oracle license management" 
psql: NOTICE:  called as session_user=hs, current_user=bob for "IBM DB2 training" 
 service_type  |          service          | session_user | current_user 
---------------+---------------------------+--------------+--------------
 closed_source | Oracle tuning             | hs           | bob
 closed_source | Oracle license management | hs           | bob
 closed_source | IBM DB2 training          | hs           | bob
(3 rows)

The “current_user” is still “bob” BUT what we see is only closed source data, which basically belongs to alice. Why does that happen? The reason is: v2 belongs to alice and therefore PostgreSQL will check alice’s RLS policy. Remember, she is supposed to see closed source data and as the “owner” of the data she is in charge. The result is: bob will see closed source data, but no open source data (which happens to be his domain). Keep these corner cases in mind – not being aware of this behavior can create nasty security problems. Always ask yourself which policy PostgreSQL will actually use behind the scenes. Having a small test case at hand can be really useful in this context.

Solving the more common security challenges

What you have seen are some corner cases many people are not aware of. Our PostgreSQL consultants have seen some horrible mistakes in this area already, and we would like to ensure that other people out there don’t make the same mistakes.

Let us drop those policies:

test=> SET ROLE postgres;
SET
test=# DROP POLICY bob_pol ON t_service;
DROP POLICY
test=# DROP POLICY alice_pol ON t_service;
DROP POLICY

Usually policies are not assigned to individual people, but to a group of people or sometimes even to “public” (basically everybody who does not happen to be a superuser in this context). The following code snippet shows a simple example:

test=# CREATE POLICY general_pol ON t_service
	FOR SELECT
	TO  public
	USING (CASE WHEN CURRENT_USER = 'bob' 
			THEN service_type = 'open_source'
		  ELSE service_type = 'closed_source' END);
CREATE POLICY

If the CURRENT_USER is bob, the system is supposed to show Open Source data. Otherwise it is all about closed source.

Let us take a look what happens:

test=# SET ROLE bob;
SET
test=> SELECT * FROM v2;
 service_type |         service         | session_user | current_user 
--------------+-------------------------+--------------+--------------
 open_source  | PostgreSQL consulting   | hs           | bob
 open_source  | PostgreSQL training     | hs           | bob
 open_source  | PostgreSQL 24x7 support | hs           | bob
(3 rows)

The most important observation is that the policy applies to everybody who is not marked as superuser and it applies to everybody who is not marked with BYPASSRLS. As expected, alice will only see her subset of data:

test=> SET ROLE alice;
SET
test=> SELECT * FROM v2;
 service_type  |          service          | session_user | current_user 
---------------+---------------------------+--------------+--------------
 closed_source | Oracle tuning             | hs           | alice
 closed_source | Oracle license management | hs           | alice
 closed_source | IBM DB2 training          | hs           | alice
(3 rows)

The most important observation here is that defining policies has to be done with great care. ALWAYS make sure that your setup is well tested and that no leaks can happen. Security is one of the most important topics in any modern IT system and nobody wants to take chances in this area.

Cybertec can help to secure databases

As a PostgreSQL consulting company we can help to make sure that your databases are indeed secure. Leaks must not happen and we can help to achieve that.
If you want to learn more about PostgreSQL security in general, check out our PostgreSQL products including Data Masking for PostgreSQL which helps you to obfuscate data, and check out PL/pgSQL_sec which has been designed explicitly to protect your code.


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