Received: from ajax2.sovam.com (ajax2.sovam.com [194.67.1.173]) by postgresql.org (8.11.3/8.11.4) with ESMTP id f7P68af55491 for ; Sat, 25 Aug 2001 02:08:37 -0400 (EDT) (envelope-from dmitry@taurussoft.org) Received: from ts16-a18.dial.sovam.com ([195.239.3.18]:1644 "HELO taurussoft.org" ident: "TIMEDOUT2" whoson: "-unregistered-" smtp-auth: TLS-CIPHER: TLS-PEER: ) by ajax2.sovam.com with SMTP id ; Sat, 25 Aug 2001 10:08:37 +0400 Received: (qmail 15695 invoked from network); 25 Aug 2001 06:08:05 -0000 Received: from flame-in-night.taurussoft.org (192.168.107.1) by kitezh.taurussoft.org with SMTP; 25 Aug 2001 06:08:05 -0000 Received: (qmail 2524 invoked from network); 25 Aug 2001 06:08:59 -0000 Received: from localhost (127.0.0.1) by localhost with SMTP; 25 Aug 2001 06:08:59 -0000 Subject: Re: Execute permsissions on fuctions From: "Dmitry G. Mastrukov" =?koi8-r?Q?=E4=CD=C9=D4=D2=C9=CA_?= =?koi8-r?Q?=E7=C5=CE=CE=C1=C4=D8=C5=D7=C9=DE_?= =?koi8-r?Q?=ED=C1=D3=D4=D2=C0=CB=CF=D7?= To: "Zot O'Connor" Cc: "pgsql-sql@postgresql.org" In-Reply-To: <3B8550E9.3BC47EA7@zotconsulting.com> References: <3B8550E9.3BC47EA7@zotconsulting.com> Content-Type: text/plain Content-Transfer-Encoding: 7bit X-Mailer: Evolution/0.12.99 (Preview Release) Date: 25 Aug 2001 10:08:58 +0400 Message-Id: <998719739.409.52.camel@flame-in-night> Mime-Version: 1.0 X-Archive-Number: 200108/310 X-Sequence-Number: 4557 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 /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('''') != true AND USER != '''' 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