Re: Functions as a Security Layer

--- Benjamin Stookey <jamstooks(at)yahoo(dot)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