Thread: Damn triggers and NEW

Damn triggers and NEW

From
"Nigel J. Andrews"
Date:
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


Re: Damn triggers and NEW

From
Joe Conway
Date:
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


Re: Damn triggers and NEW

From
"Nigel J. Andrews"
Date:
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


Re: Damn triggers and NEW

From
Tom Lane
Date:
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

Re: Damn triggers and NEW

From
"Nigel J. Andrews"
Date:
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


plpython? (Was: Re: Damn triggers and NEW)

From
Erv Young
Date:
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


Re: plpython? (Was: Re: Damn triggers and NEW)

From
Tom Lane
Date:
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

Re: plpython? (Was: Re: Damn triggers and NEW)

From
culley harrelson
Date:
> 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...


Re: plpython? (Was: Re: Damn triggers and NEW)

From
"Jay O'Connor"
Date:
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



Re: plpython? (Was: Re: Damn triggers and NEW)

From
culley harrelson
Date:
> 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


Re: plpython? (Was: Re: Damn triggers and NEW)

From
Tom Lane
Date:
"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

Re: plpython? (Was: Re: Damn triggers and NEW)

From
Bruce Momjian
Date:
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

Re: plpython? (Was: Re: Damn triggers and NEW)

From
Tom Lane
Date:
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

Re: plpython? (Was: Re: Damn triggers and NEW)

From
"Sander Steffann"
Date:
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.


Re: plpython? (Was: Re: Damn triggers and NEW)

From
elein
Date:
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


Re: plpython? (Was: Re: Damn triggers and NEW)

From
"scott.marlowe"
Date:
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
> >
> >
>
>


Re: plpython? (Was: Re: Damn triggers and NEW)

From
Tom Lane
Date:
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

Re: plpython? (Was: Re: Damn triggers and NEW)

From
elein
Date:

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


Re: plpython? (Was: Re: Damn triggers and NEW)

From
elein
Date:
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


Re: plpython? (Was: Re: Damn triggers and NEW)

From
Mike Mascari
Date:
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