PostgreSQL 9.5 - Row level security / ROLE best practices

Question

I'm tying to grasp the best way to use the new row level security feature in a multi-tenant database that supports a web application.

Currently, the application has a few different ROLEs available, depending on the action it is attempting to take.

Once the application makes a connection using its own ROLE, the application passes authentication parameters (provided by the user) into different functions that filter out rows based on the user supplied authentication parameters. The system is designed to work with thousands of users and it seems to work; however, it's defiantly clunky (and slow).

It seems that if I wanted to use the new row level security feature I would need to create a new ROLE for each real world user (not just for the web application) to access the database.

Is this correct? and if so, is it a good idea to create thousands of ROLEs in the database?

---

Update from a_horse_with_no_name's link in the comments (thanks, that thread is spot on):

CREATE USER application;

CREATE TABLE t1 (id int primary key, f1 text, app_user text);
INSERT INTO t1 VALUES(1,'a','bob');
INSERT INTO t1 VALUES(2,'b','alice');
ALTER TABLE t1 ENABLE ROW LEVEL SECURITY;
CREATE POLICY P ON t1 USING (app_user = current_setting('app_name.app_user'));
GRANT SELECT ON t1 TO application;

SET SESSION AUTHORIZATION application;

SET app_name.app_user = 'bob';

SELECT * FROM t1;

 id | f1 | app_user
----+----+----------
  1 | a  | bob
(1 row)

SET app_name.app_user = 'alice';
SELECT * FROM t1;

 id | f1 | app_user
----+----+----------
  2 | b  | alice
(1 row)

SET app_name.app_user = 'none';
SELECT * FROM t1;

 id | f1 | app_user
----+----+----------
(0 rows)

Now, I'm confused by current_setting('app_name.app_user') as I was under the impression this was only for configuration parameters... where is app_name defined?

Cara

postgresql.org/message-id/[email protected] – Pangenesis

@a_horse_with_no_name - nailed it, thanks; however, the example given in the thread is a bit cryptic... I have updated the question. – Cara

It is for "configuration" parameters. Using them for this is essentially a "hack". You don't need to define them before hand - this can be done dynamically. Note that current_setting('app_name.app_user') will result in an error if the parameter has not been defined before.To prevent that, you can define a dummy value in postgresql.conf – Pangenesis

For the sake of completeness, there is also an ACL extension for fine grained permissions that integrates with row level security. There is no need to use ROLE. – Firooc

Answer 1

Setting security policies based on a session setting is a BAD BAD BAD idea (I hate both CAPS and bold so trust me that I mean it). Any user can SET SESSION 'app_name.app_user' = 'bob', so as soon as someone figures out that "app_name.app_user" is the door in (trust me, they will) then your whole security is out the door.

The only way that I see is to use a table accessible to your webadmin only which stores session tokens (uuid type comes to mind, cast to text for ease of use). The login() function is SECURITY DEFINER (assuming owner webadmin), setting the token as well as a session SETting, and then the table being owned by (or having appropriate privileges for) webadmin refers to that table and the session setting in its policy.

Unfortunately, you cannot use temporary (session) tables here because you cannot build policies on a temporary table so you have to use a "real" table. That is something of a performance penalty, but weigh that against the damage of a hack...

In practice:

CREATE FUNCTION login (uname text, pwd text) RETURNS boolean AS $$
DECLARE 
  t uuid;
BEGIN
  PERFORM * FROM users WHERE user = uname AND password = pwd;
  IF FOUND THEN
    INSERT INTO sessions SET token = uuid_generate_v4()::text, user ....
       RETURNING token INTO t;
    SET SESSION "app_name.token" = t;
    RETURN true;
  ELSE
    SET SESSION "app_name.token" = '';
    RETURN false;
  END IF;
END; $$ LANGUAGE plpgsql STRICT;

And now your policy would link to sessions:

CREATE POLICY p ON t1 FOR SELECT
  USING (SELECT true FROM sessions WHERE token = current_setting('app_name.token'));

(Since uuids may be assumed to be unique, no need for LIMIT 1. ordering or other magic, if the uuid is in the table the policy will pass, otherwise fail.) The uuid is impossible to guess (within your lifetime anyway) and impossible to retrieve by anyone but webadmin.

Silverware

This isn't entirely accurate. Policies can be specified for a given role, so, in your example, the policy could be created only for the 'webadmin' role and then setting the "app_name.app_user" GUC would only impact queries issued by that role. That may or may not be sufficient for your security requirements, but it would keep "any user" from being able to access any record in the table by changing the GUC. This does require that the application using the "webadmin" role properly verify the user and be free of SQL-injection bugs, of course. – Stephenson

2nd Quadrant has examples using cryptographic signing to manage authorization in a way that users can't bypass. – Dissolve