43.5. Trusted and Untrusted PL/Perl#
Normally, PL/Perl is installed as a“trusted” programming language namedplperl. 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, anduse (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.
Warning
Trusted PL/Perl relies on the PerlOpcode module to preserve security. Perldocuments that the module is not effective for the trusted PL/Perl use case. If your security needs are incompatible with the uncertainty in that warning, consider executingREVOKE USAGE ON LANGUAGE plperl FROM PUBLIC.
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 calledPL/PerlU). In this case the full Perl language is available. When installing the language, the language nameplperlu will select the untrusted PL/Perl variant.
The writer of aPL/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 languageplperlu, execution would succeed.
In the same way, anonymous code blocks written in Perl can use restricted operations if the language is specified asplperlu rather thanplperl, but the caller must be a superuser.
Note
WhilePL/Perl functions run in a separate Perl interpreter for each SQL role, allPL/PerlU functions executed in a given session run in a single Perl interpreter (which is not any of the ones used forPL/Perl functions). This allowsPL/PerlU functions to share data freely, but no communication can occur betweenPL/Perl andPL/PerlU functions.
Note
Perl cannot support multiple interpreters within one process unless it was built with the appropriate flags, namely eitherusemultiplicity oruseithreads. (usemultiplicity is preferred unless you actually need to use threads. For more details, see theperlembed man page.) IfPL/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 eitherPL/PerlU functions, orPL/Perl functions that are all called by the same SQL role.