Received: from lin.local.lan ([216.221.221.2]) by mail.postgresql.org (8.11.1/8.11.1) with ESMTP id f0VF4Sq44185 for ; Wed, 31 Jan 2001 10:04:28 -0500 (EST) (envelope-from najm@mondo-live.com) Received: from mondo-live.com (IDENT:najm@ll20.local2.lan [192.168.2.20]) by lin.local.lan (8.9.3/8.8.7) with ESMTP id NAA03358; Wed, 31 Jan 2001 13:58:34 GMT Message-ID: <3A797C29.62D812E1@mondo-live.com> Date: Thu, 01 Feb 2001 10:09:29 -0500 From: Najm Hashmi X-Mailer: Mozilla 4.76 [en] (X11; U; Linux 2.2.14-5.0 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/477 X-Sequence-Number: 1153 Hey Rick, I am sure there are more elegant solutions but I have a simple one. Write a trigger that will grant the permissions before insert or update and and revoke all privileges after the insert or update. -Najm 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