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(at)momjian(dot)us> https://momjian.us
EDB https://enterprisedb.com

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