PostgreSQL 加载动态库详解

背景

PostgreSQL 支持通过动态库的方式扩展PG的功能，在调用动态库涉及的函数时会自动加载这些库。

但是某些动态库可能需要预加载，例如使用了hook的动态库，或者是需要初始化共享内存，需要fork work process的动态库。

还有一些库是允许会话级加载，以及使用LOAD命令加载的，但是他们有什么分别呢？又需要什么权限呢？

正文

举个例子：

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

digoal’s 大量PostgreSQL文章入口