Thread

permission-error in tables with referential integrity

PostgreSQL Bugs List <pgsql-bugs@postgresql.org> — 2000-10-15T19:36:46Z

Gert Pache (uhx2@rz.uni-karlsruhe.de) 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