Received: from sisyphus.owlscreech.com (owlscreech.com [207.174.31.35]) by mail.postgresql.org (8.11.1/8.11.1) with ESMTP id eALMvEs30553 for ; Tue, 21 Nov 2000 17:57:14 -0500 (EST) (envelope-from brian@owlscreech.com) Received: from [192.168.0.2] ([63.229.251.51]) by sisyphus.owlscreech.com (8.9.3/8.8.7) with ESMTP id PAA09881; Tue, 21 Nov 2000 15:54:54 -0700 User-Agent: Microsoft-Entourage/9.0.2509 Date: Tue, 21 Nov 2000 15:54:49 -0700 Subject: Re: Permissions Broken in 7.0.x? From: Brian Powell To: Tom Lane CC: Message-ID: In-Reply-To: <2953.974841258@sss.pgh.pa.us> Mime-version: 1.0 Content-type: text/plain; charset="US-ASCII" Content-transfer-encoding: 7bit X-Archive-Number: 200011/180 X-Sequence-Number: 211 Tom, Thank you for the reply. I have found another issue that I am curious about: I have a table with foreign key constraint to itself (a parent_id field). I have a view to the table with permissions for a user to insert into the view (of course, a rule exists to insert into the real table). The problem is that it fails because of the constraint! Any way around this? Or, am I sort of stuck granting all of these privileges to the table so that triggers, constraints, etc. will work? Thanks, Brian > Brian Powell writes: >> % psql bobdb >> psql => create table test ( >> name char(32) not null, >> age int4 >> ); >> psql => revoke all on test from tester; >> CHANGE >> psql => select * from test; >> ERROR: test: Permission denied. > > Yeah, that's a bug --- confusion about the default permissions set. > After doing an explicit GRANT or REVOKE on a table you own, you need to > grant permissions to yourself again; the default assumption that you > have all permissions gets lost :-(. This is fixed for 7.1. > >> Additionally, if I have a trigger on a table which sets the updated_date and >> updated_by on any update, and I have a view with a rule which passes any >> update to the table (thus calling the trigger), I get a permission denied. >> I am supposing that the user updating the view does not have privileges to >> the trigger? How would I grant them privileges to the trigger? > > Don't think there's a way to do that right now. There's been talk of > making triggers run "setuid" to the creating user, but it's not been > done AFAIR. --Brian