I am very confused after I read the guide as follows. It means I only need to set the search_path to make the pg_temp as the last entry. or I need configure search_path and at the same time, I need create the security definer? Is thers anybody help me? Thank you very much. I really appreciate it. The following is what I got from the postgresql 8.2 guide. Writing SECURITY DEFINER Functions Safely Because a SECURITY DEFINER function is executed with the privileges of the user that created it, care is needed to ensure that the function cannot be misused. For security, search_path should be set to exclude any schemas writable by untrusted users. This prevents malicious users from creating objects that mask objects used by the function. Particularly important in this regard is the temporary-table schema, which is searched first by default, and is normally writable by anyone. A secure arrangement can be had by forcing the temporary schema to be searched last. To do this, write pg_temp as the last entry in search_path. This function illustrates safe usage: 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; -- View this message in context: http://postgresql.1045698.n5.nabble.com/postgresql-8-2-security-definer-is-a-built-in-function-very-confused-tp5588409p5588420.html Sent from the PostgreSQL - general mailing list archive at Nabble.com. -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
