From kyle@actarg.com Wed Apr 25 15:56:54 2001 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 f3PFus370252 for ; Wed, 25 Apr 2001 11:56:54 -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 JAA02697 for ; Wed, 25 Apr 2001 09:56:50 -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 JAA06514 for ; Wed, 25 Apr 2001 09:57:03 -0600 Message-ID: <3AE6F3CF.BE4F1C72@actarg.com> Date: Wed, 25 Apr 2001 09:57:03 -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: pgsql-sql@postgresql.org Subject: RI permission problem Content-Type: multipart/mixed; boundary="------------F02609D58039EE1728540DB9" X-Archive-Number: 200104/378 X-Sequence-Number: 2736 This is a multi-part message in MIME format. --------------F02609D58039EE1728540DB9 Content-Type: multipart/alternative; boundary="------------0DBAABF4C4B849C6732418A3" --------------0DBAABF4C4B849C6732418A3 Content-Type: text/plain; charset=us-ascii Content-Transfer-Encoding: 7bit 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 --------------0DBAABF4C4B849C6732418A3 Content-Type: text/html; charset=us-ascii Content-Transfer-Encoding: 7bit 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
  --------------0DBAABF4C4B849C6732418A3-- --------------F02609D58039EE1728540DB9 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 --------------F02609D58039EE1728540DB9-- From peter_e@gmx.net Wed Apr 25 17:17:43 2001 Received: from mailout03.sul.t-online.com (mailout03.sul.t-online.com [194.25.134.81]) by postgresql.org (8.11.3/8.11.1) with ESMTP id f3PHHh305381 for ; Wed, 25 Apr 2001 13:17:43 -0400 (EDT) (envelope-from peter_e@gmx.net) Received: from fwd05.sul.t-online.com by mailout03.sul.t-online.com with smtp id 14sSuw-0002vc-04; Wed, 25 Apr 2001 19:17:30 +0200 Received: from peter.localdomain (520083510237-0001@[217.80.146.114]) by fmrl05.sul.t-online.com with esmtp id 14sSv0-0G9D4SC; Wed, 25 Apr 2001 19:17:34 +0200 Date: Wed, 25 Apr 2001 19:28:55 +0200 (CEST) From: Peter Eisentraut To: Kyle cc: Subject: Re: RI permission problem In-Reply-To: <3AE6F3CF.BE4F1C72@actarg.com> Message-ID: MIME-Version: 1.0 Content-Type: TEXT/PLAIN; charset=US-ASCII X-Sender: 520083510237-0001@t-dialin.net X-Archive-Number: 200104/379 X-Sequence-Number: 2737 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 From kyle@actarg.com Wed Apr 25 18:45:07 2001 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 f3PIj7341419 for ; Wed, 25 Apr 2001 14:45:07 -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 MAA05555; Wed, 25 Apr 2001 12:45:06 -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 MAA06618; Wed, 25 Apr 2001 12:45:06 -0600 Message-ID: <3AE71B32.1040CF9@actarg.com> Date: Wed, 25 Apr 2001 12:45:06 -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="------------E431319F52E8E2418C3ADD15" X-Archive-Number: 200104/383 X-Sequence-Number: 2741 This is a multi-part message in MIME format. --------------E431319F52E8E2418C3ADD15 Content-Type: text/plain; charset=us-ascii Content-Transfer-Encoding: 7bit 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. --------------E431319F52E8E2418C3ADD15 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 --------------E431319F52E8E2418C3ADD15-- From sszabo@megazone23.bigpanda.com Wed Apr 25 19:34:10 2001 Received: from megazone23.bigpanda.com ([216.136.151.41]) by postgresql.org (8.11.3/8.11.1) with ESMTP id f3PJYA358741 for ; Wed, 25 Apr 2001 15:34:10 -0400 (EDT) (envelope-from sszabo@megazone23.bigpanda.com) Received: from localhost (sszabo@localhost) by megazone23.bigpanda.com (8.11.2/8.11.2) with ESMTP id f3PJY1r16027; Wed, 25 Apr 2001 12:34:05 -0700 (PDT) Date: Wed, 25 Apr 2001 12:34:01 -0700 (PDT) From: Stephan Szabo To: Kyle cc: Peter Eisentraut , pgsql-sql@postgresql.org Subject: Re: RI permission problem In-Reply-To: <3AE71B32.1040CF9@actarg.com> Message-ID: MIME-Version: 1.0 Content-Type: TEXT/PLAIN; charset=US-ASCII X-Archive-Number: 200104/385 X-Sequence-Number: 2743 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. From peter_e@gmx.net Wed Apr 25 20:20:21 2001 Received: from mailout04.sul.t-online.com (mailout04.sul.t-online.com [194.25.134.18]) by postgresql.org (8.11.3/8.11.1) with ESMTP id f3PKKL374160 for ; Wed, 25 Apr 2001 16:20:21 -0400 (EDT) (envelope-from peter_e@gmx.net) Received: from fwd05.sul.t-online.com by mailout04.sul.t-online.com with smtp id 14sVlO-0007kY-05; Wed, 25 Apr 2001 22:19:50 +0200 Received: from peter.localdomain (520083510237-0001@[217.80.146.114]) by fmrl05.sul.t-online.com with esmtp id 14sVlS-0dAdwuC; Wed, 25 Apr 2001 22:19:54 +0200 Date: Wed, 25 Apr 2001 22:31:16 +0200 (CEST) From: Peter Eisentraut To: Kyle cc: Subject: Re: RI permission problem In-Reply-To: <3AE71B32.1040CF9@actarg.com> Message-ID: MIME-Version: 1.0 Content-Type: MULTIPART/MIXED; BOUNDARY="-1463811484-746282116-988230676=:762" X-Sender: 520083510237-0001@t-dialin.net X-Archive-Number: 200104/388 X-Sequence-Number: 2746 This message is in MIME format. The first part should be readable text, while the remaining parts are likely unreadable without MIME-aware tools. Send mail to mime@docserver.cac.washington.edu for more info. ---1463811484-746282116-988230676=:762 Content-Type: TEXT/PLAIN; charset=US-ASCII 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 ---1463811484-746282116-988230676=:762 Content-Type: APPLICATION/x-gzip; name="ri-perm.patch.gz" Content-Transfer-Encoding: BASE64 Content-ID: Content-Description: Patch Content-Disposition: attachment; filename="ri-perm.patch.gz" H4sICJgx5zoAA3JpLXBlcm0ucGF0Y2gA7VltT+M4EP4cfoUP6aQWUmI776xA QmzZrYB21YJ0p9MqyiZumyUkxXEL/PsbJzSYXrhj2YOj4iqUxpN4PDOPnxlP 6WUxu9lFPAkETyYTxoudaGPv5z8bw8MRGicp20XGNL9kxozn31kkCmM2Ka5S I1oUPM/F3ajgkfEtjC5YFhtzkaSFEcbCeGCUvtjgDIZskWQTxOGrSPIMkR1q bsTJeIw6EepwOVxxZmtr66FEoxgTA5sGpQg7uwT+XK3U0+l0Gl+1DGojinex v4ttqVD9lAv4FtF9y0GlQKqpBB6C+84GQlo0DbmmxSwNsnmaFn8Me8HpwW9B //z0uPv7CG0j8vWDfO9bnqealhTyrVKQZEIDwYeNbaQNkhjui3DBgnkSK6Lx RZBfZ4zLGXLSkI0ZZ5lIwrSXCTbhibgdZeGsmOZisGCcJzFDe0jwOSunNLrk errvmYpLHgaBU7uEtpCYz1K2Uw0M+QWGcJaC6ikLZ0E+YxlviUnIJ6XHR8fB sHvSPzjtBgfDT/3BVx3185M8umiXviy9gOlDloYC8P3ExFHOL1uV3nZnH661 p9psuZpELA5F2NkXEykq55av5Gkc8Px69R05KI1/1H2CPaLDxb8PAIwsEPlu HQJNM7aqbwhGH5YBgJlgKMqzQvAQsFs+NaSD99iBPeDaecF4L25V3mvaqJYs I1E+qVQkY9QafekF7IZFs9bVLA0zXS4XLMJ0zorqvtxbOsJt9Msekm8PjoOP 3ZPuWbddadEgfJNWdzgcDHW0ea+vjcYhkDVGSYZKoNhtEIVFFMYsAMWt9ma7 jNSKnUt3GuwcJ1lSTFuqJUe9fm/0+YEl/cFZ77B7Z0o95Wm2NKJGqKcTYirb FkYOiCyzRu1xjtX331+fb4Q4NtjpYtV0V4o8onIuzGIEu3oNuJex6ybugbim 3hP42Rgr6rs6MbGtxMrEgLxJyePknM9AP0NiyhC7SQpRFZISuYgVL8tUWLtm qrxvYur5l48HP8VUUPxmmKra0gihafmAl22pEEoGmC5d46pJTA+yjekTxS0L Q9mwCH4vldPyTZ3YWA2BTSiI4BT1KDmjKYsu0Djnr8/NR6ooURkxgip6ePYM bnIGJ4EkEs8so3BsBv8L0LkPyaJp+V+LZaDkxoWl73YuWiR5BSfqoM27mRra vGC30j6YBlFO0/sox2jM80t4sKk342rbsI9th1a4/lMhXaUduN1Bj0LXeRYD YYqx3GFwXCW2Zz+tyr9YTqgsag5fSQvfU2jhEEgWDqX/1/eVWLmymLtEPca5 JqQQ18L/dQq5f7gayNlqINvLLfFjh4LGxNMcJgcY6Tpqj+L60La4/mrnWatf hxrqYfDBw0o7TTwTg8h8N92nT+Ao5FOVAL4lRbb3Dg+4BRNS35toRZtsaYbQ gXzvO2q+931PpxjfF6m/dp6XoYimgbidMYWV84LBwRryWxxczRm/fX1OgtW4 Mr32hmLTBZHSWK8FJ1+ualGo5zpclHRMAXKdUkLfMWnfQlfaZEsjhBQ7Ei+l d6HUkhDazhpXVPDBBB8sT3XLg1xEfWu92PvsikplF04tQpUQWDbkNMsx3yc5 YzYO5+lzG9N/nZ9N5jQDaUOWtWzldEhtTHW4+D/yE69aaF+dj5Ykn+Wbqg8W uGVb7nrx8QWrqeNCKnbU/0BRF7oA6lrvtJouGVIXse2/I+xq5/lihK3N+ROk GzyjTB4AAA== ---1463811484-746282116-988230676=:762-- From kyle@actarg.com Mon May 7 16:23:47 2001 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-- From peter_e@gmx.net Mon May 7 19:37:00 2001 Received: from mailout04.sul.t-online.com (mailout04.sul.t-online.com [194.25.134.18]) by postgresql.org (8.11.3/8.11.1) with ESMTP id f47Jb0A55123 for ; Mon, 7 May 2001 15:37:00 -0400 (EDT) (envelope-from peter_e@gmx.net) Received: from fwd07.sul.t-online.de by mailout04.sul.t-online.com with smtp id 14wqoI-0000up-06; Mon, 07 May 2001 21:36:46 +0200 Received: from peter.localdomain (520083510237-0001@[212.185.245.39]) by fmrl07.sul.t-online.com with esmtp id 14wqoK-29ALRYC; Mon, 7 May 2001 21:36:48 +0200 Date: Mon, 7 May 2001 21:36:47 +0200 (CEST) From: Peter Eisentraut To: Kyle cc: Subject: Re: RI permission problem In-Reply-To: <3AF6CBCB.124ABA1D@actarg.com> Message-ID: MIME-Version: 1.0 Content-Type: TEXT/PLAIN; charset=US-ASCII X-Sender: 520083510237-0001@t-dialin.net X-Archive-Number: 200105/51 X-Sequence-Number: 2852 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 From tgl@sss.pgh.pa.us Tue May 8 04:56:32 2001 Received: from sss.pgh.pa.us (sss.pgh.pa.us [216.151.103.158]) by postgresql.org (8.11.3/8.11.1) with ESMTP id f484uWA40302 for ; Tue, 8 May 2001 00:56:32 -0400 (EDT) (envelope-from tgl@sss.pgh.pa.us) Received: from sss2.sss.pgh.pa.us (tgl@localhost [127.0.0.1]) by sss.pgh.pa.us (8.11.3/8.11.3) with ESMTP id f484txR27414; Tue, 8 May 2001 00:55:59 -0400 (EDT) To: Peter Eisentraut cc: Kyle , pgsql-sql@postgresql.org Subject: Re: RI permission problem In-reply-to: References: Comments: In-reply-to Peter Eisentraut message dated "Mon, 07 May 2001 21:36:47 +0200" Date: Tue, 08 May 2001 00:55:59 -0400 Message-ID: <27411.989297759@sss.pgh.pa.us> From: Tom Lane X-Archive-Number: 200105/54 X-Sequence-Number: 2855 Peter Eisentraut 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