【DB吐槽大會】第61期 - PG 審計功能有巨大增強空間

背景

1、産品的問題點

• PG 審計功能有巨大增強空間

2、問題點背後涉及的技術原理

• PG 通過配置log_statement參數控制日志列印的類别: ddl dml all
• 通過配置log_min_duration_statement , 列印執行時間超過門檻值的SQL
• 日志格式可以配置為csvlog, 或指定格式

3、這個問題将影響哪些行業以及業務場景

• 通用

4、會導緻什麼問題?

• SQL審計日志的輸出位置和其他日志混合列印到相同的檔案中, 會導緻查詢和管理的複雜度增加. 基于檔案的搜尋也比較麻煩, 不支援索引等.
• SQL審計日志可以控制的類别非常有限, 無法滿足精細化審計需要.

• • all列印的内容過多, 導緻性能下降、存儲資源消耗過度. DML則記錄下了所有的修改操作, DDL隻記錄DDL語句.

5、業務上應該如何避免這個坑

• 可以使用pgaudit插件, 增加配置次元

• • 《PostgreSQL 審計 - pg_audit module》

/* Bits within auditLogBitmap, defines the classes we understand */    
#define LOG_DDL                 (1 << 0)        /* CREATE/DROP/ALTER objects */    
#define LOG_FUNCTION    (1 << 1)        /* Functions and DO blocks */    
#define LOG_MISC                (1 << 2)        /* Statements not covered */    
#define LOG_READ                (1 << 3)        /* SELECTs */    
#define LOG_ROLE                (1 << 4)        /* GRANT/REVOKE, CREATE/ALTER/DROP ROLE */    
#define LOG_WRITE               (1 << 5)        /* INSERT, UPDATE, DELETE, TRUNCATE */    
    
#define LOG_NONE                0                       /* nothing */    
#define LOG_ALL                 (0xFFFFFFFF)    /* All */  
  
/*    
 * GUC variable for pg_audit.role    
 *    
 * Administrators can choose which role to base OBJECT auditing off of.    
 * Object-level auditing uses the privileges which are granted to this role to    
 * determine if a statement should be logged.    
 */    
char *auditRole = NULL;    
    
/*    
 * Object type, used for SELECT/DML statements and function calls.    
 *    
 * For relation objects, this is essentially relkind (though we do not have    
 * access to a function which will just return a string given a relkind;    
 * getRelationTypeDescription() comes close but is not public currently).    
 *    
 * We also handle functions, so it isn't quite as simple as just relkind.    
 *    
 * This should be kept consistent with what is returned from    
 * pg_event_trigger_ddl_commands(), as that's what we use for DDL.    
 */    
#define OBJECT_TYPE_TABLE                       "TABLE"    
#define OBJECT_TYPE_INDEX                       "INDEX"    
#define OBJECT_TYPE_SEQUENCE            "SEQUENCE"    
#define OBJECT_TYPE_TOASTVALUE          "TOAST TABLE"    
#define OBJECT_TYPE_VIEW                        "VIEW"    
#define OBJECT_TYPE_MATVIEW                     "MATERIALIZED VIEW"    
#define OBJECT_TYPE_COMPOSITE_TYPE      "COMPOSITE TYPE"    
#define OBJECT_TYPE_FOREIGN_TABLE       "FOREIGN TABLE"    
#define OBJECT_TYPE_FUNCTION            "FUNCTION"    
    
#define OBJECT_TYPE_UNKNOWN                     "UNKNOWN"          

6、業務上避免這個坑犧牲了什麼, 會引入什麼新的問題

• pgaudit依舊有增強空間, 同時pgaudit屬于第三方插件, 品質和代碼品質如何保障? 遇到問題的修複周期如果保障?

7、資料庫未來産品疊代如何修複這個坑

• 希望核心支援更強大的審計配置, 包含pgaudit的能力.

• • 不帶條件的DML (delete, update)
  • 影響行數超過N（可配置）的DML(update, delete)
  • 傳回結果超過N（可配置）的查詢（select）
  • 支援白名單SQL (支援變量, 采用query hashid表達) (不記錄)
  • 支援黑名單SQL (遇到就記錄下來)
  • 支援user, dbname, query, 等組合條件配置.

• • • 例如如果是某個使用者執行的, 則審計, 否則不審計.

• 希望可配置審計日志的輸出目标, 差別于其他日志檔案. 或者可以存儲在表中.
• 支援敏感資訊加密存儲. 例如修改使用者密碼, 密碼部分在輸出到日志檔案中時加密存儲.

https://github.com/digoal/blog/blob/master/202109/20210929_06.md#postgresql-%E8%AE%B8%E6%84%BF%E9%93%BE%E6%8E%A5 https://github.com/digoal/blog/issues/76