pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Kyle <kyle@actarg.com>
To: Peter Eisentraut <peter_e@gmx.net>
Cc: pgsql-sql@postgresql.org
Subject: Re: RI permission problem
Date: Mon, 07 May 2001 10:22:35 -0600
Message-ID: <3AF6CBCB.124ABA1D@actarg.com> (raw)
References: <Pine.LNX.4.30.0104252229480.762-101000@peter.localdomain>

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

Attachments:

kyle.vcf (text/x-vcard, 184B) download 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

view thread (8+ messages)  latest in thread

Message-ID: <3AF6CBCB.124ABA1D@actarg.com>
Permalink:  ../3AF6CBCB.124ABA1D@actarg.com/
Also on:    postgresql.org/message-id/3AF6CBCB.124ABA1D@actarg.com

 · 

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients from your mail client; the link fills in
  the headers below: reply via email
  Some clients, webmail especially, drop In-Reply-To and References from
  a link, and the reply then starts a new thread: use the mbox below.

  To: pgsql-sql@postgresql.org
  Cc: kyle@actarg.com, peter_e@gmx.net
  Subject: Re: RI permission problem
  In-Reply-To: <3AF6CBCB.124ABA1D@actarg.com>
  References: <Pine.LNX.4.30.0104252229480.762-101000@peter.localdomain> <3AF6CBCB.124ABA1D@actarg.com>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox