Skip site navigation (1) Skip section navigation (2)

Re: Triggers after a rule

From: Richard Huxton <dev(at)archonet(dot)com>
To: Wijnand Wiersma <wwiersma(at)gmail(dot)com>
Cc: pgsql-general(at)postgresql(dot)org
Subject: Re: Triggers after a rule
Date: 2005-09-28 11:11:13
Message-ID: 433A7A51.1070805@archonet.com (view raw or flat)
Thread:
Lists: pgsql-general
Wijnand Wiersma wrote:
> 2005/9/28, Richard Huxton <dev(at)archonet(dot)com>:
> 
>>Wijnand Wiersma wrote:
>>
>>>I thought that when a rule is applied the triggers which are triggerd
>>>would also have the same userid as the rule query, but I was wrong.
>>
>>Can you show the (cut down) contents of your trigger? It's difficult to
>>see what you mean. I can't think of any way a trigger can provide
>>results to the user, so any selects within it should be safe enough.
> 
> 
> The trigger function is very very long and touches tables the normal
> user should not touch. I can't grant select, update and insert to the
> users, there is only one user who has the rights to do that. Normal
> users should work with the given views and those views are made so
> that users only see information that is relevant for them.
> 
> database=> update v_my_account set pause='yes';
> ERROR:  permission denied for relation contact
> CONTEXT:  PL/pgSQL function "activate_contact" line 5 at select into variables
> 
> The rule update_v_my_account works and runs as the special user since
> that user is owner of v_my_account. It is just strange that the
> triggers run as my normal user.

Ah! Now I understand.

The solution is to mark your trigger functions with the "SECURITY 
DEFINER" attribute and create them as a user who can access relation 
"contact".
http://www.postgresql.org/docs/8.0/static/sql-createfunction.html

Of course, you'll need to make sure your trigger function doesn't allow 
your view restrictions to be bypassed.

Now - why does the trigger cause problems? Because it's accessing the 
"raw" table and not the view. The view doesn't add privileges to a user, 
the user is granted access to the view.

Ideally, you could define the trigger on the view, and just let all 
access go through there, but that's not possible (at the moment, anyway).

-- 
   Richard Huxton
   Archonet Ltd

In response to

Responses

pgsql-general by date

Next:From: Richard HuxtonDate: 2005-09-28 11:13:27
Subject: Re: trigger self recursion
Previous:From: Martijn van OosterhoutDate: 2005-09-28 11:11:03
Subject: Re: SQL command to dump the contents of table failed: PQendcopy()

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group