Referential integrity and access permission.

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

                        POSTGRESQL BUG REPORT TEMPLATE
============================================================================



Your name  : Marco Morandini
Your email address : morandini@aero.polimi.it


System Configuration
---------------------
  Architecture    : Cyrix MII 300

  Operating System   : Linux 2.2.14 ELF, SuSE 6.4 distribution

  PostgreSQL version  :   PostgreSQL-7.0.2

  Compiler used   : gcc 2.95.2


Please enter a FULL description of your problem:
------------------------------------------------
I have two database users, postgres and httpd.
The backend is run by the system user postgres.
I also have two tables, a and b.
Table b references table a.
The database user httpd has SELECT permission on a
and ALL permissions on b, but is not able
to modify table b.

Everything works fine if httpd has select,update
permissions on a.



Please describe a way to repeat the problem.   Please try to provide a
concise reproducible example, if at all possible:
----------------------------------------------------------------------
createdb -U postgres testdb
createuser -U postgres -D -A httpd
psql -U postgres testdb
create table a( id int2 primary key);
create table b(id int2 references a, name text);
insert into a values (1);
insert into b values (1,'aaa');
grant select on a to httpd;
grant all on b to httpd;
\q
psql -U httpd testdb
update b set name = 'bbb' where id = 1;

and get the following error message:
ERROR:  a: Permission denied.




If you know how this problem might be fixed, list the solution below:
---------------------------------------------------------------------
I think the error is raised from

RI_FKey_check in src/backend/utils/adt/ri_triggers.c

where a least one

select ??? from a FOR UPDATE

is performed, but I don't have the knowledge
to understand if this is a bug and how (and why)
table a should be locked.


Marco Morandini