From aa27977fe21a7dfa4da4376ad66ae37cb8f0d0b5 Mon Sep 17 00:00:00 2001
From: Tom Lane
Date: Fri, 20 Apr 2007 02:37:38 +0000
Subject: Support explicit placement of the temporary-table schema within
 search_path. This is needed to allow a security-definer function to set a
 truly secure value of search_path.  Without it, a malicious user can use
 temporary objects to execute code with the privileges of the security-definer
 function.  Even pushing the temp schema to the back of the search path is not
 quite good enough, because a function or operator at the back of the path
 might still capture control from one nearer the front due to having a more
 exact datatype match.  Hence, disable searching the temp schema altogether
 for functions and operators.

Security: CVE-2007-2138
---
 src/test/regress/expected/temp.out | 58 ++++++++++++++++++++++++++++++++++++++
 src/test/regress/sql/temp.sql      | 33 ++++++++++++++++++++++
 2 files changed, 91 insertions(+)

(limited to 'src/test')

diff --git a/src/test/regress/expected/temp.out b/src/test/regress/expected/temp.out
index 3ba19b55a8..335a48c727 100644
--- a/src/test/regress/expected/temp.out
+++ b/src/test/regress/expected/temp.out
@@ -137,3 +137,61 @@ CREATE TEMP TABLE temptest4(col int REFERENCES temptest3);
 COMMIT;
 ERROR:  unsupported ON COMMIT and foreign key combination
 DETAIL:  Table "temptest4" references "temptest3", but they do not have the same ON COMMIT setting.
+-- Test manipulation of temp schema's placement in search path
+create table public.whereami (f1 text);
+insert into public.whereami values ('public');
+create temp table whereami (f1 text);
+insert into whereami values ('temp');
+create function public.whoami() returns text
+  as $$select 'public'::text$$ language sql;
+create function pg_temp.whoami() returns text
+  as $$select 'temp'::text$$ language sql;
+-- default should have pg_temp implicitly first, but only for tables
+select * from whereami;
+  f1  
+------
+ temp
+(1 row)
+
+select whoami();
+ whoami 
+--------
+ public
+(1 row)
+
+-- can list temp first explicitly, but it still doesn't affect functions
+set search_path = pg_temp, public;
+select * from whereami;
+  f1  
+------
+ temp
+(1 row)
+
+select whoami();
+ whoami 
+--------
+ public
+(1 row)
+
+-- or put it last for security
+set search_path = public, pg_temp;
+select * from whereami;
+   f1   
+--------
+ public
+(1 row)
+
+select whoami();
+ whoami 
+--------
+ public
+(1 row)
+
+-- you can invoke a temp function explicitly, though
+select pg_temp.whoami();
+ whoami 
+--------
+ temp
+(1 row)
+
+drop table public.whereami;
diff --git a/src/test/regress/sql/temp.sql b/src/test/regress/sql/temp.sql
index 6a4b856144..82d7834bd1 100644
--- a/src/test/regress/sql/temp.sql
+++ b/src/test/regress/sql/temp.sql
@@ -118,3 +118,36 @@ BEGIN;
 CREATE TEMP TABLE temptest3(col int PRIMARY KEY) ON COMMIT DELETE ROWS;
 CREATE TEMP TABLE temptest4(col int REFERENCES temptest3);
 COMMIT;
+
+-- Test manipulation of temp schema's placement in search path
+
+create table public.whereami (f1 text);
+insert into public.whereami values ('public');
+
+create temp table whereami (f1 text);
+insert into whereami values ('temp');
+
+create function public.whoami() returns text
+  as $$select 'public'::text$$ language sql;
+
+create function pg_temp.whoami() returns text
+  as $$select 'temp'::text$$ language sql;
+
+-- default should have pg_temp implicitly first, but only for tables
+select * from whereami;
+select whoami();
+
+-- can list temp first explicitly, but it still doesn't affect functions
+set search_path = pg_temp, public;
+select * from whereami;
+select whoami();
+
+-- or put it last for security
+set search_path = public, pg_temp;
+select * from whereami;
+select whoami();
+
+-- you can invoke a temp function explicitly, though
+select pg_temp.whoami();
+
+drop table public.whereami;
-- 
cgit v1.2.3