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()

Name	Type	Description
classid	Oid	OID of catalog the object belongs in
objid	Oid	OID of the object in the catalog
objsubid	integer	Object sub-id (e.g. attribute number for columns)
command_tag	text	command tag
object_type	text	Type of the object
schema_name	text	Name of the schema the object belongs in, if any; otherwise NULL. No quoting is applied.
object_identity	text	Text rendering of the object identity, schema-qualified. Each and every identifier present in the identity is quoted if necessary.
in_extension	bool	whether the command is part of an extension script
command	pg_ddl_command	A complete representation of the command, in internal format. This cannot be output directly, but it can be passed to other functions to obtain different pieces of information about the command.

1.2 sql_drop

通過這個函數進行捕獲：pg_event_trigger_dropped_objects()

Name	Type	Description
classid	Oid	OID of catalog the object belonged in
objid	Oid	OID the object had within the catalog
objsubid	int32	Object sub-id (e.g. attribute number for columns)
original	bool	Flag used to identify the root object(s) of the deletion
normal	bool	Flag indicating that there's a normal dependency relationship in the dependency graph leading to this object
is_temporary	bool	Flag indicating that the object was a temporary object.
object_type	text	Type of the object
schema_name	text	Name of the schema the object belonged in, if any; otherwise NULL. No quoting is applied.
object_name	text	Name of the object, if the combination of schema and name can be used as a unique identifier for the object; otherwise NULL. No quoting is applied, and name is never schema-qualified.
object_identity	text	Text rendering of the object identity, schema-qualified. Each and every identifier present in the identity is quoted if necessary.
address_names	text[]	An array that, together with object_type and address_args, can be used by the pg_get_object_address() to recreate the object address in a remote server containing an identically named object of the same kind.
address_args	text[]	Complement for address_names above.

1.3 table_rewrite

通過如下函數進行捕獲：

Name	Return Type	Description
pg_event_trigger_table_rewrite_oid()	Oid	The OID of the table about to be rewritten.
pg_event_trigger_table_rewrite_reason()	int	The reason code(s) explaining the reason for rewriting. The exact meaning of the codes is release dependent.

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

最後更新：2017-10-28 23:03:43

PostgreSQL 事件觸發器應用 - DDL審計

標簽

背景

事件觸發器審計DDL操作

PG 9.4的例子

小結

參考

上一篇： PostgreSQL 空間、多維序列生成方法

下一篇： PostgreSQL漢字轉拚音

相關內容

熱門內容

最新內容

PostgreSQL 事件觸發器應用 - DDL審計

標簽

背景

事件觸發器審計DDL操作

PG 9.4的例子

小結

參考

上一篇： PostgreSQL 空間、多維 序列 生成方法

下一篇： PostgreSQL漢字轉拚音

相關內容

熱門內容

最新內容

上一篇： PostgreSQL 空間、多維序列生成方法