PostgreSQL中的事件触发器，用途多多_pg查询表上的触发器

微信公众号：数据库杂记   个人微信: _iihero
我是iihero. 也可以叫我Sean.
iihero@CSDN(https://blog.csdn.net/iihero) 
Sean@墨天轮 (https://www.modb.pro/u/16258)
iihero@zhihu (https://www.zhihu.com/people/zhou-mo-xu)
数据库领域的资深爱好者一枚。SAP数据库技术专家与架构师，PostgreSQL ACE.
水木早期数据库论坛发起人db2@smth. 早期多年水木论坛数据库版版主。
国内最早一批DB2 DBA。前后对Sybase, PostgreSQL, HANA, 
Oracle, DB2, SQLite均有涉猎。曾长期担任CSDN相关数据库版版主。
三本著作：<<Java2网络协议内幕>> <<Oracle Spatial及OCI高级编程>> 
<<Sybase ASE 15.X全程实践>>
兴趣领域：数据库技术及云计算、GenAI
​
业余专长爱好：中国武术六段 陈式太极拳第13代传人（北京陈式太极拳第5代传人）
职业太极拳教练，兼任北京陈式太极拳研究会副秘书长。
如果想通过习练陈式太极拳强身健体，也可以与我联系。

前言:

DBA的工作范围以内，比较头疼的几件事之一，恐怕就是数据被误删。于是经常看到网上的一些文字，诸如：”删库跑路“。那也只是戏说，真正追究起来，跑是不跑不掉的。所以，千万不要干傻事。

经验老道的DBA，向来是拿”备份“技术作为生存第一要素。数据丢了，连过去的有效备份都不具备的话，那可真是要被迫跑路了。

本文试着从另外一个角度，即防御角度，看看PostgreSQL中如何通过事件触发器来阻断用户的drop table或truncate table之类的操作。顺便对PostgreSQL中的事件触发器的使用做些介绍。

实例使用

基本语法：

1CREATE EVENT TRIGGER name
2    ON event
3    [ WHEN filter_variable IN (filter_value [, ... ]) [ AND ... ] ]
4    EXECUTE { FUNCTION | PROCEDURE } function_name()
5
6CREATE EVENT TRIGGER创建一个新的事件触发器。 只要指定的事件发生，并且满足与触发器关联的WHEN条件(如果有)，就会执行触发器函数。 有关事件触发器的一般介绍，建事件触发器的用户成为事件触发器的所有者。 

相关参数解释如下：

name
The name to give the new trigger. This name must be unique within the database.

event
The name of the event that triggers a call to the given function. See Section 39.1 for more information on event names.

filter_variable
The name of a variable used to filter events. This makes it possible to restrict the firing of the trigger to a subset of the cases in which it is supported. Currently the only supported filter_variable is TAG.

filter_value
A list of values for the associated filter_variable for which the trigger should fire. For TAG, this means a list of command tags (e.g., 'DROP FUNCTION').

function_name
A user-supplied function that is declared as taking no argument and returning type event_trigger.

In the syntax of CREATE EVENT TRIGGER, the keywords FUNCTION and PROCEDURE are equivalent, but the referenced function must in any case be a function, not a procedure. The use of the keyword PROCEDURE here is historical and deprecated.

目前支持的event:

• ddl_command_start：    CREATE, ALTER, DROP, SECURITY LABEL, COMMENT, GRANT or REVOKE.

• ddl_command_end ：    To obtain more details on the DDL operations that took place, use the set-returning function pg_event_trigger_ddl_commands() from the ddl_command_end event trigger code.

• table_rewrite：        occurs just before a table is rewritten by some actions of the commands ALTER TABLE and ALTER TYPE. While other control statements are available to rewrite a table, like CLUSTER and VACUUM, the table_rewrite event is not triggered by them

• sql_drop：                occurs just before the ddl_command_end event trigger for any operation that drops database objects. To list the objects that have been dropped, use the set-returning function pg_event_trigger_dropped_objects() from the sql_drop event trigger code.

注意上边将ddl_command_end和sql_drop两者分开了。前者通过：pg_event_trigger_ddl_commands()记录CREATE、ALTER类的操作，而后者主要则包含DROP类的操作(相关操作记录在pg_event_trigger_dropped_objects()里)。使用时稍加注意即可。

实例：阻断删表操作

禁用所有DDL

首先试试禁用所有的ddl command的操作：

1create or replace function abort_any_ddl() returns event_trigger as
2$$
3begin
4  raise exception 'command % is diabled.', tg_tag;
5end
6$$
7language plpgsql;
8
9create event trigger abort_ddl on ddl_command_start execute function abort_any_ddl();

这样，abort_ddl成功创建，意味着所有的ddl_command_start中涉及的ddl都会被阻止。

 1mydb=# drop table s1.sys_tenant;
 2ERROR:  command DROP TABLE is diabled.
 3CONTEXT:  PL/pgSQL function abort_any_ddl() line 3 at RAISE
 4
 5mydb=# create table t(id int, col2 varchar(32));
 6ERROR:  command CREATE TABLE is diabled.
 7CONTEXT:  PL/pgSQL function abort_any_ddl() line 3 at RAISE
 8
 9mydb=# truncate table s1.sys_tenant;
10TRUNCATE TABLE
11-- trunate操作还是可以执行的。

禁用事件触发器：(通过alter或者drop可以达到目的)

1mydb=# alter event trigger abort_ddl disable;
2ALTER EVENT TRIGGER
3mydb=# create table t(id int, col2 varchar(32));
4CREATE TABLE
5mydb=# drop event trigger abort_ddl;
6DROP EVENT TRIGGER

禁用Drop Table

正题来了，我们进一步缩小DDL范围，通过filter_variable, filter_value，来禁止删表操作

 1create or replace function abort_any_ddl() returns event_trigger as
 2$$
 3begin
 4        raise exception 'command % is diabled.', tg_tag;
 5end
 6$$
 7language plpgsql;
 8
 9create event trigger abort_drop_table on ddl_command_start when TAG in ('DROP TABLE') execute function abort_any_ddl();
10
11-- 列出定义
12mydb=# \dy
13                                 List of event triggers
14       Name       |       Event       |  Owner   | Enabled |   Function    |    Tags
15------------------+-------------------+----------+---------+---------------+------------
16 abort_drop_table | ddl_command_start | postgres | enabled | abort_any_ddl | DROP TABLE
17(1 row)

验证：

1mydb=# drop table t;
2ERROR:  command DROP TABLE is diabled.
3CONTEXT:  PL/pgSQL function abort_any_ddl() line 3 at RAISE

这里头的TAG值，可以从官方文档中看到完整的列表：

see:  https://www.postgresql.org/docs/15/event-trigger-matrix.html

实例：使用sql_drop，阻止删除表操作

1create event trigger sql_drop_test on sql_drop execute function abort_any_ddl();
2
3-- test
4mydb=# drop table t;
5ERROR:  command DROP TABLE is diabled.
6CONTEXT:  PL/pgSQL function abort_any_ddl() line 3 at RAISE
7mydb=# drop table t2;
8ERROR:  command DROP TABLE is diabled.
9CONTEXT:  PL/pgSQL function abort_any_ddl() line 3 at RAISE

这个就显得比ddl_command_start和TAG结合使用更直接。

实例：使用sql_drop，只记录删除的操作

 1drop event trigger sql_drop_test ;
 2
 3create table audit_drop_operation(op_time timestamp,ddl_tag text,classid oid,objid oid,objsubid oid,object_type text,schema_name text,object_name text,object_identity text);
 4
 5CREATE OR REPLACE FUNCTION event_trigger_log_drops() RETURNS event_trigger
 6AS $$
 7DECLARE
 8        obj record;
 9BEGIN
10        INSERT INTO audit_drop_operation SELECT     now(),tg_tag,classid,objid,objsubid,object_type,schema_name,object_name,object_identity FROM pg_event_trigger_dropped_objects();
11END;
12$$ LANGUAGE plpgsql;
13
14create event trigger event_trigger_log_drops on sql_drop execute procedure event_trigger_log_drops();

这里我们在sql_drop事件里，只是调用函数event_trigger_log_drops()，它不过是从dropped_objects()里头提取相关记录，存储到我们预定义的表：audit_drop_operation里头。

当然，如果你要做的好一点，应该禁止对表：audit_drop_operation的删除。一旦将表audit_drop_operation删除了，就会出现不一致的问题。

实例：利用ddl_command_end记录DDL操作

1、创建一个audit表

 1create table audit_ddl_command_end_objects(
 2    op_time timestamp,
 3    classid oid,
 4    objid oid,
 5    objsubid integer,
 6    command_tag text,
 7    object_type text,
 8    schema_name text,
 9    object_identity text,
10in_extension bool);

2、创建对应的事件触发器函数

 1CREATE OR REPLACE FUNCTION event_trigger_ddl_command_end() RETURNS event_trigger
 2AS $$
 3BEGIN
 4    raise notice 'dll trigger: event_trigger_ddl_command_end executing';
 5    INSERT INTO audit_ddl_command_end_objects
 6    SELECT
 7    now(),
 8    classid,
 9    objid,
10    objsubid,
11    command_tag,
12    object_type,
13    schema_name,
14    object_identity,
15    in_extension
16    FROM pg_event_trigger_ddl_commands();
17END;
18$$ LANGUAGE plpgsql;

3、创建ddl_command_end类型事件触发器

1create event trigger my_trigger_ddl_command_end on ddl_command_end execute procedure event_trigger_ddl_command_end();
2
3--
4mydb=# \dy+
5                                                 List of event triggers
6            Name            |      Event      |  Owner   | Enabled |           Function            | Tags | Description
7----------------------------+-----------------+----------+---------+-------------------------------+------+-------------
8 my_trigger_ddl_command_end | ddl_command_end | postgres | enabled | event_trigger_ddl_command_end |      |
9(1 row)

4、创建测试表

1create table t2(id int, col2 text);
2NOTICE:  dll trigger: event_trigger_ddl_command_end executing
3CREATE TABLE

5、验证事件触发器结果

1select * from audit_ddl_command_end_objects;
2
3          op_time           | classid | objid | objsubid | command_tag  | object_type | schema_name | object_identity | in_extension
4----------------------------+---------+-------+----------+--------------+-------------+-------------+-----------------+--------------
5 2023-07-24 23:33:13.745184 |    1259 | 16978 |        0 | CREATE TABLE | table       | s1          | s1.t2           | f
6(1 row)

6、修改表，并查看触发器执行结果：

 1mydb=# alter table t2 add col3 int;
 2NOTICE:  dll trigger: event_trigger_ddl_command_end executing
 3ALTER TABLE
 4
 5select * from audit_ddl_command_end_objects;
 6          op_time           | classid | objid | objsubid | command_tag  | object_type | schema_name | object_identity | in_extension
 7----------------------------+---------+-------+----------+--------------+-------------+-------------+-----------------+--------------
 8 2023-07-24 23:33:13.745184 |    1259 | 16978 |        0 | CREATE TABLE | table       | s1          | s1.t2           | f
 9 2023-07-24 23:34:25.842651 |    1259 | 16978 |        0 | ALTER TABLE  | table       | s1          | s1.t2           | f
10(2 rows)

7、删除表，看看结果

1mydb=# drop table t2;
2NOTICE:  dll trigger: event_trigger_ddl_command_end executing
3DROP TABLE
4mydb=# select * from audit_ddl_command_end_objects;
5          op_time           | classid | objid | objsubid | command_tag  | object_type | schema_name | object_identity | in_extension
6----------------------------+---------+-------+----------+--------------+-------------+-------------+-----------------+--------------
7 2023-07-24 23:33:13.745184 |    1259 | 16978 |        0 | CREATE TABLE | table       | s1          | s1.t2           | f
8 2023-07-24 23:34:25.842651 |    1259 | 16978 |        0 | ALTER TABLE  | table       | s1          | s1.t2           | f
9(2 rows)

这里头没有drop table的记录。因为: event_trigger_ddl_command_end只记录非DROP的记录。要想记录DROP相关记录，可以利用前边的sql_drop事件中的：pg_event_trigger_dropped_objects().

8、补上sql_drop的触发器相关记录

 1create event trigger event_trigger_log_drops on sql_drop execute procedure event_trigger_log_drops();
 2-- 验证
 3
 4mydb=# create event trigger event_trigger_log_drops on sql_drop execute procedure event_trigger_log_drops();
 5CREATE EVENT TRIGGER
 6mydb=# create table t3(id int);
 7NOTICE:  dll trigger: event_trigger_ddl_command_end executing
 8CREATE TABLE
 9mydb=# drop table t3;
10NOTICE:  dll trigger: event_trigger_ddl_command_end executing
11DROP TABLE
12mydb=# select * from audit_ddl_command_end_objects;
13          op_time           | classid | objid | objsubid | command_tag  | object_type | schema_name | object_identity | in_extension
14----------------------------+---------+-------+----------+--------------+-------------+-------------+-----------------+--------------
15 2023-07-24 23:33:13.745184 |    1259 | 16978 |        0 | CREATE TABLE | table       | s1          | s1.t2           | f
16 2023-07-24 23:34:25.842651 |    1259 | 16978 |        0 | ALTER TABLE  | table       | s1          | s1.t2           | f
17 2023-07-24 23:38:32.801786 |    1259 | 16984 |        0 | CREATE TABLE | table       | s1          | s1.t3           | f
18(3 rows)
19
20mydb=# select * from audit_drop_operation;
21          op_time           |  ddl_tag   | classid | objid | objsubid | object_type | schema_name | object_name | object_identity
22----------------------------+------------+---------+-------+----------+-------------+-------------+-------------+-----------------
23 2023-07-24 23:38:40.498583 | DROP TABLE |    1259 | 16984 |        0 | table       | s1          | t3          | s1.t3
24 2023-07-24 23:38:40.498583 | DROP TABLE |    1247 | 16986 |        0 | type        | s1          | t3          | s1.t3
25 2023-07-24 23:38:40.498583 | DROP TABLE |    1247 | 16985 |        0 | type        | s1          | _t3         | s1.t3[]
26(6 rows)

我们在audit_drop_operation表里头可以看到相关的drop操作。

扩展实例

有了上边的实例，可能也不太满足于现状。比如，您是一个相关维护人员，可能就想在用户删表或Truncate表的同时，不仅记录它的操作，还要把相关的原始数据给放到”回收“区，以防将来哪一天，用户后悔了，想恢复一下，那都是有可能的。

如果是TRUNCATE操作，可以使用普通的触发器，因为里边的TG_OP 数据类型是text；是值为INSERT、UPDATE、DELETE或TRUNCATE的一个字符串，它说明触发器是为哪个操作引发。 只能对所有的表，按表名创建进行捕获。

实现思路：如果是DROP操作，我们试试上边的sql_drop的事件触发器，下边列出相应的伪码。有兴趣可以自行扩充调试。

 1create or replace function recycle_any_ddl() returns event_trigger as
 2$$
 3begin
 4        raise notice 'command % is triggered.', tg_tag;
 5        -- 伪码部分
 6        execute 'CREATE TABLE {schema_name}.{object_name}.recycle (like {schema_name}.{object_name} including all)';
 7        insert into {schema_name}.{object_name}_recycle select * from {schema_name}.{object_name};
 8end
 9$$
10language plpgsql;
11
12create event trigger recycle_drop_table on ddl_command_start when TAG in ('DROP TABLE') execute function recycle_any_ddl();

这样在recycle_drop_table触发后，会自动建表， 以"_recycle"为后缀。并且完整的填充数据。这些动作都是在drop table动作start的时候做的，时机非常重要。