Thread: Triggers after a rule

Triggers after a rule

From
Wijnand Wiersma
Date:
Hi list,

I am currently trying to give normal users some read access to some
tables in the database. I also need to give update access to one
column of one table.

I have the table contact, the user should not be able to read or
update anything in it, except for his own record. So I created the
view v_my_account. When the user selects * from it he only sees his
own record. That works great. I also made a rule:
CREATE RULE update_v_my_account AS ON UPDATE TO v_my_account
DO INSTEAD
UPDATE contact set pause=NEW.pause where username=USER;

This does not work since there are some triggers on the contact table
and the trigger function selects the contact table and I don't want to
give the user access to that.

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.

Does anyone know a nice workaround?

Wijnand
(I hope I was clear enough)

Re: Triggers after a rule

From
Richard Huxton
Date:
Wijnand Wiersma wrote:
> I have the table contact, the user should not be able to read or
> update anything in it, except for his own record. So I created the
> view v_my_account. When the user selects * from it he only sees his
> own record. That works great. I also made a rule:
> CREATE RULE update_v_my_account AS ON UPDATE TO v_my_account
> DO INSTEAD
> UPDATE contact set pause=NEW.pause where username=USER;
>
> This does not work since there are some triggers on the contact table
> and the trigger function selects the contact table and I don't want to
> give the user access to that.
>
> 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.

--
   Richard Huxton
   Archonet Ltd

Re: Triggers after a rule

From
Wijnand Wiersma
Date:
2005/9/28, Richard Huxton <dev@archonet.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.

Wijnand

Re: Triggers after a rule

From
Richard Huxton
Date:
Wijnand Wiersma wrote:
> 2005/9/28, Richard Huxton <dev@archonet.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

Re: Triggers after a rule

From
Wijnand Wiersma
Date:
2005/9/28, Richard Huxton <dev@archonet.com>:
> 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

Damn, I really really missed that one :-(
Thank you very much Richard!

Wijnand

Re: Triggers after a rule

From
Jan Wieck
Date:
On 9/28/2005 5:44 AM, Wijnand Wiersma wrote:

> Hi list,
>
> I am currently trying to give normal users some read access to some
> tables in the database. I also need to give update access to one
> column of one table.
>
> I have the table contact, the user should not be able to read or
> update anything in it, except for his own record. So I created the
> view v_my_account. When the user selects * from it he only sees his
> own record. That works great. I also made a rule:
> CREATE RULE update_v_my_account AS ON UPDATE TO v_my_account
> DO INSTEAD
> UPDATE contact set pause=NEW.pause where username=USER;

You probably want that to be

   DO INSTEAD
   UPDATE contact set pause=NEW.pause where username=OLD.username;

This will still not allow the user to update other's records, because
the internal querytree for the update will have the views where clause
attached too and that limits the result set already.

>
> This does not work since there are some triggers on the contact table
> and the trigger function selects the contact table and I don't want to
> give the user access to that.

You want the trigger functions to be declared SECURITY DEFINER.


Jan

--
#======================================================================#
# It's easier to get forgiveness for being wrong than for being right. #
# Let's break this rule - forgive me.                                  #
#================================================== JanWieck@Yahoo.com #