471,083 Members | 1,151 Online
Bytes | Software Development & Data Engineering Community
Post +

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 471,083 software developers and data experts.

insert through function only

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.
---------------------------(end of broadcast)---------------------------
TIP 8: explain analyze is your friend

Nov 23 '05 #1
8 1498
On Mon, May 03, 2004 at 15:12:00 -0700,
Marvin McNett <mm*****@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.

---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to ma*******@postgresql.org)

Nov 23 '05 #2
On Mon, May 03, 2004 at 15:12:00 -0700,
Marvin McNett <mm*****@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.

---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to ma*******@postgresql.org)

Nov 23 '05 #3
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)

---------------------------(end of broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster

Nov 23 '05 #4
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)

---------------------------(end of broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster

Nov 23 '05 #5
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 <mm*****@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.

---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?

http://archives.postgresql.org

Nov 23 '05 #6
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 <mm*****@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.

---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?

http://archives.postgresql.org

Nov 23 '05 #7


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
---------------------------(end of broadcast)---------------------------
TIP 7: don't forget to increase your free space map settings

Nov 23 '05 #8


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
---------------------------(end of broadcast)---------------------------
TIP 7: don't forget to increase your free space map settings

Nov 23 '05 #9

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

8 posts views Thread by Johannes A. Brunner | last post: by
16 posts views Thread by Philip Boonzaaier | last post: by
3 posts views Thread by Andrew Clark | last post: by
6 posts views Thread by rn5a | last post: by

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.