Postgres Pro
Downloads
Home   >   mailing lists

Thread: insert through function only

insert through function only

From
Marvin McNett
Date:
How do I go about ensuring that data is only added to a table through a
function?  I've tried granting execute persission on the function which
inserts data, but can't get it to work unless the user also has insert
permission on the table.  I don't want the user to be able to
arbitrarily insert data.

Re: insert through function only

From
Bruno Wolff III
Date:
On Mon, May 03, 2004 at 15:12:00 -0700,
  Marvin McNett <mmcnett@cs.ucsd.edu> wrote:
> How do I go about ensuring that data is only added to a table through a
> function?  I've tried granting execute persission on the function which
> inserts data, but can't get it to work unless the user also has insert
> permission on the table.  I don't want the user to be able to
> arbitrarily insert data.

You need to use SECURITY DEFINER so that the function runs with the
access of its definer instead of its invoker.

Re: insert through function only

From
Marvin McNett
Date:
Thanks Bruno,

This is exactly what I needed to know.

Cordially,
Marvin

Bruno Wolff III wrote:
> On Mon, May 03, 2004 at 15:12:00 -0700,
>   Marvin McNett <mmcnett@cs.ucsd.edu> wrote:
>
>>How do I go about ensuring that data is only added to a table through a
>>function?  I've tried granting execute persission on the function which
>>inserts data, but can't get it to work unless the user also has insert
>>permission on the table.  I don't want the user to be able to
>>arbitrarily insert data.
>
>
> You need to use SECURITY DEFINER so that the function runs with the
> access of its definer instead of its invoker.

Re: insert through function only

From
Alvaro Herrera
Date:
On Mon, May 03, 2004 at 03:12:00PM -0700, Marvin McNett wrote:
> How do I go about ensuring that data is only added to a table through a
> function?  I've tried granting execute persission on the function which
> inserts data, but can't get it to work unless the user also has insert
> permission on the table.  I don't want the user to be able to
> arbitrarily insert data.

Was the function created with SECURITY DEFINER?

--
Alvaro Herrera (<alvherre[a]dcc.uchile.cl>)
"Porque francamente, si para saber manejarse a uno mismo hubiera que
rendir examen... ¿Quién es el machito que tendría carnet?"  (Mafalda)

Re: insert through function only

From
Brent Wood
Date:

On Mon, 3 May 2004, Marvin McNett wrote:

> How do I go about ensuring that data is only added to a table through a
> function?  I've tried granting execute persission on the function which
> inserts data, but can't get it to work unless the user also has insert
> permission on the table.  I don't want the user to be able to
> arbitrarily insert data.
>

Maybe something along these lines?

Have the function grant insert to the user prior to inserting & revoke it
afterwards. The user will have generic insert access while the function is
running, but if this is a problem, have the function lock the table
during the insert operation, then revoke the insert permision before
unlocking the table.

Brent Wood