PostgreSQL审计插件之supa_audit

supa_audit插件网址

参考文章：Postgres Auditing in 150 lines of SQL

supa_audit插件支持审计指定表的insert,update,delete和truncate操作。

被审计的表要求有主键。

安装supa_audit插件前提

审计记录表使用了PostgreSQL14版本内置的uuid功能。PG14之前的版本，需要安装uuid-ossp扩展，是用来产生通用唯一标识符。

安装uuid-ossp扩展之前，要安装系统依赖包

# yum install uuid uuid-devel

并且在编译数据库软件时，configure 命令添加 --with-uuid=ossp 参数

./configure --prefix=/u01/pg13/pg13.6 --with-uuid=ossp  ...... 

安装supa_audit插件

下载并解压插件

unzip /home/postgres/supa_audit-main.zip
cd supa_audit-main/
cp supa_audit* $PGHOME/share/postgresql/extension/

安装supa_audit插件，在pg13.6版本中，安装此插件过程中，会自动在当前数据库中创建audit模式和uuid-oosp扩展等。

$ psql
psql (13.6)
Type "help" for help.

postgres=# create database auditdb;
CREATE DATABASE
postgres=# \c auditdb;
You are now connected to database "auditdb" as user "postgres".
auditdb=# create extension supa_audit cascade;
NOTICE:  installing required extension "uuid-ossp"
CREATE EXTENSION
auditdb=# \dx
                            List of installed extensions
    Name    | Version |   Schema   |                   Description                   
------------+---------+------------+-------------------------------------------------
 plpgsql    | 1.0     | pg_catalog | PL/pgSQL procedural language
 supa_audit | 0.2.3   | public     | Generic table auditing
 uuid-ossp  | 1.1     | public     | generate universally unique identifiers (UUIDs)
(3 rows)

auditdb=# \df *.uuid*
                                   List of functions
   Schema   |        Name        | Result data type |    Argument data types    | Type 
------------+--------------------+------------------+---------------------------+------
 pg_catalog | uuid_cmp           | integer          | uuid, uuid                | func
 pg_catalog | uuid_eq            | boolean          | uuid, uuid                | func
 pg_catalog | uuid_ge            | boolean          | uuid, uuid                | func
 pg_catalog | uuid_gt            | boolean          | uuid, uuid                | func
 pg_catalog | uuid_hash          | integer          | uuid                      | func
 pg_catalog | uuid_hash_extended | bigint           | uuid, bigint              | func
 pg_catalog | uuid_in            | uuid             | cstring                   | func
 pg_catalog | uuid_le            | boolean          | uuid, uuid                | func
 pg_catalog | uuid_lt            | boolean          | uuid, uuid                | func
 pg_catalog | uuid_ne            | boolean          | uuid, uuid                | func
 pg_catalog | uuid_out           | cstring          | uuid                      | func
 pg_catalog | uuid_recv          | uuid             | internal                  | func
 pg_catalog | uuid_send          | bytea            | uuid                      | func
 pg_catalog | uuid_sortsupport   | void             | internal                  | func
 public     | uuid_generate_v1   | uuid             |                           | func
 public     | uuid_generate_v1mc | uuid             |                           | func
 public     | uuid_generate_v3   | uuid             | namespace uuid, name text | func
 public     | uuid_generate_v4   | uuid             |                           | func
 public     | uuid_generate_v5   | uuid             | namespace uuid, name text | func
 public     | uuid_nil           | uuid             |                           | func
 public     | uuid_ns_dns        | uuid             |                           | func
 public     | uuid_ns_oid        | uuid             |                           | func
 public     | uuid_ns_url        | uuid             |                           | func
 public     | uuid_ns_x500       | uuid             |                           | func
(24 rows)

auditdb=# \dn
  List of schemas
  Name  |  Owner   
--------+----------
 audit  | postgres
 public | postgres
(2 rows)

auditdb=# \df audit.*
                                               List of functions
 Schema |             Name             | Result data type |             Argument data types             | Type 
--------+------------------------------+------------------+---------------------------------------------+------
 audit  | disable_tracking             | void             | regclass                                    | func
 audit  | enable_tracking              | void             | regclass                                    | func
 audit  | insert_update_delete_trigger | trigger          |                                             | func
 audit  | primary_key_columns          | text[]           | entity_oid oid                              | func
 audit  | to_record_id                 | uuid             | entity_oid oid, pkey_cols text[], rec jsonb | func
 audit  | truncate_trigger             | trigger          |                                             | func
(6 rows)

开启表的审计功能

创建有主键的表

create table public.account( id int primary key, name text not null );

开启表的审计功能

select audit.enable_tracking('public.account'::regclass);

审计结果展示

-- 查询某一行数据的所有操作记录
auditdb=# select * from audit.record_version where record_id ='0b43fbb6-8481-57b5-be9b-7e493aef8619' or old_record_id ='0b43fbb6-8481-57b5-be9b-7e493aef8619';
   id    |              record_id               |            old_record_id             |   op   |              ts               | table_oid | table_schema | table_name |         record         |       old_r
ecord       
---------+--------------------------------------+--------------------------------------+--------+-------------------------------+-----------+--------------+------------+------------------------+------------
------------
       1 | 0b43fbb6-8481-57b5-be9b-7e493aef8619 |                                      | INSERT | 2022-04-08 14:45:24.344436+08 |     16437 | public       | account    | {"id": 1, "name": "a"} | 
 1000001 | 0b43fbb6-8481-57b5-be9b-7e493aef8619 | 0b43fbb6-8481-57b5-be9b-7e493aef8619 | UPDATE | 2022-04-08 14:50:39.186128+08 |     16437 | public       | account    | {"id": 1, "name": "b"} | {"id": 1, "
name": "a"}
 1500000 |                                      | 0b43fbb6-8481-57b5-be9b-7e493aef8619 | DELETE | 2022-04-08 14:52:29.97163+08  |     16437 | public       | account    |                        | {"id": 1, "
name": "b"}
(3 rows)
-- 查询表的truncate记录
auditdb=# select * from audit.record_version where op = 'TRUNCATE' and table_schema='public' and table_name = 'account';
   id    | record_id | old_record_id |    op    |              ts               | table_oid | table_schema | table_name | record | old_record 
---------+-----------+---------------+----------+-------------------------------+-----------+--------------+------------+--------+------------
 1999999 |           |               | TRUNCATE | 2022-04-08 14:56:45.787114+08 |     16437 | public       | account    |        | 
(1 row)