Application users vs. Row Level Security
A few days ago I’ve blogged about the common issues with roles and privileges we discover during security reviews.
Of course, PostgreSQL offers many advanced security-related features, one of them being Row Level Security (RLS), available since PostgreSQL 9.5.
As 9.5 was released in January 2016 (so just a few months ago), RLS is fairly new feature and we’re not really dealing with many production deployments yet. Instead RLS is a common subject of “how to implement” discussions, and one of the most common questions is how to make it work with application-level users. So let’s see what possible solutions there are.
Introduction to RLS
Let’s see a very simple example first, explaining what RLS is about. Let’s say we have a chat
table storing messages sent between users – the users can insert rows into it to send messages to other users, and query it to see messages sent to them by other users. So the table might look like this:
CREATE TABLE chat ( message_uuid UUID PRIMARY KEY DEFAULT uuid_generate_v4(), message_time TIMESTAMP NOT NULL DEFAULT now(), message_from NAME NOT NULL DEFAULT current_user, message_to NAME NOT NULL, message_subject VARCHAR(64) NOT NULL, message_body TEXT );
The classic role-based security only allows us to restrict access to either the whole table or vertical slices of it (columns). So we can’t use it to prevent users from reading messages intended for other users, or sending messages with a fake message_from
field.
And that’s exactly what RLS is for – it allows you to create rules (policies) restricting access to subsets of rows. So for example you can do this:
CREATE POLICY chat_policy ON chat USING ((message_to = current_user) OR (message_from = current_user)) WITH CHECK (message_from = current_user)
This policy ensures a user can only see messages sent by him or intended for him – that’s what the condition in USING
clause does. The second part of the policy (WITH CHECK
) assures a user can only insert messages with his username in message_from
column, preventing messages with forged sender.
You can also imagine RLS as an automatic way to append additional WHERE conditions. You could do that manually at the application level (and before RLS people often did that), but RLS does that in a reliable and safe way (a lot of effort was put into preventing various information leaks, for example).
Note: Before RLS, a popular way to achieve something similar was to make the table inaccessible directly (revoke all the privileges), and provide a set of security definer functions to access it. That achieved mostly the same goal, but functions have various disadvantages – they tend to confuse the optimizer, and seriously limit the flexibility (if the user needs to do something and there’s no suitable function for it, he’s out of luck). And of course, you have to write those functions.
Application users
If you read the official documentation about RLS, you may notice one detail – all the examples use current_user
, i.e. the current database user. But that’s not how most database applications work these days. Web applications with many registered users don’t maintain 1:1 mapping to database users, but instead use a single database user to run queries and manage application users on their own – perhaps in a users
table.
Technically it’s not a problem to create many database users in PostgreSQL. The database should handle that without any problems, but applications don’t do that for a number of practical reasons. For example they need to track additional information for each user (e.g. department, position within the organization, contact details, …), so the application would need the users
table anyway.
Another reason may be connection pooling – using a single shared user account, although we know that’s solvable using inheritance and SET ROLE
(see the previous post).
But let’s assume you don’t want to create separate database users – you want to keep using a single shared database account, and use RLS with application users. How to do that?
Session variables
Essentially what we need is to pass additional context to the database session, so that we can later use it from the security policy (instead of the current_user
variable). And the easiest way to do that in PostgreSQL are session variables:
SET my.username = 'tomas'
If this resembles the usual configuration parameters (e.g. SET work_mem = '...'
), you’re absolutely right – it’s mostly the same thing. The command defines a new namespace (my
), and adds a username
variable into it. The new namespace is required, as the global one is reserved for the server configuration and we can’t add new variables to it. This allows us to change the security policy like this:
CREATE POLICY chat_policy ON chat USING (current_setting('my.username') IN (message_from, message_to)) WITH CHECK (message_from = current_setting('my.username'))
All we need to do is to make sure the connection pool / application sets the user name whenever it gets a new connection and assigns it to the user task.
Let me point out that this approach collapses once you allow the users to run arbitrary SQL on the connection, or if the user manages to discover a suitable SQL injection vulnerability. In that case there’s nothing that could stop them from setting arbitrary username. But don’t despair, there’s a bunch of solutions to that problem, and we’ll quickly go through them.
Signed session variables
The first solution is a simple improvement of the session variables – we can’t really prevent the users from setting arbitrary value, but what if we could verify that the value was not subverted? That’s fairly easy to do using a simple digital signature. Instead of just storing the username, the trusted part (connection pool, application) can do something like this:
signature = sha256(username + timestamp + SECRET)
and then store both the value and the signature into the session variable:
SET my.username = 'username:timestamp:signature'
Assuming the user does not know the SECRET string (e.g. 128B of random data), it shouldn’t be possible to modify the value without invalidating the signature.
Note: This is not a new idea – it’s essentially the same thing as signed HTTP cookies. Django has a quite nice documentation about that.
The easiest way to protect the SECRET value is by storing it in a table inaccessible by the user, and providing a security definer
function, requiring a password (so that the user can’t simply sign arbitrary values).
CREATE FUNCTION set_username(uname TEXT, pwd TEXT) RETURNS text AS $ DECLARE v_key TEXT; v_value TEXT; BEGIN SELECT sign_key INTO v_key FROM secrets; v_value := uname || ':' || extract(epoch from now())::int; v_value := v_value || ':' || crypt(v_value || ':' || v_key, gen_salt('bf')); PERFORM set_config('my.username', v_value, false); RETURN v_value; END; $ LANGUAGE plpgsql SECURITY DEFINER STABLE;
The function simply looks up the signing key (secret) in a table, computes the signature and then sets the value into the session variable. It also returns the value, mostly for convenience.
So the trusted part can do this right before handing the connection to the user (obviously ‘passphrase’ is not a very good password for production):
SELECT set_username('tomas', 'passphrase')
And then of course we need another function that simply verifies the signature and either errors out or returns the username if the signature matches.
CREATE FUNCTION get_username() RETURNS text AS $ DECLARE v_key TEXT; v_parts TEXT[]; v_uname TEXT; v_value TEXT; v_timestamp INT; v_signature TEXT; BEGIN -- no password verification this time SELECT sign_key INTO v_key FROM secrets; v_parts := regexp_split_to_array(current_setting('my.username', true), ':'); v_uname := v_parts[1]; v_timestamp := v_parts[2]; v_signature := v_parts[3]; v_value := v_uname || ':' || v_timestamp || ':' || v_key; IF v_signature = crypt(v_value, v_signature) THEN RETURN v_uname; END IF; RAISE EXCEPTION 'invalid username / timestamp'; END; $ LANGUAGE plpgsql SECURITY DEFINER STABLE;
And as this function does not need the passphrase, the user can simply do this:
SELECT get_username()
But the get_username()
function is meant for security policies, e.g. like this:
CREATE POLICY chat_policy ON chat USING (get_username() IN (message_from, message_to)) WITH CHECK (message_from = get_username())
A more complete example, packed as a simple extension, may be found here.
Notice all the objects (table and functions) are owned by a privileged user, not the user accessing the database. The user only has EXECUTE
privilege on the functions, that are however defined as SECURITY DEFINER
. That’s what makes this scheme works while protecting the secret from the user. The functions are defined as STABLE
, to limit the number of calls to the crypt()
function (which is intentionally expensive to prevent bruteforcing).
The example functions definitely need more work. But hopefully it’s good enough for a proof of concept demonstrating how to store additional context in a protected session variable.
What needs to be fixed you ask? Firstly the functions don’t handle various error conditions very nicely. Secondly, while the signed value includes a timestamp, we’re not really doing anything with it – it may be used to expire the value, for example. It’s possible to add additional bits into the value, e.g. a department of the user, or even information about the session (e.g. PID of the backend process to prevent reusing the same value on other connections).
Crypto
The two functions rely on cryptography – we’re not using much except some simple hashing functions, but it’s still a simple crypto scheme. And everyone knows you should not do your own crypto. Which is why I used the pgcrypto extension, particularly the crypt()
function, to get around this problem. But I’m not a cryptographer, so while I believe the whole scheme is fine, maybe I’m missing something – let me know if you spot something.
Also, the signing would be a great match for public-key cryptography – we could use a regular PGP key with a passphrase for the signing, and the public part for signature verification. Sadly although pgcrypto supports PGP for encryption, it does not support the signing.
Alternative approaches
Of course, there are various alternative solutions. For example instead of storing the signing secret in a table, you may hard-code it into the function (but then you need to make sure the user can’t see the source code). Or you may do the signing in a C function, in which case it’s hidden from everyone who does not have access to memory (in which case you lost anyway).
Also, if you don’t like the signing approach at all, you may replace the signed variable with a more traditional “vault” solution. We need a way to store the data, but we need to make sure the user can’t see or modify the contents arbitrarily, except in a defined way. But hey, that’s what regular tables with an API implemented using security definer
functions can do!
I’m not going to present the whole reworked example here (check this extension for a complete example), but what we need is a sessions
table acting as the vault:
CREATE TABLE sessions ( session_id UUID PRIMARY KEY, session_user NAME NOT NULL )
The table must not be accessible by regular database users – a simple REVOKE ALL FROM ...
should take care of that. And then an API consisting of two main functions:
set_username(user_name, passphrase)
– generates a random UUID, inserts data into the vault and stores the UUID into a session variableget_username()
– reads the UUID from a session variable and looks-up the row in the table (errors if no matching row)
This approach replaces the signature protection with randomness of the UUID – the user may tweak the session variable, but the probability of hitting an existing ID is negligible (UUIDs are 128-bit random values).
It’s a bit more traditional approach, relying on traditional role-based security, but it also has a few disadvantages – for example it actually does database writes, which means it’s inherently incompatible with hot standby systems.
Getting rid of the passphrase
It’s also possible to design the vault so that the passphrase is not necessary. We have introduced it because we assumed set_username
happens on the same connection – we have to keep the function executable (so messing with roles or privileges is not a solution), and the passphrase ensures only the trusted component can actually use it.
But what if the signing / session creation happens on a separate connection, and only the result (signed value or session UUID) is copied into the connection handed to the user? Well, then we don’t need the passphrase any more. (It’s a bit similar to what Kerberos does – generating a ticket on a trusted connection, then use the ticket for other services.)
Summary
So let me quickly recap this blog post:
- While all the RLS examples use database users (by means of
current_user
), it’s not very difficult to make RLS work with application users. - Session variables are a reliable and quite simple solution, assuming the system has a trusted component that can set the variable before handing the connection to a user.
- When the user can execute arbitrary SQL (either by design or thanks to a vulnerability), a signed variable prevents the user from changing the value.
- Other solutions are possible, e.g. replacing the session variables with table storing info about sessions identified by random UUID.
- A nice thing is the session variables do no database writes, so this approach can work on read-only systems (e.g. hot standby).
In the next part of this blog series we’ll look at using application users when the system does not have a trusted component (so it can’t set the session variable or create a row in the sessions
table), or when we want to perform (additional) custom authentication within the database.
The attacker can call SELECT set_username(‘tomas’, ‘myattackerpassphrase’) too. Is there some protection against?
Well, the whole scheme is based on keeping the passphrase secret. He may try with a custom passphrase, but the set_username() function should check that of course.
If the attacker knows the passphrase, it’s game over. I haven’t mentioned that in the post, but there are a few weaknesses – e.g. the passphrase might get written to the server log, etc.
An interesting read, although the emphasis is a bit too much on authenticating application users and too little on RLS for my tastes.
I think you forgot one important element in the whole mechanism, in particular when the “vault” approach is used with connection pooling over long sessions from the application user perspective. In a first call to the database the application user will login(username, password) (which I find a tad more intuitive than set_username() ) and receive a session token in return when a match with a users table is found. In later calls of the same application user, the first call on a new connection should be something like session_resume(session_token) to re-establish the database session variable to be used in RLS. A logout(session_token) would also be nice to clear the row from the “vault”. Otherwise (call login() on every new connection from the pool) there is no point in transferring a session_token at all, but you would have to create a new token on every login).
And then on the finer level of detail:
– CREATE UNLOGGED TABLE sessions (…) is much faster than a regular table. No need to persist data over database sessions because when the server goes down for whatever reason all sessions are terminated anyway.
– UUID v4 has 124 bits of randomness, the remaining 4 bits are used for identification.
Haha! This sounds like a solution to the problem I just posed in my comment!
Hey there,
I’ve spent some time thinking about similar concepts, albeit from a totally different direction (http://schinckel.net/2015/12/07/row-level-security-in-postgres-and-django/). I like the idea of not having to maintain a mirrored list of users in postgres and django, for instance.
However, since a password (passphrase, whatever) is required on each request, how are you going to obtain that, at any time other than during a login event? Sure, if it was HTTP Basic Auth, then you’d be able to grab it then.
I’d love to hear some ideas about how to handle that: perhaps some type of token generation?
Great article! This was super helpful. I did run into some concurrency issues since this implementation uses a global variable. I added a transaction level implementation here: https://github.com/armiiller/rls-examples
Thanks for this post. It helped me a lot with my setup and answered my question at: https://stackoverflow.com/questions/47424076/raw-query-and-row-level-access-control-over-multiple-models-in-django
How to implement row level security with a OAuth login (facebook, twitter, google etc.) where we haven’t a user password in the database? Any ideas?
Has this turned out to be a viable way to restrict access to data in a multi tenant environment? How does this behave with connection pooling via Pgbouncer?
Yes, it’s effective for multi-tenant use, and that was a large part of the motivation for it.
It works fine via tools like pgbouncer if you use appropriate policies and app behaviour. You can have your app SET ROLE to a desired role after connecting, or SET a custom setting like myapp.user_role you test in your policies, for example. The main issue is that there’s no way to lock those down, so you have no way to stop users breaking out of their restrictions if they can run raw SQL.
Great post – we use a variant of this and we are pretty happy with it. In particular because it is fairly easy to set up more advanced policies with the right information in the session variables.
I’m curios about the missing follow up post. I would love to hear your thoughts on that as we are looking for alternatives to session variables for third party BI and don’t want to compromise on RLS and rely on the built in filters which in almost all cases are implemented with WHERE clauses.
Hi, thanks for the article. One thing that’s unclear in the examples is that set_username() does not check the passphrase (pwd) against anything; it fetches the secret from the secure table, too.
Thank you very much! The abstract matched exactly my requirements and thanks for the explanation! What I don’t understand, for what is sign_key? Is that a column of the table `secrets`? It seems I misinterpret what the columns for this table are. Thanks again!
The point of the
set_username
function is setting context securely, i.e. so that the user can’t forge it. That’s what the sign_key is for – it signs the value (in this case username + timestamp) and stores it in a session variable with a signature (essentially a hash). The user can’t modify that without invalidating the hash. This of course relies on sign_key being unknown to the user.BTW I’ve just noticed there’s a minor issue in the `set_password` function – it should check the passphrase, of course.
How would this work with SQLAlchemy? Do I have run those functions before each database call?