recent <productname>Perl</productname> versions, but it was not

in earlier versions, and in any case it is the choice of whomever

installed Perl at your site.

If you intend to make more than incidental use of

<application>PL/Perl</application>, you should ensure that the

<productname>Perl</productname> installation was built with the

<literal>usemultiplicity</> option enabled (<literal>perl -V</>

will show whether this is the case).

</para>

<para>

<para>

Users of source packages must specially enable the build of

PL/Perl during the installation process. (Refer to <xref

linkend="install-short"> for more information.) Users of

linkend="installation"> for more information.) Users of

binary packages might find PL/Perl in a separate subpackage.

</para>

</note>

most convenient to use dollar quoting (see <xref

linkend="sql-syntax-dollar-quoting">) for the string constant.

If you choose to use escape string syntax <literal>E''</>,

you must doublethe single quote marks (<literal>'</>) and backslashes

you must doubleany single quote marks (<literal>'</>) and backslashes

(<literal>\</>) used in the body of the function

(see <xref linkend="sql-syntax-strings">).

</para>

</para>

<para>

The <varname>%_SHARED</varname> variable and other global state within

the language are public data, available to all PL/Perl functions within a

session. Use with care, especially in situations that involve use of

multiple roles or <literal>SECURITY DEFINER</> functions.

For security reasons, PL/Perl executes functions called by any one SQL role

in a separate Perl interpreter for that role. This prevents accidental or

malicious interference by one user with the behavior of another user's

PL/Perl functions. Each such interpreter has its own value of the

<varname>%_SHARED</varname> variable and other global state. Thus, two

PL/Perl functions will share the same value of <varname>%_SHARED</varname>

if and only if they are executed by the same SQL role. In an application

wherein a single session executes code under multiple SQL roles (via

<literal>SECURITY DEFINER</> functions, use of <command>SET ROLE</>, etc)

you may need to take explicit steps to ensure that PL/Perl functions can

share data via <varname>%_SHARED</varname>. To do that, make sure that

functions that should communicate are owned by the same user, and mark

them <literal>SECURITY DEFINER</>. You must of course take care that

such functions can't be used to do anything unintended.

</para>

</sect1>

</para>

<note>

<para>

For security reasons, to stop a leak of privileged operations from

<application>PL/PerlU</> to <application>PL/Perl</>, these two languages

have to run in separate instances of the Perl interpreter. If your

Perl installation has been appropriately compiled, this is not a problem.

However, not all installations are compiled with the requisite flags.

If <productname>PostgreSQL</> detects that this is the case then it will

not start a second interpreter, but instead create an error. In

consequence, in such an installation, you cannot use both

<application>PL/PerlU</> and <application>PL/Perl</> in the same backend

process. The remedy for this is to obtain a Perl installation configured

with the appropriate flags, namely either <literal>usemultiplicity</>

or <literal>useithreads</>. <literal>usemultiplicity</> is preferred

unless you actually need to use threads. For more details, see the

<citerefentry><refentrytitle>perlembed</></citerefentry> man page.

</para>

<para>

While <application>PL/Perl</> functions run in a separate Perl

interpreter for each SQL role, all <application>PL/PerlU</> functions

executed in a given session run in a single Perl interpreter (which is

not any of the ones used for <application>PL/Perl</> functions).

This allows <application>PL/PerlU</> functions to share data freely,

but no communication can occur between <application>PL/Perl</> and

<application>PL/PerlU</> functions.

</para>

</note>

<note>

<para>

Perl cannot support multiple interpreters within one process unless

it was built with the appropriate flags, namely either

<literal>usemultiplicity</> or <literal>useithreads</>.

(<literal>usemultiplicity</> is preferred unless you actually need

to use threads. For more details, see the

<citerefentry><refentrytitle>perlembed</></citerefentry> man page.)

If <application>PL/Perl</> is used with a copy of Perl that was not built

this way, then it is only possible to have one Perl interpreter per

session, and so any one session can only execute either

<application>PL/PerlU</> functions, or <application>PL/Perl</> functions

that are all called by the same SQL role.

</para>

</note>

</sect1>

</indexterm>

<listitem>

<para>

Specifies Perl code to be executed when a Perl interpreter is first initialized

and before it is specialized for use by <literal>plperl</> or <literal>plperlu</>.

The SPI functions are not available when this code is executed.

If the code fails with an error it will abort the initialization of the interpreter

and propagate out to the calling query, causing the current transaction

or subtransaction to be aborted.

Specifies Perl code to be executed when a Perl interpreter is first

initialized, before it is specialized for use by <literal>plperl</> or

<literal>plperlu</>.

The SPI functions are not available when this code is executed.

If the code fails with an error it will abort the initialization of

the interpreter and propagate out to the calling query, causing the

current transaction or subtransaction to be aborted.

</para>

<para>

The Perl code is limited to a single string. Longer code can be placed

</programlisting>

</para>

<para>

Initialization will happen in the postmaster if the plperl library is included

in <literal>shared_preload_libraries</> (see <xref linkend="guc-shared-preload-libraries">),

in which case extra consideration should be given to the risk of destabilizing the postmaster.

Initialization will happen in the postmaster if the plperl library is

included in <xref linkend="guc-shared-preload-libraries">, in which

case extra consideration should be given to the risk of destabilizing

the postmaster. The principal reason for making use of this feature

is that Perl modules loaded by <literal>plperl.on_init</> need be

loaded only at postmaster start, and will be instantly available

without loading overhead in individual database sessions. However,

keep in mind that the overhead is avoided only for the first Perl

interpreter used by a database session &mdash; either PL/PerlU, or

PL/Perl for the first SQL role that calls a PL/Perl function. Any

additional Perl interpreters created in a database session will have

to execute <literal>plperl.on_init</> afresh. Also, on Windows there

will be no savings whatsoever from preloading, since the Perl

interpreter created in the postmaster process does not propagate to

child processes.

</para>

<para>

This parameter can only be set in the postgresql.conf file or on the server command line.

</indexterm>

<listitem>

<para>

These parameters specify Perl code to be executed when the

<literal>plperl</>, or <literal>plperlu</> language is first used in a

session. Changes to these parameters after the corresponding language

has been used will have no effect.

The SPI functions are not available when this code is executed.

Only superusers can change these settings.

The Perl code in <literal>plperl.on_plperl_init</> can only perform trusted operations.

</para>

<para>

The effect of setting these parameters is very similar to executing a

<literal>DO</> command with the Perl code before any other use of the

language. The parameters are useful when you want to execute the Perl

code automatically on every connection, or when a connection is not

interactive. The parameters can be used by non-superusers by having a

superuser execute an <literal>ALTER USER ... SET ...</> command.

For example:

<programlisting>

ALTER USER joe SET plperl.on_plperl_init = '$_SHARED{debug} = 1';

</programlisting>

These parameters specify Perl code to be executed when a Perl

interpreter is specialized for <literal>plperl</> or

<literal>plperlu</> respectively. This will happen when a PL/Perl or

PL/PerlU function is first executed in a database session, or when

an additional interpreter has to be created because the other language

is called or a PL/Perl function is called by a new SQL role. This

follows any initialization done by <literal>plperl.on_init</>.

The SPI functions are not available when this code is executed.

The Perl code in <literal>plperl.on_plperl_init</> is executed after

<quote>locking down</> the interpreter, and thus it can only perform

trusted operations.

</para>

<para>

If the code fails with an error it will abort the initialization and

propagate out to the calling query, causing the current transaction or

subtransaction to be aborted. Any changes within Perl won't be undone.

If the language is used again the initialization will be repeated.

If the code fails with an error it will abort the initialization and

propagate out to the calling query, causing the current transaction or

subtransaction to be aborted. Any actions already done within Perl

won't be undone; however, that interpreter won't be used again.

If the language is used again the initialization will be attempted

again within a fresh Perl interpreter.

</para>

<para>

The difference between these two settings and the

<literal>plperl.on_init</> setting is that these can be used for

settings specific to the trusted or untrusted language variant, such

as setting values in the <varname>%_SHARED</> variable. By contrast,

<literal>plperl.on_init</> is more useful for doing things like

setting the library search path for <productname>Perl</> or

loading Perl modules that don't interact directly with

<productname>PostgreSQL</>.

Only superusers can change these settings. Although these settings

can be changed within a session, such changes will not affect Perl

interpreters that have already been used to execute functions.

</para>

</listitem>

</varlistentry>

</indexterm>

<listitem>

<para>

When set true subsequent compilations of PL/Perl functions have the <literal>strict</> pragma enabled.

This parameter does not affect functions already compiled in the current session.

When set true subsequent compilations of PL/Perl functions will have

the <literal>strict</> pragma enabled. This parameter does not affect

functions already compiled in the current session.

</para>

</listitem>

</varlistentry>

Sometimes it

is useful to have some global data that is held between two

calls to a function or is shared between different functions.

This is easily done since

all PL/Tcl functions executed in one session share the same

safe Tcl interpreter. So, any global Tcl variable is accessible to

all PL/Tcl function calls and will persist for the duration of the

SQL session. (Note that <application>PL/TclU</> functions likewise share

global data, but they are in a different Tcl interpreter and cannot

communicate with PL/Tcl functions.)

This is easily done in PL/Tcl, but there are some restrictions that

must be understood.

</para>

<para>

For security reasons, PL/Tcl executes functions called by any one SQL

role in a separate Tcl interpreter for that role. This prevents

accidental or malicious interference by one user with the behavior of

another user's PL/Tcl functions. Each such interpreter will have its own

values for any <quote>global</> Tcl variables. Thus, two PL/Tcl

functions will share the same global variables if and only if they are

executed by the same SQL role. In an application wherein a single

session executes code under multiple SQL roles (via <literal>SECURITY

DEFINER</> functions, use of <command>SET ROLE</>, etc) you may need to

take explicit steps to ensure that PL/Tcl functions can share data. To

do that, make sure that functions that should communicate are owned by

the same user, and mark them <literal>SECURITY DEFINER</>. You must of

course take care that such functions can't be used to do anything

unintended.

</para>

<para>

All PL/TclU functions used in a session execute in the same Tcl

interpreter, which of course is distinct from the interpreter(s)

used for PL/Tcl functions. So global data is automatically shared

between PL/TclU functions. This is not considered a security risk

because all PL/TclU functions execute at the same trust level,

namely that of a database superuser.

</para>

<para>

To help protect PL/Tcl functions from unintentionally interfering

with each other, a global

<literal>GD</> be used

for persistent private data of a function. Use regular Tcl global

variables only for values that you specifically intend to be shared among

multiple functions.

multiple functions. (Note that the <literal>GD</> arrays are only

global within a particular interpreter, so they do not bypass the

security restrictions mentioned above.)

</para>

<para>

exists, the module <literal>unknown</> is fetched from the table

and loaded into the Tcl interpreter immediately before the first

execution of a PL/Tcl function in a database session. (This

happens separately forPL/Tcland PL/TclU, ifboth are used,

because separate interpreters are used for the two languages.)

happens separately foreachTclinterpreter, ifmore than one is

used in a session; see <xref linkend="pltcl-global">.)

</para>

<para>

While the <literal>unknown</> module could actually contain any

<itemizedlist>

<listitem>

<para>

Use a separate interpreter for each calling SQL userid in PL/Perl and

PL/Tcl (Tom Lane)

</para>

<para>

This change prevents security problems that can be caused by subverting

Perl or Tcl code that will be executed later in the same session under

another SQL user identity (for example, within a <literal>SECURITY

DEFINER</> function). Most scripting languages offer numerous ways that

that might be done, such as redefining standard functions or operators

called by the target function. Without this change, any SQL user with

Perl or Tcl language usage rights can do essentially anything with the

SQL privileges of the target function's owner.

</para>

<para>

The cost of this change is that intentional communication among Perl

and Tcl functions becomes more difficult. To provide an escape hatch,

PL/PerlU and PL/TclU functions continue to use only one interpreter

per session. This is not considered a security issue since all such

functions execute at the trust level of a database superuser already.

</para>

<para>

It is likely that third-party procedural languages that claim to offer

trusted execution have similar security issues. We advise contacting

the authors of any PL you are depending on for security-critical

purposes.

</para>

<para>

Our thanks to Tim Bunce for pointing out this issue (CVE-2010-3433).

</para>

</listitem>

<listitem>

<para>

Prevent possible crashes in <function>pg_get_expr()</> by disallowing

<itemizedlist>

<listitem>

<para>

Use a separate interpreter for each calling SQL userid in PL/Perl and

PL/Tcl (Tom Lane)

</para>

<para>

This change prevents security problems that can be caused by subverting

Perl or Tcl code that will be executed later in the same session under

another SQL user identity (for example, within a <literal>SECURITY

DEFINER</> function). Most scripting languages offer numerous ways that

that might be done, such as redefining standard functions or operators

called by the target function. Without this change, any SQL user with

Perl or Tcl language usage rights can do essentially anything with the

SQL privileges of the target function's owner.

</para>

<para>

The cost of this change is that intentional communication among Perl

and Tcl functions becomes more difficult. To provide an escape hatch,

PL/PerlU and PL/TclU functions continue to use only one interpreter

per session. This is not considered a security issue since all such

functions execute at the trust level of a database superuser already.

</para>

<para>

It is likely that third-party procedural languages that claim to offer

trusted execution have similar security issues. We advise contacting

the authors of any PL you are depending on for security-critical

purposes.

</para>

<para>

Our thanks to Tim Bunce for pointing out this issue (CVE-2010-3433).

</para>

</listitem>

<listitem>

<para>

Prevent possible crashes in <function>pg_get_expr()</> by disallowing