PostgreSQL 加载动态库详解

PostgreSQL 支持通过动态库的方式扩展PG的功能，在调用动态库涉及的函数时会自动加载这些库。
但是某些动态库可能需要预加载，例如使用了hook的动态库，或者是需要初始化共享内存，需要fork work process的动态库。
举个例子：
pg_stat_statements这个插件依赖的动态库就使用了HOOK，并且需要初始化共享内存。

下面再细分一下：
.1. 需要初始化共享内存，或者fork work process的动态库，必须在数据库启动时加载(将来数据库支持在启动后动态创建共享内存，或者动态fork work process的话，可以不需要在数据库启动时加载)。
.2. 需要用到HOOK，但是对应的so没有数据库函数来触发自动LOAD的情况，则需要独立的LOAD过程。例如auto_explain, pg_plan_hint插件。他们没有函数，但是需要用到HOOK。
_PG_init接口就是被这些插件使用的自动装置。

a dynamically loaded file can contain initialization and finalization functions.
If the file includes a function named _PG_init, that function will be called immediately after loading the file. The function receives no parameters and should return void.
If the file includes a function named _PG_fini, that function will be called immediately before unloading the file. Likewise, the function receives no parameters and should return void.
Note that _PG_fini will only be called during an unload of the file, not during process termination.

下面来聊一下如何预加载so：
加载SO分为2种
.1. 数据库启动时加载，通过shared_preload_libraries参数控制。
配置shared_preload_libraries参数，必须重启数据库。
例子（可以省略.so，如果没有配置相对路径，则在dynamic_library_path参数提供的路径中查找对应的SO文件）：

shared_preload_libraries = 'auto_explain,$libdir/plugins/pg_hint_plan.so'

.2. 用户与数据库建立连接时加载，通过两个参数控制。
local_preload_libraries
这个参数允许普通用户修改，只能动态加载$libdir/plugins下面的so文件。如果是加载$libdir下面的so文件，会报错（原因见本文末尾代码）。
如果未指定相对目录，自动到$libdir/plugins/中搜索so。
(PostgreSQL在涉及动态库时，就把$libdir/plugins列为DBA应该有责任对so把关，仅将安全的so放到$libdir/plugins目录中，因为允许普通用户动态加载)
例子 :

alter role test set local_preload_libraries='pg_hint_plan';
 或
alter role test set local_preload_libraries='$libdir/plugins/pg_hint_plan';

session_preload_libraries
这个参数只允许超级用户修改，能动态加载所有目录下面的so文件，如果未指定相对目录，自动到dynamic_library_path指定的目录中搜索so。
例子 :

alter role all set session_preload_libraries='pg_hint_plan';
 或
alter role all set session_preload_libraries='$libdir/pg_hint_plan';

如何让一个普通用户可以在不重启数据库的情况下，预加载非$libdir/plugins目录中的so呢？
可以让超级用户修改这个普通用户的session_preload_libraries参数。

alter role test set session_preload_libraries='$libdir/pg_hint_plan';

除了预加载(启动数据库时加载，或者链接数据库时加载)，PostgreSQL还有一种方法加载so，使用LOAD语句。
同样分为普通用户和超级用户的概念。
普通用户只能动态加载$libdir/plugins下面的so文件。如果未指定相对目录，自动到$libdir/plugins/中搜索so。
超级用户能动态加载所有目录下面的so文件，如果未指定相对目录，自动到dynamic_library_path指定的目录中搜索so。

load用法如下：

LOAD -- load a shared library file
Synopsis  

LOAD 'filename'  

Description
This command loads a shared library file into the PostgreSQL server's address space. If the file has been loaded already, the command does nothing. Shared library files that contain C functions are automatically loaded whenever one of their functions is called. Therefore, an explicit LOAD is usually only needed to load a library that modifies the server's behavior through "hooks" rather than providing a set of functions.  

The file name is specified in the same way as for shared library names in CREATE FUNCTION; in particular, one can rely on a search path and automatic addition of the system's standard shared library file name extension. See Section 35.9 for more information on this topic.  

Non-superusers can only apply LOAD to library files located in $libdir/plugins/ — the specified filename must begin with exactly that string. (It is the database administrator's responsibility to ensure that only "safe" libraries are installed there.)  

Compatibility  

LOAD is a PostgreSQL extension.

动态库搜索路径的配置
dynamic_library_path (string)

The default value for this parameter is '$libdir'.
If the value is set to an empty string '', the automatic path search is turned off.

例如

dynamic_library_path = '/usr/local/lib/postgresql:/home/my_project/lib:$libdir'

代码

/*
 * process any libraries that should be preloaded at backend start
 */
void
process_session_preload_libraries(void)
{
    load_libraries(session_preload_libraries_string,
                   "session_preload_libraries",
                   false);
    load_libraries(local_preload_libraries_string,
                   "local_preload_libraries",
                   true);
}
load_libraries(const char libraries, const char gucname, bool restricted)
...
    foreach(l, elemlist)
    {
        char       tok = (char ) lfirst(l);
        char       *filename;

        filename = pstrdup(tok);
        canonicalize_path(filename);
        / If restricting, insert $libdir/plugins if not mentioned already /
        if (restricted && first_dir_separator(filename) == NULL)
        {
            char       *expanded;

            expanded = psprintf("$libdir/plugins/%s", filename);
            pfree(filename);
            filename = expanded;
        }
        load_file(filename, restricted);
        ereport(DEBUG1,
                (errmsg("loaded library \"%s\"", filename)));
        pfree(filename);
    }
...
load_file(const char *filename, bool restricted)
{
    char       *fullname;

    / Apply security restriction if requested /
    if (restricted)
        check_restricted_library_name(filename);
...
/*
 * Check a restricted library name.  It must begin with "$libdir/plugins/"
 * and there must not be any directory separators after that (this is
 * sufficient to prevent ".." style attacks).
 */
static void
check_restricted_library_name(const char *name)
{
    if (strncmp(name, "$libdir/plugins/", 16) != 0 ||
        first_dir_separator(name + 16) != NULL)
        ereport(ERROR,
                (errcode(ERRCODE_INSUFFICIENT_PRIVILEGE),
                 errmsg("access to library \"%s\" is not allowed",
                        name)));
}

参考
http://www.postgresql.org/docs/9.5/static/runtime-config-client.html#RUNTIME-CONFIG-CLIENT-PRELOAD
http://www.postgresql.org/docs/9.5/static/sql-load.html
http://www.postgresql.org/docs/9.5/static/xfunc-c.html#XFUNC-C-DYNLOAD