diff options
Diffstat (limited to 'doc/src')
| -rw-r--r-- | doc/src/sgml/catalogs.sgml | 9 | ||||
| -rw-r--r-- | doc/src/sgml/ref/alter_function.sgml | 43 | ||||
| -rw-r--r-- | doc/src/sgml/ref/create_function.sgml | 82 |
3 files changed, 116 insertions, 18 deletions
diff --git a/doc/src/sgml/catalogs.sgml b/doc/src/sgml/catalogs.sgml index 165ad37a358..525d24feace 100644 --- a/doc/src/sgml/catalogs.sgml +++ b/doc/src/sgml/catalogs.sgml @@ -1,4 +1,4 @@ -<!-- $PostgreSQL: pgsql/doc/src/sgml/catalogs.sgml,v 2.155 2007/06/20 19:24:05 neilc Exp $ --> +<!-- $PostgreSQL: pgsql/doc/src/sgml/catalogs.sgml,v 2.156 2007/09/03 00:39:11 tgl Exp $ --> <!-- Documentation of the system catalogs, directed toward PostgreSQL developers --> @@ -3636,6 +3636,13 @@ </row> <row> + <entry><structfield>proconfig</structfield></entry> + <entry><type>text[]</type></entry> + <entry></entry> + <entry>Function's local settings for run-time configuration variables</entry> + </row> + + <row> <entry><structfield>proacl</structfield></entry> <entry><type>aclitem[]</type></entry> <entry></entry> diff --git a/doc/src/sgml/ref/alter_function.sgml b/doc/src/sgml/ref/alter_function.sgml index 150ca3d418f..964603b067a 100644 --- a/doc/src/sgml/ref/alter_function.sgml +++ b/doc/src/sgml/ref/alter_function.sgml @@ -1,5 +1,5 @@ <!-- -$PostgreSQL: pgsql/doc/src/sgml/ref/alter_function.sgml,v 1.13 2007/01/22 01:35:19 tgl Exp $ +$PostgreSQL: pgsql/doc/src/sgml/ref/alter_function.sgml,v 1.14 2007/09/03 00:39:12 tgl Exp $ PostgreSQL documentation --> @@ -36,6 +36,8 @@ where <replaceable class="PARAMETER">action</replaceable> is one of: [ 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 } + RESET <replaceable class="parameter">parameter</replaceable> </synopsis> </refsynopsisdiv> @@ -212,6 +214,28 @@ where <replaceable class="PARAMETER">action</replaceable> is one of: </listitem> </varlistentry> + <varlistentry> + <term><replaceable>parameter</replaceable></term> + <term><replaceable>value</replaceable></term> + <listitem> + <para> + Add or change the assignment to be made to a configuration parameter + when the function is called. If + <replaceable>value</replaceable> is <literal>DEFAULT</literal> + or, equivalently, <literal>RESET</literal> is used, the function-local + setting is removed, so that the function executes with the value + present in its environment. Use <literal>RESET + ALL</literal> to clear all function-local settings. + </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><literal>RESTRICT</literal></term> @@ -250,6 +274,22 @@ ALTER FUNCTION sqrt(integer) OWNER TO joe; ALTER FUNCTION sqrt(integer) SET SCHEMA maths; </programlisting> </para> + + <para> + To adjust the search path that is automatically set for a function: +<programlisting> +ALTER FUNCTION check_password(text) SET search_path = admin, pg_temp; +</programlisting> + </para> + + <para> + To disable automatic setting of <varname>search_path</> for a function: +<programlisting> +ALTER FUNCTION check_password(text) RESET search_path; +</programlisting> + The function will now execute with whatever search path is used by its + caller. + </para> </refsect1> <refsect1> @@ -260,6 +300,7 @@ ALTER FUNCTION sqrt(integer) SET SCHEMA maths; FUNCTION</> statement in the SQL standard. The standard allows more properties of a function to be modified, but does not provide the ability to rename a function, make a function a security definer, + attach configuration parameter values to a function, or change the owner, schema, or volatility of a function. The standard also requires the <literal>RESTRICT</> key word, which is optional in <productname>PostgreSQL</>. 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> |