postgresql DDL审计

postgres=# create database db1 ;
postgres=# \c db1 ;
db1=# create extension hstore;
db1=# create or replace function ef_alter() returns event_trigger as $$

declare  
rec hstore;
begin  
select hstore(pg_stat_activity.*) into rec from pg_stat_activity where pid=pg_backend_pid();  -- 记录pg_stat_activity的内容  
insert into aud_alter (ctx) values (rec);
end;
$$ language plpgsql strict;
db1=# create event trigger e_alter on ddl_command_end when tag in ('ALTER TABLE') execute procedure ef_alter();
db1=# create table aud_alter(id serial primary key, crt_time timestamp default now(), ctx hstore);
### 测试
db1=# create table test(id int);
db1=# alter table test alter column id type int8;
db1=# \x;
db1=# select * from aud_alter;
-[ RECORD 1 ]-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

id       | 1
crt_time | 2018-07-29 13:20:14.783321
ctx      | "pid"=>"32512", "datid"=>"16386", "query"=>"alter table test alter column id type int8;", "state"=>"active", "datname"=>"db1", "usename"=>"postgres", "usesysid"=>"10", "wait_event"=>NULL, "xact_start"=>"2018-07-29 13:40:12.142124+08", "backend_xid"=>"698", "client_addr"=>NULL, "client_port"=>"-1", "query_start"=>"2018-07-29 13:40:12.142124+08", "backend_type"=>"client backend", "backend_xmin"=>"698", "state_change"=>"2018-07-29 13:40:12.142128+08", "backend_start"=>"2018-07-29 13:38:50.710944+08", "client_hostname"=>NULL, "wait_event_type"=>NULL, "application_name"=>"psql"


db1=# \x;
db1=# select each(ctx) from aud_alter where id=1;
                         each                          
-------------------------------------------------------
 (pid,32512)
 (datid,16386)
 (query,"alter table test alter column id type int8;")
 (state,active)
 (datname,db1)
 (usename,postgres)
 (usesysid,10)
 (wait_event,)
 (xact_start,"2018-07-29 13:40:12.142124+08")
 (backend_xid,698)
 (client_addr,)
 (client_port,-1)
 (query_start,"2018-07-29 13:40:12.142124+08")
 (backend_type,"client backend")
 (backend_xmin,698)
 (state_change,"2018-07-29 13:40:12.142128+08")
 (backend_start,"2018-07-29 13:38:50.710944+08")
 (client_hostname,)
 (wait_event_type,)
 (application_name,psql)
(20 rows)