Thread: DML Restriction unless through a function

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

val@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/static/sql-createfunction.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

On Wed, Jun 30, 2004 at 13:21:00 +0100,
  val@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.

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

> On Wed, Jun 30, 2004 at 13:21:00 +0100,
> val@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.

On Wed, Jun 30, 2004 at 15:02:33 +0100,
  Adam Witney <awitney@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.

On Wed, Jun 30, 2004 at 12:00:44 -0400,
  Tom Lane <tgl@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.

Bruno Wolff III <bruno@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