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@cistron.nl>
To:      submit@bugs.debian.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.Elphick@lfix.co.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



Wed, 20 Sep 2000 14:16:57 +0100
"Oliver Elphick" <olly@lfix.co.uk>
Debian Bug#72084: Broken permissions required with foreign keys (fwd)