数据库

 首页 > 数据库 > postgresql > PostgreSQL9.3Beta1:支持事件触发器 ( event triggers )

PostgreSQL9.3Beta1:支持事件触发器 ( event triggers )

分享到:
【字体:
导读:
         摘要:PostgreSQL9.3新增事件触发器,事件触发器为全局触发器,影响范围为指定的某个库,并且可以捕获DDL事件,而传统的触发器是基于表级别,并且只能捕获DML事件。一基本知识--1.1事件触发器语法NameCREATEEVENTTRIGGER--defineaneweventtrigg...

PostgreSQL9.3Beta1:支持事件触发器 ( event triggers )

      PostgreSQL 9.3 新增事件触发器,事件触发器为全局触发器,影响范围为指定的某个库,并且可以捕获 

DDL 事件,而传统的触发器是基于表级别,并且只能捕获 DML 事件。

一 基本知识

--1.1 事件触发器语法

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 使用场景

  1 逻辑复制

  2 审计

  3 限制用户的DDL 命令

  备注:这里列出主要的应用场景,可能还有更多。  

       以上是 PostgreSQL 9.3 事件触发器的基本知识,接下来通过一个示例进一步了解 EVENT trigger。

二 示例:审计数据库的 drop 命令

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

--2.1 创建 drop 命令审计表

 [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 触发器函数

 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 创建触发器

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 创建测试表和测试函数

[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 删除测试表和测试函数

 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 ) - francs - PostgreSQL DBA

   备注:这步在服务端(192.168.1.36) 上操作,测试成功,以上只是简单的演示 EVENT TRIGGER,生产

            上的审计比这复杂得多。

三 附:  Event Trigger Functions

     pg_event_trigger_dropped_objects returns a list of all object dropped by the command in whose 

sql_drop event it is called. If called in any other context, pg_event_trigger_dropped_objects raises 

an error. pg_event_trigger_dropped_objects returns the following columns:

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)
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 an 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.
PostgreSQL9.3Beta1:支持事件触发器 ( event triggers )
分享到:
Ubuntu系统安装postgresql数据库
Ubuntu系统安装postgresql数据库 想在debian下装个PostgreSQL,可是发现源里的pgsql居然是7.*.*的版本,着实吓我一跳,不过没关系,自己编译个就行了。闲 话少说,开始干活: 1. 先下个源码包,http://www.postgresql.org/ftp/source/v8.2.5/,这里的postgresql- 8.2.5.tar.gz就不错,还算比较新。 2. 为PostgreSQL...
PostgreSQL如何增量备份
PostgreSQL如何增量备份 增量备份 PostgreSQL 一、介绍    PITR 的全称是 Point In Time Recovery, 它结合文件系统级备份 WAL 日志文件, 达    到增量备份 PostgreSQL 数据库系统.    WAL 的全称是 Write Ahead Log, 它记录着数据库修改数据文件的每一个动作. 如果    系统挂了, 读入这...
  •         php迷,一个php技术的分享社区,专属您自己的技术摘抄本、收藏夹。
  • 在这里……