Permissions on Stored Procedures

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/