Received: from edge.consumercontact.com (dante.consumercontact.com.202.167.209.in-addr.arpa [209.167.202.10] (may be forged)) by mail.postgresql.org (8.11.1/8.11.1) with ESMTP id f0V0oBq71149 for ; Tue, 30 Jan 2001 19:50:11 -0500 (EST) (envelope-from rick@consumercontact.com) Received: from consumercontact.com (obiwan.consumercontact.com [192.168.1.54]) by edge.consumercontact.com (8.11.0/8.11.0) with ESMTP id f0V0lfE30987; Tue, 30 Jan 2001 19:47:41 -0500 Message-ID: <3A77613D.184BB8D8@consumercontact.com> Date: Tue, 30 Jan 2001 19:50:05 -0500 From: Rick Delaney X-Mailer: Mozilla 4.7 [en] (Win95; U) X-Accept-Language: en MIME-Version: 1.0 To: pgsql-sql@postgresql.org Subject: Permissions for foreign keys Content-Type: text/plain; charset=us-ascii Content-Transfer-Encoding: 7bit X-Archive-Number: 200101/462 X-Sequence-Number: 1138 I'm using 7.0 and have noticed that I need to grant SELECT and UPDATE permissions on any referentially-related tables. Can/should I get around this? A somewhat contrived example: CREATE TABLE emp ( id integer PRIMARY KEY, salary integer ); CREATE TABLE proj ( id integer PRIMARY KEY, emp_id integer REFERENCES emp ); CREATE TABLE bill ( id integer PRIMARY KEY, proj_id integer REFERENCES proj ); INSERT INTO emp VALUES (1, 100000); INSERT INTO proj VALUES (1, 1); INSERT INTO bill VALUES (1, 1); GRANT ALL ON proj TO someone; Connect as someone: => INSERT INTO proj VALUES (2, 1); ERROR: emp: Permission denied. => UPDATE proj SET id = 2; ERROR: bill: Permission denied. It appears that I need to grant: SELECT,UPDATE on emp to UPDATE or INSERT into proj. SELECT,UPDATE on bill to UPDATE proj. When I grant these permissions, the above statements succeed. If I don't want users to have UPDATE (or even SELECT) access on the other tables (bill and emp), but I want referential integrity, what can I do? -- Rick Delaney