Re: Possible SET SESSION AUTHORIZATION bug

Makes sense thanks for the explanation. Using FOR-IN-EXECUTE works just as
well in our case. Every call to the function is likely to be a different
schema, so caching wouldn't help us much anyways. The only negative is
that the functions get uglier using EXECUTE:)

Chris

----- Original Message -----
From: "Tom Lane" <tgl(at)sss(dot)pgh(dot)pa(dot)us>
To: "Chris Ochs" <chris(at)paymentonline(dot)com>
Cc: <pgsql-general(at)postgreSQL(dot)org>
Sent: Monday, June 21, 2004 5:52 PM
Subject: Re: [GENERAL] Possible SET SESSION AUTHORIZATION bug

> "Chris Ochs" <chris(at)paymentonline(dot)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
>