Hi all
I'm interested in an opinion: How should RLS treat cascading RI constraints?
The current tests check ON DELETE NO ACTION constraints, causing
insertion to fail if a PK row exists but is not visible, and deletion to
fail if there's still an invisible FK reference to a visible PK.
That seems sane, with the caveat that inconsistent RLS rows can
introduce apparent referential integrity violations where a valid
constraint says a row should exist but it doesn't, or shouldn't exist
but it does. I don't see a good answer to that.
What I'm wondering about is how cascade RI affects things. Right now, a
user can update a row they cannot see if there is an ON DELETE SET NULL
or ON UPDATE CASCADE constraint on it. They can also delete a row they
can't see with ON DELETE CASCADE.
This seems consistent with table-level permissions, where a user can
delete from a parent table and affect rows in a child table they don't
have the rights to see or update directly. So I'm happy with the current
behaviour. If everyone else is, I'll add appropriate tests to the test
suite.
-- Craig Ringer http://www.2ndQuadrant.com/PostgreSQL Development, 24x7 Support, Training & Services
