PostgreSQL PL/Perl 钩子安全性分析
背景
plperl 是PostgreSQL支持的函数语言之一。
在使用plperl时，可以使用plperl提供的钩子功能，满足一些特殊场景的需求。
钩子分2种，一种是加载plperl.so库时的钩子，一种是加载perl语言解释器时的钩子。
钩子的使用有安全问题吗？
钩子用法介绍
加载plperl.so库时的钩子
相关参数
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.
当设置了 shared_preload_libraries = ‘plperl’ 预加载时，plperl.on_init 只会被调用一次。
当没有设置 shared_preload_libraries = ‘plperl’ 预加载时，plperl.on_init 会在每个会话第一次装载plperl.so时被调用。
代码
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) 只能设置在配置文件中，或者在启动postgres时命令行指定。
对只开放普通数据库用户的环境来说没有安全问题。
加载perl语言解释器时的钩子
plperl 函数语言钩子，当在会话中第一次加载perl语言解释器时，perl 函数解释器将自动调用
plperl.on_plperl_init (string)
plperl.on_plperlu_init (string)
或设置的串。
具体调用哪个，和函数语言有关，plperl则调用on_plperl_init， plperlu则调用on_plperlu_init。
需要注意的是，这两个函数可以在参数中设置，也能在会话中设置，但是在会话中设置的话，如果perl解释器已经加载了，不会触发修改后的值。
另外需要注意on_plperl_init是在plperl安全化后执行的，所以即使在这里配置了不安全的属性，也不怕，因为会直接报错。（与调研plperl的用户权限无关，plperl是不允许执行不安全操作的，例如调研system接口）
这两个参数的解释 ：
https://www.postgresql.org/docs/9.5/static/plperl-under-the-hood.html
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.
代码如下
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;
只有超级用户能设置这两个值，普通用户在会话中设置plperl.on_plperl_init时，触发设置则报错。
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
测试例子
postgresql.conf 参数
#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") '
测试
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();
使用 stat touch /home/digoal/t123 查看时间戳的变化
判断是否触发。
plperl和plperlu语言的区别
plperl是trust语言，在创建它的函数时，会监测安全性，例如过滤一些OS操作，等。普通用户和超级用户都可以创建plperl语言的函数。
plperlu则是untruste语言，允许任何操作，只有超级用户能创建plperlu的函数。
如果已经设置了plperl.on_plperl_init是一个不安全的值，则新建plperl函数会报错。
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
调用时，如果触发了不安全的plperl.on_plperl_init，也会报错。
$ 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
小结
1.
PostgreSQL将函数语言分为两类，一类是trust的另一类是untrust的。
trust的语言，不允许执行有破坏性的操作，例如系统命令，文件访问等。普通用户可以创建trust语言的函数。
untrust的语言，允许执行任何操作，只有superuser能创建untrust语言的函数。
如果只开放普通数据库用户出去，是没有安全风险的。
2.
PostgreSQL 为 plperl或plperlu语言设置了两种钩子，分别允许在加载libperl.so时被触发(在_PG_init(void)里面实现);
或者在加载perl解释器时被触发，其中加载解释器时又分为两种，plperl和perlu的设置。
用户利用钩子，可以实现一些特殊场景的应用。
3.
数据库普通用户无法修改钩子参数
#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.
即使设置了危险的plperl.on_plperl_init参数，因为这个参数的内容是在plperl函数风险评估后执行的，所以如果有风险也不允许执行，不存在安全风险。
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"
综上，PostgreSQL对语言的管理是非常安全的，只要不随意把超级用户放出去，不随意使用untrust语言创建不安全的函数。