From 2abae34a2e8fde42be731b4e18d44cd08901464d Mon Sep 17 00:00:00 2001 From: Tom Lane Date: Mon, 3 Sep 2007 00:39:26 +0000 Subject: Implement function-local GUC parameter settings, as per recent discussion. There are still some loose ends: I didn't do anything about the SET FROM CURRENT idea yet, and it's not real clear whether we are happy with the interaction of SET LOCAL with function-local settings. The documentation is a bit spartan, too. --- doc/src/sgml/catalogs.sgml | 9 +++- doc/src/sgml/ref/alter_function.sgml | 43 +++++++++++++++++- doc/src/sgml/ref/create_function.sgml | 82 ++++++++++++++++++++++++++++------- 3 files changed, 116 insertions(+), 18 deletions(-) (limited to 'doc/src') 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 @@ - + @@ -3635,6 +3635,13 @@ + + proconfig + text[] + + Function's local settings for run-time configuration variables + + proacl aclitem[] 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 @@ @@ -36,6 +36,8 @@ where action is one of: [ EXTERNAL ] SECURITY INVOKER | [ EXTERNAL ] SECURITY DEFINER COST execution_cost ROWS result_rows + SET parameter { TO | = } { value | DEFAULT } + RESET parameter @@ -212,6 +214,28 @@ where action is one of: + + parameter + value + + + Add or change the assignment to be made to a configuration parameter + when the function is called. If + value is DEFAULT + or, equivalently, RESET is used, the function-local + setting is removed, so that the function executes with the value + present in its environment. Use RESET + ALL to clear all function-local settings. + + + + See and + + for more information about allowed parameter names and values. + + + + RESTRICT @@ -250,6 +274,22 @@ ALTER FUNCTION sqrt(integer) OWNER TO joe; ALTER FUNCTION sqrt(integer) SET SCHEMA maths; + + + To adjust the search path that is automatically set for a function: + +ALTER FUNCTION check_password(text) SET search_path = admin, pg_temp; + + + + + To disable automatic setting of search_path for a function: + +ALTER FUNCTION check_password(text) RESET search_path; + + The function will now execute with whatever search path is used by its + caller. + @@ -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 RESTRICT key word, which is optional in 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 @@ @@ -28,6 +28,7 @@ CREATE [ OR REPLACE ] FUNCTION | [ EXTERNAL ] SECURITY INVOKER | [ EXTERNAL ] SECURITY DEFINER | COST execution_cost | ROWS result_rows + | SET parameter { TO | = } { value | DEFAULT } | AS 'definition' | AS 'obj_file', 'link_symbol' } ... @@ -71,6 +72,8 @@ CREATE [ OR REPLACE ] FUNCTION triggers, etc. that refer to the old function. Use CREATE OR REPLACE FUNCTION to change a function definition without breaking objects that refer to the function. + Also, ALTER FUNCTION can be used to change most of the + auxiliary properties of an existing function. @@ -320,6 +323,24 @@ CREATE [ OR REPLACE ] FUNCTION + + parameter + value + + + The 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. + + + + See and + + for more information about allowed parameter names and values. + + + + definition @@ -451,6 +472,18 @@ CREATE FUNCTION foo(int, out text) ... be escaped by doubling them. + + If any SET clauses are attached to a function, then + the effects of a 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 SET clause(s). However, an ordinary + SET command (without LOCAL) overrides the + SET clause, much as it would do for a previous SET + LOCAL command: the effects of such a command will persist after + function exit, unless the current transaction is rolled back. + + To be able to define a function, the user must have the USAGE privilege on the language. @@ -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; + + + + Before PostgreSQL version 8.3, the + SET option was not available, and so older functions may + contain rather complicated logic to save, set, and restore + search_path. The SET option is far easier + to use for this purpose. + + + + Another point to keep in mind is that by default, execute privilege + is granted to PUBLIC for newly created functions + (see 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 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: + + + +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; -- cgit v1.2.3