PostgreSQL DDL事件触发器 / iVAN

背景

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

http://www.postgresql.org/docs/devel/static/event-trigger-matrix.html

但是触发事件中不包括对系统共享对象的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信息, 信息在 http://www.postgresql.org/docs/devel/static/event-trigger-matrix.html查询.

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

如下 :

创建两个触发器函数, 返回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的用法介绍 :

http://blog.163.com/digoal@126/blog/static/1638770402013283547959/

http://blog.163.com/digoal@126/blog/static/1638770402013211102130526/

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