Referential integrity and access permission.

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

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

Your name : Marco Morandini
Your email address : morandini(at)aero(dot)polimi(dot)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