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

本文涉及的产品
RDS MySQL Serverless 基础系列,0.5-2RCU 50GB
云原生数据库 PolarDB MySQL 版,Serverless 5000PCU 100GB
云原生数据库 PolarDB 分布式版,标准版 2核8GB
简介:

标签

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

相关实践学习
使用PolarDB和ECS搭建门户网站
本场景主要介绍基于PolarDB和ECS实现搭建门户网站。
阿里云数据库产品家族及特性
阿里云智能数据库产品团队一直致力于不断健全产品体系,提升产品性能,打磨产品功能,从而帮助客户实现更加极致的弹性能力、具备更强的扩展能力、并利用云设施进一步降低企业成本。以云原生+分布式为核心技术抓手,打造以自研的在线事务型(OLTP)数据库Polar DB和在线分析型(OLAP)数据库Analytic DB为代表的新一代企业级云原生数据库产品体系, 结合NoSQL数据库、数据库生态工具、云原生智能化数据库管控平台,为阿里巴巴经济体以及各个行业的企业客户和开发者提供从公共云到混合云再到私有云的完整解决方案,提供基于云基础设施进行数据从处理、到存储、再到计算与分析的一体化解决方案。本节课带你了解阿里云数据库产品家族及特性。
相关文章
|
7天前
|
SQL 关系型数据库 MySQL
轻松入门MySQL:保障数据完整性,MySQL事务在进销存管理系统中的应用(12)
轻松入门MySQL:保障数据完整性,MySQL事务在进销存管理系统中的应用(12)
|
7天前
|
SQL druid 关系型数据库
MySQL单机应用的性能优化
MySQL单机应用的性能优化
17 1
|
7天前
|
存储 前端开发 JavaScript
简单实现一个事件触发器
简单实现一个事件触发器
33 0
|
7天前
|
关系型数据库 MySQL 数据挖掘
轻松入门MySQL:深入理解MySQL聚合函数,实战进销存项目中的应用与技巧(8)
轻松入门MySQL:深入理解MySQL聚合函数,实战进销存项目中的应用与技巧(8)
|
5天前
|
存储 关系型数据库 MySQL
MySQL数据类型详解及实例应用
MySQL数据类型详解及实例应用
|
7天前
|
SQL 数据库
10. 触发器和事件
10. 触发器和事件
|
7天前
|
缓存 运维 Serverless
Serverless 应用引擎产品使用之在阿里函数计算中,使用签名认证调用HTTP触发器的步骤如何解决
阿里云Serverless 应用引擎(SAE)提供了完整的微服务应用生命周期管理能力,包括应用部署、服务治理、开发运维、资源管理等功能,并通过扩展功能支持多环境管理、API Gateway、事件驱动等高级应用场景,帮助企业快速构建、部署、运维和扩展微服务架构,实现Serverless化的应用部署与运维模式。以下是对SAE产品使用合集的概述,包括应用管理、服务治理、开发运维、资源管理等方面。
303 7
|
7天前
|
运维 监控 关系型数据库
Serverless 应用引擎产品使用之在阿里云函数计算(FC)中,要访问另一个账号的rds配置rds的白名单如何解决
阿里云Serverless 应用引擎(SAE)提供了完整的微服务应用生命周期管理能力,包括应用部署、服务治理、开发运维、资源管理等功能,并通过扩展功能支持多环境管理、API Gateway、事件驱动等高级应用场景,帮助企业快速构建、部署、运维和扩展微服务架构,实现Serverless化的应用部署与运维模式。以下是对SAE产品使用合集的概述,包括应用管理、服务治理、开发运维、资源管理等方面。
53 0
|
7天前
|
运维 Serverless API
Serverless 应用引擎产品使用之阿里函数计算中要关掉http触发器的jwt认证才可以进行性能探测如何解决
阿里云Serverless 应用引擎(SAE)提供了完整的微服务应用生命周期管理能力,包括应用部署、服务治理、开发运维、资源管理等功能,并通过扩展功能支持多环境管理、API Gateway、事件驱动等高级应用场景,帮助企业快速构建、部署、运维和扩展微服务架构,实现Serverless化的应用部署与运维模式。以下是对SAE产品使用合集的概述,包括应用管理、服务治理、开发运维、资源管理等方面。
16 1
|
7天前
|
人工智能 运维 Serverless
Serverless 应用引擎产品使用之阿里函数计算中3.0的函数只加自定义域名没有任何触发器的意义如何解决
阿里云Serverless 应用引擎(SAE)提供了完整的微服务应用生命周期管理能力,包括应用部署、服务治理、开发运维、资源管理等功能,并通过扩展功能支持多环境管理、API Gateway、事件驱动等高级应用场景,帮助企业快速构建、部署、运维和扩展微服务架构,实现Serverless化的应用部署与运维模式。以下是对SAE产品使用合集的概述,包括应用管理、服务治理、开发运维、资源管理等方面。
49 1

相关产品

  • 云原生数据库 PolarDB