(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 withCREATE 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 withCREATE 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 withCREATE 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)
Try