Is it safe to use plpgsql functions with 'security definer'?
For example we have table for spamassassin preferences, and user spamassassin.
I don't want spamassassin user to see user database (passwords, ...)
So I use function:
CREATE OR REPLACE FUNCTION get_sa_preferences( VARCHAR )
RETURNS SETOF sa_preferences AS $$
DECLARE
rec RECORD;
BEGIN
SELECT user_id, sa_group
INTO rec
FROM users
JOIN emails USING (user_id)
WHERE email = $1;
IF FOUND THEN
IF rec.user_id != rec.sa_group AND rec.sa_group != 0 THEN
RETURN QUERY SELECT *
FROM sa_preferences
WHERE user_id = rec.sa_group;
END IF;
RETURN QUERY SELECT *
FROM sa_preferences
WHERE user_id = rec.user_id;
END IF;
END;
$$ LANGUAGE 'plpgsql' SECURITY DEFINER;
There is no grants for spamassassin user to tables users, emails.
spamassassin user can get preferences but can't see users database.
If I change or add new features to system, I don't need to change
queries on every server, just replace function.
I want use functions like this for every lookup - domains, users,
forwards, many more
Most important I want to use plpgsql functions for web functions, like
add_email, chenge_password, ...
Is there sql injections possible with those functions, should I check,
quote $1, $2 variables?
How about performance? Is there some overhead when using plpgsql?
How are transactions used within plpgsql functions?
If I run query:
SELECT * FROM get_sa_preferences('[EMAIL PROTECTED]');
I'm actually running?:
BEGIN;
SELECT * FROM get_sa_preferences('[EMAIL PROTECTED]');
COMMIT;
Can I use:
BEGIN;
SELECT * FROM add_user('description');
SELECT * FROM add_email('[EMAIL PROTECTED]');
SELECT * FROM add_settings('setting1', 'setting2');
... other functions ...
COMMIT;
thanks,
Artis
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
