Received: from nts1.rtp.epa.gov (nts1.rtp.epa.gov [134.67.208.208]) by mail.postgresql.org (8.11.1/8.11.1) with ESMTP id f0VFh1q47422 for ; Wed, 31 Jan 2001 10:43:01 -0500 (EST) (envelope-from volpe.mark@epamail.epa.gov) Received: from epamail.epa.gov (scanmeifyoudare.rtp.epa.gov [134.67.139.14]) by nts1.rtp.epa.gov (8.9.3/8.9.3) with ESMTP id KAA00721; Wed, 31 Jan 2001 10:47:39 -0500 (EST) Message-ID: <3A783283.6B408FD9@epamail.epa.gov> Date: Wed, 31 Jan 2001 10:42:59 -0500 From: Mark Volpe X-Mailer: Mozilla 4.75 [en] (X11; U; Linux 2.2.17-21mdk i686) X-Accept-Language: en MIME-Version: 1.0 To: Rick Delaney CC: pgsql-sql@postgresql.org Subject: Re: Permissions for foreign keys References: <3A77613D.184BB8D8@consumercontact.com> Content-Type: text/plain; charset=us-ascii Content-Transfer-Encoding: 7bit X-Archive-Number: 200101/479 X-Sequence-Number: 1155 The problem is fixed in the 7.1 beta series. Rick Delaney wrote: > > 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