PostgreSQLHooK介紹
標籤
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
http://blog.163.com/digoal@126/blog/#m=0&t=2&c=2013-10
相關文章
- 介紹
- LAMP架構介紹、MYSQL介紹、安裝LAMP架構MySql
- php介紹PHP
- CSRedisCore 介紹Redis
- BitMap介紹
- GeoServer介紹Server
- RabbitMQ 介紹MQ
- 模式介紹模式
- Pyzmq介紹MQ
- Java介紹Java
- css介紹CSS
- kafka介紹Kafka
- 【RESTEasy 介紹】REST
- Kafka 介紹Kafka
- nginx介紹Nginx
- 埠介紹
- MongoDB介紹MongoDB
- docker 介紹Docker
- TypeScript介紹TypeScript
- Smbclient介紹client
- JVM 介紹JVM
- Spark介紹Spark
- MQT介紹MQQT
- HttpClient介紹HTTPclient
- Mongoose介紹Go
- git介紹Git
- 自我介紹
- JCache 介紹
- Yocto 介紹
- Docker介紹Docker
- GO 介紹Go
- GraphRAG介紹
- github介紹Github
- Ceph介紹
- MySql介紹MySql
- 公文介紹
- Ninja介紹
- Dubbo介紹