Re: Triggers after a rule

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(at)Yahoo(dot)com #