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 -- Sent via pgsql-hackers mailing list (firstname.lastname@example.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers