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

Re: Triggers after a rule

From: Jan Wieck <JanWieck(at)Yahoo(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-29 16:19:33
Message-ID: (view raw, whole thread or download thread mbox)
Lists: 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
> UPDATE contact set pause=NEW.pause where username=USER;

You probably want that to be

   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.


# 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 #

In response to

pgsql-general by date

Next:From: Keary SuskaDate: 2005-09-29 17:23:22
Subject: Re: DBI/DBD::Pg mem. use goes exponential
Previous:From: Tom LaneDate: 2005-09-29 15:44:42
Subject: Re: How can I check if my cursor statement is using index

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