Обсуждение: ...
Hi, I am trying to figure out how to restrict user access to a database to a few defined functions, so that users could use the database (and update it) but only through a set of procedures, written for exemple in C and using the Server Programming interface. I am new to PostgreSQL so sorry if this question is irrelevant to this list. Thanks in advance, Emmanuel Motchane
Emmanuel Motchane wrote: > > Hi, > > I am trying to figure out how to restrict user access to a database to > a few defined functions, so that users could use the database (and update > it) but only through a set of procedures, written for exemple in C and > using the Server Programming > interface. If this is some kind of security measure, it won't work because in the extreme case anybody can just open a socket and send the appropriate protocol down it (like you can with any client server database). Otherwise I suggest you ask your user's politely or see if the postgres grant permissions can do what you want.
One problem you may have with this is that if a function accesses some table, the user who uses that function must also have permissions on the table. I have a similar problem. I'd like to give permissions on a view, but not on the table underlying the view (the view serves to filter out some records the user shouldn't see). I can't give permission to use view without giving permission to use the table. ---------------------------------------------------------------- Travis Bauer | CS Grad Student | IU |www.cs.indiana.edu/~trbauer ---------------------------------------------------------------- On Tue, 23 May 2000, Emmanuel Motchane wrote: > Hi, > > I am trying to figure out how to restrict user access to a database to > a few defined functions, so that users could use the database (and update > it) but only through a set of procedures, written for exemple in C and > using the Server Programming > interface. > > I am new to PostgreSQL so sorry if this question is irrelevant to this > list. > > Thanks in advance, > > Emmanuel Motchane > > > >
On Wed, May 24, 2000 at 12:45:59PM -0500, Travis Bauer wrote: > One problem you may have with this is that if a function accesses some > table, the user who uses that function must also have permissions on the > table. I have a similar problem. I'd like to give permissions on a view, > but not on the table underlying the view (the view serves to filter out > some records the user shouldn't see). I can't give permission to use view > without giving permission to use the table. Have you tried it? This is one of the things views are for. The view accesses it's underlying tables as the user who created the view, as far as I recall. I, for example, have an entire database where every table has a 'pub' boolean. I've created views that return only rows with pub = 't', and given the anonymous user (which the web server connect as) select privileges only on the view. idas=> select count(*) from urls; count ----- 23 (1 row) idas=> select count(*) from urls_p; count ----- 23 (1 row) idas=> select count(*) from urls; ERROR: urls: Permission denied. idas=> \c - anonymous connecting as new user: anonymous idas=> select count(*) from urls_p; count ----- 23 (1 row) idas=> Ross -- Ross J. Reedstrom, Ph.D., <reedstrm@rice.edu> NSBRI Research Scientist/Programmer Computer and Information Technology Institute Rice University, 6100 S. Main St., Houston, TX 77005
Uh, I cut & pasted the transcript in two pieces to get the selects in the same order, and messed up. The error happens _after_ connecting as anonymous, not before. Ross On Wed, May 24, 2000 at 01:09:58PM -0500, Ross J. Reedstrom wrote: > > idas=> select count(*) from urls; > ERROR: urls: Permission denied. > idas=> \c - anonymous > connecting as new user: anonymous > idas=> select count(*) from urls_p; > count > ----- > 23 > (1 row) > > idas=>
Ooops. I have to withdraw that comment. I spent hours the other day beating my head against the wall over this. I was sure that it didn't work . . . Sorry, ---------------------------------------------------------------- Travis Bauer | CS Grad Student | IU |www.cs.indiana.edu/~trbauer ---------------------------------------------------------------- On Wed, 24 May 2000, Ross J. Reedstrom wrote: > On Wed, May 24, 2000 at 12:45:59PM -0500, Travis Bauer wrote: > > One problem you may have with this is that if a function accesses some > > table, the user who uses that function must also have permissions on the > > table. I have a similar problem. I'd like to give permissions on a view, > > but not on the table underlying the view (the view serves to filter out > > some records the user shouldn't see). I can't give permission to use view > > without giving permission to use the table. > > Have you tried it? This is one of the things views are for. The view > accesses it's underlying tables as the user who created the view, as far > as I recall. I, for example, have an entire database where every table > has a 'pub' boolean. I've created views that return only rows with pub = > 't', and given the anonymous user (which the web server connect as) > select privileges only on the view. >