Thread: GRANTing privileges to a plpgsql function doesn't give required result (pg 7.3.4)

Hi,

I'm implementing a database with very 'restrictive'
privileges. However I've hit a dead end trying to
solve one issue.

When GRANTing execute to a plpgsql function it
appears to run with the priviledge of the user and
NOT with the priviledges of the owner of the function.

This is causing a problem as within the function
an insertion into a table owned by the function owner
is required.

It's not acceptable to grant INSERT on this table
for the function executor as the whole point is about
controlling access to the table within the function.

Does anyone have any suggestions as to what I may be
doing wrong? (Or how can I do it better?)

This is with postgresql 7.3.4

Many thanks,

   -Greg

--

"Greg" <greg.wickham@grangenet.net> writes:
> When GRANTing execute to a plpgsql function it
> appears to run with the priviledge of the user and
> NOT with the priviledges of the owner of the function.

That's what it's supposed to do.  See the SECURITY DEFINER
option of CREATE FUNCTION for the other behavior.

            regards, tom lane

"Greg" <greg.wickham@grangenet.net> writes:

> Hi,
>
> When GRANTing execute to a plpgsql function it
> appears to run with the priviledge of the user and
> NOT with the priviledges of the owner of the function.

Look at the WITH SECURITY DEFINER clause to CREATE FUNCTION (I think
that's where it is).

-Doug