Date: Fri, 7 Oct 2022 10:02:22 -0400
From: Bruce Momjian <bruce@momjian.us>
To: Erki Eessaar <erki.eessaar@taltech.ee>
Cc: "pgsql-docs@lists.postgresql.org" <pgsql-docs@lists.postgresql.org>,
	Noah Misch <noah@leadboat.com>,
	Peter Eisentraut <peter.eisentraut@enterprisedb.com>
Subject: Re: SQL-standard function bodies and creating SECURITY DEFINER
 routines securely
Message-ID: <Y0Axbu5hdZtJ1NgR@momjian.us>
References: 
 <AM9PR01MB8268BF5E74E119828251FD34FE409@AM9PR01MB8268.eurprd01.prod.exchangelabs.com>
 <Yvvw1JB/Y12HvAYn@momjian.us>
 <YzR/X5Hhyu4HaUj2@momjian.us>
 <AM9PR01MB8268FB1629B5AAF1680E0594FE5C9@AM9PR01MB8268.eurprd01.prod.exchangelabs.com>
 <Y0ArNYxcHbimR6zN@momjian.us>
 <AM9PR01MB8268561F390A27AE8C56F9B6FE5F9@AM9PR01MB8268.eurprd01.prod.exchangelabs.com>
MIME-Version: 1.0
Content-Type: text/plain; charset=us-ascii
Content-Disposition: inline
In-Reply-To: 
 <AM9PR01MB8268561F390A27AE8C56F9B6FE5F9@AM9PR01MB8268.eurprd01.prod.exchangelabs.com>
Archived-At: 
 <https://www.postgresql.org/message-id/Y0Axbu5hdZtJ1NgR%40momjian.us>
Precedence: bulk

On Fri, Oct  7, 2022 at 01:50:14PM +0000, Erki Eessaar wrote:
> Hello
> 
> Another example where explicit search path is needed.
> 
> CREATE TABLE public.B(b INTEGER);
> CREATE TABLE pg_temp.B(b INTEGER);
> 
> CREATE OR REPLACE FUNCTION f3 () RETURNS VOID
> LANGUAGE sql SECURITY DEFINER
> BEGIN ATOMIC
> INSERT INTO B(b) VALUES (1);
> END;
> 
> SELECT f3();
> 
> SELECT * FROM public.B;
> /*Result has 0 rows.*/
> 
> SELECT * FROM pg_temp.B;
> /*Result has 1 row. Function f3 was associated with pg_temp.B because
> f3() did not have explicitly set search path.*/
> 
> I see now that there are multiple reasons why to still use search path. I agree
> now that this extra paragaraph is perhaps too confusing and is not needed.

Thanks.  I learned a few things in this discussion and we can revisit it
if we feel there is need of improvement.

-- 
  Bruce Momjian  <bruce@momjian.us>        https://momjian.us
  EDB                                      https://enterprisedb.com

  Indecision is a decision.  Inaction is an action.  Mark Batterson