Thread: 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/
I hoped to see that and per database allow/deny ctreation of tables to groups/users in PostgreSQL 7.1 but as far as I know it will not be done. Rumen --- jmoschet@netscape.net wrote: > 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/ __________________________________________________ Do You Yahoo!? Yahoo! Shopping - Thousands of Stores. Millions of Products. http://shopping.yahoo.com/