PostgreSQL DDL事件触发器

标签

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

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  

参考

1. http://www.postgresql.org/docs/devel/static/event-triggers.html

2. http://www.postgresql.org/docs/devel/static/plpgsql-trigger.html

3. http://www.postgresql.org/docs/devel/static/sql-createeventtrigger.html

4. http://www.postgresql.org/docs/devel/static/catalog-pg-event-trigger.html

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

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

7. http://git.postgresql.org/gitweb/?p=postgresql.git;a=commitdiff;h=3a0e4d36ebd7f477822d5bae41ba121a40d22ccc