基於PG資料庫插件的SQL規範審核工具

最新 06-15

內容來源：2017 年 10 月 21 日，平安科技資料庫架構師陳剛在「PostgreSQL 2017中國技術大會」進行《基於PG資料庫插件的SQL規範審核工具》演講分享。IT 大咖說（微信id：itdakashuo）作為獨家視頻合作方，經主辦方和講者審閱授權發布。

閱讀字數：6453 | 17分鐘閱讀

摘要

此議題的主題是PG資料庫插件和SQL規範審核相關的內容。首先了解一下hook技術的基本原理。接下來將介紹一下SQL語句在PG資料庫的分析解析和執行過程。然後結合hook和SQL執行過程介紹一下SQL規範審核這個插件，聊一聊該插件的實現原理。最後做一下展望。

嘉賓演講視頻及PPT回顧：http://suo.im/5s8NNM

內容概覽

這個是本次分享的主要內容：

Hook技術基礎簡介

Hook中文的意思是鉤子，它的概念主要是，可以讓用戶有機會切入到PG資料庫的內部運行機制中，進行中斷、增加或者修改原來的程序邏輯，從而實現一些用戶自定義的功能。單獨看文字可能理解不是很直觀，我們來看看示意圖。

這個藍色的箭頭方塊表示某一個軟體的原始的程序邏輯，默認情況下是無中斷的順序執行。但是我們原程序在設計的時候，可以在適當的位置定義一個鉤子，加入一些判斷機制，當滿足一定條件時，允許跳轉到一段用戶自定義的程序，用戶自定義程序運行完成之後再回到原程序繼續執行下去，這種技術就叫做hook機制。

當然，在設計這個鉤子的時候，也允許用戶使用自定義程序來替代原始的程序，以達到改變或優化原程序的業務邏輯的目的。這就是hook技術的感念。

要實現這個hook機制，需要滿足四方面的要素：

第一，原始程序中要設計有這麼一個鉤子，後續才能讓用戶有機會利用這個鉤子。這個是前提條件，如果原程序中根本就沒有設計這種機制，那麼我們也就無從談起利用這個鉤子了。

第二，要開發一段自定義的程序，用來完成某些特殊的功能，這點也就不用過多解釋。

第三，現在原程序的鉤子已經定義好了，自定義程序也已經實現，那麼什麼時候將這段自定義程序加入到原程序的執行流程中呢？默認情況下，原程序是不會理睬我們定義的這段程序的，需要我們人為的設置一下，這就是hook的安裝，只有安裝好了這個鉤子，且滿足一定的條件，原程序才會流轉到我們自定義的這段程序中。

第四、有了鉤子的安裝，自然就有鉤子的卸載，就是在不需要再執行自定義程序的時候，將這個關聯關係卸載掉，讓程序恢復原狀，這就是鉤子的卸載。當然這個卸載的操作不一定是必須的，因為有些hook機制會一直使用下去，直到系統停機就自然而然的卸載掉了。也就不用我們特意的來實施這個卸載的操作。這點需要根據不同的業務場景而定。

hook機制實現方法

剛才我們大致了解了hook技術的概念和基本原理，下面我們就用實際的代碼來說明hook機制的實現方法。這些是摘自PG資料庫的源碼，是用C語言開發的。

首先，第一點是鉤子的定義，主要是在原程序中定義一個函數指針，並且默認情況下該函數指針賦一個空值。然後在原始代碼的合適的地方，增加一個if判斷，當原始程序在執行這段代碼時，會判斷這個指針函數是否為空，如果為空的話，就跳過，繼續執行後續的代碼。如果不為空那麼就執行這個函數指針所指向的那段代碼。那麼顯而易見，默認情況下，這個函數指針始終為空，所以原系統會不間斷的執行原始的代碼，就當作這個鉤子不存在。

第二點，需要開發一段程序用來實現自定義的業務邏輯，這點不需要做過多解釋。大家注意到沒有，這段自定義代碼的函數中，還有另外一個判斷，這個待會兒再做解釋。

第三點，hook的安裝。就是在適當的時候，將原程序中的那個函數指針指向我們自定義的這個函數上，當然在修改原函數指針的時候，還要將原函數指針所指向的其他函數方保存下來。

大家想想，原來的函數指針不是默認為空嗎？為什麼還要保存原來的函數指針呢？這就涉及到hook的嵌套機制，因為當我們在安裝我們自己開發的插件的時候，有可能其他插件也在這個鉤子上安裝了他們的自定義程序，這就形成了一個鉤子鏈條。

為了不妨礙其他插件的運行，我們在修改函數指針之前要將原函數指針也保存下來，然後在我們的自定義程序中再加上一個判斷，看看原函數指針是否為空，如果不為空還需要執行其他插件中自定義的程序。這點尤其重要，千萬不能忽略掉了。

另外，這個hook安裝的操作是在這個_PG_int函數中完成的。這個是PG插件開發的規範介面名稱。當資料庫在載入這個插件的時候，會默認調用這個函數做一些初始化的操作，所以我們就將hook的安裝放在這個函數中。

第四點，就是鉤子的卸載，雖然這點不是必須的，我們也稍加解釋一下，就是在_PG_fini函數中，做一些收尾的操作，比如將之前修改過的原程序的指針還原。這個_PG_fini函數名也是約定好了的，在插件卸載的時候由資料庫自動調用。

有了這4要素之外，還需要其他一些輔助的操作，才能讓這個插件真正的運行起來，比如要修改參數文件，將這個插件名加入到shared_preload_libraries這個參數中，或者使用load命令手工載入這個插件。另外在必要的時候可能還需要重啟資料庫實例。

注意事項

下面再來看看，我們在開發某個插件的時候，要注意哪些事項。雖然我們開發的插件與資料庫的內核程序是松耦合關係，我們很方便的安裝或者卸載這個插件，但是一旦這個插件被安裝上了之後，這段自定義程序就成為整個資料庫的一部分了，比如這段原始程序執行多少次，那麼我們的這段自定義程序就會執行多少次，這段自定義的程序質量的高低也會影響資料庫核心的原程序。所以也需要確保這段自定義程序的健壯性、性能以及容錯機制。同時還要檢查是否有內存泄漏的問題。另外如果這段程序需要佔用大量內存，還需要做好內存消耗的評估等等。

Auth_delay案例

下面我們來介紹一個實際的插件的例子，這個例子是來自於隨PG一道發布的工程中的。叫做auth_delay。

可能大家都知道這個插件的作用，就是當使用用戶名和密碼連接PG資料庫時，如果當用戶名和密碼錯誤，可以讓資料庫延遲幾秒再返回給前端，這樣是防止利用窮舉密碼的方式來嘗試登陸資料庫。

我們就來看看這個插件的原理。首先第一點，在PG核心源碼中的用戶登陸驗證的程序中，定義了這麼一個函數指針，默認情況下該函數指針為空。然後在用戶名和密碼驗證結束的這個函數中增加了一個if判斷，如果這個函數指針不為空，則調用這個函數指針指向的程序。

第二點，開發了這個自定義函數，這個函數的邏輯非常簡單，首先判斷原函數指針是否為空，如果不為空則先執行這個函數指針指向的其他函數。然後就是運行這個插件要完成的主要任務，即判斷這個用戶名和密碼驗證的結果是否為OK這個狀態，如果不是OK說明使用了錯誤的用戶名和密碼登陸，那麼此時將sleep幾秒，也就是延遲返回。這個就是該插件的主要功能。

然後第三點就是在這個PG_INIT這個函數中安裝這個鉤子，即首先將原函數指針保留在一個函數指針變數中，然後將這個自定義的函數賦值給原函數指針這個變數。

這個插件非常簡單，簡短的幾行代碼就完成了密碼驗證錯誤後延遲返回的這麼一個需求。所以我把它稱為史上最簡單的PG插件。

常用hook

剛才我們介紹了HOOK技術的第一個要素，就是PG原程序中要已經預定義了這個鉤子，如果原程序中根本就沒有定義這個鉤子，那我們就無能為力了。

那麼目前PG原程序代碼中到底有多少個這種鉤子呢？我之前在基於10.0這個新版本中大致搜了一下，如果沒有遺漏的話大概有26個。

我們可以根據這些hook名稱和所在的位置大致猜到它們的作用，比如這個第4行這個鉤子check_password_hook是允許我們校驗密碼的複雜度。這5、6、7、8是在執行SQL語句的前後允許我們記錄一些什麼東西，比如SQL執行的開始、結束時間等等。還有第10行這個ClientAuthentication_hook就是我們剛才介紹的這個auth_delay插件所利用的鉤子。還有第19行這個shmem_startup_hook是在資料庫啟動過程中，當完成共享內存初始化之後，允許我們自定義一些共享內存，用來存儲自定義的數據。

這裡我就不一一介紹每一個hook的作用了，大家後續可以根據每一個hook所處的源碼位置來確定具體的作用，然後大家自由發揮，想想這些hook可以應用在哪些場景中，然後開發出對應的插件。

SQL執行過程

也許有朋友研究過SQL語句執行的過程，主要包括以下幾個階段：

SQL語句的詞法、語法解析、SQL語義分析生成查詢樹，然後對這個查詢樹進行優化重寫。接下來生成執行計劃，然後就根據這個執行計划進行實際的執行，最後將執行結果返回。

在這一整個SQL執行過程中，PG資料庫在如下階段安裝有HOOK，比如在查詢樹重寫之後有一個hook，允許我們對所生成的查詢樹進行分析。再比如在sql執行的前後均有幾個hook允許我們對SQL執行的前後做一些記錄或其他事情。

Query Tree

下面就重點介紹一下SQL語義分析之後所生成的QUERY TREE，看看QUERY TREE是長什麼樣子，都記錄哪些信息。

query tree在C語言中就是一個結構體，該結構體中的每個變數也都有其特定的含義，每個結構體的變數也都包含其他結構體，這樣就現成了一個多叉樹的結構，這個多叉樹的每個節點都包含這個SQL語句的某一組成部分的詳細信息。

上面的截圖是PG資料庫將某一個SQL語句解析後的query tree的各個節點的內容列印在後台日誌中。

就拿這個簡單的查詢SQL為例，首先這個根節點中的commandType為1的屬性就表示這個SQL是一個SELECT語句。還有的表示是否有聚合函數，是否有窗口函數以及是否有子查詢，是否有distinct運算，是否有for update等等。

下面這個節點，用來描述from後面的表，從哪張表中查詢數據，該表有哪些欄位等等描述都在這個節點中。

再往下個節點是關於表的join的信息，後面跟的這個節點是where條件的表達式，再後面這個返回列的信息，這個SQL是總共返回兩列，所以這裡有兩個節點。

再後來就是關於排序的節點信息，這裡有一個order by ，所以這個排序的節點就不為空。下面就是返回數據行的描述，比如limit offset和limit count，因為這個語句中沒有寫limit，所以這兩個節點都是為空。這個就是SQL語句語義解析後的這個QUERY TREE的大致的樣子。

這張圖是執行計劃生成出來之後的一個多叉樹，它記錄的信息和查詢樹有些區別，比如其中的某個節點記錄了表的掃描方式，是全表還是根據索引。

SQL規範審核插件

有了前面的HOOK技術和SQL解析的基礎之後，接下來就介紹本次議題的主角了。SQL規範審核插件。

首先，我們需要定義一些SQL的審核規則，這裡羅列的是部分規則，有些是已經實現了的，有些還在開發測試中。

這些規則中有些是針對DML，還有些規則是針對於DCL和DDL的。表中二三行表示的是當一個UPDATE語句沒有where條件，或者where條件始終為true時，需要給出警告的審核。再下面是關於授權的審核，比如有些dml許可權不能授權給查詢用戶。下面這些是關於命名方面的規範。

規範審核案例

有了這些規則之後，我們就來針對某些規則介紹一下實現原理。

首先看看這個規則，update語句必須出現where子句。還是回到這個SQL執行過程，在SQL語義解析後會生成QUERY TRUE，而且在這個位置又提供了一個hook，那麼我們就可以利用這個hook來分析這個查詢樹。針對這個語句，我們就可以找到查詢樹中關於where子句的這個節點，看看這個節點是否為空即可。

有時候可能對這個QUERY TREE結構不了解的初學者來說，一下子找不到相應的節點。那麼這裡有個小技巧，就是可以寫一個另外一個相似的SQL語句，一個帶where條件，一個不帶where條件，然後用類似於UE文本比較的工具，看看這兩個SQL語句列印出來的查詢樹有些什麼差異，通過這種方法可以快速定位到所需要的Node。

再來看看下一個規則，剛才的這個規則是判斷where條件是否為空，接下來這個規則是update語句有寫where條件，但是有可能這個where條件的表達式始終為true，這樣就相當於沒有where條件，也會做全表update。比如這兩個語句：一個where條件為1=1，另一個where條件就直接是true。

如果我們這個使用仍然向上一個規則一樣來分析query true行不行呢？顯然是不行的，因為在這個查詢樹中，where條件這個節點並不為空，它是包含where條件的，所以我們需要換一種思路。

前面我們了解到，在執行計劃生成後也會產生一個執行計劃plan tree，我們可以看看這個plan tree中的這個where條件的節點長什麼樣子。

通過對比，我們可以發現如果where條件表達式的邏輯始終為true的話，在執行計劃樹中的這個節點也為空，這說明優化器在生成執行計劃的時候就直接忽略掉這個where條件。所以通過這種方法，我們也可以達到我們判斷業務邏輯的目的。當然這個規則的判斷需要安裝在SQL語句執行前的這個鉤子上。

下面再來看看第三個規則，關於授權。這個規則的意思是不能將某些DML許可權授權給查詢用戶，這些查詢用戶只能授與這些白名單上的許可權，比如select、usage等，類似與這個語句。

要審核這個規則，我們就需要使用這個鉤子，這個鉤子是定義在執行類似於這種授權語句的代碼中。我們可以去分析這個授權SQL語句語義解析後的這個結構體。具體判斷這個結構體中授權對象是不是QRY角色，以及是不是有超出白名單的許可權。從而判斷是否違規授權。

查詢樹遍歷

前面我們介紹了三條規則審核的例子。其中前兩個例子中都有提到那顆多叉樹。雖然我們人眼一眼就可以定位到我們關係的樹中的某個node，但是程序程序是如何定位的呢。這就涉及到樹的遍歷的問題了。

還記得上圖圖中這一大段的內容是從哪裡來的嗎，對了就是從後台日誌中獲取到的，PG程序肯定有地方也會遍歷這課樹，把這個樹中的每個節點的內容列印出來。

在源碼的src/backend/nodes/outfuncs.c位置可以找到遍歷樹方法。我們可以仿照著這個遍歷的過程也寫一個遍曆數的函數，當遍歷某個節點時再根據規則名稱來判斷對應節點的內容是否符合違規條件。比如這個是遍歷plan tree樹中對錶進行全表掃描的節點，看看這個節點中過濾條件的節點是否為空，如果為空則說明該SQL語句會影響整表的數據。

下面顯示的就是審核插件的最終效果。