Stephen Frost <sfrost@snowman.net> writes:
> * Tom Lane (tgl@sss.pgh.pa.us) wrote:
>> Instead of
>> re-establishing the original search_path in a rather vain hope that the
>> same objects will be re-selected by parse analysis, we should consider
>> that the prepared query has a dependency on the active search path, and
>> thus force a replan if the effective search path changes.
> Presuming that this flows through to SPI and in effect pl/pgsql, this is
> exactly what I was arguing for a while back, when we ran into cases with
> connection pooling where the plans generated by a pl/pgsql function
> remained the same, referring to the objects against which it was
> originally planned, even though the search_path had changed. As I
> recall, the same might have even been true across 'set role' actions
> where the text of 'search_path' wasn't actually changed, but the '$user'
> variable inside it was.
The implementation I have in mind would compare the lists of schema OIDs
computed from the text of search_path, so it ought to do the right thing
with $user.
> Now, there is definitely legitimate concern about search_path rejiggery
> and security definer functions, so nothing done here should change how
> we handle that case.
Right offhand I see no security risk that wouldn't occur anyway given a
different calling sequence (ie, if the vulnerable call had happened
first). Certainly it's conceivable that somebody's app somewhere is
dependent on the current behavior, but it seems relatively unlikely that
that would amount to a security bug. Anyway, we're not talking about
a back-patched fix I think, but something we'd change in a new major
release.
>> Consider for example that the search path is a, b, c,
>> and we have a prepared query "select * from t", and that currently
>> refers to b.t. If now someone creates a.t, or renames a.x to a.t,
>> then a replan would cause the query to select from a.t ... but there
>> was no invalidation event that will impinge on the stored plan, and the
>> search_path setting didn't change either. I don't think we want to
>> accept the overhead of saying "any DDL anywhere invalidates all cached
>> plans", so I don't see any good way to make this case transparent.
>> How much do we care?
> That may simply be a trade-off that we need to make.
I thought about the possibility of issuing an sinval message against a
schema each time we create/drop/rename an object belonging to that
schema, and then invalidating cached plans if an sinval is received
against any schema that was in the search_path at plan time. I think
that this would be a watertight fix (in combination with the other
invalidation rules mentioned). However, it could still come annoyingly
close to "any DDL invalidates all cached plans", at least for apps that
keep most of their objects in one schema. Not entirely sure it's worth
the implementation hassle and extra sinval traffic.
regards, tom lane
