Re: Triggers after a rule - Mailing list pgsql-general

From Jan Wieck
Subject Re: Triggers after a rule
Date
Msg-id 433C1415.9040103@Yahoo.com
Whole thread Raw
In response to Triggers after a rule  (Wijnand Wiersma <wwiersma@gmail.com>)
List pgsql-general
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 #

pgsql-general by date:

Previous
From: Tom Lane
Date:
Subject: Re: How can I check if my cursor statement is using index
Next
From: Keary Suska
Date:
Subject: Re: DBI/DBD::Pg mem. use goes exponential