41.5. Trusted and Untrusted PL/Perl

Normally, PL/Perl is installed as a "trusted" programming language named plperl. In this setup, certain Perl operations are disabled to preserve security. In general, the operations that are restricted are those that interact with the environment. This includes file handle operations, require, and use (for external modules). There is no way to access internals of the database server process or to gain OS-level access with the permissions of the server process, as a C function can do. Thus, any unprivileged database user can be permitted to use this language.

Here is an example of a function that will not work because file system operations are not allowed for security reasons:

CREATE FUNCTION badfunc() RETURNS integer AS $$
    my $tmpfile = "/tmp/badfile";
    open my $fh, '>', $tmpfile
        or elog(ERROR, qq{could not open the file "$tmpfile": $!});
    print $fh "Testing writing to a file\n";
    close $fh or elog(ERROR, qq{could not close the file "$tmpfile": $!});
    return 1;
$$ LANGUAGE plperl;

The creation of this function will fail as its use of a forbidden operation will be caught by the validator.

Sometimes it is desirable to write Perl functions that are not restricted. For example, one might want a Perl function that sends mail. To handle these cases, PL/Perl can also be installed as an "untrusted" language (usually called PL/PerlU). In this case the full Perl language is available. If the createlang program is used to install the language, the language name plperlu will select the untrusted PL/Perl variant.

The writer of a PL/PerlU function must take care that the function cannot be used to do anything unwanted, since it will be able to do anything that could be done by a user logged in as the database administrator. Note that the database system allows only database superusers to create functions in untrusted languages.

If the above function was created by a superuser using the language plperlu, execution would succeed.

In the same way, anonymous code blocks written in Perl can use restricted operations if the language is specified as plperlu rather than plperl, but the caller must be a superuser.

Note: For security reasons, to stop a leak of privileged operations from PL/PerlU to 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 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 PL/PerlU and PL/Perl in the same backend process. The remedy for this is to obtain a Perl installation configured with the appropriate flags, namely either usemultiplicity or useithreads. usemultiplicity is preferred unless you actually need to use threads. For more details, see the perlembed man page.