Thread: Do update permissions require select permissions

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.

christopher-piker@uiowa.edu (Chris Piker) writes:
> 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?  

Because the latter requires reading, not only writing, the table.

One way to look at it is that if we didn't restrict that, then a person
having only UPDATE rights could nonetheless extract information from the
table.  For example consider
update data set stuff = stuff where id = 42;

This allows the user to determine whether id 42 exists in the table
(by noting the returned UPDATE count).  If you had not given that user
SELECT rights, presumably you don't really want him to be able to find
that out.
        regards, tom lane