Gert Pache ([EMAIL PROTECTED]) reports a bug with a severity of 1
The lower the number the more severe it is.
Short Description
permission-error in tables with referential integrity
Long Description
Suppose you have a table A, which no user should be able to alter
Table B is a table, the user tom schould be able to insert/delete/select/update data.
It isn't possible to grant tom only select-rights on B, as postgre complains missing
permissions for tom. (see code exapmple)
I think that's a devasting problem, since if i also grant the update right on A to
make him able to insert/delete/update his record in B,
he also gets the delete right (as update/delete isn't distinguished in postgre). An
furthermore he gets also the insert-right (the other bug-report)
Posgre-Version: 7.0.1
Sample Code
-- Superuser creates Table A and B with rights as explanied above
delme=# create TABLE a ( int id primary key, value varchar );
ERROR: Unable to locate type name 'id' in catalog
delme=# create TABLE a ( id int primary key, value varchar );
NOTICE: CREATE TABLE/PRIMARY KEY will create implicit index 'a_pkey' for table 'a'
CREATE
delme=# insert into a values (0,'wert0');
INSERT 76484 1
delme=# create TABLE b (id int, ref int references a);
NOTICE: CREATE TABLE will create implicit trigger(s) for FOREIGN KEY check(s)
CREATE
delme=# revoke all on a from public;
CHANGE
delme=# grant select on a to pgtester;
CHANGE
delme=# grant update, insert, select, delete on b to pgtester;
CHANGE
delme=# \dp a
Access permissions for database "delme"
Relation | Access permissions
----------+--------------------
a | {"=","pgtester=r"}
(1 row)
delme=# \dp b
Access permissions for database "delme"
Relation | Access permissions
----------+----------------------
b | {"=","pgtester=arw"}
(1 row)
-- user pgtester isn't able to insert records in table B
-- (Because he has no permissions on A(!))
delme=> insert into b values ( 0, 0 );
ERROR: a: Permission denied.
-- superuser also grants update to pgtester
delme=# grant update on a to pgtester;
CHANGE
-- now pgdelme=> insert into b values ( 0, 0 );
INSERT 76450 1
tester is able to insert into b
No file was uploaded with this report
