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.