Обсуждение: RI permission problem

create table a (
   pk    int4 primary key,
   aval    text
);

create table b (
    fk    int4 references a (pk) on update cascade,
    bval
);

Then I grant a user update to table a but not to table b.  He should be able to modify all the values in a freely, including the primary key (in practice it is an invoice number that often gets entered incorrectly and must be corrected.)

But the user should not have update privilege to table b (most particularly, bval is privileged).  But I would like the user to be able to update the primary key and hence cascade the update to table b.

Is there a way to get this to work without granting update to table b?

Tom, I understand someone was working on setuid functions.  Is that a long way off?  It would be nifty if triggers could execute with the privileges of the user that created them rather than the user who is executing them.  This would help greatly in closing some security holes like this we are dealing with.

Kyle Bateman
 

Kyle writes:

> Is there a way to get this to work without granting update to table b?

Update to 7.1.

> Tom, I understand someone was working on setuid functions.  Is that a
> long way off?  It would be nifty if triggers could execute with the
> privileges of the user that created them rather than the user who is
> executing them.  This would help greatly in closing some security holes
> like this we are dealing with.

It wouldn't be hard to implement, but there were some disputes about the
particular method of how to do it.  Maybe 7.2.

-- 
Peter Eisentraut   peter_e@gmx.net   http://funkturm.homeip.net/~peter

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?

>
>
> > Tom, I understand someone was working on setuid functions.  Is that a
> > long way off?  It would be nifty if triggers could execute with the
> > privileges of the user that created them rather than the user who is
> > executing them.  This would help greatly in closing some security holes
> > like this we are dealing with.

On Wed, 25 Apr 2001, Kyle wrote:

> 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?

IIRC, only the checks and no actions were changed, not the rest of the  
referential actions.

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.

-- 
Peter Eisentraut   peter_e@gmx.net   http://funkturm.homeip.net/~peter

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.
 

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
 

Kyle writes:

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

I would think not.

-- 
Peter Eisentraut   peter_e@gmx.net   http://funkturm.homeip.net/~peter

Peter Eisentraut <peter_e@gmx.net> writes:
> Kyle writes:
>> Shouldn't the select access to the view trickle down to subordinate select functions?

> I would think not.

I agree with Peter.  If user A creates a view that includes a call of a
function written by user B, does user A want user B's function to run
with all of user A's permissions?  Not if he's adequately paranoid.
The function could do all sorts of damage above and beyond its
advertised task.

The correct solution to this is to allow a function to run with its
owner's permissions (if the owner wants to mark it setuid).  In your
example, func_v1 and func_v2 should have permission to read atab_v1
and atab_v2 in their own right, independently of who calls them.

This does bring up the thought that we might like to control who can
call func_v1 and func_v2, however (by analogy to execute permissions
on Unix programs).  We have ACLs for tables, but none yet for
functions...
        regards, tom lane