自定义SQL实现PostgreSQL安全审计

数据审计是一个跟踪表内容随时间变化的系统。数据审计是安全合规的必备功能之一。PostgreSQL作为一个强大的现代开源关系型数据库，还有一个相关的插件PGAudit可以提供审计功能。PGAudit插件可以在以后详细介绍。在本文中，我们介绍了一个通过简单的SQL语句实现的数据集审计功能。概述最终实现效果为：创建示例表：createextensionsupa_auditcascade；创建表public.account（idint主键，名称文本不为空）；启用审计：selectaudit.enable_tracking('public.account'::regclass);增改删操作：insertintopublic.account(id,name)values(1,'冲冲');updatepublic.accountsetname='CC'whereid=1;deletefrompublic.accountwhereid=1;清空表：truncatetablepublic.account；查看审计日志：select*fromaudit.record_history注意更新一行时record_id和old_record_id保持不变，方便查询单行历史。要关闭审计跟踪，只需执行：selectaudit.disable_tracking('public.account'::regclass);实现首先创建一个名为audit的schema用于审计：createschemaifnotexistsaudit；记录存储接下来，您需要一个表来跟踪插入、更新和删除。传统上，审计模式与其他元数据列一起使用，例如提交的时间戳。该解决方案带来了一些维护挑战：对表启用审计需要数据库迁移当源表的模式发生变化时，被审计表的模式也必须改变为此，使用PostgreSQL的无模式JSONB数据类型来存储每条记录的数据在单列中间。这种方法的另一个好处是它允许将多个表的审计历史存储在单个审计表中。创建表audit.record_version（idbigserial主键，record_iduuid，old_record_iduuid，opvarchar（8）不为空，tstimestamptz不为空默认现在（），table_oidoid不为空，table_schema名称不为空，table_name名称不为空，记录jsonb,old_recordjsonb);查询和索引查询性能很重要，如果不能快速查询日志，审计日志的实际意义不大。为了提高查询性能，需要对最常用的查询涉及的字段创建索引。一个时间范围内的查询对于一个时间范围，需要一个索引ts。由于审计表只用于插入记录，而ts列插入的是操作时间，其值ts自然是按升序排列的。PostgreSQL内置的BRIN索引可以利用值和物理位置之间的相关性来生成一个比默认索引（BTREE索引）小数百倍并且具有更快查找时间的索引。使用brin(ts)创建索引record_version_tsonaudit.record_version；对于表查询，包括一个table_oid列，用于跟踪PostgreSQL内部数字表标识符。可以为该列而不是table_schema和able_name列添加索引，从而最小化索引大小并提供更好的性能。使用btree(table_oid)在audit.record_version上创建索引record_version_table_oid；记录唯一标识符将每一行的数据存储为jsonb的缺点之一是基于列值的过滤变得非常低效。如果您想快速查找一行的历史记录，您需要为每一行提取和索引一个唯一标识符。对于全局唯一标识符，使用以下结构：[table_oid,primary_key_value_1,primary_key_value_2,...]并将此数组散列为UUIDv5以获得有效的可索引UUID类型，以识别对数据更改具有鲁棒性的行。使用实用函数查找记录的主键列的名称：createorreplacefunctionaudit.primary_key_columns(entity_oidoid)returnstext[]stablesecuritydefinerlanguagesqlas$$--查找表的主键列的名称selectcoalesce(array_agg(pa.attname::textorderbypa.attnum),array[]::text[])column_namesfrompg_indexpijoinpg_attributepaonpi.indrelid=pa.attrelidandpa.attnum=any(pi.indkey)whereindrelid=$1andindisprimary$$;另一个对于table_oid和主键，将结果转换为记录的UUID。创建或替换函数audit.to_record_id(entity_oidoid,pkey_colstext[],recjsonb)返回uuidstablelanguagesqlas$$selectcasewhenrecisnullthennull--如果不存在主键，则使用随机uuidwhenpkey_cols=array[::text[]然后uuid_generate_v4()else(selectuuid_generate_v5('fd62bc3d-8d6e-43c2-919c-802ba3762271',(jsonb_build_array(to_jsonb($1))||jsonb_agg($3->key_)::text)fromunnest($2)x(键_））结束$$；最后，索引record_id和old_record_id包含这些用于快速查找的唯一标识符的列。创建索引record_version_record_idonaudit.record_version(record_id)其中record_id不为空；创建索引record_version_old_record_idonaudit.record_version(record_id)其中old_record_id不为空；触发器为使审计功能真正发挥作用，需要在最终用户未对其事务进行任何更改时执行此操作，以防向审计表中插入记录。为此，设置一个触发器以在数据更改时触发，并为每个插入/更新/删除操作触发一个触发器。创建或替换函数audit.insert_update_delete_trigger()返回triggersecuritydefinerlanguageplpgsqlas$$declarepkey_colstext[]=audit.primary_key_columns(TG_RELID);record_jsonbjsonb=to_jsonb(new);record_iduuid=audit.to_record_id(TG_RELID,pkey_cols,record_jsonb);old_record_jsonbjsonb=to_jsonb(old);old_record_iduuid=audit.to_record_id(TG_RELID,pkey_cols,old_record_jsonb);开始插入audit.record_version(record_id,old_record_id,op,table_oid,table_schema,table_name,record,old_record)selectrecord_id,old_record_id,TG_OP,TG_RELID，TG_TABLE_SCHEMA，TG_TABLE_NAME，record_jsonb，old_record_jsonb；返回合并（新，旧）；结束；$$；API将公开用于在表上启用审计的API：selectaudit.enable_tracking('.

'::regclass);禁用跟踪：选择audit.disable_tracking('.'::regclass);这些函数根据请求按表注册审计触发器：创建或替换函数audit.enable_tracking(regclass)返回voidvolatilesecuritydefinerlanguageplpgsqlas$$declarestatement_rowtext=format('createtriggeraudit_i_u_dbbeforeinsertorupdateordeleteon%Iforeachrowexecuteprocedureaudit.insert_update_delete_trigger();',$1);pkey_colstext[]=audit.primary_key_columns($1);beginifpkey_ray::artext[]然后引发异常“表%不能被审计，因为它没有主键”，$1；如果不存在则结束（从pg_trigger选择1，其中tgrelid=$1和tgname='audit_i_u_d'）然后执行语句行；如果结束；结束；$$；创建或替换函数audit.disable_tracking(regclass)返回voidvolatilesecuritydefinerlanguageplpgsqlas$$declarestatement_rowtext=format('droptriggerifexistsaudit_i_u_don%I;',$1);beginexecutestatement_row;end;$$;performanceOverhead开启审计表会降低插入、更新和删除的吞吐量，但当吞吐量低于每秒1000次写入时，它的开销通常可以忽略不计。对于写入频率高的表，推荐使用pgAudit。总结Postgresql数据库的安全审计是通过简单的纯sql语句实现的。总体上只实现了150行sql语句。大家可以手动试试，主要是了解它的原理。如果生产环境有需要，推荐使用pgAudit。