PostgreSQL 事件触发器应用 - DDL审计-阿里云开发者社区

开发者社区> 阿里云数据库> 正文

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

版权声明:本文内容由阿里云实名注册用户自发贡献,版权归原作者所有,阿里云开发者社区不拥有其著作权,亦不承担相应法律责任。具体规则请查看《阿里云开发者社区用户服务协议》和《阿里云开发者社区知识产权保护指引》。如果您发现本社区中有涉嫌抄袭的内容,填写侵权投诉表单进行举报,一经查实,本社区将立刻删除涉嫌侵权内容。

分享:
阿里云数据库
使用钉钉扫一扫加入圈子
+ 订阅

帮用户承担一切数据库风险,给您何止是安心!

官方博客
链接