postgresql DDL审计
以Postgres10为例: 如下教程需要先安装这个组件:yum install postgresql10-contrib 安装完成后,然后,登录到pgsql,执行如下的一系列命令(以要审计db1为例):
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) 参考:德哥https://yq.aliyun.com/articles/65362 (编辑:李大同) 【声明】本站内容均来自网络,其相关言论仅代表作者个人观点,不代表本站立场。若无意侵犯到您的权利,请及时与联系站长删除相关内容! |