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