注册 登录  
 加关注
   显示下一条  |  关闭
温馨提示!由于新浪微博认证机制调整,您的新浪微博帐号绑定已过期,请重新绑定!立即重新绑定新浪微博》  |  关闭

PostgreSQL 中文网

 
 
 

日志

 
 

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

2013-05-21 12:56:09|  分类: Postgres基础 |  标签: |举报 |字号 订阅

  下载LOFTER 我的照片书  |

      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:
NameTypeDescription
classidOidOID of catalog the object belonged in
objidOidOID the object had within the catalog
objsubidint32Object sub-id (e.g. attribute number for columns)
object_typetextType of the object
schema_nametextName of the schema the object belonged in, if any; otherwise NULL. No quoting is applied.
object_nametextName 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_identitytextText rendering of the object identity, schema-qualified. Each and every identifier present in the identity is quoted if necessary.
  
   
四 参考

  评论这张
 
阅读(10516)| 评论(1)
推荐 转载

历史上的今天

在LOFTER的更多文章

评论

<#--最新日志,群博日志--> <#--推荐日志--> <#--引用记录--> <#--博主推荐--> <#--随机阅读--> <#--首页推荐--> <#--历史上的今天--> <#--被推荐日志--> <#--上一篇,下一篇--> <#-- 热度 --> <#-- 网易新闻广告 --> <#--右边模块结构--> <#--评论模块结构--> <#--引用模块结构--> <#--博主发起的投票-->
 
 
 
 
 
 
 
 
 
 
 
 
 
 

页脚

网易公司版权所有 ©1997-2016