Re: Execute permsissions on fuctions

23 Aug 2001 11:52:25 -0700, Zot O'Connor ____
> Other SQL servers have the concept of stored procedures having different
> permissions.
>
> For instance a procedure that can update a table.
>
> Since a web site typically connects as the webuser (or equiv postgres
> user), I do not want to offer update to the webuser.
>
> The way I have done this elsewhere is to create a stored procedure that
> could update the table, and allow the webuser to update the table. The
> procedure had perms of a user who could update the table, but the
> webuser could not.
>
> How can I do this in Postgres?
>
You can do it indirectly. There is patch for 7.1.2 which adds SET
AUTHORIZATION INVOKER/DEFINER clause to PLPGSQL. Sorry, I don't remember
link, you can find it in -sql or -hackers mailing list archives on June
or I can send it to you. Next step is to create some function like (I
use here array iterator from <src>/contrib/array)

CREATE FUNCTION IS_MEMBER(char(32)) RETURNS bool
AS '
DECLARE
group_name ALIAS FOR $1;
sel INTEGER;
BEGIN
sel := (SELECT COUNT(*) FROM pg_group WHERE grolist *= (SELECT usesysid
FROM pg_user WHERE usename = current_user) AND CAST(groname AS char(32))
= group_name);

IF sel > 0
THEN
return true;
ELSE
return false;
END IF;
END;
' LANGUAGE 'PLPGSQL';

Now you can do something like

IF IS_MEMBER(''<some_group>'') != true AND USER != ''<definer>''
THEN
return NULL;
END IF;

SET AUTHORIZATION DEFINER;

on top of your function.

Such thing works fine for me, but I prefer to have clear GRANT EXECUTE
syntax with CREATE FUNCTION func (....) AUTH INVOKER/DEFINER; ability.
IMHO setuid functions with control of who can execute them are very
helpful in implementing database logic. Standart SELECT/UPDATE/DELETE
for views/tables often can be weak.
When I some time ago asked about GRANT EXECUTE someone (Peter or Tom i
cannot recall) told something like "you feel free to send patch to
implement such behaviour".

Regards,
Dmitry