Обсуждение: Damn triggers and NEW
Okay, I'm obviously doing something wrong but what?
I have a trigger defined as:
create or replace function cms_user_id_insert_tgr_1 ( )
returns trigger
as '
begin
execute ''
select 1
from
individuals i
,user_roles ur
,roles r
where
r.name = ''''CMS''''
and
ur.role_id = r.id
and
ur.individual_id = i.id
and
i.id = NEW.'' || quote_ident(TG_ARGV[0]) || ''
and
i.active is true'';
if not found then
raise exception ''TRIGGER: UserNotFound'';
end if;
return NEW;
end;
'
language 'plpgsql';
create trigger cms_user_id_insert before insert
on groups
for each row
execute procedure cms_user_id_insert_tgr_1 ( 'principal_user_id' );
And when this trigger gets kicked I get a message about NEW used in a non-rule
query. The postmaster log is below.
I'd appreciate some pointers on this as it appears new/old can't be used in an
execute statement in triggers but that sounds completely wrong.
--
Nigel J. Andrews
2003-06-17 10:09:08 LOG: query: insert into ttacms1.workflow_events (
article_id,
after_status_id,
xml_changes,
event_dt,
notes,
before_status_id,
user_id,
id
)
(select
article_id,
after_status_id,
xml_changes,
ur.role_id = r.id
and
ur.individual_id = i.id
and
i.id = NEW.' || quote_ident( $1 ) || '
and
i.active is true'
2003-06-17 10:27:50 LOG: query: SELECT 0
2003-06-17 10:27:50 LOG: query:
select 1
from
individuals i
,user_roles ur
,roles r
where
r.name = 'CMS'
and
ur.role_id = r.id
and
ur.individual_id = i.id
and
i.id = NEW.principal_user_id
and
i.active is true
2003-06-17 10:27:50 ERROR: NEW used in non-rule query
Nigel J. Andrews wrote: > I'd appreciate some pointers on this as it appears new/old can't be used in an > execute statement in triggers but that sounds completely wrong. > I've tried this before, and unfortunately I think that statement is currently true. I dug in to it one day and it didn't look too easy to fix either (no big surprise there, otherwise someone probably would have already fixed it ;-)) I ended up concluding that, short of a patch to the backend, a C code trigger would be needed. You might be able to do something with pltcl or one of the other PLs though. Joe
On Tue, 17 Jun 2003, Joe Conway wrote: > Nigel J. Andrews wrote: > > I'd appreciate some pointers on this as it appears new/old can't be used in an > > execute statement in triggers but that sounds completely wrong. > > > > I've tried this before, and unfortunately I think that statement is > currently true. I dug in to it one day and it didn't look too easy to > fix either (no big surprise there, otherwise someone probably would have > already fixed it ;-)) > > I ended up concluding that, short of a patch to the backend, a C code > trigger would be needed. You might be able to do something with pltcl or > one of the other PLs though. Ok, thanks Joe. That's what I was thinking I might have to do, a C code version that is. -- Nigel J. Andrews
Joe Conway <mail@joeconway.com> writes:
> Nigel J. Andrews wrote:
>> I'd appreciate some pointers on this as it appears new/old can't be used in an
>> execute statement in triggers but that sounds completely wrong.
> I've tried this before, and unfortunately I think that statement is
> currently true.
The problem's not really specific to either NEW/OLD or to EXECUTE;
AFAICT the issue is just that plpgsql does not do run-time field
selection. A field access has to look like foo.bar where both foo
and bar are simple identifiers; you can't play games wherein the name
bar is determined at runtime.
> I ended up concluding that, short of a patch to the backend, a C code
> trigger would be needed. You might be able to do something with pltcl or
> one of the other PLs though.
I believe you can do this in pltcl --- it doesn't do any pre-parsing
or pre-optimization of the code, so whether the field name is static
or just calculated won't matter to it. Also, I believe it allows you
to inquire about the set of field names belonging to NEW or OLD, which
is another thing that's impossible in plpgsql (and wouldn't do you any
good if it were possible, because of the field-access syntax limitation).
Use the right tool for the job.
regards, tom lane
On Tue, 17 Jun 2003, Tom Lane wrote: > Joe Conway <mail@joeconway.com> writes: > > Nigel J. Andrews wrote: > >> I'd appreciate some pointers on this as it appears new/old can't be used in an > >> execute statement in triggers but that sounds completely wrong. > > > I've tried this before, and unfortunately I think that statement is > > currently true. > > The problem's not really specific to either NEW/OLD or to EXECUTE; > AFAICT the issue is just that plpgsql does not do run-time field > selection. A field access has to look like foo.bar where both foo > and bar are simple identifiers; you can't play games wherein the name > bar is determined at runtime. > > > I ended up concluding that, short of a patch to the backend, a C code > > trigger would be needed. You might be able to do something with pltcl or > > one of the other PLs though. > > I believe you can do this in pltcl --- it doesn't do any pre-parsing > or pre-optimization of the code, so whether the field name is static > or just calculated won't matter to it. Also, I believe it allows you > to inquire about the set of field names belonging to NEW or OLD, which > is another thing that's impossible in plpgsql (and wouldn't do you any > good if it were possible, because of the field-access syntax limitation). > > Use the right tool for the job. Quite. When I floated the idea of doing some server programming in pltcl or plpython the client was unhappy with it so I've therefore just been bashing out stuff in plpgsql until I can slow down a little and think about coding the things in C. Of course, given that plpython seems on it's last legs for now it was probably a wise choice not to go with that particular language. Sorry, for asking another stupid plpgsql question. Unfortunately I hit the problem as I was trying to load up a new development database, having discovered yesterday that at least one function had been written for a changed design without the dev database containing the change and it looking like a time consuming task to revert and check everything when that time was better spent actually loading a new db. -- Nigel J. Andrews
At 04:14 PM 6/17/2003 +0100, Nigel J. Andrews wrote: >Of course, given that plpython seems on it's last legs for now.... Ummm.... (I haven't actually been on one of the nearby planets for the last 18 months, but the effect is much the same.) Tell me more. --Erv
Erv Young <ervyoung@nc.rr.com> writes:
> At 04:14 PM 6/17/2003 +0100, Nigel J. Andrews wrote:
>> Of course, given that plpython seems on it's last legs for now....
> Ummm.... (I haven't actually been on one of the nearby planets for the
> last 18 months, but the effect is much the same.) Tell me more.
You can check our archives (search for "python" a month or two back)
but it seems that the latest Python release removes rexec because it
had some fundamental security holes. That breaks plpython because it
depends on rexec. We could change plpython to an untrusted language
if someone cares enough to develop a patch to remove the use of
rexec. Otherwise I fear we'll have to pull it.
regards, tom lane
> You can check our archives (search for "python" a month or two back) > but it seems that the latest Python release removes rexec because it > had some fundamental security holes. That breaks plpython because it > depends on rexec. We could change plpython to an untrusted language > if someone cares enough to develop a patch to remove the use of > rexec. Otherwise I fear we'll have to pull it. > plruby could fill that empty slot. I think it at least deserves a mention in the documentation...
At 06:42 AM 06/18/2003 +0200, you wrote: > >> You can check our archives (search for "python" a month or two back) >> but it seems that the latest Python release removes rexec because it >> had some fundamental security holes. That breaks plpython because it >> depends on rexec. We could change plpython to an untrusted language >> if someone cares enough to develop a patch to remove the use of >> rexec. Otherwise I fear we'll have to pull it. >> > >plruby could fill that empty slot. I think it at least deserves a >mention in the documentation... When you say "have to pull it" does that mean dropping plpython completely? I'm cuious becuause I'm starting to use plpython for more server functions. and where can I find plruby? Thanks
> When you say "have to pull it" does that mean dropping plpython completely? > I'm cuious becuause I'm starting to use plpython for more server functions. > > and where can I find plruby? > http://moulon.inra.fr/ruby/plruby.html
"Jay O'Connor" <joconnor@cybermesa.com> writes:
> At 06:42 AM 06/18/2003 +0200, you wrote:
>> We could change plpython to an untrusted language
>> if someone cares enough to develop a patch to remove the use of
>> rexec. Otherwise I fear we'll have to pull it.
> When you say "have to pull it" does that mean dropping plpython completely?
Yes. I can't see that we have any other alternative. The existing
plpython won't work at all with newer Python installations, and while
it'd still work with older ones, it has exactly the same security holes
that prompted the Python folk to pull rexec. That means it's foolish
to pretend that it can still be considered a trusted language. So
I feel we cannot just leave it sit there. Either somebody does the
legwork to convert it into an untrusted language that doesn't use rexec,
or it goes. And I don't think any of the core team has the time to do
that legwork. If there's no plpython user with the commitment to fix
it, it's history :-(. Any volunteers out there?
regards, tom lane
Is this a TODO item? --------------------------------------------------------------------------- Tom Lane wrote: > "Jay O'Connor" <joconnor@cybermesa.com> writes: > > At 06:42 AM 06/18/2003 +0200, you wrote: > >> We could change plpython to an untrusted language > >> if someone cares enough to develop a patch to remove the use of > >> rexec. Otherwise I fear we'll have to pull it. > > > When you say "have to pull it" does that mean dropping plpython completely? > > Yes. I can't see that we have any other alternative. The existing > plpython won't work at all with newer Python installations, and while > it'd still work with older ones, it has exactly the same security holes > that prompted the Python folk to pull rexec. That means it's foolish > to pretend that it can still be considered a trusted language. So > I feel we cannot just leave it sit there. Either somebody does the > legwork to convert it into an untrusted language that doesn't use rexec, > or it goes. And I don't think any of the core team has the time to do > that legwork. If there's no plpython user with the commitment to fix > it, it's history :-(. Any volunteers out there? > > regards, tom lane > > ---------------------------(end of broadcast)--------------------------- > TIP 8: explain analyze is your friend > -- Bruce Momjian | http://candle.pha.pa.us pgman@candle.pha.pa.us | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup. | Newtown Square, Pennsylvania 19073
Bruce Momjian <pgman@candle.pha.pa.us> writes:
> Is this a TODO item?
Not only that, but a MUST FIX FOR 7.4 item. IMHO anyway.
regards, tom lane
> ---------------------------------------------------------------------------
> Tom Lane wrote:
>> "Jay O'Connor" <joconnor@cybermesa.com> writes:
> At 06:42 AM 06/18/2003 +0200, you wrote:
> We could change plpython to an untrusted language
> if someone cares enough to develop a patch to remove the use of
> rexec. Otherwise I fear we'll have to pull it.
>>
> When you say "have to pull it" does that mean dropping plpython completely?
>>
>> Yes. I can't see that we have any other alternative. The existing
>> plpython won't work at all with newer Python installations, and while
>> it'd still work with older ones, it has exactly the same security holes
>> that prompted the Python folk to pull rexec. That means it's foolish
>> to pretend that it can still be considered a trusted language. So
>> I feel we cannot just leave it sit there. Either somebody does the
>> legwork to convert it into an untrusted language that doesn't use rexec,
>> or it goes. And I don't think any of the core team has the time to do
>> that legwork. If there's no plpython user with the commitment to fix
>> it, it's history :-(. Any volunteers out there?
>>
>> regards, tom lane
Hi, > Yes. I can't see that we have any other alternative. The existing > plpython won't work at all with newer Python installations, and while > it'd still work with older ones, it has exactly the same security holes > that prompted the Python folk to pull rexec. That means it's foolish > to pretend that it can still be considered a trusted language. So > I feel we cannot just leave it sit there. Either somebody does the > legwork to convert it into an untrusted language that doesn't use rexec, > or it goes. And I don't think any of the core team has the time to do > that legwork. If there's no plpython user with the commitment to fix > it, it's history :-(. Any volunteers out there? Not that I have time to do this, but I realy _need_ plpython in my current project. So if nobody else will do it I'll have to make/find time... I also don't have much experience with integrating Python and C, so I would have to learn that. I think it would be better if someone with more experience and time would do this, but if nobody else is available I'll do it. Sander.
I strongly protest the pulling of plpython. Make it untrusted if necessary, but don't pull it. The capabilities of plpython are much too valuable to trash. At this point I am available as a tester, but not as a developer for any changes to the interface. Drag me into the frey. I may also be free to do development a bit later. I am also actively promoting the use of plpython on General Bits and other places so we may get even more resistance to just trashing it. elein@varlena.com On Tuesday 17 June 2003 13:28, Tom Lane wrote: > Erv Young <ervyoung@nc.rr.com> writes: > > At 04:14 PM 6/17/2003 +0100, Nigel J. Andrews wrote: > >> Of course, given that plpython seems on it's last legs for now.... > > > Ummm.... (I haven't actually been on one of the nearby planets for the > > last 18 months, but the effect is much the same.) Tell me more. > > You can check our archives (search for "python" a month or two back) > but it seems that the latest Python release removes rexec because it > had some fundamental security holes. That breaks plpython because it > depends on rexec. We could change plpython to an untrusted language > if someone cares enough to develop a patch to remove the use of > rexec. Otherwise I fear we'll have to pull it. > > regards, tom lane > > ---------------------------(end of broadcast)--------------------------- > TIP 5: Have you checked our extensive FAQ? > > http://www.postgresql.org/docs/faqs/FAQ.html > > -- ============================================================= elein@varlena.com Database Consulting www.varlena.com PostgreSQL General Bits http:/www.varlena.com/GeneralBits/ "Free your mind the rest will follow" -- en vogue
As I understand it, plpython currently relies on the rexec call / interface to function, and that functionality is going away, so SOMEONE has to step up to the plate and change the way plpython is called in order to keep it. I do believe someone on hackers proferred an interest in doing this in the last day or two. On Thu, 19 Jun 2003, elein wrote: > > I strongly protest the pulling of plpython. > Make it untrusted if necessary, but don't > pull it. The capabilities of plpython are > much too valuable to trash. > > At this point I am available as a tester, > but not as a developer for any changes to the > interface. Drag me into the frey. I may also > be free to do development a bit later. > > I am also actively promoting the use of plpython > on General Bits and other places > so we may get even more resistance to > just trashing it. > > elein@varlena.com > > On Tuesday 17 June 2003 13:28, Tom Lane wrote: > > Erv Young <ervyoung@nc.rr.com> writes: > > > At 04:14 PM 6/17/2003 +0100, Nigel J. Andrews wrote: > > >> Of course, given that plpython seems on it's last legs for now.... > > > > > Ummm.... (I haven't actually been on one of the nearby planets for the > > > last 18 months, but the effect is much the same.) Tell me more. > > > > You can check our archives (search for "python" a month or two back) > > but it seems that the latest Python release removes rexec because it > > had some fundamental security holes. That breaks plpython because it > > depends on rexec. We could change plpython to an untrusted language > > if someone cares enough to develop a patch to remove the use of > > rexec. Otherwise I fear we'll have to pull it. > > > > regards, tom lane > > > > ---------------------------(end of broadcast)--------------------------- > > TIP 5: Have you checked our extensive FAQ? > > > > http://www.postgresql.org/docs/faqs/FAQ.html > > > > > >
elein <elein@varlena.com> writes:
> I strongly protest the pulling of plpython.
I seem to have missed the part of this where you volunteer to fix it.
Complaining will not change anything; someone stepping up and doing the
work will change things.
regards, tom lane
I just didn't want anyone to say "no one complained". That has been a reason some features were changed without a lot of notice in the past. It is important to understand who uses what. And I have offered to do as much as I can at this time. It is not as if I were whining about something and being unwilling to participate. elein On Thursday 19 June 2003 14:39, Tom Lane wrote: > elein <elein@varlena.com> writes: > > I strongly protest the pulling of plpython. > > I seem to have missed the part of this where you volunteer to fix it. > Complaining will not change anything; someone stepping up and doing the > work will change things. > > regards, tom lane > > -- ============================================================= elein@varlena.com Database Consulting www.varlena.com PostgreSQL General Bits http:/www.varlena.com/GeneralBits/ "Free your mind the rest will follow" -- en vogue
Thanks, Scott. I believe that was Kevin Jacobs in a message to pgsql-general and I've sent him a message about being available for testing. -elein On Thursday 19 June 2003 14:27, scott.marlowe wrote: > As I understand it, plpython currently relies on the rexec call / > interface to function, and that functionality is going away, so SOMEONE > has to step up to the plate and change the way plpython is called in order > to keep it. > > I do believe someone on hackers proferred an interest in doing this in the > last day or two. > > On Thu, 19 Jun 2003, elein wrote: > > > > > I strongly protest the pulling of plpython. > > Make it untrusted if necessary, but don't > > pull it. The capabilities of plpython are > > much too valuable to trash. > > > > At this point I am available as a tester, > > but not as a developer for any changes to the > > interface. Drag me into the frey. I may also > > be free to do development a bit later. > > > > I am also actively promoting the use of plpython > > on General Bits and other places > > so we may get even more resistance to > > just trashing it. > > > > elein@varlena.com > > > > On Tuesday 17 June 2003 13:28, Tom Lane wrote: > > > Erv Young <ervyoung@nc.rr.com> writes: > > > > At 04:14 PM 6/17/2003 +0100, Nigel J. Andrews wrote: > > > >> Of course, given that plpython seems on it's last legs for now.... > > > > > > > Ummm.... (I haven't actually been on one of the nearby planets for the > > > > last 18 months, but the effect is much the same.) Tell me more. > > > > > > You can check our archives (search for "python" a month or two back) > > > but it seems that the latest Python release removes rexec because it > > > had some fundamental security holes. That breaks plpython because it > > > depends on rexec. We could change plpython to an untrusted language > > > if someone cares enough to develop a patch to remove the use of > > > rexec. Otherwise I fear we'll have to pull it. > > > > > > regards, tom lane > > > > > > ---------------------------(end of broadcast)--------------------------- > > > TIP 5: Have you checked our extensive FAQ? > > > > > > http://www.postgresql.org/docs/faqs/FAQ.html > > > > > > > > > > > > > ---------------------------(end of broadcast)--------------------------- > TIP 9: the planner will ignore your desire to choose an index scan if your > joining column's datatypes do not match > > -- ============================================================= elein@varlena.com Database Consulting www.varlena.com PostgreSQL General Bits http:/www.varlena.com/GeneralBits/ "Free your mind the rest will follow" -- en vogue
elein wrote: > Thanks, Scott. > > I believe that was Kevin Jacobs in a message to pgsql-general > and I've sent him a message about being available for testing. It sure is a shame the Python folks just decided to punt instead of rolling up their sleeves and fixing the problem. Now a cascade of dependencies will be broken because of this. I've embedded Python in applications myself and used the rexec module for "alleged" protection. Imagine if the Java applet folks, having encountered security problems in the past, just said, "this is too hard" and bailed. Not good... :-( Mike Mascari mascarm@mascari.com