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;
BEGINsel := (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 > 0THEN    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>'' THENreturn 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