Received: from deborah.paradise.net.nz (deborah.paradise.net.nz [203.96.152.32]) by postgresql.org (8.11.3/8.11.4) with ESMTP id f7P8KBf38780 for ; Sat, 25 Aug 2001 04:20:20 -0400 (EDT) (envelope-from csawtell@paradise.net.nz) Received: from there (202-0-37-152.cable.paradise.net.nz [202.0.37.152]) by deborah.paradise.net.nz (Postfix) with SMTP id E04BC1F9E33; Sat, 25 Aug 2001 20:20:01 +1200 (NZST) Content-Type: text/plain; charset="us-ascii" From: Christopher Sawtell To: "Zot O'Connor" Subject: Re: Execute permsissions on fuctions Date: Sat, 25 Aug 2001 20:19:48 +1200 X-Mailer: KMail [version 1.2.3] Cc: "pgsql-sql@postgresql.org" References: <3B8550E9.3BC47EA7@zotconsulting.com> <20010824051425.22A6C1FA01E@deborah.paradise.net.nz> <3B872CA4.C888A771@zotconsulting.com> In-Reply-To: <3B872CA4.C888A771@zotconsulting.com> MIME-Version: 1.0 Content-Transfer-Encoding: 8bit Message-Id: <20010825082001.E04BC1F9E33@deborah.paradise.net.nz> X-Archive-Number: 200108/311 X-Sequence-Number: 4558 On Sat, 25 Aug 2001 16:42, Zot O'Connor wrote: > Christopher Sawtell wrote: > > On Fri, 24 Aug 2001 06:52, Zot O'Connor wrote: > > > 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? > > > > By not GRANTing the webuser write permission to the tables in question. > > I guess I should have been more clear. I want the webuser to > be able to upadte the table VIA the function, and but not directly. > > Currently this does not work, since CREATE FUNCTION acts as any > old function: > > zot=# CREATE TABLE testperms (id int4); > CREATE > zot=# CREATE FUNCTION effect_testperms (int4) RETURNS int4 AS 'INSERT INTO > testperms (id) VALUES ($1); RETURN 1;' LANGUAGE 'sql'; SELECT > effect_testperms(1); > effect_testperms > ------------------ > 1 > (1 row) > zot=# \connect - nobody > You are now connected as new user nobody. > zot=> select * from testperms; > ERROR: testperms: Permission denied. > zot=> SELECT effect_testperms(2); > ERROR: testperms: Permission denied. > zot=> > > So it appears that FUCNTION effect_testperms() is taking on > the perms of the user calling it. > > So it may be a generic issue with Postgres that other DBMS's > effectively run the stored procedure as SUID-like, in that it > takes on the perms of the owner of the procedure, not the > user calling the procedure.