New PL/Perl failure with Safe 2.2x due to recursion (8.x & 9.0)

[Resend. I misspelled the mailing list address on the original.]

David Wheeler has discovered a new PL/Perl failure when using Safe 2.2x.

It's not good.

In this email I'll try to explain the cause and some possible solutions.

PL/Perl compiles plperl functions inside a 'Safe compartment' which
restricts what operations can be compiled and effectively does a chroot
in the package namespace.

The compilation returns a reference to the compiled subroutine.

Until recent versions of Safe the _execution_ of that subroutine
reference happened 'outside' of the Safe compartment. Safe was only
'in effect' during the compilation.

The "big change" in Safe 2.20 (contributed by Alex Hunsaker) was that
returned subroutine references were 'wrapped' in extra code that
executed the subroutine inside the Safe compartment.

The ticket for this change, with much discussion, is at
http://rt.perl.org/rt3/Ticket/Display.html?id=60374#txn-628047

The original motivation for the change was to fix a problem with using
sort {} in a threaded perl. As a side-effect it also increased security:
the Safe restrictions for plperl were in effect at runtime as well as
compiletime.

Unfortunately it also had a number of subtle knock-on effects that we've
been dealing with via Safe 2.21..2.23.

So far so good, but now we've hit another problem.

----- Forwarded message from "David E. Wheeler" <david(dot)wheeler(at)pgexperts(dot)com> -----

[...] When I move that function so that it's created just before my test
function that uses it, it works. It's only if it's created by another
process and already in the database when my test script connects that it fails.

Okay, here's the test case: Create this function:

CREATE OR REPLACE FUNCTION foo( integer) RETURNS SETOF INT LANGUAGE plperl AS $$ $$;

Then disconnect and reconnect and run this:

CREATE OR REPLACE FUNCTION try() RETURNS VOID LANGUAGE plperl AS $$
my $sth = spi_query("SELECT id FROM foo( 0 ) AS g(id)");
while( my $row = spi_fetchrow($sth) ) {
}
$$;
SELECT try();

Result:

ERROR: error from Perl function "try": Undefined subroutine &main::mksafefunc called at line 3.
(in cleanup) creation of Perl function "foo" failed: (in cleanup) Undefined subroutine &main::mksafefunc called at line 3. at line 3.

FYI, it's the call to spi_fetchrow() that fails, not spi_prepare().
PostgreSQL 8.4.2, Perl 5.10.1 with ithreads, Safe 2.23.

----- End forwarded message -----

I believe (but haven't yet confirmed) that the problem here is recursion.

When plperl recurses into itself via foo() the Safe restrictions are
still in effect. So plperl can't find the mksafefunc subroutine because
it's still 'inside' the chroot'd namespace.

This affects all versions of PostgreSQL.

After some head scratching I think the best course of action is to
change Safe to make the new behaviour optional and default to off.
In other words restore the pre-2.20 behaviour.

That'll fix the immediate problem for all PostgreSQL versions.
Security will be no better or worse that it was before Safe 2.20.
The old sort { } bug (where $a & $b) don't work will return but
that seems a very small price to pay for a simple fix.

I'd like to see PostgreSQL re-enable use of the wrapped subroutines
in the future but it'll require some development effort. The plperl
entry points will need to detect if Safe is 'in effect' and locally undo
it. There's some prior art in http://search.cpan.org/perldoc?Safe::Hole
that might be useful.

I don't think it's viable to tackle this for PostgreSQL 9.0.

Tomorrow I'll confirm my hypothesis and work on (another) patch for Safe
to disable the wrapping and test it with PostgreSQL 8.4 and 9.0.

Tim.