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

insert/upate/delete permissions on views

From: Dani Mezher <dany(dot)mezher(at)fi(dot)usj(dot)edu(dot)lb>
To: pgsql-bugs(at)postgresql(dot)org
Subject: insert/upate/delete permissions on views
Date: 2004-05-10 20:53:19
Message-ID: 111A067B-A2C4-11D8-B847-000A95C4FBC6@fi.usj.edu.lb (view raw or flat)
Thread:
Lists: pgsql-bugs
Hello,

    I would like to report a bug in postgresql 7.4.1 regarding the 
permission for insert/update/delete on views.
I actually created a view based on a table to control user access to 
individual tuples. I revoked all privileges
on the underlying table and created insert/update/delete rules over the 
view. It seems that even if I revoke update/insert
permissions from the users, they can still update/insert data into the 
views and therefore into the table.

   An alternative way is to  use triggers and stored procedures but the 
postgresql rule systems seems more elegant
and intuitive. Is there a patch to fix this problem??? Is it fixed in 
postgresql 7.4.2???

Here is an illustration of the problem.
==================================
baronsgroup=# create table test(code integer);
CREATE TABLE
baronsgroup=# create view vtest as select * from test;
CREATE VIEW
baronsgroup=# create rule update as on update to vtest do instead 
update test set code=new.code where code=old.code;
CREATE RULE
baronsgroup=# revoke all on test from spiro;
REVOKE
baronsgroup=# revoke all on vtest from spiro;
REVOKE
baronsgroup=# grant select on vtest to spiro;
GRANT
baronsgroup=# insert into test values(2);
INSERT 134507 1
baronsgroup=# \c baronsgroup spiro
You are now connected to database "baronsgroup" as user "spiro".
baronsgroup=> select * from vtest;
  code
------
     2
(1 row)

baronsgroup=> select * from test;
ERROR:  permission denied for relation test
ERROR:  permission denied for relation test
baronsgroup=> update test set code=20;
ERROR:  permission denied for relation test
ERROR:  permission denied for relation test
baronsgroup=> \z vtest;
        Access privileges for database "baronsgroup"
  Schema | Table |            Access privileges
--------+-------+-----------------------------------------
  public | vtest | {dani=a*r*w*d*R*x*t*/dani,spiro=r/dani}
(1 row)

baronsgroup=> select has_table_privilege('vtest','update');
  has_table_privilege
---------------------
  f
(1 row)

baronsgroup=> update vtest set code=20;
UPDATE 1
baronsgroup=> select * from vtest;
  code
------
    20
(1 row)

==========================


Regards
Dani Mezher




Responses

pgsql-bugs by date

Next:From: Tom LaneDate: 2004-05-10 23:31:08
Subject: Re: insert/upate/delete permissions on views
Previous:From: Tom LaneDate: 2004-05-08 22:09:54
Subject: Re: unique index problems

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