pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedRI permission problem
8+ messages / 4 participants
[nested] [flat]
* RI permission problem
@ 2001-04-25 15:57 Kyle <kyle@actarg.com>
2001-04-25 17:28 ` Re: RI permission problem Peter Eisentraut <peter_e@gmx.net>
0 siblings, 1 reply; 8+ messages in thread
From: Kyle @ 2001-04-25 15:57 UTC (permalink / raw)
To: pgsql-sql
Here's an interesting security problem: Suppose I create two tables:
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
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
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: RI permission problem
2001-04-25 15:57 RI permission problem Kyle <kyle@actarg.com>
@ 2001-04-25 17:28 ` Peter Eisentraut <peter_e@gmx.net>
2001-04-25 18:45 ` Re: RI permission problem Kyle <kyle@actarg.com>
0 siblings, 1 reply; 8+ messages in thread
From: Peter Eisentraut @ 2001-04-25 17:28 UTC (permalink / raw)
To: Kyle <kyle@actarg.com>; +Cc: pgsql-sql
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
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: RI permission problem
2001-04-25 15:57 RI permission problem Kyle <kyle@actarg.com>
2001-04-25 17:28 ` Re: RI permission problem Peter Eisentraut <peter_e@gmx.net>
@ 2001-04-25 18:45 ` Kyle <kyle@actarg.com>
2001-04-25 19:34 ` Re: RI permission problem Stephan Szabo <sszabo@megazone23.bigpanda.com>
2001-04-25 20:31 ` Re: RI permission problem Peter Eisentraut <peter_e@gmx.net>
0 siblings, 2 replies; 8+ messages in thread
From: Kyle @ 2001-04-25 18:45 UTC (permalink / raw)
To: Peter Eisentraut <peter_e@gmx.net>; +Cc: pgsql-sql
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.
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
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: RI permission problem
2001-04-25 15:57 RI permission problem Kyle <kyle@actarg.com>
2001-04-25 17:28 ` Re: RI permission problem Peter Eisentraut <peter_e@gmx.net>
2001-04-25 18:45 ` Re: RI permission problem Kyle <kyle@actarg.com>
@ 2001-04-25 19:34 ` Stephan Szabo <sszabo@megazone23.bigpanda.com>
1 sibling, 0 replies; 8+ messages in thread
From: Stephan Szabo @ 2001-04-25 19:34 UTC (permalink / raw)
To: Kyle <kyle@actarg.com>; +Cc: Peter Eisentraut <peter_e@gmx.net>; pgsql-sql
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.
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: RI permission problem
2001-04-25 15:57 RI permission problem Kyle <kyle@actarg.com>
2001-04-25 17:28 ` Re: RI permission problem Peter Eisentraut <peter_e@gmx.net>
2001-04-25 18:45 ` Re: RI permission problem Kyle <kyle@actarg.com>
@ 2001-04-25 20:31 ` Peter Eisentraut <peter_e@gmx.net>
2001-05-07 16:22 ` Re: RI permission problem Kyle <kyle@actarg.com>
1 sibling, 1 reply; 8+ messages in thread
From: Peter Eisentraut @ 2001-04-25 20:31 UTC (permalink / raw)
To: Kyle <kyle@actarg.com>; +Cc: pgsql-sql
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
Attachments:
ri-perm.patch.gz (application/x-gzip, 1.3K) download
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: RI permission problem
2001-04-25 15:57 RI permission problem Kyle <kyle@actarg.com>
2001-04-25 17:28 ` Re: RI permission problem Peter Eisentraut <peter_e@gmx.net>
2001-04-25 18:45 ` Re: RI permission problem Kyle <kyle@actarg.com>
2001-04-25 20:31 ` Re: RI permission problem Peter Eisentraut <peter_e@gmx.net>
@ 2001-05-07 16:22 ` Kyle <kyle@actarg.com>
2001-05-07 19:36 ` Re: RI permission problem Peter Eisentraut <peter_e@gmx.net>
0 siblings, 1 reply; 8+ messages in thread
From: Kyle @ 2001-05-07 16:22 UTC (permalink / raw)
To: Peter Eisentraut <peter_e@gmx.net>; +Cc: pgsql-sql
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
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: RI permission problem
2001-04-25 15:57 RI permission problem Kyle <kyle@actarg.com>
2001-04-25 17:28 ` Re: RI permission problem Peter Eisentraut <peter_e@gmx.net>
2001-04-25 18:45 ` Re: RI permission problem Kyle <kyle@actarg.com>
2001-04-25 20:31 ` Re: RI permission problem Peter Eisentraut <peter_e@gmx.net>
2001-05-07 16:22 ` Re: RI permission problem Kyle <kyle@actarg.com>
@ 2001-05-07 19:36 ` Peter Eisentraut <peter_e@gmx.net>
2001-05-08 04:55 ` Re: RI permission problem Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 1 reply; 8+ messages in thread
From: Peter Eisentraut @ 2001-05-07 19:36 UTC (permalink / raw)
To: Kyle <kyle@actarg.com>; +Cc: pgsql-sql
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
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: RI permission problem
2001-04-25 15:57 RI permission problem Kyle <kyle@actarg.com>
2001-04-25 17:28 ` Re: RI permission problem Peter Eisentraut <peter_e@gmx.net>
2001-04-25 18:45 ` Re: RI permission problem Kyle <kyle@actarg.com>
2001-04-25 20:31 ` Re: RI permission problem Peter Eisentraut <peter_e@gmx.net>
2001-05-07 16:22 ` Re: RI permission problem Kyle <kyle@actarg.com>
2001-05-07 19:36 ` Re: RI permission problem Peter Eisentraut <peter_e@gmx.net>
@ 2001-05-08 04:55 ` Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 0 replies; 8+ messages in thread
From: Tom Lane @ 2001-05-08 04:55 UTC (permalink / raw)
To: Peter Eisentraut <peter_e@gmx.net>; +Cc: Kyle <kyle@actarg.com>; pgsql-sql
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
^ permalink raw reply [nested|flat] 8+ messages in thread
end of thread, other threads:[~2001-05-08 04:55 UTC | newest]
Thread overview: 8+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2001-04-25 15:57 RI permission problem Kyle <kyle@actarg.com>
2001-04-25 17:28 ` Peter Eisentraut <peter_e@gmx.net>
2001-04-25 18:45 ` Kyle <kyle@actarg.com>
2001-04-25 19:34 ` Stephan Szabo <sszabo@megazone23.bigpanda.com>
2001-04-25 20:31 ` Peter Eisentraut <peter_e@gmx.net>
2001-05-07 16:22 ` Kyle <kyle@actarg.com>
2001-05-07 19:36 ` Peter Eisentraut <peter_e@gmx.net>
2001-05-08 04:55 ` Tom Lane <tgl@sss.pgh.pa.us>
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