PostgreSQL Event trigger 使用记录：阻断危险SQL、记录DDL操作

PostgreSQL Event trigger 使用记录：阻断危险SQL、记录DDL操作

CREATE EVENT TRIGGER — define a new event trigger

语法

CREATE EVENT TRIGGER name
    ON event
    [ WHEN filter_variable IN (filter_value [, ... ]) [ AND ... ] ]
    EXECUTE { FUNCTION | PROCEDURE } function_name()

描述

CREATE EVENT TRIGGER creates a new event trigger. Whenever the designated event occurs and the WHEN condition associated with the trigger, if any, is satisfied, the trigger function will be executed. For a general introduction to event triggers, see Chapter 39. The user who creates an event trigger becomes its owner.

参数解释

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 (see Section 9.29). 
 * 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 (see Section 9.29). 

禁用event触发器

无法通过alter table 的方式禁用：
postgres=# alter table test disable trigger abort_ddl;
2021-02-03 11:09:05.010 CST [58060] ERROR:  command ALTER TABLE is diabled.
2021-02-03 11:09:05.010 CST [58060] CONTEXT:  PL/pgSQL function abort_any_ddl() line 3 at RAISE
2021-02-03 11:09:05.010 CST [58060] STATEMENT:  alter table test disable trigger abort_ddl;
ERROR:  command ALTER TABLE is diabled.
CONTEXT:  PL/pgSQL function abort_any_ddl() line 3 at RAISE

需要直接对触发器禁用：
postgres=# alter event trigger abort_ddl disable;
ALTER EVENT TRIGGER
postgres=# drop event trigger abort_ddl ;
DROP EVENT TRIGGER

示例1：禁用所有ddl

postgres=# create or replace function abort_any_ddl() returns event_trigger as
$$
begin
        raise exception 'command % is diabled.', tg_tag;
end
$$
language plpgsql;
CREATE FUNCTION
postgres=#
postgres=# create event trigger abort_ddl on ddl_command_start execute function abort_any_ddl();
CREATE EVENT TRIGGER
postgres=# drop table test;
2021-02-03 11:05:02.217 CST [58060] ERROR:  command DROP TABLE is diabled.
2021-02-03 11:05:02.217 CST [58060] CONTEXT:  PL/pgSQL function abort_any_ddl() line 3 at RAISE
2021-02-03 11:05:02.217 CST [58060] STATEMENT:  drop table test;
ERROR:  command DROP TABLE is diabled.
CONTEXT:  PL/pgSQL function abort_any_ddl() line 3 at RAISE

示例2：添加筛选条件filter_variable，filter_value，阻止删除表的SQL

postgres=# create table test (id int, info text);
CREATE TABLE
postgres=# create or replace function abort_any_ddl() returns event_trigger as
$$
begin
        raise exception 'command % is diabled.', tg_tag;
end
$$
language plpgsql;
CREATE FUNCTION
postgres=#
postgres=# create event trigger abort_drop on ddl_command_start when TAG in ('DROP TABLE') execute function abort_any_ddl();
CREATE EVENT TRIGGER
postgres=# drop table test;
2021-02-03 11:21:54.245 CST [58060] ERROR:  command DROP TABLE is diabled.
2021-02-03 11:21:54.245 CST [58060] CONTEXT:  PL/pgSQL function abort_any_ddl() line 3 at RAISE
2021-02-03 11:21:54.245 CST [58060] STATEMENT:  drop table test;
ERROR:  command DROP TABLE is diabled.
CONTEXT:  PL/pgSQL function abort_any_ddl() line 3 at RAISE

* 说明：filter_variable目前只支持TAG，filter_value目前只实验了DROP FUNCTION和DROP TABLE，官网文档没有说明有多少枚举类型

示例3：sql_drop类型事件触发器，阻止删除表的SQL

postgres=# create event trigger sql_drop_test on sql_drop execute function abort_any_ddl();
CREATE EVENT TRIGGER
postgres=#
postgres=#
postgres=# drop table test;
2021-02-03 11:30:51.082 CST [58060] ERROR:  command DROP TABLE is diabled.
2021-02-03 11:30:51.082 CST [58060] CONTEXT:  PL/pgSQL function abort_any_ddl() line 3 at RAISE
2021-02-03 11:30:51.082 CST [58060] STATEMENT:  drop table test;
ERROR:  command DROP TABLE is diabled.
CONTEXT:  PL/pgSQL function abort_any_ddl() line 3 at RAISE
postgres=# d
       List of relations
 Schema | Name | Type  | Owner
--------+------+-------+-------
 public | test | table | pithe
(1 row)

postgres=# alter table test add column c1 int default 0;
ALTER TABLE

示例4：sql_drop类型事件触发器，允许删除表，但是记录删除记录

postgres=# drop event trigger sql_drop_test ;
DROP EVENT TRIGGER
* 创建记录drop sql的表
postgres=#
postgres=# create table log_drop_objects(op_time timestamp,ddl_tag text,classid oid,objid oid,objsubid oid,object_type text,schema_name text,object_name text,object_identity text);
CREATE TABLE
postgres=# CREATE OR REPLACE FUNCTION event_trigger_log_drops() RETURNS event_trigger
AS $$
DECLARE
        obj record;
BEGIN
        INSERT INTO log_drop_objects SELECT now(),tg_tag,classid,objid,objsubid,object_type,schema_name,object_name,object_identity FROM pg_event_trigger_dropped_objects();
END;
$$ LANGUAGE plpgsql;
CREATE FUNCTION
postgres=# create event trigger event_trigger_log_drops on sql_drop execute procedure event_trigger_log_drops();
CREATE EVENT TRIGGER
postgres=# drop table test;
DROP TABLE
postgres=# select * from log_drop_objects;
          op_time           |  ddl_tag   | classid | objid | objsubid |  object_type  | schema_name |     object_name      |        object_identity
----------------------------+------------+---------+-------+----------+---------------+-------------+----------------------+-------------------------------
 2021-02-03 11:35:42.124712 | DROP TABLE |    1259 | 16419 |        0 | table         | public      | test                 | public.test
 2021-02-03 11:35:42.124712 | DROP TABLE |    1247 | 16421 |        0 | type          | public      | test                 | public.test
 2021-02-03 11:35:42.124712 | DROP TABLE |    1247 | 16420 |        0 | type          | public      | _test                | public.test[]
 2021-02-03 11:35:42.124712 | DROP TABLE |    1259 | 16422 |        0 | toast table   | pg_toast    | pg_toast_16419       | pg_toast.pg_toast_16419
 2021-02-03 11:35:42.124712 | DROP TABLE |    1247 | 16423 |        0 | type          | pg_toast    | pg_toast_16419       | pg_toast.pg_toast_16419
 2021-02-03 11:35:42.124712 | DROP TABLE |    1259 | 16424 |        0 | index         | pg_toast    | pg_toast_16419_index | pg_toast.pg_toast_16419_index
 2021-02-03 11:35:42.124712 | DROP TABLE |    2604 | 16427 |        0 | default value |             |                      | for public.test.c1
(7 rows)

示例5：ddl_command_end类型事件触发器，允许DDL，但是记录DDL操作

1.	创建记录表：
postgres=# create table ddl_command_end_objects(
op_time timestamp,
classid oid,
objid oid,
objsubid integer,
command_tag text,
object_type text,
schema_name text,
object_identity text,
in_extension bool);
CREATE TABLE

2.	创建事件触发器函数
postgres=# CREATE OR REPLACE FUNCTION event_trigger_ddl_command_end() RETURNS event_trigger
AS $$
BEGIN
raise notice 'dll trigger: event_trigger_ddl_command_end executing';
INSERT INTO ddl_command_end_objects
SELECT
now(),
classid,
objid,
objsubid,
command_tag,
object_type,
schema_name,
object_identity,
in_extension
FROM pg_event_trigger_ddl_commands();
END;
$$ LANGUAGE plpgsql;
CREATE FUNCTION

3.	创建ddl_command_end类型事件触发器：
postgres=# create event trigger test_trigger_ddl_command_end on ddl_command_end execute procedure event_trigger_ddl_command_end();
CREATE EVENT TRIGGER

4.	创建测试表：
create table test(id int, info text);
NOTICE:  dll trigger: event_trigger_ddl_command_end executing
CREATE TABLE

5.	查看触发器执行结果：
postgres=# select * from ddl_command_end_objects;
          op_time           | classid | objid | objsubid | command_tag  | object_type | schema_name | object_identity | in_extension
----------------------------+---------+-------+----------+--------------+-------------+-------------+-----------------+--------------
 2021-02-03 11:43:18.247004 |    1259 | 16444 |        0 | CREATE TABLE | table       | public      | public.test     | f
(1 row)

6.	修改表，并查看触发器执行结果：
postgres=# alter table test add column crt_time timestamp;
NOTICE:  dll trigger: event_trigger_ddl_command_end executing
ALTER TABLE
postgres=# select * from ddl_command_end_objects;
          op_time           | classid | objid | objsubid | command_tag  | object_type | schema_name | object_identity | in_extension
----------------------------+---------+-------+----------+--------------+-------------+-------------+-----------------+--------------
 2021-02-03 11:43:18.247004 |    1259 | 16444 |        0 | CREATE TABLE | table       | public      | public.test     | f
 2021-02-03 11:43:32.035742 |    1259 | 16444 |        0 | ALTER TABLE  | table       | public      | public.test     | f
(2 rows)

7.	删除表，在ddl_command_end_objects中没有记录，因为在触发器函数中，没有获取删除的日志，但会看到触发器打出的日志：
postgres=# drop table test;
NOTICE:  dll trigger: event_trigger_ddl_command_end executing
DROP TABLE

postgres=#  select * from ddl_command_end_objects;
          op_time           | classid | objid | objsubid | command_tag  | object_type | schema_name | object_identity | in_extension
----------------------------+---------+-------+----------+--------------+-------------+-------------+-----------------+--------------
 2021-02-03 11:43:18.247004 |    1259 | 16444 |        0 | CREATE TABLE | table       | public      | public.test     | f
 2021-02-03 11:43:32.035742 |    1259 | 16444 |        0 | ALTER TABLE  | table       | public      | public.test     | f
(2 rows)

postgres=# select * from log_drop_objects;
          op_time           |  ddl_tag   | classid | objid | objsubid |  object_type  | schema_name |     object_name      |        object_identity
----------------------------+------------+---------+-------+----------+---------------+-------------+----------------------+-------------------------------
 2021-02-03 11:35:42.124712 | DROP TABLE |    1259 | 16419 |        0 | table         | public      | test                 | public.test
 2021-02-03 11:35:42.124712 | DROP TABLE |    1247 | 16421 |        0 | type          | public      | test                 | public.test
 2021-02-03 11:35:42.124712 | DROP TABLE |    1247 | 16420 |        0 | type          | public      | _test                | public.test[]
 2021-02-03 11:35:42.124712 | DROP TABLE |    1259 | 16422 |        0 | toast table   | pg_toast    | pg_toast_16419       | pg_toast.pg_toast_16419
 2021-02-03 11:35:42.124712 | DROP TABLE |    1247 | 16423 |        0 | type          | pg_toast    | pg_toast_16419       | pg_toast.pg_toast_16419
 2021-02-03 11:35:42.124712 | DROP TABLE |    1259 | 16424 |        0 | index         | pg_toast    | pg_toast_16419_index | pg_toast.pg_toast_16419_index
 2021-02-03 11:35:42.124712 | DROP TABLE |    2604 | 16427 |        0 | default value |             |                      | for public.test.c1
 2021-02-03 11:44:02.071083 | DROP TABLE |    1259 | 16444 |        0 | table         | public      | test                 | public.test
 2021-02-03 11:44:02.071083 | DROP TABLE |    1247 | 16446 |        0 | type          | public      | test                 | public.test
 2021-02-03 11:44:02.071083 | DROP TABLE |    1247 | 16445 |        0 | type          | public      | _test                | public.test[]
 2021-02-03 11:44:02.071083 | DROP TABLE |    1259 | 16447 |        0 | toast table   | pg_toast    | pg_toast_16444       | pg_toast.pg_toast_16444
 2021-02-03 11:44:02.071083 | DROP TABLE |    1247 | 16448 |        0 | type          | pg_toast    | pg_toast_16444       | pg_toast.pg_toast_16444
 2021-02-03 11:44:02.071083 | DROP TABLE |    1259 | 16449 |        0 | index         | pg_toast    | pg_toast_16444_index | pg_toast.pg_toast_16444_index
(13 rows)