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
byChrist Jesus." Philippians 4:19