Received: from actarg.com (host25.actarg.com [209.180.91.25] (may be forged)) by postgresql.org (8.11.3/8.11.1) with ESMTP id f47GNlA70375 for ; Mon, 7 May 2001 12:23:47 -0400 (EDT) (envelope-from kyle@actarg.com) Received: from chi.actarg.com (IDENT:root@chi.actarg.com [192.168.2.16]) by actarg.com (8.9.3/8.9.3) with ESMTP id KAA04302; Mon, 7 May 2001 10:22:26 -0600 Received: from actarg.com (IDENT:kyle@localhost [127.0.0.1]) by chi.actarg.com (8.9.3/8.9.3) with ESMTP id KAA24430; Mon, 7 May 2001 10:22:35 -0600 Message-ID: <3AF6CBCB.124ABA1D@actarg.com> Date: Mon, 07 May 2001 10:22:35 -0600 From: Kyle Organization: Action Target Inc. X-Mailer: Mozilla 4.7 [en] (X11; I; Linux 2.2.12-20 i686) X-Accept-Language: en MIME-Version: 1.0 To: Peter Eisentraut CC: pgsql-sql@postgresql.org Subject: Re: RI permission problem References: Content-Type: multipart/mixed; boundary="------------E0430B2DD337F513DB9A9203" X-Archive-Number: 200105/47 X-Sequence-Number: 2848 This is a multi-part message in MIME format. --------------E0430B2DD337F513DB9A9203 Content-Type: multipart/alternative; boundary="------------D5D7D5926F7BEFEA9E91C445" --------------D5D7D5926F7BEFEA9E91C445 Content-Type: text/plain; charset=us-ascii Content-Transfer-Encoding: 7bit Peter Eisentraut wrote: > Kyle writes: > > > Peter Eisentraut wrote: > > > > > Kyle writes: > > > > > > > Is there a way to get this to work without granting update to table b? > > > > > > Update to 7.1.] > > > > I'm on 7.1. Should an RI trigger under 7.1 run as the DBA or as the current > > user? > > Okay, we missed a few cases. Try the attached patch. > OK, here's another similar one. Should this work? (sorry there's really a little more here than you absolutely need, but it demonstrates the problem) drop view atab_v1; drop view atab_v2; drop view atab_v3; drop view atab_v4; drop table atab; drop function func_atab (); drop function func_v1 (); drop function func_v2 (); create table atab ( f1 int4 ); insert into atab (f1) values (1); insert into atab (f1) values (2); insert into atab (f1) values (3); create view atab_v1 as select * from atab; create view atab_v2 as select * from atab; create function func_atab () returns numeric as ' select sum(f1) from atab; ' language 'sql'; create function func_v1 () returns numeric as ' select sum(f1) from atab_v1; ' language 'sql'; create function func_v2 () returns numeric as ' select sum(f1) from atab_v2; ' language 'sql'; create view atab_v3 as select *,func_v1() from atab_v2; create view atab_v4 as select *,func_atab() from atab_v2; grant select on atab_v2 to kyle; grant select on atab_v3 to kyle; grant select on atab_v4 to kyle; Now as user Kyle, try to select from atab_v3 or atab_v4. Both give permission denied because no explicit permission is given to the view/table underlying the summing function. Shouldn't the select access to the view trickle down to subordinate select functions? Kyle --------------D5D7D5926F7BEFEA9E91C445 Content-Type: text/html; charset=us-ascii Content-Transfer-Encoding: 7bit Peter Eisentraut wrote:
Kyle writes:

> Peter Eisentraut wrote:
>
> > Kyle writes:
> >
> > > Is there a way to get this to work without granting update to table b?
> >
> > Update to 7.1.]
>
> I'm on 7.1.  Should an RI trigger under 7.1 run as the DBA or as the current
> user?

Okay, we missed a few cases.  Try the attached patch.
 

OK, here's another similar one.  Should this work?  (sorry there's really a little more here than you absolutely need, but it demonstrates the problem)

drop view atab_v1;
drop view atab_v2;
drop view atab_v3;
drop view atab_v4;
drop table atab;
drop function func_atab ();
drop function func_v1 ();
drop function func_v2 ();

create table atab (
    f1  int4
);

insert into atab (f1) values (1);
insert into atab (f1) values (2);
insert into atab (f1) values (3);

create view atab_v1 as select * from atab;
create view atab_v2 as select * from atab;

create function func_atab () returns numeric as '
    select sum(f1) from atab;
    ' language 'sql';

create function func_v1 () returns numeric as '
    select sum(f1) from atab_v1;
    ' language 'sql';

create function func_v2 () returns numeric as '
    select sum(f1) from atab_v2;
    ' language 'sql';

create view atab_v3 as select *,func_v1() from atab_v2;
create view atab_v4 as select *,func_atab() from atab_v2;

grant select on atab_v2 to kyle;
grant select on atab_v3 to kyle;
grant select on atab_v4 to kyle;

Now as user Kyle, try to select from atab_v3 or atab_v4.  Both give permission denied because no explicit permission is given to the view/table underlying the summing function.

Shouldn't the select access to the view trickle down to subordinate select functions?

Kyle
  --------------D5D7D5926F7BEFEA9E91C445-- --------------E0430B2DD337F513DB9A9203 Content-Type: text/x-vcard; charset=us-ascii; name="kyle.vcf" Content-Transfer-Encoding: 7bit Content-Description: Card for Kyle Content-Disposition: attachment; filename="kyle.vcf" begin:vcard n:Bateman;Kyle x-mozilla-html:FALSE org:Action Target Inc. adr:;;;;;; version:2.1 email;internet:kyle@actarg.com title:President x-mozilla-cpt:;0 fn:Kyle Bateman end:vcard --------------E0430B2DD337F513DB9A9203--