Thread: How should RLS treat ON DELETE CASCADE, ON UPDATE CASCADE, and ON DELETE SET NULL?
How should RLS treat ON DELETE CASCADE, ON UPDATE CASCADE, and ON DELETE SET NULL?
From
Craig Ringer
Date:
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