From: christopher-piker@uiowa.edu (Chris Piker)
Subject: Do update permissions require select permissions
Date: 25 Nov 2003 18:00:35 -0800
Organization: http://groups.google.com
Lines: 36
Message-ID: <14d07696.0311251800.7f5855b7@posting.google.com>
Content-Type: text/plain; charset=ISO-8859-1
Content-Transfer-Encoding: 8bit
To: pgsql-sql@postgresql.org

I have run across a problem while creating a database with row level
permission
checking via views and triggers.  The just of which is update does not
work
for a user unless they also have select permissions.  Select
permissions are handled with a view, while insert, update and delete
permissions are handled via triggers.

The simplest table which demonstrates the problem is:

   create table data_t (id int4, stuff text);
   grant insert,update,delete on data_t to public;

As the table creator issue the query:

   insert into data_t values (1, 'Some stuff');

Now as some other user one can do:

   update data set stuff = 'other stuff';

And it works okay.  But the following fails:

   update data set stuff = 'yet other stuff' where id = 1;

Why is this?  

The reason that I need to not let the user have select on the table is
that
they are only allowed to select certian rows and there is a view that
takes
care of this.  For insert update delete there are trigger functions. 
I know that rules on the view would handle the problem but I am using
inheretence and the query plans grow to over 270 rows when rules on
views on
base tables are put together.