PostgreSQL 灵活审计插件 - PostgreSQL per database or per user audit use pg_log_userqueries

背景

PostgreSQL 本身并不带针对某数据库或某用户的SQL审计功能。

通过pg_log_userqueries可以达到这样的目的。

首先到pgxn下载pg_log_userqueries模块 :

我测试的是0.4.0版本。

1. 安装

# unzip pg_log_userqueries-0.4.0.zip  
# mv pg_log_userqueries-0.4.0 /opt/soft_bak/postgresql-9.1.2/contrib/  
# . /home/postgres/.bash_profile  
> cd /opt/soft_bak/postgresql-9.1.2/contrib/pg_log_userqueries-0.4.0  
> make && make install  

2. 修改postgresql.conf 配置文件 :

shared_preload_libraries = 'pg_log_userqueries'          # 如果以前已经配置了其他模块,则用逗号隔开.  
custom_variable_classes = 'pg_log_userqueries'          # list of custom variable class names  
pg_log_userqueries.log_db = 'digoal'  
pg_log_userqueries.log_user = 'digoal'  
pg_log_userqueries.syslog_facility = 'LOCAL0'  
pg_log_userqueries.syslog_ident = 'pg_log_userqueries'  
pg_log_userqueries.log_level = 'NOTICE'  
pg_log_userqueries.log_label = 'user query: '  
pg_log_userqueries.log_destination = 'syslog'  

2.1 解释 :

log_db='digoal', 表示我需要审计digoal库的所有查询,  
log_user='digoal' 表示我需要审计digoal用户的所有查询  
log_level = 'NOTICE' 审计的日志级别.  

3. 我这里配置的log_destination是syslog, 因此需要配置操作系统的syslog.conf。

可以参考我前一篇BLOG。例如 :

3.1. 配置操作系统/etc/syslog.conf

把local0.*;加入到以下行的头部 :

*.info;mail.none;authpriv.none;cron.none               /var/log/messages  

更改后变成

local0.*;*.info;mail.none;authpriv.none;cron.none               /var/log/messages  

3.2. 重启syslog服务

service syslog restart  

3.3. 确保syslog服务是自动启动的,

chkconfig --list|grep syslog  
syslog          0:off   1:off   2:on    3:on    4:on    5:on    6:off  

4. 重启数据库

pg_ctl stop -m fast -D $PGDATA  
pg_ctl start -D $PGDATA  
Jan 19 11:15:26 db-172 postgres[6229]: [1-1] LOG:  loaded library "pg_log_userqueries"  

5. 测试 :

5.1 查看连接到digoal库的审计

postgres@db-172-> psql -h 127.0.0.1 -U postgres digoal  
psql (9.1.2)  
Type "help" for help.  
digoal=# select now();  
              now                
-------------------------------  
 2012-01-19 11:57:30.678585+08  

日志 :

Jan 19 11:57:30 db-172 postgres[6690]: [1] user query: select now();  

5.2 查看用户digoal的审计

postgres@db-172-> psql -h 127.0.0.1 -U digoal postgres  
psql (9.1.2)  
Type "help" for help.  
postgres=> select current_date;  
    date      
------------  
 2012-01-19  

日志 :

Jan 19 12:00:22 db-172 postgres[6729]: [1]  user query : select current_date;  

5.3 验证非审计范围的SQL无输出

postgres=> \c postgres postgres  
You are now connected to database "postgres" as user "postgres".  
postgres=# select 1;  
 ?column?   
----------  
        1  
(1 row)  

日志 :

无

缺陷

暂时没有办法记录用户名和库名. 例如配置以下无法解析到用户名和库名 :

希望下一版本可以支持.

pg_log_userqueries.log_label = '"$dbname" "$user": '  

参考

http://pgxn.org/dist/pg_log_userqueries/0.4.0/

可配置参数 ( 截取自源代码文件) :

DefineCustomStringVariable( "pg_log_userqueries.log_label",  
  "Label in front of the user query."  
DefineCustomStringVariable( "pg_log_userqueries.log_user",  
  "Log statement according to the given user."  
DefineCustomStringVariable( "pg_log_userqueries.log_db",  
  "Log statement according to the given database."  
DefineCustomEnumVariable( "pg_log_userqueries.log_destination",  
  "Selects log destination (either stderr or syslog)."  
DefineCustomEnumVariable( "pg_log_userqueries.syslog_facility",  
  "Selects syslog level of log (same options than PostgreSQL syslog_facility)."  
DefineCustomStringVariable( "pg_log_userqueries.syslog_ident",  
  "Select syslog program identity name."  
DefineCustomEnumVariable( "pg_log_userqueries.log_level",  
  "Selects level of log (same options than log_min_messages."  

digoal’s 大量PostgreSQL文章入口