SQL防火牆

德哥發表於2017-08-10
SQL防火牆

標籤

PostgreSQL , SQL防火牆 , 注入 , HOOK

背景

SQL隱碼攻擊通常是業務層做的事情，例如使用繫結變數，使用關鍵字過濾等手段，避免被SQL隱碼攻擊。

另一方面，資料庫也有類似的功能，例如SQL防火牆。

PostgreSQL內建了很多的HOOK，這些HOOK可以方便開發者加入一些功能，例如在SQL parser階段的HOOK，可以加入一些SQL統計，SQL篡改，SQL REWRITE的功能。

在SQL執行階段的HOOK，可以用來拒絕執行等。

共享記憶體分配階段的HOOK，可以用來分配使用者自定義程式的共享記憶體等。

SQL_FIREWALL是PostgreSQL的一個SQL防火牆外掛，利用了一些HOOK，實現了SQL防火牆的功能。

/* 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;

1、shmem_startup_hook_type 內部程式通訊

src/backend/storage/ipc/ipci.c

/*  
 * CreateSharedMemoryAndSemaphores  
 *              Creates and initializes shared memory and semaphores.  
 *  
 * This is called by the postmaster or by a standalone backend.  
 * It is also called by a backend forked from the postmaster in the  
 * EXEC_BACKEND case.  In the latter case, the shared memory segment  
 * already exists and has been physically attached to, but we have to  
 * initialize pointers in local memory that reference the shared structures,  
 * because we didn`t inherit the correct pointer values from the postmaster  
 * as we do in the fork() scenario.  The easiest way to do that is to run  
 * through the same code as before.  (Note that the called routines mostly  
 * check IsUnderPostmaster, rather than EXEC_BACKEND, to detect this case.  
 * This is a bit code-wasteful and could be cleaned up.)  
 *  
 * If "makePrivate" is true then we only need private memory, not shared  
 * memory.  This is true for a standalone backend, false for a postmaster.  
 */  
void  
CreateSharedMemoryAndSemaphores(bool makePrivate, int port)  
{  
...  
  
        /*  
         * Now give loadable modules a chance to set up their shmem allocations  
         */  
        if (shmem_startup_hook)  
                shmem_startup_hook();  
}

2、parser hook

src/backend/parser/analyze.c

/* Hook for plugins to get control at end of parse analysis */  
post_parse_analyze_hook_type post_parse_analyze_hook = NULL;  
  
  
/*  
 * parse_analyze  
 *              Analyze a raw parse tree and transform it to Query form.  
 *  
 * Optionally, information about $n parameter types can be supplied.  
 * References to $n indexes not defined by paramTypes[] are disallowed.  
 *  
 * The result is a Query node.  Optimizable statements require considerable  
 * transformation, while utility-type statements are simply hung off  
 * a dummy CMD_UTILITY Query node.  
 */  
Query *  
parse_analyze(RawStmt *parseTree, const char *sourceText,  
                          Oid *paramTypes, int numParams,  
                          QueryEnvironment *queryEnv)  
{  
        ParseState *pstate = make_parsestate(NULL);  
        Query      *query;  
  
        Assert(sourceText != NULL); /* required as of 8.4 */  
  
        pstate->p_sourcetext = sourceText;  
  
        if (numParams > 0)  
                parse_fixed_parameters(pstate, paramTypes, numParams);  
  
        pstate->p_queryEnv = queryEnv;  
  
        query = transformTopLevelStmt(pstate, parseTree);  
  
        if (post_parse_analyze_hook)  
                (*post_parse_analyze_hook) (pstate, query);  
  
        free_parsestate(pstate);  
  
        return query;  
}

其他的HOOK不再贅述，簡單來說，SQL 防火牆的功能是：

學習資料庫中被呼叫的SQL，記錄被呼叫的SQL到規則庫，根據規則庫的內容，防止呼叫不在規則庫的SQL（根據配置）。

sql firewall的功能介紹

pic

1、學習模式，將SQL PARSER後的值儲存起來。儲存在規則表中。（因此支援繫結變數）

2、從外部CSV檔案匯入到規則中。

3、當不在規則中的SQL被執行時，根據配置 返回錯誤（不允許執行），或返回警告（允許執行）。

4、記錄警告、錯誤的次數。

sql firewall的用法

1、配置postgresql.conf

* shared_preload_libraries  
  
  sql_firewall module needs to be loaded in the  
  shared_preload_libraries parameter as following:  
  
     shared_preload_libraries = `sql_firewall`  
  
   Note for developers  
   -------------------  
  
   pg_stat_statements built with `--enable-cassert` causes assert when  
   queryId already has non-zero value.  
  
   So, to use both pg_stat_statements and sql_firewall at the same  
   time, pg_stat_statements needs to be loaded prior to sql_firewall  
   in the shared_preload_libraries parameter as following.  
  
     shared_preload_libraries = `pg_stat_statements,sql_firewall`  
  
   Then, sql_firewall can skip queryId calculation if queryId is  
   already set by pg_stat_statements, and avoid the assert.  
  
* sql_firewall.firewall  
  
  sql_firewall.firewall is able to take one of the following values:  
  `disabled`, `learning`, `permissive` and `enforcing`.  
  The default value is `disabled`.  
  
* sql_firewall.max  
最多能存多少條規則。  
  
  Number of queries the SQL Firewall can learn.  
  It can take an int value between 100 and INT_MAX.  
  The default value is 5000.  
  The queries which exceed this value in the "learning" mode would never  
  be learned.

2、如果使用者一開始並不知道資料庫中會執行哪些SQL，通常可以設定為學習模式，學習一個月，基本上能跑的SQL都能學到。

* sql_firewall.firewall = learning

3、當然，如果使用者知道這個資料庫會執行哪些SQL，那麼可以將SQL匯入。

必須為disabled模式，才能匯入。

* sql_firewall_import_rule(`/path/to/rule.txt`)  
  
  sql_firewall_import_rule() reads the firewall rules from the  
  specified CSV file.  
  
  This function is available only under the disabled mode with  
  superuser privilege.

4、規則固定下來後，sql_firewall.firewall設定為enforcing模式，不允許執行不在規則中的SQL。

5、或者sql_firewall.firewall設定為permissive模式，允許執行，但是發出告警。

sql firewall函式介面

  
* sql_firewall_reset()  
  
  sql_firewall_reset() clears the firewall rules.  
  
  This function is available only under the disabled mode with  
  superuser privilege.  
  
* sql_firewall_stat_reset()  
  
  sql_firewall_reset() clears the counters of warning and error. Only  
  available with superuser privilege.  
  
* sql_firewall_export_rule(`/path/to/rule.txt`)  
  
  sql_firewall_export_rule() writes the firewall rules in the  
  specified CSV file.  
  
  This function is available only under the disabled mode with  
  superuser privilege.  
  
* sql_firewall_import_rule(`/path/to/rule.txt`)  
  
  sql_firewall_import_rule() reads the firewall rules from the  
  specified CSV file.  
  
  This function is available only under the disabled mode with  
  superuser privilege.

sql firewall管理檢視

  
* sql_firewall.sql_firewall_statements  
  
  sql_firewall_statements view shows the firewall rules and execution  
  counter for each query.  
  
    postgres=# select * from sql_firewall.sql_firewall_statements;  
     userid |  queryid   |              query              | calls  
    --------+------------+---------------------------------+-------  
         10 | 3294787656 | select * from k1 where uid = ?; |     4  
    (1 row)  
      
    postgres=#  
  
* sql_firewall.sql_firewall_stat  
  
  sql_firewall_stat view has two counters: "sql_warning" and  
  "sql_error".  
  
  "sql_warning" shows number of executed queries with warnings in the  
  "permissive" mode.  
  
  "sql_error" shows number of prevented queries in the "enforcing"  
  mode.  
  
    postgres=# select * from sql_firewall.sql_firewall_stat;  
     sql_warning | sql_error  
    -------------+-----------  
               2 |         1  
    (1 row)  
      
    postgres=#

例子

* Permissive mode  
  
    postgres=# select * from sql_firewall.sql_firewall_statements;  
    WARNING:  Prohibited SQL statement  
     userid |  queryid   |              query              | calls  
    --------+------------+---------------------------------+-------  
         10 | 3294787656 | select * from k1 where uid = 1; |     1  
    (1 row)  
      
    postgres=# select * from k1 where uid = 1;  
     uid |    uname  
    -----+-------------  
       1 | Park Gyu-ri  
    (1 row)  
      
    postgres=# select * from k1 where uid = 3;  
     uid |   uname  
    -----+-----------  
       3 | Goo Ha-ra  
    (1 row)  
      
    postgres=# select * from k1 where uid = 3 or 1 = 1;  
    WARNING:  Prohibited SQL statement  
     uid |     uname  
    -----+----------------  
       1 | Park Gyu-ri  
       2 | Nicole Jung  
       3 | Goo Ha-ra  
       4 | Han Seung-yeon  
       5 | Kang Ji-young  
    (5 rows)  
    
    postgres=#   
  
* Enforcing mode  
  
    postgres=# select * from k1 where uid = 3;  
     uid |   uname  
    -----+-----------  
       3 | Goo Ha-ra  
    (1 row)  
      
    postgres=# select * from k1 where uid = 3 or 1 = 1;  
    ERROR:  Prohibited SQL statement  
    postgres=#

參考

https://github.com/uptimejp/sql_firewall


相關文章