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.
