PostgreSQL触发器,日志审计小神器
最近遇到的项目问题,审计日志记录不够详细,比如某用户编辑了某台设备,只记录了用户操作的设备名、操作时间、登录用户和登录IP,至于设备其他属性编辑前和编辑后的信息就没有更详细的对比了,审计粒度不够细,显然是不能让客户满意的,秉承客户满意优先原则,只好技术加持一波了。
实际客户想要记录的更多,涉及的业务属性比较广,返本溯源,我们决定在数据库层面解决,以期能最少的改动业务代码。
利用PostgreSql触发器在源表数据发生变化的时候准备一张对应表进行备份记录,把变化数据就像提交svn一样插入到历史表中,如图
解决方案有了,但其实还存在一个问题触发器只在数据库内部运行,如何记录是当前是哪个登录用户对数据源进行的变更还是个问题,经过一位大佬的指导,可以利用PostgreSql的会话变量解决,简单的测试代码如下
打印结果
这样就可以利用数据库会话变量的特性,在每一次的数据库变更前,先将全局操作pid关联到用户uid为一张表,而变更的时候,将将全局操作pid插入到历史数据表中,这样通过唯一的全局操作pid就可以关联查询出是哪个用户的操作了。
于是解决方案图变为
开心的代码实现
创建审计主表
创建审计历史表
创建用户id和操作id关联表
在用户进行数据操作变更的postgresql连接时,我们都先将数据库全局变量插入到operation_log表中,将当前登录的用户id与这次变更操作绑定在一起,这个可以封装在web框架层实现。
创建触发器
触发器函数定义了触发器被触发后执行的操作,下面列一下触发器函数中可以使用的变量,例子中的 OLD,就表示触发操作的旧数据行,详细如下:
l NEW:INSERT、UPDATE 操作触发的行级触发器中存储的新的数据行,
l OLD:INSERT、UPDATE 操作触发的行级触发器中存储的的数据行,
l TG_OP:代表当前触发器监听到的操作类型,数据类型为 text,内容为 INSERT、UPDATE、DELETE、TRUNCATE。
小结
本文只是大概如何利用PostgreSql触发器对用户行为进行详细日志审计,旨在提供一个触发器记录数据源变更和web框架结合的思路,希望对你有所帮助,如果你有更好的解决方案可以留言告诉我哦
PS:如果文章对你有价值,欢迎点个推荐。