Обсуждение: Functions as a Security Layer
Functions, with some databases, are used as security layers so that a user that wouldn't otherwise have read/write privileges on a table can perform some sort of controlled update. I've written a function to serve as a type of counter to update a table called "users". This function takes one (relevant) parameter: userID. This then updates the counter with that user's id. However, I am getting a permissions error because the users who run the function don't have write access to the counter table. My question is, can I somehow give permissions to the function, but not to the user to protect the counter table from being modified in any ways I don't want? Thanks, -Ben __________________________________________ Yahoo! DSL Something to write home about. Just $16.99/mo. or less. dsl.yahoo.com
Benjamin Stookey <jamstooks@yahoo.com> writes: > My question is, can I somehow give permissions to the > function, but not to the user to protect the counter > table from being modified in any ways I don't want? Label the function SECURITY DEFINER. regards, tom lane
--- Benjamin Stookey <jamstooks@yahoo.com> wrote: > Functions, with some databases, are used as security > layers so that a user that wouldn't otherwise have > read/write privileges on a table can perform some sort > of controlled update. > > I've written a function to serve as a type of counter > to update a table called "users". This function takes > one (relevant) parameter: userID. This then updates > the counter with that user's id. However, I am getting > a permissions error because the users who run the > function don't have write access to the counter table. > > My question is, can I somehow give permissions to the > function, but not to the user to protect the counter > table from being modified in any ways I don't want? > > Thanks, > -Ben > http://www.postgresql.org/docs/8.1/static/sql-createfunction.html Check out the difference between "security invoker" and "security definer". If the creating user has the necessary access to the underlying objects you'll get the behavior you desire. Shelby Cain __________________________________________ Yahoo! DSL Something to write home about. Just $16.99/mo. or less. dsl.yahoo.com
Thanks so much. That did the trick! --- Tom Lane <tgl@sss.pgh.pa.us> wrote: > Benjamin Stookey <jamstooks@yahoo.com> writes: > > My question is, can I somehow give permissions to > the > > function, but not to the user to protect the > counter > > table from being modified in any ways I don't > want? > > Label the function SECURITY DEFINER. > > regards, tom lane > > ---------------------------(end of > broadcast)--------------------------- > TIP 5: don't forget to increase your free space map > settings > __________________________________________ Yahoo! DSL Something to write home about. Just $16.99/mo. or less. dsl.yahoo.com