本文带你学习
PostgreSQL
事件触发器,同时也介绍其他解决的解决方案,如日志DDL
,跟踪提交时间戳。通过对比学习可以更好理解事件触发器的优势。
PostgreSQL
提供了非常有用的事件触发器,用于检测数据变化并自动执行后续任务。例如,触发器可用于检测一个表的变化,然后在另一个表上执行相关任务,通常使用这种方式实现集中审计表,其中包括所有表的变化信息。在9.3版本之前DDL变化不能被可靠检测,9.3版本引入事件触发器让其变为可能。
在介绍事件触发其之前,我们先了解日志DDL及 跟踪提交时间戳。
日志DDL
在事件触发器之前,可以使用DDL检测监控表结构变化,通过设置日志参数:
“log_statement = ‘ddl’” (or “log_statement = ‘all’”)
这样可以顺序在日志中记录DDL语句。这种方式的缺点是,尤其对于有大量DML日志的数据库,DDL语句太多被埋没在日志中,尝试解析日志需要花费大量时间,另外由于日志文件滚动导致在部分文件中找不到感兴趣的DDL日志。
跟踪提交时间戳
从9.1版本开始,开始使用track_commit_timestamp
选项,实现事务与时间戳关联,从而可以感知 ddl
变化,为记录变化信息至 pg_class
铺平了道路。
postgres=# show track_commit_timestamp ;
track_commit_timestamp
------------------------
on
(1 row)
postgres=# create table students (id int, name text);
CREATE TABLE
postgres=# select pg_xact_commit_timestamp(xmin), oid, relname from pg_class where relname = 'students';
pg_xact_commit_timestamp | oid | relname
-------------------------------+-------+----------
2021-11-27 10:36:44.467373+08 | 18278 | students
(1 行记录)
postgres=# alter table students add column phone_number text;
ALTER TABLE
postgres=# select pg_xact_commit_timestamp(xmin), oid, relname from pg_class where relname = 'students';
pg_xact_commit_timestamp | oid | relname
-------------------------------+-------+----------
2021-11-27 10:37:48.859722+08 | 18278 | students
(1 行记录)
如果 track_commit_timestamp
为 off
可以通过 ALTER SYSTEM SET track_commit_timestamp = on;
设置参数,并重启数据框服务。
当 在表上执行DDL
时,在 pg_class
中的表实体被更新。通过检测属于表记录的xmin
值,我们能看到最后执行的ddl。但通过这种方式跟踪过的问题是,基于 xmin
值仅记录最后的 最后 DDL
事件。如果需要了解历史,需要频繁从 pg_class
表中拉去信息,而不能自动方式生成记录变化。
事件触发器
使用事件触发器,当DDL发生时可以利用该机制更新历史表,下面我们先创建历史表:
CREATE TABLE ddl_history (
id serial primary key,
ddl_date timestamptz,
ddl_tag text,
object_name text
);
接着我们需要两个函数,一个跟踪创建、修改对象,另一个跟踪删除对象:
CREATE OR REPLACE FUNCTION log_ddl()
RETURNS event_trigger AS $$
DECLARE
audit_query TEXT;
r RECORD;
BEGIN
IF tg_tag <> 'DROP TABLE'
THEN
r := pg_event_trigger_ddl_commands();
INSERT INTO ddl_history (ddl_date, ddl_tag, object_name)
VALUES (statement_timestamp(), tg_tag, r.object_identity);
END IF;
END;
$$ LANGUAGE plpgsql;
CREATE OR REPLACE FUNCTION log_ddl_drop()
RETURNS event_trigger AS $$
DECLARE
audit_query TEXT;
r RECORD;
BEGIN
IF tg_tag = 'DROP TABLE'
THEN
FOR r IN SELECT * FROM pg_event_trigger_ddl_commands()
LOOP
INSERT INTO ddl_history (ddl_date, ddl_tag, object_name)
VALUES (statement_timestamp(), tg_tag, r.object_identity);
END LOOP;
END IF;
END;
$$ LANGUAGE plpgsql;
创建好两个函数后,我们创建触发器:
CREATE EVENT TRIGGER log_ddl_info ON ddl_command_end EXECUTE PROCEDURE log_ddl();
CREATE EVENT TRIGGER log_ddl_drop_info ON sql_drop EXECUTE PROCEDURE log_ddl_drop();
触发器创建好之后,我们来测试下:
postgres=# CREATE TABLE testtable (id int, first_name text);
CREATE TABLE
postgres=# ALTER TABLE testtable ADD COLUMN last_name text;
ALTER TABLE
postgres=# ALTER TABLE testtable ADD COLUMN midlname text;
ALTER TABLE
postgres=# ALTER TABLE testtable RENAME COLUMN midlname TO middle_name;
ALTER TABLE
postgres=# ALTER TABLE testtable DROP COLUMN middle_name;
ALTER TABLE
postgres=# DROP TABLE testtable;
DROP TABLE
postgres=# select * from ddl_history;
id | ddl_date | ddl_tag | object_name
----+-------------------------------+--------------+------------------------------
1 | 2021-11-27 10:53:07.047529+08 | CREATE TABLE | public.testtable
2 | 2021-11-27 10:53:19.360351+08 | ALTER TABLE | public.testtable
3 | 2021-11-27 10:53:28.442136+08 | ALTER TABLE | public.testtable
4 | 2021-11-27 10:53:35.61037+08 | ALTER TABLE | public.testtable.middle_name
5 | 2021-11-27 10:53:45.370094+08 | ALTER TABLE | public.testtable
(5 行记录)
我们看到变化情况,如果读者还想记录更多内容,可以查看 pg_event_trigger_ddl_commands()
更多属性:
Name | Type | Description |
---|---|---|
classid | Oid | OID of catalog the object belongs in |
objid | Oid | OID of the object in the catalog |
objsubid | integer | Object sub-id (e.g. attribute number for columns) |
command_tag | text | command tag |
object_type | text | Type of the object |
schema_name | text | Name of the schema the object belongs in, if any; otherwise NULL. No quoting is applied. |
object_identity | text | Text rendering of the object identity, schema-qualified. Each and every identifier present in the identity is quoted if necessary. |
in_extension | bool | whether the command is part of an extension script |
command | pg_ddl_command | A complete representation of the command, in internal format. This cannot be output directly, but it can be passed to other functions to obtain different pieces of information about the command. |
总结
事件触发是实现安全审计的强大工具。我们还可以利用它实现其他功能:
- 监控DDL性能
- 对特定用户限制执行DDL
- 对于逻辑复制环境的订阅节点执行DDL复制工作