"Chris Ochs" <chris@paymentonline.com> writes:
> On my system I get permission denied when I switch to pgtest2 and select *
> from pgtest_func.
What's being cached here is not the authorization, but the table
reference --- that is, the function's SELECT FROM pgtest_table
is resolved as pgtest1.pgtest_table the first time you run it,
and that remains true even though the schema search path is
different during the second call. The permissions failure occurs
because the correct user is trying to access the wrong table,
not wrong user and right table.
There's been some talk of trying to deal with this by associating
cached plans with particular schema search path strings, but no
one's done anything about it yet --- it looks expensive, and it
wouldn't prevent every failure of this sort anyway. Consider
for example that your search path is a,b,c, and on the first time
through "t1" is resolved as "b.t1". Now you create an "a.t1"
and call the function again. The search path is still the same,
so a cache based on checking that would not notice that it ought
to recompile the query.
regards, tom lane