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




---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to majordomo (AT) postgresql (DOT) org

Dani Mezher <dany.mezher (AT) fi (DOT) usj.edu.lb> writes:
Yes. See
http://www.postgresql.org/docs/7.4/s...#RELEASE-7-4-2
The actual bug is mentioned here:
http://www.postgresql.org/docs/7.4/s...ase-7-3-6.html

regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faqs/FAQ.html