(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
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).
t0was 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)
t1was 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)
t2was 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 | 18.104.22.168 | 31337 1234 | x99 | 10.0.0.89 | 23 (4 rows)