基于表的ddl（alter table）审计

环境

系统平台：Linux x86-64 Red Hat Enterprise Linux 7
版本：4.3.2

文档用途

使用事件触发器审计表的alter table操作

详细信息

如果你要记录表被用户执行DDL，修改定义, 设置默认值, 等等alter table可以完成的工作时的记录, 可以使用事件触发器来达到这个目的.

例子如下 :

# create or replace function ef_alter() returns event_trigger as $$declarerec hstore;beginselecthstore(pg_stat_activity.*)intorecfrompg_stat_activitywherepid=pg_backend_pid();insertintoaud_alter(ctx)values(rec);end;$$languageplpgsql strict;CREATEFUNCTIONtestdb=# create event trigger e_alter on ddl_command_end when tag in ('ALTER TABLE') execute procedure ef_alter();CREATEEVENTTRIGGERtestdb=# create table aud_alter(id serial primary key, crt_time timestamp default now(), ctx hstore);CREATETABLEtestdb=# create table test(id int);CREATETABLEtestdb=# alter table test alter column id type int8;ALTERTABLEtestdb=# select * from aud_alter;-[RECORD1]------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------id|1crt_time|2018-05-0313:44:12.309983ctx|"pid"=>"9408","datid"=>"16384","query"=>"alter table test alter column id type int8;","state"=>"active","datname"=>"testdb","usename"=>"postgres","waiting"=>"f","usesysid"=>"10","xact_start"=>"2018-05-03 13:44:12.309983+08","backend_xid"=>"8367","client_addr"=>NULL,"client_port"=>"-1","query_start"=>"2018-05-03 13:44:12.309983+08","backend_xmin"=>"8367","state_change"=>"2018-05-03 13:44:12.309985+08","backend_start"=>"2018-05-03 13:40:15.0561+08","client_hostname"=>NULL,"application_name"=>"psql"testdb=# select each(ctx) from aud_alter where id=1;each-------------------------------------------------------(pid,9408)(datid,16384)(query,"alter table test alter column id type int8;")(state,active)(datname,testdb)(usename,postgres)(waiting,f)(usesysid,10)(xact_start,"2018-05-03 13:44:12.309983+08")(backend_xid,8367)(client_addr,)(client_port,-1)(query_start,"2018-05-03 13:44:12.309983+08")(backend_xmin,8367)(state_change,"2018-05-03 13:44:12.309985+08")(backend_start,"2018-05-03 13:40:15.0561+08")(client_hostname,)(application_name,psql)(18rows)

query即当时的ALTER TABLE SQL.

其他辅助的还有用户当时的IP, PORT, 用户, 链接的数据库等信息。

如果只想跟踪表的字段被修改的前后类型, 更严格的做法应该是从parsetree中取出被修改的字段, 类型。