Re: Proposed feature: Selective Foreign Keys

Tom Dunstan <pgsql@tomd.cc> writes:
> Well, with this patch, under the hood the FK query is doing (in the case of RESTRICT):

> SELECT 1 FROM ONLY "public"."comment" x WHERE (the id) OPERATOR(pg_catalog.=) "parent_id" AND (parent_entity =
'event')FOR KEY SHARE OF x;
 

Hm.  The RI trigger code goes to extreme lengths to make sure that the
query strings it generates will invoke exactly the operators it intends
--- thus the OPERATOR(pg_catalog.=) syntax, which would otherwise be
merely overkill.  The added text you are showing above seems trivially
vulnerable to unwanted behavior and even security bugs, if executed in
say an unexpected search_path context.  I am not sure that we have the
technology to automatically make arbitrary expressions proof against that
sort of hazard, but in any case you don't seem to be trying very hard.

Another issue that would need to be thought about is trojan-horse
functions in the WHERE clause.  IIRC, RI trigger queries might run as the
owner of either the referencing or the referenced tables.  If those two
don't fully trust each other then this is opening the gates for mischief.
        regards, tom lane