# PostgreSQL Event Trigger 應用之一
前幾天介紹了 event trigger,現在來介紹一下應用.
## aduit, log
audit 或是 log 是常見的應用,在網路上也能找到一些例子.
上次的介紹中,使用列印出訊息的方式,現在改用table儲存.
### 建立table 與 function 及 event trigger
```sql=
create table ddl_history (
id int generated always as identity primary key
, ts timestamptz
, tag text
, obj text
, cmd text
);
create or replace function f_ddl_end()
returns event_trigger
language plpgsql as
$code$
declare
event_tuple record;
begin
for event_tuple in select command_tag, object_identity from pg_event_trigger_ddl_commands() loop
insert into ddl_history(ts, tag, obj, cmd)
values (statement_timestamp(), event_tuple.command_tag
, event_tuple.object_identity, current_query());
end loop;
end;
$code$;
--
create or replace function f_drop_info()
returns event_trigger
language plpgsql as
$code$
declare
event_tuple record;
begin
for event_tuple in select object_type, object_identity from pg_event_trigger_dropped_objects()
loop
insert into ddl_history(ts, tag, obj, cmd)
values (statement_timestamp(), tg_tag || ' / ' || event_tuple.object_type
, event_tuple.object_identity, current_query());
end loop;
end;
$code$;
--
create event trigger tr_ddl_end
on ddl_command_end
execute function f_ddl_end();
create event trigger tr_drop
on sql_drop
execute function f_drop_info();
```
### 測試
```sql=
create table bar (
id int
);
alter table bar
add column bname text;
alter table bar
add column j text;
alter table bar
alter column j type int using(j::int);
alter table bar
alter column id type text;
drop table bar;
commit;
--------
select * from ddl_history;
id | ts | tag | obj | cmd
----+-------------------------------+--------------------------+--------------------------------+----------------------------------------
1 | 2021-05-19 14:35:29.047706+08 | CREATE TABLE | s12.bar | create table bar ( +
| | | | id int +
| | | | );
2 | 2021-05-19 14:35:35.123379+08 | ALTER TABLE | s12.bar | alter table bar +
| | | | add column bname text;
3 | 2021-05-19 14:35:40.758333+08 | ALTER TABLE | s12.bar | alter table bar +
| | | | add column j text;
4 | 2021-05-19 14:35:47.137456+08 | ALTER TABLE | s12.bar | alter table bar +
| | | | alter column j type int using(j::int);
5 | 2021-05-19 14:35:54.399101+08 | ALTER TABLE | s12.bar | alter table bar +
| | | | alter column id type text;
6 | 2021-05-19 14:36:00.47093+08 | DROP TABLE / table | s12.bar | drop table bar;
7 | 2021-05-19 14:36:00.47093+08 | DROP TABLE / type | s12.bar | drop table bar;
8 | 2021-05-19 14:36:00.47093+08 | DROP TABLE / type | s12.bar[] | drop table bar;
9 | 2021-05-19 14:36:00.47093+08 | DROP TABLE / toast table | pg_toast.pg_toast_818036 | drop table bar;
10 | 2021-05-19 14:36:00.47093+08 | DROP TABLE / type | pg_toast.pg_toast_818036 | drop table bar;
11 | 2021-05-19 14:36:00.47093+08 | DROP TABLE / index | pg_toast.pg_toast_818036_index | drop table bar;
(11 rows)
----------
--stemp 2:
create or replace procedure someddl()
language sql as
$code$
create table baz (
id int
);
alter table baz
add column bname text;
alter table baz
add column j text;
alter table baz
alter column j type int using(j::int);
alter table baz
alter column id type text;
drop table baz;
$code$;
commit;
call someddl();
commit;
----------
select * from ddl_history;
....
13 | 2021-05-19 14:40:42.710363+08 | CREATE TABLE | s12.baz | call someddl();
14 | 2021-05-19 14:40:42.710363+08 | ALTER TABLE | s12.baz | call someddl();
15 | 2021-05-19 14:40:42.710363+08 | ALTER TABLE | s12.baz | call someddl();
16 | 2021-05-19 14:40:42.710363+08 | ALTER TABLE | s12.baz | call someddl();
17 | 2021-05-19 14:40:42.710363+08 | ALTER TABLE | s12.baz | call someddl();
18 | 2021-05-19 14:40:42.710363+08 | DROP TABLE / table | s12.baz | call someddl();
19 | 2021-05-19 14:40:42.710363+08 | DROP TABLE / type | s12.baz | call someddl();
20 | 2021-05-19 14:40:42.710363+08 | DROP TABLE / type | s12.baz[] | call someddl();
21 | 2021-05-19 14:40:42.710363+08 | DROP TABLE / toast table | pg_toast.pg_toast_818055 | call someddl();
22 | 2021-05-19 14:40:42.710363+08 | DROP TABLE / type | pg_toast.pg_toast_818055 | call someddl();
23 | 2021-05-19 14:40:42.710363+08 | DROP TABLE / index | pg_toast.pg_toast_818055_index | call someddl();
--
alter event trigger tr_drop disable;
alter event trigger tr_ddl_end disable;
```
### 測試結果討論
可以觀察到drop 會產生連鎖動作,所以使用了單獨的function及event trigger,將其資訊細化.
另外使用了 procedure 來觀察,可以看到是紀錄了 procedure name.
## 限制DDL
第二種應用是限制DDL.
### 例如我們想要限制create view
```sql=
create or replace function f_abort_tag()
returns event_trigger
language plpgsql as
$code$
begin
raise exception '禁止執行 %', tg_tag;
end;
$code$;
create event trigger abort_view
on ddl_command_start
when tag in ('CREATE VIEW')
execute function f_abort_tag();
-- 測試:
create view v_ddl_hist as
select tag,obj
from ddl_history;
ERROR: P0001: 禁止執行 CREATE VIEW
--會阻擋執行 create view.
--將這個 event trigger abort_view 先 drop 掉.
drop event trigger abort_view;
commit;
```
這樣雖然有功能,但是不夠細緻,例如想要指名特定的物件.
### 例如我們想保護 ddl_history , 不能被 alter.
```sql=
create or replace function f_prot_tbl()
returns event_trigger
language plpgsql as
$code$
declare
event_tuple record;
begin
for event_tuple in select command_tag, object_identity from pg_event_trigger_ddl_commands() loop
if event_tuple.command_tag = 'ALTER TABLE' and
event_tuple.object_identity = 's12.ddl_history'
then
raise exception '結界保護 %', 's12.ddl_history';
end if;
end loop;
end;
$code$;
create event trigger tr_prot_tbl
on ddl_command_end
when tag in ('ALTER TABLE')
execute function f_prot_tbl();
-- 測試：
alter table ddl_history
add column j int;
ERROR: P0001: 結界保護 s12.ddl_history
---
\dy tr_prot_tbl
List of event triggers
Name | Event | Owner | Enabled | Function | Tags
-------------+-----------------+--------+---------+------------+-------------
tr_prot_tbl | ddl_command_end | pagila | enabled | f_prot_tbl | ALTER TABLE
alter event trigger tr_prot_tbl disable;
```
### 我全都要!!
若建立 event trigger on ddl_command_start 沒有指定 tag, 使用f_abort_tag(). 而 f_abort_tag() 裡面也沒有細化判斷,也就是說所有能攔截到的 ddl command 在 start 時,就被 exception 中斷了.
```sql=
create event trigger abort_ddl
on ddl_command_start
execute function f_abort_tag();
--
測試:
create table foz (id int);
ERROR: P0001: 禁止執行 CREATE TABLE
```
### 這樣是否就全部被封印了?
在這情形下, Event and Command Tag Matrix 裡的 ddl 都被禁止了.
但是 event trigger 的 相關是不被攔截的,所以我們是可以控制.
```sql=
alter event trigger abort_ddl disable;
create table foz (id int);
\d foz
Table "s12.foz"
Column | Type | Collation | Nullable | Default
--------+---------+-----------+----------+---------
id | integer | | |
```
這樣就復元了!
PostgreSQL 的 event trigger 是不攔截 event trigger 相關的,這樣避免作繭自縛.
### 驗證其他ddl
```sql=
--驗證 drop 是否被攔截
alter event trigger abort_ddl enable;
drop table foz;
ERROR: P0001: 禁止執行 DROP TABLE
--驗證是否能夠 CREATE TEMPORARY TABLE
create temporary table foy (id int);
ERROR: P0001: 禁止執行 CREATE TABLE
alter event trigger abort_ddl disable;
```
### 全部封印的探討
這樣 database 就進入保護模式, 結構不會被改變,在一些情境下有其用處.
例如測試時,為了減少不確定因素;或是資安政策,需要對資料庫的結構有版本管理等等.
但是上面的 f_abort_tag(), 裡面很簡單,就是全攔截了,不夠細化.
在實務應用上,可能還會有 create temporary table 或是 create unlogged table 的需要.
### 開放部分功能的方式
```sql=
create or replace function f_abort_tag2()
returns event_trigger
language plpgsql as
$code$
declare
event_tuple record;
v_reltype char;
begin
if tg_tag = 'CREATE TABLE'
then
for event_tuple in select objid from pg_event_trigger_ddl_commands() loop
select relpersistence
into v_reltype
from pg_class
where oid = event_tuple.objid;
if v_reltype = 'p' then
raise exception '禁止建立普通的 table';
end if;
end loop;
else
raise exception '禁止執行 %', tg_tag;
end if;
end;
$code$;
-- 接著建立 on ddl_command_end 的 trigger
create event trigger freeze_ddl
on ddl_command_end
execute function f_abort_tag2();
---
-- 驗證是否能夠 CREATE TEMPORARY TABLE
create temporary table foy (id int);
\d foy
Table "pg_temp_3.foy"
Column | Type | Collation | Nullable | Default
--------+---------+-----------+----------+---------
id | integer | | |
-- 驗證是否能夠 CREATE UNLOGGED TABLE
create unlogged table fot (id int);
\d fot
Unlogged table "s12.fot"
Column | Type | Collation | Nullable | Default
--------+---------+-----------+----------+---------
id | integer | | |
create table fos (id int);
ERROR: P0001: 禁止建立普通的 table
create view v_test as
select * from test;
ERROR: P0001: 禁止執行 CREATE VIEW
alter event trigger freeze_ddl disable;
```
## 結論
今天探討了兩種應用的方式.在資料庫的應用方面,有許多深入的細節,是一般較少討論或是被忽略了.
最近資安各種議題頗為熱門,event trigger 除了 aduit 以外尚有限制保護的功能,在實務應用上可以適當搭配提高開發效率及資安保護層級.
Sign in with Wallet
Connect another wallet