PostgreSQLHooK介紹

背景

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。