plpgsql functions or queries

["Artis Caune" <artis.caune@xxxxxxxxx>]

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