Re: How should row-security affects ON UPDATE RESTRICT / CASCADE ?

Craig Ringer <craig(at)2ndquadrant(dot)com> writes:
> During my testing of Kohei KaiGai's row-security patches I've been
> looking into how foreign keys should be and are handled. There are some
> interesting wrinkles around FK cascades, the rights under which FK
> checks execute, and about the consistency effects of changing or
> applying an RLS policy.

As I recall, I've been saying since day one that row-level security cannot
sensibly coexist with foreign-key constraints, and I've been told that the
potential users of such a feature don't care. I'm glad to see somebody
else complaining.

Here's another example wherein there basically isn't a sensible solution:
suppose you have delete rights on table A, and there is a table B
with a foreign-key reference to A, and RLS says that there are rows in
B that you can't see. You try to delete some row in A that is referenced
by an invisible-to-you row in B. There are only two possible outcomes:
the system refuses your request, and thereby exposes to you the fact that
a referencing row exists; or the system allows the FK constraint to be
violated.

As far as the points you're making go, I think we must say that RLS checks
are not applied during FK trigger queries, ie the FK triggers can always
see everything even though they don't run as superuser. Otherwise you're
going to end up with constraint violations, and as a database weenie
I consider that unacceptable. This will mean that a poorly-chosen FK
arrangement will allow some leakage of row-existence info, but I don't
believe that that can be avoided anyway, per the above example.

regards, tom lane