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."