|PostgreSQL 8.0.26 Documentation|
|Prev||Fast Backward||Chapter 37. PL/Perl - Perl Procedural Language||Fast Forward||Next|
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 may 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 $$ open(TEMP, ">/tmp/badfile"); print TEMP "Gotcha!\n"; return 1; $$ LANGUAGE plperl;
The creation of the function will succeed, but executing it will not.
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.
Note: While PL/Perl functions run in a separate Perl interpreter for each SQL role, all PL/PerlU functions executed in a given session run in a single Perl interpreter (which is not any of the ones used for PL/Perl functions). This allows PL/PerlU functions to share data freely, but no communication can occur between PL/Perl and PL/PerlU functions.
Note: Perl cannot support multiple interpreters within one process unless it was built 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.) If 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 PL/PerlU functions, or PL/Perl functions that are all called by the same SQL role.
An interesting usage of an untrusted perl script beside a mailing function could be a ping function in order to check if a computer is up.
Something like this:
CREATE OR REPLACE FUNCTION ping_computer(inet) RETURNS integer AS $$
my $pingreturn = 0;
my $ip = @_;
$pingreturn = system("/sbin/ping -c 1 $ip > /dev/null");
$$ LANGUAGE plperlu;
This is the simplest one ;-)
Perhaps someone wants to decorate my function with some luxury...