Обсуждение: 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