secure arrangement can be obtained
    by forcing the temporary schema to be searched last.  To do this,
    write <literal>pg_temp</literal><indexterm><primary>pg_temp</primary><secondary>securing functions</secondary></indexterm> as the last entry in <varname>search_path</varname>.
    This function illustrates safe usage:

<programlisting>
CREATE FUNCTION check_password(uname TEXT, pass TEXT)
RETURNS BOOLEAN AS $$
DECLARE passed BOOLEAN;
BEGIN
        SELECT  (pwd = $2) INTO passed
        FROM    pwds
        WHERE   username = $1;

        RETURN passed;
END;
$$  LANGUAGE plpgsql
    SECURITY DEFINER
    -- Set a secure search_path: trusted schema(s), then 'pg_temp'.
    SET search_path = admin, pg_temp;
</programlisting>

    This function's intention is to access a table <literal>admin.pwds</literal>.
    But without the <literal>SET</literal> clause, or with a <literal>SET</literal> clause
    mentioning only <literal>admin</literal>, the function could be subverted by
    creating a temporary table named <literal>pwds</literal>.
   </para>

   <para>
    If the security definer function intends to create roles, and if it
    is running as a non-superuser, <varname>createrole_self_grant</varname>
    should also be set to a known value using the <literal>SET</literal>
    clause.
   </para>

   <para>
    Another point to keep in mind is that by default, execute privilege
    is granted to <literal>PUBLIC</literal> for newly created functions
    (see <xref linkend="ddl-priv"/> for more
    information).  Frequently you will wish to restrict use of a security
    definer function to only some users.  To do that, you must revoke
    the default <literal>PUBLIC</literal> privileges and then grant execute
    privilege selectively.  To avoid having a window where the new function
    is accessible to all, create it and set the privileges within a single
    transaction.  For example:
   </para>

<programlisting>
BEGIN;
CREATE FUNCTION check_password(uname TEXT, pass TEXT) ... SECURITY DEFINER;
REVOKE ALL ON FUNCTION check_password(uname TEXT, pass TEXT) FROM PUBLIC;
GRANT EXECUTE ON FUNCTION check_password(uname TEXT, pass TEXT) TO admins;
COMMIT;
</programlisting>

 </refsect1>

 <refsect1 id="sql-createfunction-compat">
  <title>Compatibility</title>

  <para>
   A <command>CREATE FUNCTION</command> command is defined in the SQL
   standard.  The <productname>PostgreSQL</productname>