On May25, 2012, at 23:32 , Kohei KaiGai wrote:
> However, it should not be applied on triggers being set on PK tables,
> because it allows to modify or delete primary-key being referenced by
> invisible foreign-key from the viewpoint of operators.
> I think, it makes sense to have exceptional cases; to make sure
> foreign-key constraint at the baseline.
Good point. If we simply ignore RLS policies when executing parent-side
FK constraint triggers, however, we'll happily UPDATE or DELETE invisible
rows should the constraint be defined as CASCADE or SET NULL. Which is,
um, not exactly desirable…
FK triggers already deal with a related issue, namely a child row being
invisible to a REPEATABLE READ transaction which attempts to update the
parent row. We handle this case by always executing queries in
parent-side FK constraint triggers with a newly created READ COMMITTED
snapshot, but verifying that all matching rows are also visible under
the transaction's actual REPEATABLE READ snapshot. If some are not, we
throw a serialization error.
FK constraints in the presence of RLS should do the same, I think. When
executing a parent-side constraint trigger we should *not* apply the
RLS policy as a filter, but instead check that all returned rows are
indeed visible according to the RLS policy. If some are not we throw,
say, a "policy enforcement error".
best regards,
Florian Pflug