PostgreSQL row-level security involving a view or a select with join

Question

(suggestions for a better or more-descriptive title are welcome).

I wonder if the following is possible in PostgreSQL using RLS (or any other mechanism). I want a user to be able to update certain rows of a table if its username matches a column in another table. In the example that follows, I want user nene, who appears as column u in table t0, to be able to update columns a and p in table t2. What I want to express is to apply a policy to the rows in t2 that would be matched by the following select statement: SELECT a, p FROM t2 INNER JOIN t1 ON (t2.t1id = t1.id) INNER JOIN t0 ON (t1.t0id = t0.id) WHERE t0.u = 'nene';

Is this possible? Any suggestions on how to proceed? An obvious workaround would be to duplicate the username on table t2, but that adds extraneous information on t2 and requires additional constraints to enforce.

Here are my three tables (in the real situation there are many more fields, and table t1 cannot be factored out of the problem; I left it in the example because needing two joins may change the solution space).

• Table t0 was created with CREATE TABLE t0 (id TEXT PRIMARY KEY, u TEXT UNIQUE, pn TEXT); and now contains:

  => SELECT * FROM t0;
    id  |  u  |  pn  
  ------+------+------
   b321 | toto | fifi
   a421 | nene | xuxu
  (2 rows)
  

• Table t1 was created with CREATE TABLE t1 (id TEXT PRIMARY KEY, t0id TEXT REFERENCES t0(id), pn TEXT); and now contains:

  => SELECT * FROM t1;
   id  | t0id |  pn  
  ------+------+------
  x99  | a421 | lala
  zy49 | a421 | popo
  l2l  | b321 | nipa
  (3 rows)
  

• Table t2 was created with CREATE TABLE t2 (id TEXT, t1id TEXT REFERENCES t1(id), a INET, p INT); and now contains

  => SELECT * FROM t2;
    id  | t1id |      a      |   p   
  ------+------+-------------+-------
   1264 | x99  |             |      
   1267 | zy49 |             |      
   1842 | l2l  | 192.0.200.3 | 31337
   1234 | x99  | 10.0.0.89   |    23
  (4 rows)
  

Percentage

Answer 1

Try

CREATE POLICY t2_policy_update ON t2 FOR UPDATE
USING (

  EXISTS (
    SELECT * 
    FROM t1 INNER JOIN t0 ON (t1.t0id = t0.id) 
    WHERE 
      t0.u = session_user AND 
      t1id = t1.id
  )

)

Kummerbund

Thanks. It required adding the corresponding privileges on t0 and t1, but it was exactly the sort of clue I needed to understand how this works. – Percentage