[GENERAL] plpgsql functions or queries

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('artis.caune@gmail.com');
I'm actually running?:
BEGIN;
SELECT * FROM get_sa_preferences('artis.caune@gmail.com');
COMMIT;

Can I use:
BEGIN;
SELECT * FROM add_user('description');
SELECT * FROM add_email('user@domain');
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