Debian Bug#72084: Broken permissions required with foreign keys (fwd)

I've seen mention of this on the list, but I can't see it mentioned in TODO
from current CVS.

------- Forwarded Message

Date: Wed, 20 Sep 2000 11:17:52 +0200
From: Martijn van de Streek <mvdstreek(at)cistron(dot)nl>
To: submit(at)bugs(dot)debian(dot)org
Subject: Bug#72084: Broken permissions required with foreign keys

Package: postgresql
Version: 7.0.2-2
Severity: important

If I create a table with a foreign key, inserts into that table won't work
unless I give the user/group UPDATE permission on the table the foreign key
refers to.

This behaviour doesn't seem logical and/or safe (I give 'SELECT only' access
for a reason).

The same thing happens in 7.0.2-5

Martijn

Example:
- --------
blurgh=# CREATE TABLE A(ID SERIAL,
PRIMARY KEY(ID));
blurgh=# CREATE TABLE B(ID SERIAL, B INT,
PRIMARY KEY(ID), FOREIGN KEY(B) REFERENCES A ON DELETE RESTRICT
);

blurgh=# CREATE GROUP A;
blurgh=# CREATE GROUP B;

blurgh=# GRANT ALL ON B TO GROUP A;
blurgh=# GRANT SELECT ON A TO GROUP A;

blurgh=# CREATE USER 'test' IN GROUP A;

blurgh=# INSERT INTO A(ID) VALUES(1);
blurgh=# INSERT INTO A(ID) VALUES(2);
blurgh=# INSERT INTO A(ID) VALUES(3);

blurgh=# \c blurgh test

blurgh=> INSERT INTO B(B) VALUES(1);
ERROR: a: Permission denied.

blurgh=# \c blurgh postgres
blurgh=# GRANT SELECT,UPDATE ON A TO GROUP A;
blurgh=# \c blurgh test

blurgh=> INSERT INTO B(B) VALUES(1);
INSERT 6178592 1

- -- System Information
Debian Release: 2.2
Architecture: i386
Kernel: Linux beeblebrox 2.2.17pre13 #1 SMP Fri Jul 21 05:48:45 CEST 2000 i686

Versions of packages postgresql depends on:
ii debianutils 1.13.3 Miscellaneous utilities specific t
ii libc6 2.1.3-13 GNU C Library: Shared libraries an
ii libncurses5 5.0-6 Shared libraries for terminal hand
ii libpgsql2 7.0.2-2 Shared library libpq.so.2 for Post
ii libreadline4 4.1-1 GNU readline and history libraries
ii postgresql-client 7.0.2-2 Front-end programs for PostgreSQL
ii procps 1:2.0.6-5 The /proc file system utilities.

- -- Configuration Files:
/etc/cron.d/postgresql changed [not included]
/etc/postgresql/pg_hba.conf changed [not included]
/etc/postgresql/postmaster.init changed [not included]
- --
Don't die on the motorway. The moon would freeze, the plants would die.
I couldn't cope if you crashed today. All the things I forgot to say.
- Radiohead, Killer Cars

------- End of Forwarded Message

--
Oliver Elphick Oliver(dot)Elphick(at)lfix(dot)co(dot)uk
Isle of Wight http://www.lfix.co.uk/oliver
PGP: 1024R/32B8FAA1: 97 EA 1D 47 72 3F 28 47 6B 7E 39 CC 56 E4 C1 47
GPG: 1024D/3E1D0C1C: CA12 09E0 E8D5 8870 5839 932A 614D 4C34 3E1D 0C1C
========================================
"But my God shall supply all your need according to his
riches in glory by Christ Jesus." Philippians 4:19