---
# System prepended metadata

title: PostgreSQL Event Trigger 應用之一

---

# 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 以外尚有限制保護的功能,在實務應用上可以適當搭配提高開發效率及資安保護層級.