标签
PostgreSQL , hook
背景
PostgreSQL 的HOOK机制，结合PostgreSQL的_PG_init与_PG_fini两个初始化函数（加载SO时自动load _PG_init(), 退出会话时自动加载_PG_fini()），使得用户可以在不修改源码的情况下，使用HOOK来实现一些数据库的功能扩展。
比如实现改写SQL执行计划，统计采样，防止暴力破解，输出超时SQL的执行计划，等等很多事情。
有哪些HOOK
1、内部定义的hook
grep -i hook src/tools/pgindent/typedefs.list|grep type
ClientAuthentication_hook_type
ExecutorCheckPerms_hook_type
ExecutorEnd_hook_type
ExecutorFinish_hook_type
ExecutorRun_hook_type
ExecutorStart_hook_type
ExplainOneQuery_hook_type
ProcessUtility_hook_type
check_password_hook_type
create_upper_paths_hook_type
emit_log_hook_type
explain_get_index_name_hook_type
fmgr_hook_type
get_attavgwidth_hook_type
get_index_stats_hook_type
get_relation_info_hook_type
get_relation_stats_hook_type
join_search_hook_type
needs_fmgr_hook_type
object_access_hook_type
planner_hook_type
post_parse_analyze_hook_type
row_security_policy_hook_type
set_join_pathlist_hook_type
set_rel_pathlist_hook_type
shmem_startup_hook_type
2、已经在使用的hook
grep -r -i hook *|grep NULL|awk '{print $2}'|sort|uniq -c|grep -i hook|grep -i type
4 ClientAuthentication_hook_type
2 ExecutorCheckPerms_hook_type
4 ExecutorEnd_hook_type
4 ExecutorFinish_hook_type
4 ExecutorRun_hook_type
4 ExecutorStart_hook_type
3 fmgr_hook_type
3 needs_fmgr_hook_type
2 object_access_hook_type
2 post_parse_analyze_hook_type
4 ProcessUtility_hook_type
2 row_security_policy_hook_type
2 shmem_startup_hook_type
已植入HOOK的源文件如下：
grep -r -i hook *|grep "if "|grep "^src"|awk '{print $1}'|sort|uniq -c
1 src/backend/catalog/index.c:
2 src/backend/commands/explain.c:
4 src/backend/commands/user.c:
5 src/backend/executor/execMain.c:
2 src/backend/executor/spi.c:
1 src/backend/libpq/auth.c:
2 src/backend/optimizer/path/allpaths.c:
1 src/backend/optimizer/path/joinpath.c:
6 src/backend/optimizer/plan/planner.c:
1 src/backend/optimizer/prep/prepunion.c:
2 src/backend/optimizer/util/clauses.c:
1 src/backend/optimizer/util/plancat.c:
2 src/backend/parser/analyze.c:
10 src/backend/parser/parse_expr.c:
4 src/backend/parser/parse_target.c:
2 src/backend/rewrite/rowsecurity.c:
2 src/backend/storage/ipc/ipci.c:
1 src/backend/storage/lmgr/lwlock.c:
1 src/backend/storage/smgr/smgr.c:
1 src/backend/tcop/postgres.c:
1 src/backend/tcop/utility.c:
6 src/backend/utils/adt/selfuncs.c:
1 src/backend/utils/cache/lsyscache.c:
2 src/backend/utils/error/elog.c:
3 src/backend/utils/fmgr/fmgr.c:
55 src/backend/utils/misc/guc.c:
1 src/backend/utils/misc/README:In
1 src/backend/utils/misc/README:NULL
1 src/bin/pg_upgrade/server.c:
1 src/bin/psql/startup.c:
1 src/bin/psql/tab-complete.c:
8 src/bin/psql/variables.c:
1 src/bin/psql/variables.h:
4 src/include/catalog/objectaccess.h:
1 src/include/nodes/relation.h:
1 src/interfaces/libpq/fe-connect.c:
2 src/interfaces/libpq/fe-exec.c:
1 src/interfaces/libpq/fe-protocol2.c:
1 src/interfaces/libpq/fe-protocol3.c:
1 src/interfaces/libpq/fe-secure-openssl.c:
3、PostgreSQL如何使用HOOK
HOOK的名字找到对应的定义，通常是HOOK被定义时，将跳转到对应的代码执行用户在插件中植入的代码片段。
例子
src/backend/executor/execMain.c
/* Hooks for plugins to get control in ExecutorStart/Run/Finish/End */
ExecutorStart_hook_type ExecutorStart_hook = NULL;
ExecutorRun_hook_type ExecutorRun_hook = NULL;
ExecutorFinish_hook_type ExecutorFinish_hook = NULL;
ExecutorEnd_hook_type ExecutorEnd_hook = NULL;
/* ----------------------------------------------------------------
* ExecutorFinish
*
* This routine must be called after the last ExecutorRun call.
* It performs cleanup such as firing AFTER triggers. It is
* separate from ExecutorEnd because EXPLAIN ANALYZE needs to
* include these actions in the total runtime.
*
* We provide a function hook variable that lets loadable plugins
* get control when ExecutorFinish is called. Such a plugin would
* normally call standard_ExecutorFinish().
*
* ----------------------------------------------------------------
*/
void
ExecutorFinish(QueryDesc *queryDesc)
{
if (ExecutorFinish_hook) // 如果定义了这个HOOK，那么跳转到如下执行。
(*ExecutorFinish_hook) (queryDesc);
else
standard_ExecutorFinish(queryDesc);
}
使用HOOK的插件
用到HOOK的插件，通常需要在会话建立时，或者在数据库启动时，加载动态库，采用 _PG_init() 接口来自动加载HOOK的定义(load so文件时自动加载)，从而让数据库执行某些函数时，跳转到对应HOOK中。
https://www.postgresql.org/docs/10/static/xfunc-c.html#XFUNC-C-DYNLOAD
Optionally, 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.
(Presently, unloads are disabled and will never occur, but this may change in the future.)
举几个例子
cd contrib
grep -r -i hook *|less
6 auth_delay/auth_delay.c:
1 auth_delay/auth_delay.c:/*
1 auth_delay/auth_delay.c:static
19 auto_explain/auto_explain.c:
1 auto_explain/auto_explain.c:/*
4 auto_explain/auto_explain.c:static
8 Binary
1 passwordcheck/passwordcheck.c:
40 pg_stat_statements/pg_stat_statements.c:
1 pg_stat_statements/pg_stat_statements.c:/*
7 pg_stat_statements/pg_stat_statements.c:static
1 postgres_fdw/expected/postgres_fdw.out:--
1 postgres_fdw/sql/postgres_fdw.sql:--
20 sepgsql/hooks.c:
3 sepgsql/hooks.c:static
23 sepgsql/label.c:
1 sepgsql/label.c:sepgsql_fmgr_hook(FmgrHookEventType
1 sepgsql/label.c:sepgsql_needs_fmgr_hook(Oid
3 sepgsql/label.c:static
1 sepgsql/Makefile:OBJS
1 sepgsql/relation.c:
1 sepgsql/selinux.c:
1 sepgsql/sepgsql.h:
1 sepgsql/uavc.c:
1、插件1，auth_delay
使用了什么HOOK： ClientAuthentication_hook_type
功效：认证失败时，SLEEP一段时间后再返回客户端，用于加大暴力破解的难度。
代码如下：
void _PG_init(void);
/* GUC Variables */
static int auth_delay_milliseconds;
/* Original Hook */
static ClientAuthentication_hook_type original_client_auth_hook = NULL; // 定义HOOK
/*
* Check authentication
*/
// 植入的代码
static void
auth_delay_checks(Port *port, int status)
{
/*
* Any other plugins which use ClientAuthentication_hook.
*/
if (original_client_auth_hook)
original_client_auth_hook(port, status);
/*
* Inject a short delay if authentication failed.
*/
if (status != STATUS_OK)
{
pg_usleep(1000L * auth_delay_milliseconds);
}
}
/*
* Module Load Callback
*/
void
_PG_init(void) // 启动会话时，自动加载_PG_init函数
{
/* Define custom GUC variables */
DefineCustomIntVariable("auth_delay.milliseconds",
"Milliseconds to delay before reporting authentication failure",
NULL,
&auth_delay_milliseconds,
0,
0, INT_MAX / 1000,
PGC_SIGHUP,
GUC_UNIT_MS,
NULL,
NULL,
NULL);
/* Install Hooks */
// HOOK变量赋值(使之有值，在PG内核有该HOOK的地方，生效，走到HOOK植入的代码中)
original_client_auth_hook = ClientAuthentication_hook;
ClientAuthentication_hook = auth_delay_checks;
}
PG内核包含这个HOOK的代码如下：
src/backend/libpq/auth.c
/*
* This hook allows plugins to get control following client authentication,
* but before the user has been informed about the results. It could be used
* to record login events, insert a delay after failed authentication, etc.
*/
ClientAuthentication_hook_type ClientAuthentication_hook = NULL;
...............
/*
* Client authentication starts here. If there is an error, this
* function does not return and the backend process is terminated.
*/
void
ClientAuthentication(Port *port)
{
................
// 走到这里，如果定义了HOOK，那么就执行HOOK定义的函数。
// 即延迟响应客户端。
// 从而实现提高暴力破解的两次重试之间的等待，提高暴力破解时间。
if (ClientAuthentication_hook)
(*ClientAuthentication_hook) (port, status);
if (status == STATUS_OK)
sendAuthRequest(port, AUTH_REQ_OK, NULL, 0);
else
auth_failed(port, status, logdetail);
}
2、插件2，auto_explain
使用了什么HOOK：
/* Saved hook values in case of unload */
static ExecutorStart_hook_type prev_ExecutorStart = NULL;
static ExecutorRun_hook_type prev_ExecutorRun = NULL;
static ExecutorFinish_hook_type prev_ExecutorFinish = NULL;
static ExecutorEnd_hook_type prev_ExecutorEnd = NULL;
功效：在开启auto_explain后，记录下设定超时SQL，并按插件设定，输出当时的执行计划详情。
3、插件3，passwordcheck/passwordcheck.c
使用了什么HOOK：check_password_hook
功效：在创建用户、修改用户密码时，检查密码复杂度是否符合规范。
4、pg_stat_statements/pg_stat_statements.c
使用了什么HOOK：
/* Saved hook values in case of unload */
static shmem_startup_hook_type prev_shmem_startup_hook = NULL;
static post_parse_analyze_hook_type prev_post_parse_analyze_hook = NULL;
static ExecutorStart_hook_type prev_ExecutorStart = NULL;
static ExecutorRun_hook_type prev_ExecutorRun = NULL;
static ExecutorFinish_hook_type prev_ExecutorFinish = NULL;
static ExecutorEnd_hook_type prev_ExecutorEnd = NULL;
static ProcessUtility_hook_type prev_ProcessUtility = NULL;
功效：在执行SQL时，收集SQL执行过程中的资源开销信息。最后可用于统计TOP SQL。
5、其他使用HOOK的插件，敬请发掘
比如分区表插件pg_pathman
https://api.pgxn.org/src/pg_pathman/pg_pathman-1.4.12/src/hooks.c
《PostgreSQL 回收站功能 - 基于HOOK的recycle bin pgtrashcan》
pg_hint_plan插件等。
参考
除了HOOK，PG还提供了丰富的接口（此处不谈），以及动态跟踪，如下
https://www.postgresql.org/docs/10/static/dynamic-trace.html
