title: PostgreSQL 9.5 - Row level security / ROLE best practices
body: <p>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. </p>
  
  <p>Currently, the application has a few different ROLEs available, depending on the action it is attempting to take.</p>
  
  <p>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).</p>
  
  <p>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.</p>
  
  <p>Is this correct? and if so, is it a good idea to create thousands of ROLEs in the database?</p>
  
  <hr>
  
  <p><strong>Update</strong> from <strong>a_horse_with_no_name</strong>'s link in the comments (thanks, that thread is spot on):</p>
  
  <pre><code>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)
  </code></pre>
  
  <p>Now, I'm confused by <code>current_setting('app_name.app_user')</code> as I was under the impression this was only for configuration parameters... where is <code>app_name</code> defined?</p>