PostgreSQL Audit by Database or Role wide parameter

本文基于postgresql 9.2讲解.

审计的一个普遍用途就是记录用户在数据库中都干了些什么?

如果系统级的参数设置日志全记录下来当然也可以达到审计的目的, 但是这样做的话很多不需要的日志也会输出, 造成硬盘资源浪费甚至影响数据库性能. 所以精细化的审计非常有必要.

之前写过一篇关于postgresql借助pg_log_userqueries插件实现用户或数据库级审计的文章.

http://blog.163.com/digoal@126/blog/static/1638770402012019112218804/

但是实际上, 使用数据库本身的配置就可以达到此目的. 主要用到postgresql的以下两项配置.

1. postgresql 的配置文件中对于日志的配置项what to log中包含了默认的记录哪些日志的配置.

   这些配置默认情况下是全局生效的, 不管是哪个用户连过来, 或者连到哪个数据库. 都是取的这些配置项.

2. postgresql 提供了用户级以及数据库级的参数配置. 

【审计举例】

假设当前的postgresql.conf log配置项如下 :

1. 审计用户digoal连到数据库digoal 的所有sql.

# 查看当前的数据库+role级别的配置 : 

# 测试 : 

# 打开一个终端观察日志的输出

# 使用digoal用户连接到digoal数据库, 并执行sql

# select current_user被记录在日志中了

# digoal用户连接到其他数据库, 或者其他用户连接到digoal数据库的sql不会被记录.

# 以上sql在日志中无记录.

2. 审计用户digoal 的所有sql.

# 测试, digoal用户连接到postgres数据库执行sql

# 日志输出 : 

3. 审计任何用户连接到数据库digoal 的所有sql.

# 测试, postgres用户连接到digoal数据库执行sql

【清除以上审计配置】

【推荐的日志配置】

【其他】

1. 以上审计的粒度可能还是太大, 只到了用户和数据库的层面. 

并且, 仅仅审计到用户调用的sql, 而不能审计到变化前后的数据, 例如用户执行一条update语句, 可能变更了整张表的数据, 但是使用log_statement只能记录下这条sql, 而不是记录被变更前后的数据.

如果要对表或者表上面的数据进行审计的话, 需要用上触发器.

例如 : 

http://blog.163.com/digoal@126/blog/static/163877040201252575529358/

如果不想将这些记录写在数据库中, 而是输出到日志的话 : 

1.  触发器中使用raise warning '....' 打印消息.

2.  postgresql.conf 配置 log_min_messages = warning 

这样的话'....'的消息就会打印到日志中.

具体的例子在另一篇文章中再详细介绍一下.

3. postgresql 9.3还引入了事件触发器的功能, 也可以作为审计的外围工具之一.

感兴趣的朋友可以参考

http://blog.163.com/digoal@126/blog/static/16387704020132131361949/

【注意】

1. 超级用户可以修改这些配置项, 所以被审计的用户最好是普通用户. 否则用户连上来可以修改这些审计项.

2. 程序用的数据库账号和个人用的数据库账号分开, 对于程序用的数据库账号可以只审计ddl操作, 而对于个人使用的数据库账号, 建议审计所有的sql.

【参考】

1. postgresql.conf

2. http://www.postgresql.org/docs/9.2/static/sql-alterrole.html

3. http://www.postgresql.org/docs/9.2/static/sql-alterdatabase.html

4. http://www.postgresql.org/docs/9.2/static/sql-set.html

5. http://www.postgresql.org/docs/9.2/static/runtime-config.html

6. http://blog.163.com/digoal@126/blog/static/163877040201252575529358/

7. http://blog.163.com/digoal@126/blog/static/1638770402012019112218804/

8. http://blog.163.com/digoal@126/blog/static/16387704020132131361949/