I've been searching the lists and found a similar question but there have no 
replies.

What I would like to be able to do in Postgres is give users/groups the 
ability to execute stored procedures on a per procedure basis.

I am looking into the Rules system, thinking that this is acheiveable through 
the use of query rewriting, dummy tables and _insert, _delete, _update rules. 
 

I see in the internals.ps (docs directory) file there is an example (2.5.8 / 
pg 49-50) of creating a dummy table and a rule that gets executed when 
someone issues a SELECT on that dummy table. 

The rule defines the actual query which is executed, so the *user* is none 
the wiser.  That's a start.

It seems that keeping track of *dummy* tables could get hairy and somone 
cleaning up could accidentally remove a dummy table and disable a whole mess 
of things inadvertenty. 

It would appear to be possible to set ACLs on a dummy table to only allow 
certain users/groups to execute the select, insert, update or delete on the 
dummy table itself. Thereby using the built-in postgres security to intervene 
and before the rules are ever executed. 

There is an example of the use of a rule to prevent an insert on a table that 
uses a special naming convention, <tablename>_insert that automatically gets 
executed when the INSERT is issued. (and similarly for the 
<tablename>_select, <tablename>_update and <tablename>_delete)
(PostgreSQL: Introduction and Concepts / Momjian, pg. 149)

Now if I'm way off and these scenarios are not possible, it would be nice to 
have ACL's on entries in the pg_proc table or an additional entry in pg_class 
for each stored proc/function (but that seems unlikely as it seems this would 
affect other parts of the postgres subsystem) and have the 'traffic-cop' 
check the permissions on the functions for us.

Thanks.

John Moschetto
attendee OSDN/OSDB Summit 
Oct 30-31 2000 - Hayes Mansion
San Jose, California

__________________________________________________________________
Get your own FREE, personal Netscape Webmail account today at 
http://webmail.netscape.com/

Tue, 26 Dec 2000 12:24:08 -0500
jmoschet@netscape.net
Permissions on Stored Procedures