Thread: pg_temp implicit search path: functions vs. tables

Hi all,

I notice slightly different handling of the implicit search_path for
temporary tables and temporary functions. Consider:

(with a default search path):

# SHOW search_path;
  search_path
----------------
 "$user",public
(1 row)


BEGIN;
  CREATE TABLE pg_temp.bar();

  CREATE FUNCTION pg_temp.foofunc() RETURNS int AS $$
      SELECT 1;
  $$ LANGUAGE SQL;

  SELECT * FROM bar;
  SELECT * FROM foofunc();

COMMIT;

The select from temporary table bar above succeeds, but I get:
ERROR:  function foofunc() does not exist

if I don't schema-qualify the function as pg_temp.foofunc(). So,
pg_temp is being implicitly included in the default search path when
looking for tables, but not for functions. Is there a reason for this
difference?

Josh

Josh Kupershmidt <schmiddy@gmail.com> writes:
> pg_temp is being implicitly included in the default search path when
> looking for tables, but not for functions. Is there a reason for this
> difference?

Yes.  They used to be the same, but awhile back we decided it was a
security hole to look for functions or operators in the implicit temp
schema.  It makes it too easy for someone to substitute a trojan-horse
function that will be picked up in preference to whatever's in the
normal search path.  See CVE-2007-2138.

If you actually do want to define and call temporary functions, you
can include "pg_temp" in the search path explicitly, or perhaps better,
explicitly qualify the intentional calls with pg_temp.

            regards, tom lane

On Thu, Oct 21, 2010 at 12:47 AM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
> Josh Kupershmidt <schmiddy@gmail.com> writes:
>> pg_temp is being implicitly included in the default search path when
>> looking for tables, but not for functions. Is there a reason for this
>> difference?
>
> Yes.  They used to be the same, but awhile back we decided it was a
> security hole to look for functions or operators in the implicit temp
> schema.  It makes it too easy for someone to substitute a trojan-horse
> function that will be picked up in preference to whatever's in the
> normal search path.  See CVE-2007-2138.
>
> If you actually do want to define and call temporary functions, you
> can include "pg_temp" in the search path explicitly, or perhaps better,
> explicitly qualify the intentional calls with pg_temp.

Thanks, thought it might be something like that.

Josh