diff options
Diffstat (limited to 'doc/src/sgml/ref/create_function.sgml')
| -rw-r--r-- | doc/src/sgml/ref/create_function.sgml | 82 |
1 files changed, 66 insertions, 16 deletions
diff --git a/doc/src/sgml/ref/create_function.sgml b/doc/src/sgml/ref/create_function.sgml index 5966ca1c0b0..7aff876d37d 100644 --- a/doc/src/sgml/ref/create_function.sgml +++ b/doc/src/sgml/ref/create_function.sgml @@ -1,5 +1,5 @@ <!-- -$PostgreSQL: pgsql/doc/src/sgml/ref/create_function.sgml,v 1.75 2007/04/23 16:52:53 neilc Exp $ +$PostgreSQL: pgsql/doc/src/sgml/ref/create_function.sgml,v 1.76 2007/09/03 00:39:13 tgl Exp $ --> <refentry id="SQL-CREATEFUNCTION"> @@ -28,6 +28,7 @@ CREATE [ OR REPLACE ] FUNCTION | [ EXTERNAL ] SECURITY INVOKER | [ EXTERNAL ] SECURITY DEFINER | COST <replaceable class="parameter">execution_cost</replaceable> | ROWS <replaceable class="parameter">result_rows</replaceable> + | SET <replaceable class="parameter">parameter</replaceable> { TO | = } { <replaceable class="parameter">value</replaceable> | DEFAULT } | AS '<replaceable class="parameter">definition</replaceable>' | AS '<replaceable class="parameter">obj_file</replaceable>', '<replaceable class="parameter">link_symbol</replaceable>' } ... @@ -71,6 +72,8 @@ CREATE [ OR REPLACE ] FUNCTION triggers, etc. that refer to the old function. Use <command>CREATE OR REPLACE FUNCTION</command> to change a function definition without breaking objects that refer to the function. + Also, <command>ALTER FUNCTION</> can be used to change most of the + auxiliary properties of an existing function. </para> <para> @@ -321,6 +324,24 @@ CREATE [ OR REPLACE ] FUNCTION </varlistentry> <varlistentry> + <term><replaceable>parameter</replaceable></term> + <term><replaceable>value</replaceable></term> + <listitem> + <para> + The <literal>SET</> clause causes the specified configuration + parameter to be set to the specified value when the function is + entered, and then restored to its prior value when the function exits. + </para> + + <para> + See <xref linkend="sql-set" endterm="sql-set-title"> and + <xref linkend="runtime-config"> + for more information about allowed parameter names and values. + </para> + </listitem> + </varlistentry> + + <varlistentry> <term><replaceable class="parameter">definition</replaceable></term> <listitem> @@ -452,6 +473,18 @@ CREATE FUNCTION foo(int, out text) ... </para> <para> + If any <literal>SET</> clauses are attached to a function, then + the effects of a <command>SET LOCAL</> command executed inside the + function are restricted to the function: the configuration parameter's + value is restored at function exit. This is true even for parameters + not mentioned in the <literal>SET</> clause(s). However, an ordinary + <command>SET</> command (without <literal>LOCAL</>) overrides the + <literal>SET</> clause, much as it would do for a previous <command>SET + LOCAL</> command: the effects of such a command will persist after + function exit, unless the current transaction is rolled back. + </para> + + <para> To be able to define a function, the user must have the <literal>USAGE</literal> privilege on the language. </para> @@ -530,28 +563,45 @@ SELECT * FROM dup(42); CREATE FUNCTION check_password(uname TEXT, pass TEXT) RETURNS BOOLEAN AS $$ DECLARE passed BOOLEAN; - old_path TEXT; BEGIN - -- Save old search_path; notice we must qualify current_setting - -- to ensure we invoke the right function - old_path := pg_catalog.current_setting('search_path'); - - -- Set a secure search_path: trusted schemas, then 'pg_temp'. - -- We set is_local = true so that the old value will be restored - -- in event of an error before we reach the function end. - PERFORM pg_catalog.set_config('search_path', 'admin, pg_temp', true); - - -- Do whatever secure work we came for. SELECT (pwd = $2) INTO passed FROM pwds WHERE username = $1; - -- Restore caller's search_path - PERFORM pg_catalog.set_config('search_path', old_path, true); - RETURN passed; END; -$$ LANGUAGE plpgsql SECURITY DEFINER; +$$ LANGUAGE plpgsql + SECURITY DEFINER + -- Set a secure search_path: trusted schema(s), then 'pg_temp'. + SET search_path = admin, pg_temp; +</programlisting> + + <para> + Before <productname>PostgreSQL</productname> version 8.3, the + <literal>SET</> option was not available, and so older functions may + contain rather complicated logic to save, set, and restore + <varname>search_path</>. The <literal>SET</> option is far easier + to use for this purpose. + </para> + + <para> + Another point to keep in mind is that by default, execute privilege + is granted to <literal>PUBLIC</> for newly created functions + (see <xref linkend="sql-grant" endterm="sql-grant-title"> 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</> 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> |