PostgreSQL Fine-Grained Table,Column,Row Level Audit

通过配置用户级或数据库级的参数可以实现用户以及数据库级别的审计, 但是这样的粒度可能还是太粗糙了.

如果需要更细致的审计, 例如针对某些表的操作审计, 某些用户对某些表的审计, 或者仅仅当某个列的值发生变化时才被审计(记录到log或表里面, 本文的例子是将审计信息输出到log, 使用raise).

这样的需求可以通过触发器来实现.

接下来以postgresql 9.2为例进行讲解.

# 基础的参数配置

# 创建测试表 : 

# 插入测试数据 : 

【审计场景1】

1. 审计某个表的insert, update, delete, truncate语句.

使用after for each statement触发器.

# 创建触发器函数

# 创建触发器

# 测试插入

# 测试更新

# 测试删除

# 测试truncate

# 注意回滚的操作不会被记录. 即使log_statement = 'ddl', 所以rollback没有被记录下来.

# 这是个弊端. 需要注意. 希望未来的postgresql版本加以改进. 现在的解决办法是修正触发器的触发点, 小结部分会提到.

# 以上操作的日志输出如下.

【审计场景2】

2. 按用户审计某个表的insert, update, delete, truncate语句.

使用after for each statement when (current_user='')触发器.

# 删除前面用到的触发器

# 创建触发器, 这次带上when条件

# 测试digoal用户的操作

# 测试其他用户的操作, 不被审计

【审计场景3】

3. 按条件审计某个表的insert, update, delete语句.

使用after for each row when (new.balance <> old.balance)触发器.

# 新建触发器函数

# 新建触发器

【审计场景4】

# 基于列的判断审计

# 创建触发器, 这里用到when条件, 只有当balance变化时才审计

# 测试

# balance变化才审计

【小结】

1. 前面提到rollback等事务相关的sql不会被审计到, 所以当sql执行失败时, log已经记录了, 但是没有记录回滚的动作, 所以信息是不完整的, 除非从xlog/clog中取出对应的xid是提交还是回滚. 

为了使记录在log中的语句一定是提交的, 那么需要调整一下触发器的创建方法, 使得回滚的事务中所有的sql都不被审计.

如下,

触发器只有在提交时才会触发, 回滚不触发. (使用constraint来创建触发器)

注意以上方法只有after ... for each row才能被用到.

【参考】

1. http://blog.163.com/digoal@126/blog/static/16387704020132208241607/

2. http://blog.163.com/digoal@126/blog/static/1638770402013283547959/

3. http://blog.163.com/digoal@126/blog/static/1638770402013211102130526/

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

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

6. http://www.postgresql.org/docs/9.2/static/sql-createtrigger.html