|
PostgreSQL PL/Perl Hook Function Security Analysis PL/Perl is one of the function languages supported by PostgreSQL. When using PL/Perl, you can leverage the hook function that it provides to address special requirements. Hooks are divided into two types, namely the hook used when the plperl.so library is loaded and the hook used when a Perl interpreter is loaded. Is there any concerns over hook security? Hook Usage Hook Used When the plperl.so Library Is Loaded Related Parameters plperl.on_init (string) Specifies Perl code to be executed when a Perl interpreter is first initialized, before it is specialized for use by plperl or 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. The Perl code is limited to a single string. Longer code can be placed into a module and loaded by the on_init string. Examples: plperl.on_init = 'require "plperlinit.pl"' plperl.on_init = 'use lib "/my/app"; use MyApp::PgInit;' Any modules loaded by plperl.on_init, either directly or indirectly, will be available for use by plperl. This may create a security risk. To see what modules have been loaded you can use: DO 'elog(WARNING, join ", ", sort keys %INC)' LANGUAGE plperl; Initialization will happen in the postmaster if the plperl library is included in 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 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 — 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 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. This parameter can only be set in the postgresql.conf file or on the server command line. If shared_preload_libraries = 'plperl' is configured, plperl.on_init is called only once. If shared_preload_libraries = 'plperl' is not configured. plperl.on_init is called when plperl.so is loaded for the first time during each session. Code src/pl/plperl/plperl.c /* * plperl.on_init is marked PGC_SIGHUP to support the idea that it might * be executed in the postmaster (if plperl is loaded into the postmaster * via shared_preload_libraries). This isn't really right either way, * though. */ DefineCustomStringVariable("plperl.on_init", gettext_noop("Perl initialization code to execute when a Perl interpreter is initialized."), NULL, &plperl_on_init, NULL, PGC_SIGHUP, 0, NULL, NULL, NULL); /* * Create a new Perl interpreter. * * We initialize the interpreter as far as we can without knowing whether * it will become a trusted or untrusted interpreter; in particular, the * plperl.on_init code will get executed. Later, either plperl_trusted_init * or plperl_untrusted_init must be called to complete the initialization. */ static PerlInterpreter * plperl_init_interp(void) { ... if (plperl_on_init && *plperl_on_init) { embedding[nargs++] = "-e"; embedding[nargs++] = plperl_on_init; } ... /* * _PG_init() - library load-time initialization * * DO NOT make this static nor change its name! */ void _PG_init(void) { ... /* * Create the first Perl interpreter, but only partially initialize it. */ plperl_held_interp = plperl_init_interp(); ... plperl.on_init (string) can be set only in a configuration file or specified using CLI when POSTGRES is started. There is no security concerns in an environment with only common database users. Hook Used When a Perl Interpreter Is Loaded When a Perl interpreter is loaded for the first time during a session, the interpreter automatically calls either of the following functions: plperl.on_plperl_init (string) plperl.on_plperlu_init (string) Alternatively, it uses a preset string. The option that it chooses depends on the function language. For PL/Perl, it calls on_plperl_init. For PL/Perlu, it calls on_plperlu_init. Note that these two functions can be set in parameters or sessions. If they are set in sessions after Perl interpreters are loaded, the new values are not used. Moreover, on_plperl_init is executed after PL/Perl security hardening. Therefore, when an insecure attribute is configured, an error is reported. (This is not related to the permissions assigned to a PL/Perl user. PL/Perl does not allow performing insecure operations, such as calling system interfaces.) plperl.on_plperl_init (string) plperl.on_plperlu_init (string) These parameters specify Perl code to be executed when a Perl interpreter is specialized for plperl or 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 plperl.on_init. The SPI functions are not available when this code is executed. The Perl code in plperl.on_plperl_init is executed after "locking down" the interpreter, and thus it can only perform trusted operations. 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. 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. The code is as follows: src/pl/plperl/plperl.c DefineCustomStringVariable("plperl.on_plperl_init", gettext_noop("Perl initialization code to execute once when plperl is first used."), NULL, &plperl_on_plperl_init, NULL, PGC_SUSET, 0, NULL, NULL, NULL); DefineCustomStringVariable("plperl.on_plperlu_init", gettext_noop("Perl initialization code to execute once when plperlu is first used."), NULL, &plperl_on_plperlu_init, NULL, PGC_SUSET, 0, NULL, NULL, NULL); src/backend/utils/misc/guc.c case PGC_SUSET: if (context == PGC_USERSET || context == PGC_BACKEND) { ereport(elevel, (errcode(ERRCODE_INSUFFICIENT_PRIVILEGE), errmsg("permission denied to set parameter \"%s\"", name))); return 0; } break; Only the super user can set the two values. If a common user sets plperl.on_plperl_init for a session, an error is reported when the setting is used. postgres=> set session plperl.on_plperl_init=''; SET postgres=> SELECT * FROM test_munge(); WARNING: 42501: permission denied to set parameter "plperl.on_plperl_init" LOCATION: set_config_option, guc.c:5794 Test Example postgresql.conf parameter #plperl.on_plperlu_init = ' system("touch /home/digoal/t123") ' plperl.on_plperl_init = ' system("touch /home/digoal/t123") ' #plperl.on_init=' system("touch /home/digoal/tttt") ' Test CREATE TABLE test ( i int, v varchar ); INSERT INTO test (i, v) VALUES (1, 'first line'); INSERT INTO test (i, v) VALUES (2, 'second line'); INSERT INTO test (i, v) VALUES (3, 'third line'); INSERT INTO test (i, v) VALUES (4, 'immortal'); CREATE OR REPLACE FUNCTION test_munge() RETURNS SETOF test AS $$ my $rv = spi_exec_query('select i, v from test;'); my $status = $rv->{status}; my $nrows = $rv->{processed}; foreach my $rn (0 .. $nrows - 1) { my $row = $rv->{rows}[$rn]; $row->{i} += 200 if defined($row->{i}); $row->{v} =~ tr/A-Za-z/a-zA-Z/ if (defined($row->{v})); return_next($row); } return undef; $$ LANGUAGE plperl; SELECT * FROM test_munge(); Use stat touch /home/digoal/t123 to check the time stamp changes and determine whether such setting is used. Differences Between PL/Perl and PL/Perlu PL/Perl is a trusted language. When a PL/Perl function is created, the function is checked to ensure that it does not include OS operations. Both common users and super users can create PL/Perl functions. PL/Perlu is an untrusted language and allows any operations. Only super users can create PL/Perlu functions. If plperl.on_plperl_init has been set to an insecure value, an error is reported when a PL/Perl function is created. postgres=# show plperl.on_plperl_init; plperl.on_plperl_init ------------------------------------- system("touch /home/digoal/t123") (1 row) postgres=# CREATE OR REPLACE FUNCTION test_munge() RETURNS SETOF test AS $$ my $rv = spi_exec_query('select i, v from test;'); my $status = $rv->{status}; my $nrows = $rv->{processed}; foreach my $rn (0 .. $nrows - 1) { my $row = $rv->{rows}[$rn]; $row->{i} += 200 if defined($row->{i}); $row->{v} =~ tr/A-Za-z/a-zA-Z/ if (defined($row->{v})); return_next($row); } return undef; $$ LANGUAGE plperl; ERROR: 38000: 'system' trapped by operation mask at line 2. CONTEXT: while executing plperl.on_plperl_init compilation of PL/Perl function "test_munge" LOCATION: plperl_trusted_init, plperl.c:1016 If an insecure plperl.on_plperl_init value is used when a function is called, an error is reported as well. $ vi postgresql.conf plperl.on_plperl_init = ' system("touch /home/digoal/t123") ' $ pg_ctl reload postgres=# SELECT * FROM test_munge(); ERROR: 38000: 'system' trapped by operation mask at line 2. CONTEXT: while executing plperl.on_plperl_init compilation of PL/Perl function "test_munge" LOCATION: plperl_trusted_init, plperl.c:1016 Summary 1. PostgreSQL divides function languages into trusted and untrusted languages. The trusted language does not allow any harmful operations, such as OS command execution and file access. Common users can create functions of the trusted language. The untrusted language allows any operations. Only super users can create functions of the language. If only common database users are allowed, no security risks exist. 2. PostgreSQL sets two types of hooks for PL/Perl and PL/Perlu. One of the hooks can be triggered when libperl.so is loaded (implemented using _PG_init(void)), and the other is triggered when a Perl interpreter is loaded. If a hook is triggered when a Perl interpreter is loaded, consider the differences between PL/Perl and PL/Perlu. A user can use a hook to address special requirements. 3. Common database users cannot modify hook parameters. #plperl.on_plperlu_init = ' system("touch /home/digoal/t123") ' plperl.on_plperl_init = ' system("touch /home/digoal/t123") ' #plperl.on_init=' system("touch /home/digoal/tttt") ' 4. If plperl.on_plperl_init is set to an insecure value, it imposes no security risks because it is used only after being verified. plperl.on_plperl_init = ' system("touch /home/digoal/t123") ' postgres=# SELECT * FROM test_munge(); ERROR: 'system' trapped by operation mask at line 2. CONTEXT: while executing plperl.on_plperl_init compilation of PL/Perl function "test_munge" To conclude, PostgreSQL manages language security well. No security risks will occur provided that super users are created only when necessary and insecure functions are not created using the untrusted language. |
|
