DML Restriction unless through a function

[val]

Hi all;

Is it possible to write a trigger or the ilk that would disallow
direct inserts, updates, or deletes to a table unless it was
being executed through a function? How can I identify that the
DML being issued is being done via a function?

We are writing an API of sorts and for various business logic
aspects, it is necessary that certain checks and sets of data
are processed through the database in a certain method. The use
of functions provides the ability to do this, but I would
definitely like to restrict access to the under lying tables.

If I revoke insert,update,delete privileges on a table, would
the user still be able to affect data manipulation through a
function? Is that another way of getting around this?

TIA

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

http://archives.postgresql.org

[Shridhar Daithankar]

va*@webtribe.net wrote:

Hi all;

Is it possible to write a trigger or the ilk that would disallow
direct inserts, updates, or deletes to a table unless it was
being executed through a function? How can I identify that the
DML being issued is being done via a function?

http://www.postgresql.org/docs/7.4/s...efunction.html

Look for security definer. Grant table access only to user who creates the
function and grant access to function to everybody concerned.

HTH

Shridhar

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

[Bruno Wolff III]

On Wed, Jun 30, 2004 at 13:21:00 +0100,
va*@webtribe.net wrote:

If I revoke insert,update,delete privileges on a table, would
the user still be able to affect data manipulation through a
function? Is that another way of getting around this?

If you use a security definer function where the function creator
has access to the table, but others don't then you can do that.
There are some issues if you start using such functions in views,
but for simple usage it will do what you want.

---------------------------(end of broadcast)---------------------------
TIP 8: explain analyze is your friend

[Adam Witney]

On 30/6/04 2:52 pm, "Bruno Wolff III" <br***@wolff.to> wrote:

On Wed, Jun 30, 2004 at 13:21:00 +0100,
va*@webtribe.net wrote:

If I revoke insert,update,delete privileges on a table, would
the user still be able to affect data manipulation through a
function? Is that another way of getting around this?

If you use a security definer function where the function creator
has access to the table, but others don't then you can do that.
There are some issues if you start using such functions in views,
but for simple usage it will do what you want.

Out of interest, what are the issues?

Thanks

adam
--
This message has been scanned for viruses and
dangerous content by MailScanner, and is
believed to be clean.
---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?

http://archives.postgresql.org

[Bruno Wolff III]

On Wed, Jun 30, 2004 at 15:02:33 +0100,
Adam Witney <aw*****@sghms.ac.uk> wrote:

Out of interest, what are the issues?

You should be able to find a more accurate description in the archives, but
my memory is that when you run a security definer function in a view
(this shouldn't apply if it is used as a default for a column in the view) it
runs with the authority of the view creator ran than the function creator.
In practice that probably isn't a problem, since normally they would be
the same person.

---------------------------(end of broadcast)---------------------------
TIP 7: don't forget to increase your free space map settings

[Bruno Wolff III]

On Wed, Jun 30, 2004 at 12:00:44 -0400,
Tom Lane <tg*@sss.pgh.pa.us> wrote:

That doesn't sound right to me at all. A SECURITY DEFINER function is
self contained --- if we ever failed to execute it as the owning user,
that would be a bug, and I'd be pleased to see an example.

I do recall that if you have a function that is *not* SECURITY DEFINER,
and you use it in a view, it will be invoked as the current user, not as
the view creator which is what some people expect. It's fairly easy to
get around this using SECURITY DEFINER, so it's unlikely that we'll
change it ...

That is what I was probably thinking of.

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

http://archives.postgresql.org

[Tom Lane]

Bruno Wolff III <br***@wolff.to> writes:

Out of interest, what are the issues?

You should be able to find a more accurate description in the archives, but
my memory is that when you run a security definer function in a view
(this shouldn't apply if it is used as a default for a column in the view) it
runs with the authority of the view creator ran than the function creator.

That doesn't sound right to me at all. A SECURITY DEFINER function is
self contained --- if we ever failed to execute it as the owning user,
that would be a bug, and I'd be pleased to see an example.

I do recall that if you have a function that is *not* SECURITY DEFINER,
and you use it in a view, it will be invoked as the current user, not as
the view creator which is what some people expect. It's fairly easy to
get around this using SECURITY DEFINER, so it's unlikely that we'll
change it ...

regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 8: explain analyze is your friend