PostgreSQL 事件触发器应用 - DDL审计

标签

PostgreSQL , 事件触发器 , DDL审计 , 表结构变更 , 建表等审计

---

背景

DDL语句的审计是非常重要的，目前PG的DDL审计记录在日志文件中。不便于查看。

为了让DDL事件记录到表中，方便查看，我们可以通过事件触发器来达到这个效果。

事件触发器审计DDL操作

事件触发器语法:

Command:     CREATE EVENT TRIGGER  
Description: define a new event trigger  
Syntax:  
CREATE EVENT TRIGGER name  
    ON event  
    [ WHEN filter_variable IN (filter_value [, ... ]) [ AND ... ] ]  
    EXECUTE PROCEDURE function_name()  

1、事件触发器的触发点(event)

目前支持4个触发点(event)

ddl_command_start, ddl_command_end, table_rewrite and sql_drop  

这四个触发点，有3个能捕获到事件发生时的信息。

1.1 ddl_command_end

通过这个函数进行捕获：pg_event_trigger_ddl_commands()

1.2 sql_drop

通过这个函数进行捕获：pg_event_trigger_dropped_objects()

1.3 table_rewrite

通过如下函数进行捕获：

2、创建三个触发点的捕获信息存储表

create schema pgaudit;
grant USAGE on schema pgaudit to public;
 
create table pgaudit.audit_ddl_command_end (  
  event text,  
  tag text,  
  username name default current_user,  
  datname name default current_database(),  
  client_addr inet default inet_client_addr(),  
  client_port int default inet_client_port(),  
  crt_time timestamp default now(),  
  classid oid,  
  objid oid,  
  objsubid int,  
  command_tag text,  
  object_type text,  
  schema_name text,  
  object_identity text,  
  is_extension bool,  
  xid bigint default txid_current()
);  
  
create table pgaudit.audit_sql_drop (  
  event text,  
  tag text,  
  username name default current_user,  
  datname name default current_database(),  
  client_addr inet default inet_client_addr(),  
  client_port int default inet_client_port(),  
  crt_time timestamp default now(),  
  classid oid,  
  objid oid,  
  objsubid int,  
  original bool,  
  normal bool,  
  is_temporary bool,  
  object_type text,  
  schema_name text,  
  object_name text,  
  object_identity text,  
  address_names text[],  
  address_args text[],  
  xid bigint default txid_current() 
);  
  
create table pgaudit.audit_table_rewrite (  
  event text,  
  tag text,  
  username name default current_user,  
  datname name default current_database(),  
  client_addr inet default inet_client_addr(),  
  client_port int default inet_client_port(),  
  crt_time timestamp default now(),  
  table_rewrite_oid oid,  
  table_rewrite_reason int,  
  xid bigint default txid_current()
);  
  
grant select,update,delete,insert,truncate on pgaudit.audit_ddl_command_end to public;  
grant select,update,delete,insert,truncate on pgaudit.audit_sql_drop to public;  
grant select,update,delete,insert,truncate on pgaudit.audit_table_rewrite to public;  

3、创建三个触发点的事件触发器函数

create or replace function pgaudit.et_ddl_command_end() returns event_trigger as $$  
declare  
begin  
  insert into pgaudit.audit_ddl_command_end (event, tag, classid, objid, objsubid, command_tag, object_type, schema_name, object_identity, is_extension )  
    select TG_EVENT, TG_TAG,    
      classid, objid, objsubid, command_tag, object_type, schema_name, object_identity, in_extension from  
      pg_event_trigger_ddl_commands();  
   -- exception when others then  
   --   return;  
end;  
$$ language plpgsql strict;  

create or replace function pgaudit.et_sql_drop() returns event_trigger as $$  
declare  
begin  
  insert into pgaudit.audit_sql_drop (event, tag, classid, objid, objsubid, original, normal, is_temporary, object_type, schema_name, object_name, object_identity, address_names, address_args)  
    select TG_EVENT, TG_TAG,
      classid, objid, objsubid, original, normal, is_temporary, object_type, schema_name, object_name, object_identity, address_names, address_args from  
      pg_event_trigger_dropped_objects();  
   -- exception when others then  
   --   return;  
end;  
$$ language plpgsql strict;  

create or replace function pgaudit.et_table_rewrite() returns event_trigger as $$  
declare  
begin  
  insert into pgaudit.audit_table_rewrite (event, tag, table_rewrite_oid, table_rewrite_reason)   
    select TG_EVENT, TG_TAG,    
      pg_event_trigger_table_rewrite_oid(),  
      pg_event_trigger_table_rewrite_reason();  
   -- exception when others then  
   --   return;  
end;  
$$ language plpgsql strict;  

4、创建三个触发点的事件触发器

CREATE EVENT TRIGGER et_ddl_command_end on ddl_command_end EXECUTE PROCEDURE pgaudit.et_ddl_command_end();  
  
CREATE EVENT TRIGGER et_sql_drop on sql_drop EXECUTE PROCEDURE pgaudit.et_sql_drop();  
  
CREATE EVENT TRIGGER et_table_rewrite on table_rewrite EXECUTE PROCEDURE pgaudit.et_table_rewrite();  

5、模板化

在模板库，执行第二到第四步。

\c template1 postgres  
-- 在模板库，执行第二到第四步。  

6、通过模板创建的数据库，会自动继承这个模板。

postgres=# create database db1 template template1;  
CREATE DATABASE  

7、例子

7.1、建表

postgres=# \c db1 test  
You are now connected to database "db1" as user "test".  
db1=> create table tbl(id int);  
CREATE TABLE  

7.2、写入数据

db1=> insert into tbl select generate_series(1,100);  
INSERT 0 100  

7.3、重写表

db1=> alter table tbl add column info text default 'abc';  
ALTER TABLE  

7.4、删表

db1=> drop table tbl;  
DROP TABLE  

8、查询审计信息

db1=> select * from pgaudit.audit_ddl_command_end ;  
-[ RECORD 1 ]---+---------------------------  
event           | ddl_command_end  
tag             | CREATE TABLE  
username        | test  
datname         | db1  
client_addr     |   
client_port     |   
crt_time        | 2017-09-25 16:05:39.459787  
classid         | 1259  
objid           | 33212  
objsubid        | 0  
command_tag     | CREATE TABLE  
object_type     | table  
schema_name     | public  
object_identity | public.tbl  
is_extension    | f  
-[ RECORD 2 ]---+---------------------------  
event           | ddl_command_end  
tag             | ALTER TABLE  
username        | test  
datname         | db1  
client_addr     |   
client_port     |   
crt_time        | 2017-09-25 16:05:59.781995  
classid         | 1259  
objid           | 33212  
objsubid        | 0  
command_tag     | ALTER TABLE  
object_type     | table  
schema_name     | public  
object_identity | public.tbl  
is_extension    | f  

db1=> select * from pgaudit.audit_sql_drop ;  
-[ RECORD 1 ]---+--------------------------------  
event           | sql_drop  
tag             | DROP TABLE  
username        | test  
datname         | db1  
client_addr     |   
client_port     |   
crt_time        | 2017-09-25 16:06:08.22198  
classid         | 1259  
objid           | 33212  
objsubid        | 0  
original        | t  
normal          | f  
is_temporary    | f  
object_type     | table  
schema_name     | public  
object_name     | tbl  
object_identity | public.tbl  
address_names   | {public,tbl}  
address_args    | {}  
-[ RECORD 2 ]---+--------------------------------  
event           | sql_drop  
tag             | DROP TABLE  
username        | test  
datname         | db1  
client_addr     |   
client_port     |   
crt_time        | 2017-09-25 16:06:08.22198  
classid         | 2604  
objid           | 33215  
objsubid        | 0  
original        | f  
normal          | f  
is_temporary    | f  
object_type     | default value  
schema_name     |   
object_name     |   
object_identity | for public.tbl.info  
address_names   | {public,tbl,info}  
address_args    | {}  
-[ RECORD 3 ]---+--------------------------------  
event           | sql_drop  
tag             | DROP TABLE  
username        | test  
datname         | db1  
client_addr     |   
client_port     |   
crt_time        | 2017-09-25 16:06:08.22198  
classid         | 1247  
objid           | 33214  
objsubid        | 0  
original        | f  
normal          | f  
is_temporary    | f  
object_type     | type  
schema_name     | public  
object_name     | tbl  
object_identity | public.tbl  
address_names   | {public.tbl}  
address_args    | {}  
-[ RECORD 4 ]---+--------------------------------  
event           | sql_drop  
tag             | DROP TABLE  
username        | test  
datname         | db1  
client_addr     |   
client_port     |   
crt_time        | 2017-09-25 16:06:08.22198  
classid         | 1247  
objid           | 33213  
objsubid        | 0  
original        | f  
normal          | f  
is_temporary    | f  
object_type     | type  
schema_name     | public  
object_name     | _tbl  
object_identity | public.tbl[]  
address_names   | {public.tbl[]}  
address_args    | {}  
-[ RECORD 5 ]---+--------------------------------  
event           | sql_drop  
tag             | DROP TABLE  
username        | test  
datname         | db1  
client_addr     |   
client_port     |   
crt_time        | 2017-09-25 16:06:08.22198  
classid         | 1259  
objid           | 33222  
objsubid        | 0  
original        | f  
normal          | f  
is_temporary    | f  
object_type     | toast table  
schema_name     | pg_toast  
object_name     | pg_toast_33212  
object_identity | pg_toast.pg_toast_33212  
address_names   | {pg_toast,pg_toast_33212}  
address_args    | {}  
-[ RECORD 6 ]---+--------------------------------  
event           | sql_drop  
tag             | DROP TABLE  
username        | test  
datname         | db1  
client_addr     |   
client_port     |   
crt_time        | 2017-09-25 16:06:08.22198  
classid         | 1259  
objid           | 33224  
objsubid        | 0  
original        | f  
normal          | f  
is_temporary    | f  
object_type     | index  
schema_name     | pg_toast  
object_name     | pg_toast_33212_index  
object_identity | pg_toast.pg_toast_33212_index  
address_names   | {pg_toast,pg_toast_33212_index}  
address_args    | {}  
-[ RECORD 7 ]---+--------------------------------  
event           | sql_drop  
tag             | DROP TABLE  
username        | test  
datname         | db1  
client_addr     |   
client_port     |   
crt_time        | 2017-09-25 16:06:08.22198  
classid         | 1247  
objid           | 33223  
objsubid        | 0  
original        | f  
normal          | f  
is_temporary    | f  
object_type     | type  
schema_name     | pg_toast  
object_name     | pg_toast_33212  
object_identity | pg_toast.pg_toast_33212  
address_names   | {pg_toast.pg_toast_33212}  
address_args    | {}  

db1=> select * from pgaudit.audit_table_rewrite ;  
     event     |     tag     | username | datname | client_addr | client_port |          crt_time          | table_rewrite_oid | table_rewrite_reason   
---------------+-------------+----------+---------+-------------+-------------+----------------------------+-------------------+----------------------  
 table_rewrite | ALTER TABLE | test     | db1     |             |             | 2017-09-25 16:05:59.781995 |             33212 |                    2  
(1 row)  

PG 9.4的例子

create schema pgaudit;
grant USAGE on schema pgaudit to public;
 
create extension hstore SCHEMA pgaudit;  
 
create table pgaudit.audit_ddl_command_end ( 
  event text,  
  tag text,  
  username name default current_user,  
  datname name default current_database(),  
  client_addr inet default inet_client_addr(),  
  client_port int default inet_client_port(),  
  crt_time timestamp default now(),  
  ctx pgaudit.hstore, 
  xid bigint default txid_current() 
);   
 
create table pgaudit.audit_sql_drop (  
  event text,  
  tag text,  
  username name default current_user,  
  datname name default current_database(),  
  client_addr inet default inet_client_addr(),  
  client_port int default inet_client_port(),  
  crt_time timestamp default now(),  
  classid oid,  
  objid oid,  
  objsubid int,  
  object_type text,  
  schema_name text,  
  object_name text,  
  object_identity text, 
  xid bigint default txid_current() 
);  
 
grant select,update,delete,insert,truncate on pgaudit.audit_ddl_command_end to public;
grant select,update,delete,insert,truncate on pgaudit.audit_sql_drop to public;
 
  
create or replace function pgaudit.ef_ddl_command_end() returns event_trigger as $$  
declare  
  rec pgaudit.hstore;  
begin  
  select pgaudit.hstore(pg_stat_activity.*) into rec from pg_stat_activity where pid=pg_backend_pid();  
  insert into pgaudit.audit_ddl_command_end (event, tag, ctx) values (TG_EVENT, TG_TAG, rec);  
end;  
$$ language plpgsql strict;  
 
 
 
create or replace function pgaudit.ef_sql_drop() returns event_trigger as $$  
declare  
begin  
  insert into pgaudit.audit_sql_drop (event, tag, classid, objid, objsubid, object_type, schema_name, object_name, object_identity)  
    select TG_EVENT, TG_TAG, classid, objid, objsubid, object_type, schema_name, object_name, object_identity from   
      pg_event_trigger_dropped_objects();  
   -- exception when others then  
   --   return;  
end;  
$$ language plpgsql strict;  
 
 
 
create event trigger ef_ddl_command_end on ddl_command_end execute procedure pgaudit.ef_ddl_command_end();  
create event trigger ef_sql_drop on sql_drop execute procedure pgaudit.ef_sql_drop();  

小结

1、本文以PG 10为例，介绍了通过事件触发器，审计DDL的功能。（其他版本可能需要略微修改。）

2、事件触发器的其他应用，例如限制用户执行某些DDL等。

《PostgreSQL Oracle 兼容性之 - 事件触发器实现类似Oracle的回收站功能》

《PostgreSQL 事件触发器 - DDL审计 , DDL逻辑复制 , 打造DDL统一管理入》

《PostgreSQL 事件触发器 - PostgreSQL 9.3 Event Trigger》

参考

https://www.postgresql.org/docs/9.6/static/functions-event-triggers.html