标签
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/
