Re: Proposed feature: Selective Foreign Keys

On 3 Dec 2013, at 12:37, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:

> Tom Dunstan <pgsql(at)tomd(dot)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.

Not trying at all.

The source for that clause has come out of deparse_expression - it doesn’t seem like it would be totally impossible to create a deparse_expression_qualified which could get get_rule_expr to emit fully qualified operators, functions etc. I can see that my regression testing to test for different expressions is going to get quite large to guard against this stuff. Thanks for the pointer.

> 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.

On insert / update in the table with the fk, the where clause is evaluated separately first against the current trigger tuple. If the check passed, we then do the check against the referenced table (as that table’s owner), which is unchanged from now and does not have a where clause.

For updates / deletes on the referenced table, the queries are run against the FK table, as that table’s owner, with the extra where clause. But given that the FK is defined on that table, I’m not sure that I see a problem - presumably the table’s owner was the one who created the FK, right?

Am I missing a scenario here?

Thanks

Tom