Re: SQL-standard function bodies and creating SECURITY DEFINER routines securely

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