It strikes me that there's a significant gap in the whole "leakproof
function" business, namely that no consideration has been given to
planner-driven transformations of queries. As an example, if we
have "a = b" and "b = c", the planner may generate and apply "a = c"
instead of one or both of those clauses. If a, b, c are not all the
same type, "a = c" might involve an operator that's not either of the
original ones. And it's possible that that operator is not leakproof
where the original ones are. This could easily result in introducing
non-leakproof operations into a secure subquery after pushdown of a
clause that was marked secure.
Another example is that in attempting to make implication or refutation
proofs involving operator clauses, the planner feels free to apply other
members of the operator's btree opclass (if it's in one). I've not
bothered to try to create a working exploit, but I'm pretty sure that
this could result in a non-leakproof function being applied during
planning of a supposedly secure subquery. It might be that that couldn't
leak anything worse than constant values within the query tree, but
perhaps it could leak data values from a protected table's pg_statistic
entries.
ISTM that the most appropriate solution here is to insist that all or none
of the members of an operator class be marked leakproof. (Possibly we
could restrict that to btree opclasses, but I'm not sure any exception is
needed for other index types.) I looked for existing violations of this
precept, and unfortunately found a *lot*. For example, texteq is marked
leakproof but its fellow text comparison operators aren't. Is that really
sane?
Here's a query to find problematic cases:
select p1.proname,o1.oprname,p2.proname,o2.oprname,a1.amopfamily from
pg_proc p1, pg_operator o1, pg_amop a1,
pg_proc p2, pg_operator o2, pg_amop a2
where
p1.oid = o1.oprcode and p2.oid = o2.oprcode and
o1.oid = a1.amopopr and o2.oid = a2.amopopr and
a1.amopfamily = a2.amopfamily and
p1.proleakproof and not p2.proleakproof;
Oh ... and just to add insult to injury, the underlying opclass support
functions (such as textcmp and hashtext) are generally not marked
leakproof even when the operators they may be executed instead of are.
This is even more silly.
regards, tom lane