Date: Tue, 16 Aug 2022 15:32:36 -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>
Subject: Re: SQL-standard function bodies and creating SECURITY DEFINER
 routines securely
Message-ID: <Yvvw1JB/Y12HvAYn@momjian.us>
References: 
 <AM9PR01MB8268BF5E74E119828251FD34FE409@AM9PR01MB8268.eurprd01.prod.exchangelabs.com>
MIME-Version: 1.0
Content-Type: multipart/mixed; boundary="6fxbbgSDZf+WynL7"
Content-Disposition: inline
In-Reply-To: 
 <AM9PR01MB8268BF5E74E119828251FD34FE409@AM9PR01MB8268.eurprd01.prod.exchangelabs.com>
Archived-At: 
 <https://www.postgresql.org/message-id/Yvvw1JB/Y12HvAYn%40momjian.us>
Precedence: bulk


--6fxbbgSDZf+WynL7
Content-Type: text/plain; charset=us-ascii
Content-Disposition: inline

On Sat, Dec 25, 2021 at 02:36:27PM +0000, Erki Eessaar wrote:
> 
> Hello
> 
> PostgreSQL 14 added the feature: "Allow SQL-language functions and procedures
> to use SQL-standard function bodies."
> 
> If I understand correctly, then in this case the system  will track
> dependencies between tables and routines that use the tables. Thus, the
> SECURITY DEFINER routines that use the new approach do not require the
> following mitigation, i.e., SET search_path= is not needed. The following part
> of documentation does not mention this.
> 
> https://www.postgresql.org/docs/current/sql-createfunction.html#
> SQL-CREATEFUNCTION-SECURITY
> 
> [elephant] PostgreSQL: Documentation: 14: CREATE FUNCTION
>            Overloading. PostgreSQL allows function overloading; that is, the
>            same name can be used for several different functions so long as
>            they have distinct input argument types.Whether or not you use it,
>            this capability entails security precautions when calling functions
>            in databases where some users mistrust other users; see Section
>            10.3.. Two functions are considered the same if they have the same
>            ...
>            www.postgresql.org

I have written the attached patch to mention this issue about sql_body
functions.

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

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


--6fxbbgSDZf+WynL7
Content-Type: text/x-diff; charset=us-ascii
Content-Disposition: attachment; filename="function.diff"

diff --git a/doc/src/sgml/ref/create_function.sgml b/doc/src/sgml/ref/create_function.sgml
index 7e6d52c7dc..976d4caad6 100644
--- a/doc/src/sgml/ref/create_function.sgml
+++ b/doc/src/sgml/ref/create_function.sgml
@@ -779,7 +779,10 @@ SELECT * FROM dup(42);
    <para>
     Because a <literal>SECURITY DEFINER</literal> function is executed
     with the privileges of the user that owns it, care is needed to
-    ensure that the function cannot be misused.  For security,
+    ensure that the function cannot be misused.  This is particularly
+    important for non-<replaceable>sql_body</replaceable> functions because
+    their function bodies are evaulated at run-time, not creation time.
+    For security,
     <xref linkend="guc-search-path"/> should be set to exclude any schemas
     writable by untrusted users.  This prevents
     malicious users from creating objects (e.g., tables, functions, and

--6fxbbgSDZf+WynL7--