digoal
2021-09-29
PostgreSQL , 审计 , 动态 , 可配置 , 可写入日志 , 可写入表 , 可区分文件和表
1、产品的问题点
- PG 审计功能有巨大增强空间
2、问题点背后涉及的技术原理
- PG 通过配置log_statement参数控制日志打印的类别: ddl dml all
- 通过配置log_min_duration_statement , 打印执行时间超过阈值的SQL
- 日志格式可以配置为csvlog, 或指定格式
一些采样配置:
#log_min_duration_statement = -1 # -1 is disabled, 0 logs all statements
# and their durations, > 0 logs only
# statements running at least this number
# of milliseconds
#log_min_duration_sample = -1 # -1 is disabled, 0 logs a sample of statements
# and their durations, > 0 logs only a sample of
# statements running at least this number
# of milliseconds;
# sample fraction is determined by log_statement_sample_rate
#log_statement_sample_rate = 1.0 # fraction of logged statements exceeding
# log_min_duration_sample to be logged;
# 1.0 logs all such statements, 0.0 never logs
#log_transaction_sample_rate = 0.0 # fraction of transactions whose statements
# are logged regardless of their duration; 1.0 logs all
# statements from all transactions, 0.0 never logs
3、这个问题将影响哪些行业以及业务场景
- 通用
4、会导致什么问题?
- SQL审计日志的输出位置和其他日志混合打印到相同的文件中, 会导致查询和管理的复杂度增加. 基于文件的搜索也比较麻烦, 不支持索引等.
- SQL审计日志可以控制的类别非常有限, 无法满足精细化审计需要.
- all打印的内容过多, 导致性能下降、存储资源消耗过度. DML则记录下了所有的修改操作, DDL只记录DDL语句.
5、业务上应该如何避免这个坑
- 可以使用pgaudit插件, 增加配置维度
/* 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, 等组合条件配置.
- 例如如果是某个用户执行的, 则审计, 否则不审计.
- 希望可配置审计日志的输出目标, 区别于其他日志文件. 或者可以存储在表中.
- 支持敏感信息加密存储. 例如修改用户密码, 密码部分在输出到日志文件中时加密存储.
您的愿望将传达给PG kernel hacker、数据库厂商等, 帮助提高数据库产品质量和功能, 说不定下一个PG版本就有您提出的功能点. 针对非常好的提议，奖励限量版PG文化衫、纪念品、贴纸、PG热门书籍等，奖品丰富，快来许愿。开不开森.
