grant/revoke bug with delete/update

Hi,

first I'm sorry to not fill the form, I'm too lazy, and it's not platform
nor version dependent AFAIK.

I recently posted a question (on Feb 23rd) to pgsql-sql concerning the
fact that update and insert are considered the same thing when you modify
permissions with grant and revoke. (Maybe it was the wrong place to post
it.)

for example a "grant delete" also grants "update" which is completely
wrong. More importantly the user is not informed, and this could lead to
VERY IMPORTANT SECURITY PROBLEMS, like someone who should only be able to
update existing records, have the permission to delete all records...

I've read postgresql documentation, especially the grant and revoke
manpages, and I've found no mention of this bug, which is IMHO a Big
Mistake (tm).

attached to this message you'll find a patch for version 6.5.2 wich
differentiate delete and update, because before they were considered as
"write". The patch only modifies .c .y and .h files, but no documentation.

the new acl rights look like: arRdu
a for append
r for read
R for rules
d for delete
u for update

instead of: arwR
a for append
r for read
w for update AND delete
R for rules

This patch seems to work at least with what I've tested, you'll find a
test session at the end of this message.

I hope this patch will help and that it will be easy to incorporate it in
7.0, which I haven't the time to do for now.

And for the bug report I posted on Feb 23rd on "drop user" which keeps the
user's acl in the database, and the deleted user id being reused, I've not
done anything, but I consider this a major problem. Please consider it for
a next version.

Because I'm not an expert, I suggest you remove gram.c before applying the
patch, in order for this file to be generated again from gram.y, but maybe
this is not necessary.

I'd be very pleased if some people could test this more than I can,
because I don't use postgresql intensively with special permissions.

I'm not sure for some parts of the patch, especially in execMain.c
so if a postgresql hacker could examine it, this would be fine.

dump of test session:
---------------------

------- CUT -------

template1=> create database db;
CREATEDB
template1=> create user john;
CREATE USER
template1=> \connect db
connecting to new database: db
db=> create table t (id INT4, name TEXT);
CREATE
db=> \z
Database = db
+----------+--------------------------+
| Relation | Grant/Revoke Permissions |
+----------+--------------------------+
| t | |
+----------+--------------------------+
db=> grant all on t to john;
CHANGE
db=> \z
Database = db
+----------+--------------------------+
| Relation | Grant/Revoke Permissions |
+----------+--------------------------+
| t | {"=","john=arduR"} |
+----------+--------------------------+
db=> \connect db john
connecting to new database: db as user: john
db=> insert into t (id, name) values (1, 'xxx');
INSERT 18560 1
db=> update t set name = 'yyy' where id=1;
UPDATE 1
db=> select * from t;
id|name
--+----
1|yyy
(1 row)

db=> delete from t;
DELETE 1
db=> select * from t;
id|name
--+----
(0 rows)

db=> insert into t (id, name) values (1, 'xxx');
INSERT 18561 1
db=> \connect db postgres
connecting to new database: db as user: postgres
db=> revoke update on t from john;
CHANGE
db=> \z
Database = db
+----------+--------------------------+
| Relation | Grant/Revoke Permissions |
+----------+--------------------------+
| t | {"=","john=ardR"} |
+----------+--------------------------+
db=> \connect db john;
connecting to new database: db as user: john
db=> insert into t (id, name) values (2, 'yyy');
INSERT 18592 1
db=> update t set name='modified by john' where id=2;
ERROR: t: Permission denied.
db=> delete from t where id=2;
DELETE 1
db=> select * from t;
id|name
--+----
1|xxx
(1 row)

db=> \connect db postgres
connecting to new database: db as user: postgres
db=> revoke insert on t from john;
CHANGE
db=> \connect db john;
connecting to new database: db as user: john
db=> \z
Database = db
+----------+--------------------------+
| Relation | Grant/Revoke Permissions |
+----------+--------------------------+
| t | {"=","john=rdR"} |
+----------+--------------------------+
db=> insert into t (id, name) values (3, 'I try to insert something');
ERROR: t: Permission denied.
db=> delete from t;
DELETE 1
db=> select * from t;
id|name
--+----
(0 rows)

db=> \connect db postgres
connecting to new database: db as user: postgres
db=> insert into t (id, name) values (1, 'xxx');
INSERT 18624 1
db=> \connect db john;
connecting to new database: db as user: john
db=> update t set name='john' where id =1;
ERROR: t: Permission denied.
db=> \connect db postgres
connecting to new database: db as user: postgres
db=> revoke delete on t from john;
CHANGE
db=> grant update on t to john;
CHANGE
db=> \connect db john;
connecting to new database: db as user: john
db=> delete from t;
ERROR: t: Permission denied.
db=> update t set name='john' where id=1;
UPDATE 1
db=> select * from t;
id|name
--+----
1|john
(1 row)

------- CUT -------

Thank you for reading.

bye,

Jerome ALET - alet(at)unice(dot)fr - http://cortex.unice.fr/~jerome
Faculte de Medecine de Nice - http://noe.unice.fr - Tel: 04 93 37 76 30
28 Avenue de Valombrose - 06107 NICE Cedex 2 - FRANCE