1. 云图网首页
  2. 技术专区
  3. 数据库

PostgreSQL9.3Beta1：支持事件触发器 ( Event Triggers )

• 数据库

PostgreSQL 9.3 新增事件触发器，事件触发器为全局触发器，影响范围为指定的某个库，并且可以捕获 DDL 事件，而传统的触发器是基于表级别，并且只能捕获 DML 事件。

事件触发器概述

1.1 事件触发器语法

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
 
Name
  
CREATE EVENT TRIGGER -- define a new event trigger
  
Synopsis
  
CREATE EVENT TRIGGER name
 ON event
 [ WHEN filter_variable IN (filter_value [, ... ]) [ AND ... ] ]
 EXECUTE PROCEDURE function_name()
Description
  
  CREATE EVENT TRIGGER creates a new event trigger. Whenever the designated event occurs and the
WHEN condition associated with the trigger, if any, is satisfied, the trigger function will be
executed. For a general introduction to event triggers, see Chapter 37. The user who creates an
event triggerbecomes its owner.

1.2 关于触发器 EVENT

当指定 EVENT 发生时触发器才会被触发，目前支持三种 EVENT: ddl_command_start，ddl_command_end ，sql_drop。

  1. ddl_command_start : 在 CREATE, ALTER, 或 DROP 命令执行之前触发，例外的情况是: 这不会在共享对象上触发，例如 database，roles， tablespace。
  2. ddl_command_end： ddl_command_end 事件在以类似同样命令执行后触发。
  3. sql_drop: sql_drop 事件在 ddl_command_end 触发之前触发，要查看删除的数据库对像，可以通过函数 pg_event_trigger_dropped_objects() 查看，当数据库对像在 catalog 表中被删除时，触发器被触发。

1.3 event trigger 使用场景

  • 逻辑复制
  • 审计
  • 限制用户的 DDL 命令

备注：这里列出主要的应用场景，可能还有更多。x以上是 PostgreSQL 9.3 事件触发器的基本知识，接下来通过一个示例进一步了解 EVENT trigger。

审计数据库的 Drop 命令

这个示例将创建 sql_drop 事件触发器，用来记录指定数据库中 drop 命令历史记录。

2.1 创建 drop 命令审计表

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
 
[pg93@redhatB ~]$ psql francs postgres
psql (9.3beta1)
Type "help" for help.
  
francs=# create table tbl_ddl_drop_log (
francs(# login_role   text,
francs(# db_name     character varying(64),
francs(# client_ip    inet,
francs(# ddl_type  character varying(32),
francs(# schema_name   text,
francs(# object_type   text,
francs(# object_name   text,
francs(# object_identity text,
francs(# drop_time    timestamp with time zone
francs(# );
CREATE TABLE
  
francs=# grant insert on tbl_ddl_drop_log to francs;
GRANT

备注：这里将审计表的 INSERT 权限赋给应用用户 francs。

2.2 创建 sql_drop 触发器函数

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
 
francs=# CREATE OR REPLACE FUNCTION fun_log_drop_command()
francs-# RETURNS event_trigger LANGUAGE plpgsql AS $$
francs$# DECLARE
francs$#   obj record;
francs$# BEGIN
francs$#   FOR obj IN (SELECT * FROM pg_event_trigger_dropped_objects() t where t.object_type
francs$#     in ('table','sequence','index','function','view'))
francs$#   LOOP
francs$#     insert into tbl_ddl_drop_log (
francs$#      login_role,
francs$#      db_name,   
francs$#      client_ip,
francs$#      ddl_type,
francs$#      schema_name,  
francs$#      object_type, 
francs$#      object_name, 
francs$#      object_identity,
francs$#      drop_time)
francs$#     values (
francs$#      current_user,
francs$#      current_database(),
francs$#      inet_client_addr(),
francs$#      tg_tag,
francs$#      obj.schema_name,
francs$#      obj.object_type,
francs$#      obj.object_name,
francs$#      obj.object_identity,
francs$#      now());                
francs$#   END LOOP;
francs$# END;    
francs$# $$; 
CREATE FUNCTION

备注：这个函数需要返回 event_trigger 类型，用于触发器调用; 另外函数中调用内置函数 pg_event_trigger_dropped_objects() 返回被删除的数据库对像，我这里做了过滤，关于 pg_event_trigger_dropped_objects 的返回类型，参考文本末尾的附。

2.3 创建触发器

1
2
3
4
5
6
7
8
9
 
francs=# CREATE EVENT TRIGGER trg_log_drop_command ON sql_drop
francs-#  EXECUTE PROCEDURE fun_log_drop_command();
CREATE EVENT TRIGGER
francs=# /dy
               List of event triggers
    Name     | Event  | Owner  | Enabled |   Procedure    | Tags
----------------------+----------+----------+---------+----------------------+------
trg_log_drop_command | sql_drop | postgres | enabled | fun_log_drop_command |
(1 row)

备注：/dy 用来查看触发器。

2.4 测试
2.4.1 创建测试表和测试函数

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
 
pg91@redhat6 ~]$ psql -h 192.168.1.36 -p 1925 francs francs
Password for user francs:
psql (9.1.2, server 9.3beta1)
WARNING: psql version 9.1, server version 9.3.
    Some psql features might not work.
Type "help" for help.
  
francs=> create table test_trigger(id serial primary key);
CREATE TABLE
  
francs=> CREATE or replace FUNCTION fun_ins_test_1(i int4) RETURNS INTEGER AS $$
francs$>   BEGIN
francs$>    return $1::text;
francs$>   END;
francs$>  $$ LANGUAGE 'plpgsql';
CREATE FUNCTION

备注：这步在客户端（192.168.1.35） 上操作。

2.4.2 删除测试表和测试函数

1
2
3
4
5
 
francs=> drop table test_trigger;
DROP TABLE
  
francs=> drop function fun_ins_test_1(i int4);
DROP FUNCTION

备注：这步在客户端（192.168.1.35） 上操作。删除测试表 test_trigger 和测试函数 fun_ins_test_1(i int4) 之后，接下来看下审计表。

2.4.3 服务端查看审计表
PostgreSQL9.3Beta1：支持事件触发器 ( Event Triggers )
备注：这步在服务端（192.168.1.36） 上操作，测试成功，以上只是简单的演示 EVENT TRIGGER，生产上的审计比这复杂得多。

附: Event Trigger Functions

pg_event_trigger_dropped_objects 返回以下列:
PostgreSQL9.3Beta1：支持事件触发器 ( Event Triggers )

参考

原创文章，作者：kirin，如若转载，请注明出处：https://blog.ytso.com/237967.html

赞 (0)
0
上一篇 2022年1月29日
下一篇 2022年1月29日

相关推荐

发表回复

登录后才能评论