标签

PostgreSQL , event trigger , ddl

---

背景

PostgreSQL 9.3 将引入事件触发器, 与普通触发器不同的是, 事件触发器是数据库全局的触发器, 可以由DDL事件来触发.

例如可以用来实施DDL的操作审计，以及防止某些用户执行某些DDL，在某些表上面执行DDL等等。

Unlike regular triggers, which are attached to a single table and capture only DML events, event triggers are global to a particular database and are capable of capturing DDL events.

事件触发器同样可以使用C, plpgsql或者其他的过程语言的函数来编写, 但是不能使用SQL语言函数来编写.

由于事件触发器涉及的权限较大, 例如能禁止DDL操作等, 所以只能使用超级用户创建事件触发器.

在创建事件触发器之前必须先创建触发器函数, 触发器函数的返回类型为event_trigger. (注意区分我们以前所熟悉的普通触发器函数的返回类型为trigger.)

事件触发器的语法

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()

语法解释 :

name : 触发器名称  event : 事件名称, 现在支持的事件为ddl_command_start 和 ddl_command_end.

支持触发事件触发器的DDL如下(包括select into) :

但是触发事件中不包括对系统共享对象的CREATE, ALTER, DROP操作, 如 :

databases, roles, and tablespaces

同样对事件触发器本身的DDL操作也不会触发事件触发器.

The ddl_command_start event occurs just before the execution of a CREATE, ALTER, or DROP command.   As an exception, however, this event does not occur for DDL commands targeting shared objects - databases, roles, and tablespaces - or for command targeting event triggers themselves.   The event trigger mechanism does not support these object types.   ddl_command_start also occurs just before the execution of a SELECT INTO command, since this is equivalent to CREATE TABLE AS.   The ddl_command_end event occurs just after the execution of this same set of commands.  filter_variable目前只支持TAG  filter_value是http://www.postgresql.org/docs/devel/static/event-trigger-matrix.html这里定义的DDL  function_name就是我们创建好的事件触发器函数.

以plpgsql函数语言为例讲解事件触发器函数的创建方法

PL/pgSQL can be used to define event triggers.   PostgreSQL requires that a procedure that is to be called as an event trigger must be declared as a function with no arguments and a return type of event_trigger.  When a PL/pgSQL function is called as a event trigger, several special variables are created automatically in the top-level block. They are:  TG_EVENT  Data type text; a string representing the event the trigger is fired for.  TG_TAG  Data type text; variable that contains the command tag for which the trigger is fired.

事件触发器函数的返回类型为event_trigger, 同时事件触发器的顶级块带入了两个特殊变量, TG_EVENT和TG_TAG.

TG_EVENT表示EVENT信息, 如现在支持的为ddl_command_start 和 ddl_command_end.

TG_TAG表示的是DDL信息, 信息在 .

如果同一个事件上建立了多个事件触发器, 执行顺序按触发器名字的字母先后顺序来执行, 这个和普通触发器的触发规则是一样的.

如下 :

创建两个触发器函数, 返回event_trigger类型 :

CREATE OR REPLACE FUNCTION etgr1()       RETURNS event_trigger                     LANGUAGE plpgsql    AS $$  BEGIN    RAISE NOTICE 'this is etgr1, event:%, command:%', tg_event, tg_tag;   END;  $$;  CREATE OR REPLACE FUNCTION etgr2()       RETURNS event_trigger                     LANGUAGE plpgsql    AS $$  BEGIN    RAISE NOTICE 'this is etgr2, event:%, command:%', tg_event, tg_tag;   END;  $$;

创建事件触发器, 这里未使用WHEN, 也就是所有的DDL都触发这些事件触发器(除了前面提到的触发器本身的DDL和共享对象的DDL) :

CREATE EVENT TRIGGER b ON ddl_command_start EXECUTE PROCEDURE etgr1();  CREATE EVENT TRIGGER a ON ddl_command_start EXECUTE PROCEDURE etgr2();

同一个事件类型ddl_command_start下创建了2个事件触发器, 事件触发器的名称分别为a和b, 调用的先后顺序按字母顺序来, 如下 :

digoal=# create table digoal(id int);  NOTICE:  this is etgr2, event:ddl_command_start, command:CREATE TABLE  NOTICE:  this is etgr1, event:ddl_command_start, command:CREATE TABLE  CREATE TABLE

查询当前数据库中有哪些事件触发器 :

digoal=# select * from pg_event_trigger ;   evtname |     evtevent      | evtowner | evtfoid | evtenabled | evttags   ---------+-------------------+----------+---------+------------+---------   b       | ddl_command_start |       10 |   16669 | O          |    a       | ddl_command_start |       10 |   16671 | O          |   (2 rows)

evtowner是创建事件触发器的用户, 例如上面两个事件触发器我是用postgres用户创建的。

digoal=# select rolname from pg_roles where oid=10;   rolname    ----------   postgres  (1 row)

evtfoid指事件触发器函数的oid,

digoal=# select proname from pg_proc where oid=16669;   proname   ---------   etgr1  (1 row)  digoal=# select proname from pg_proc where oid=16671;   proname   ---------   etgr2  (1 row)

事件触发器和DDL语句本身是在同一个事务中处理的, 所以任何事件触发器抛出异常的话, 整个事务都会回滚, 并且后续的操作也不会执行下去.

例如 :

创建事件触发器函数, 函数直接抛出异常.

digoal=# create or replace function abort1() returns event_trigger as $$  declare  begin    raise exception 'event:%, command:%. abort.', TG_EVENT, TG_TAG;  end;  $$ language plpgsql;

创建ddl_command_end 事件触发器

digoal=# create event trigger tg_abort1 on ddl_command_end execute procedure abort1();  CREATE EVENT TRIGGER

执行DDL语句, 如下, 在调用了a和b事件触发器后, 最后调用ddl_command_end的触发器, 抛出异常

digoal=# create table digoal1(id int);  NOTICE:  this is etgr2, event:ddl_command_start, command:CREATE TABLE  NOTICE:  this is etgr1, event:ddl_command_start, command:CREATE TABLE  ERROR:  event:ddl_command_end, command:CREATE TABLE. abort.

异常导致表创建失败

digoal=# \d digoal1  Did not find any relation named "digoal1".

再创建1个事件触发器, 放在ddl_command_start 事件中

digoal=# create event trigger tg_abort2 on ddl_command_start execute procedure abort1();  CREATE EVENT TRIGGER  digoal=# create table digoal1(id int);  NOTICE:  this is etgr2, event:ddl_command_start, command:CREATE TABLE  NOTICE:  this is etgr1, event:ddl_command_start, command:CREATE TABLE  ERROR:  event:ddl_command_start, command:CREATE TABLE. abort.

同样会导致DDL执行失败. 这就达到了禁止执行DDL的目的.

digoal=# create event trigger abort2 on ddl_command_start execute procedure abort1();  CREATE EVENT TRIGGER  digoal=# create table digoal1(id int);  NOTICE:  this is etgr2, event:ddl_command_start, command:CREATE TABLE  ERROR:  event:ddl_command_start, command:CREATE TABLE. abort.  digoal=# \d digoal1  Did not find any relation named "digoal1".

当前数据库中的事件触发器如下

digoal=# select * from pg_event_trigger ;    evtname  |     evtevent      | evtowner | evtfoid | evtenabled | evttags   -----------+-------------------+----------+---------+------------+---------   b         | ddl_command_start |       10 |   16669 | O          |    a         | ddl_command_start |       10 |   16671 | O          |    tg_abort1 | ddl_command_end   |       10 |   16676 | O          |    tg_abort2 | ddl_command_start |       10 |   16676 | O          |    abort2    | ddl_command_start |       10 |   16676 | O          |   (5 rows)

事件触发器应用举例

1. 禁止postgres用户在数据库digoal中执行CREATE TABLE和DROP TABLE命令.

首先把已有的事件触发器删除, 方便观看测试效果.

digoal=# drop event trigger tg_abort1;  DROP EVENT TRIGGER  digoal=# drop event trigger tg_abort2;  DROP EVENT TRIGGER  digoal=# drop event trigger abort2;  DROP EVENT TRIGGER  digoal=# drop event trigger a;  DROP EVENT TRIGGER  digoal=# drop event trigger b;  DROP EVENT TRIGGER  digoal=# select * from pg_event_trigger ;   evtname | evtevent | evtowner | evtfoid | evtenabled | evttags   ---------+----------+----------+---------+------------+---------  (0 rows)

创建触发器函数 :

CREATE OR REPLACE FUNCTION abort()       RETURNS event_trigger                     LANGUAGE plpgsql    AS $$  BEGIN    if current_user = 'postgres' then      RAISE EXCEPTION 'event:%, command:%', tg_event, tg_tag;    end if;   END;  $$;

创建触发器 :

digoal=# create event trigger a on ddl_command_start when TAG IN ('CREATE TABLE', 'DROP TABLE') execute procedure abort();  CREATE EVENT TRIGGER  digoal=# select * from pg_event_trigger ;   evtname |     evtevent      | evtowner | evtfoid | evtenabled |            evttags              ---------+-------------------+----------+---------+------------+-------------------------------   a       | ddl_command_start |       10 |   16683 | O          | {"CREATE TABLE","DROP TABLE"}  (1 row)

测试postgres用户是否可以使用create table和drop table .

digoal=# \c digoal postgres  You are now connected to database "digoal" as user "postgres".

无法新建表了

digoal=# create table new(id int);  ERROR:  event:ddl_command_start, command:CREATE TABLE  digoal=# \d new  Did not find any relation named "new".  digoal=# \dt            List of relations   Schema |  Name   | Type  |  Owner     --------+---------+-------+----------   public | digoal  | table | postgres   public | digoal1 | table | postgres   public | test    | table | postgres  (3 rows)

无法删表了

digoal=# drop table digoal;  ERROR:  event:ddl_command_start, command:DROP TABLE  digoal=# \d digoal      Table "public.digoal"   Column |  Type   | Modifiers   --------+---------+-----------   id     | integer |

测试其他用户是否会有影响

digoal=# \c digoal digoal  You are now connected to database "digoal" as user "digoal".  digoal=> create table tbl(id int);  CREATE TABLE  digoal=> drop table tbl;  DROP TABLE

未受到影响.

其他

1. 事件触发器还可以结合会话参数session_replication_role来使用, 例如仅针对replica角色生效, 其他不生效.

Command:     ALTER EVENT TRIGGER  Description: change the definition of an event trigger  Syntax:  ALTER EVENT TRIGGER name DISABLE  ALTER EVENT TRIGGER name ENABLE [ REPLICA | ALWAYS ]  ALTER EVENT TRIGGER name OWNER TO new_owner  ALTER EVENT TRIGGER name RENAME TO new_name

具体用法可参见trigger的用法介绍 :

2. 我们知道PostgreSQL没有像Oracle里面的DBA_OBJECTS表, 无法得知创建时间, ALTER时间.

使用事件触发器这个将会变成可能, 但是目前的事件触发器函数仅仅支持TG_EVENT和TG_TAG变量, 如果能加入TG_RELID, 那么就可以在DDL的时候记录这个事件到一个对象表中. 从而达到跟踪对象被执行DDL的时间的目的.

3. 事件触发器实际上是通过钩子实现的，例如 InvokeObjectPostCreateHook 在创建对象结束时调用。

src/backend/catalog/objectaccess.c

/*   * RunObjectPostCreateHook   *   * It is entrypoint of OAT_POST_CREATE event   */  void  RunObjectPostCreateHook(Oid classId, Oid objectId, int subId,                                                  bool is_internal)  {          ObjectAccessPostCreate pc_arg;          /* caller should check, but just in case... */          Assert(object_access_hook != NULL);          memset(&pc_arg, 0, sizeof(ObjectAccessPostCreate));          pc_arg.is_internal = is_internal;          (*object_access_hook) (OAT_POST_CREATE,                                                     classId, objectId, subId,                                                     (void *) &pc_arg);  }

src/include/catalog/objectaccess.h

/* Core code uses these functions to call the hook (see macros below). */  extern void RunObjectPostCreateHook(Oid classId, Oid objectId, int subId,                                                  bool is_internal);  extern void RunObjectDropHook(Oid classId, Oid objectId, int subId,                                    int dropflags);  extern void RunObjectPostAlterHook(Oid classId, Oid objectId, int subId,                                             Oid auxiliaryId, bool is_internal);  extern bool RunNamespaceSearchHook(Oid objectId, bool ereport_on_volation);  extern void RunFunctionExecuteHook(Oid objectId);  ......  /*   * The following macros are wrappers around the functions above; these should   * normally be used to invoke the hook in lieu of calling the above functions   * directly.   */  #define InvokeObjectPostCreateHook(classId,objectId,subId)                      \          InvokeObjectPostCreateHookArg((classId),(objectId),(subId),false)  #define InvokeObjectPostCreateHookArg(classId,objectId,subId,is_internal) \          do {                                                                                                                    \                  if (object_access_hook)                                                                         \                          RunObjectPostCreateHook((classId),(objectId),(subId),   \                                                                          (is_internal));                                 \          } while(0)  ......

在函数中执行DDL，同样被审查，因为HOOK不是语义层面的，而是执行层面的。

例如：

postgres=# create or replace function fe() returns event_trigger as $$  declare  begin    if current_user = 'digoal' then      raise exception 'can not execute ddl';    end if;  end;  $$ language plpgsql strict;  CREATE FUNCTION  postgres=# CREATE EVENT TRIGGER a ON ddl_command_start EXECUTE PROCEDURE fe();  CREATE EVENT TRIGGER  postgres=# \c postgres digoal  You are now connected to database "postgres" as user "digoal".  postgres=> create table tbl(id int);  ERROR:  can not execute ddl  postgres=> do language plpgsql $$  postgres$> declare  postgres$> begin  postgres$>   execute 'create table tbl (id int)';  postgres$> end;  postgres$> $$;  ERROR:  can not execute ddl  CONTEXT:  SQL statement "create table tbl (id int)"  PL/pgSQL function inline_code_block line 4 at EXECUTE statement

参考

1. 

2. 

3. 

4. 

5. 

6. 

7.