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?

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.