PostgreSQL 事件觸發器應用 - DDL審計

PostgreSQL , 事件觸發器 , DDL審計 , 表結構變更 , 建表等審計

DDL語句的審計是非常重要的，目前PG的DDL審計記錄在日志檔案中。不便于檢視。

為了讓DDL事件記錄到表中，友善檢視，我們可以通過事件觸發器來達到這個效果。

事件觸發器文法:

1、事件觸發器的觸發點(event)

目前支援4個觸發點(event)

這四個觸發點，有3個能捕獲到事件發生時的資訊。

1.1 ddl_command_end

通過這個函數進行捕獲：pg_event_trigger_ddl_commands()

Name

Type

Description

classid

Oid

OID of catalog the object belongs in

objid

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

Type of the object

schema_name

Name of the schema the object belongs in, if any; otherwise NULL. No quoting is applied.

object_identity

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.

1.2 sql_drop

通過這個函數進行捕獲：pg_event_trigger_dropped_objects()

OID of catalog the object belonged in

OID the object had within the catalog

int32

original

Flag used to identify the root object(s) of the deletion

normal

Flag indicating that there's a normal dependency relationship in the dependency graph leading to this object

is_temporary

Flag indicating that the object was a temporary object.

Name of the schema the object belonged in, if any; otherwise NULL. No quoting is applied.

object_name

Name of the object, if the combination of schema and name can be used as a unique identifier for the object; otherwise NULL. No quoting is applied, and name is never schema-qualified.

address_names

text[]

An array that, together with object_type and address_args, can be used by the pg_get_object_address() to recreate the object address in a remote server containing an identically named object of the same kind.

address_args

Complement for address_names above.

1.3 table_rewrite

通過如下函數進行捕獲：

Return Type

pg_event_trigger_table_rewrite_oid()

The OID of the table about to be rewritten.

pg_event_trigger_table_rewrite_reason()

int

The reason code(s) explaining the reason for rewriting. The exact meaning of the codes is release dependent.

2、建立三個觸發點的捕獲資訊存儲表

3、建立三個觸發點的事件觸發器函數

4、建立三個觸發點的事件觸發器

5、模闆化

在模闆庫，執行第二到第四步。

6、通過模闆建立的資料庫，會自動繼承這個模闆。

7、例子

7.1、建表

7.2、寫入資料

7.3、重寫表

7.4、删表

8、查詢審計資訊

1、本文以PG 10為例，介紹了通過事件觸發器，審計DDL的功能。（其他版本可能需要略微修改。）

2、事件觸發器的其他應用，例如限制使用者執行某些DDL等。

<a href="https://github.com/digoal/blog/blob/master/201504/20150429_01.md">《PostgreSQL Oracle 相容性之 - 事件觸發器實作類似Oracle的資源回收筒功能》</a>

<a href="https://github.com/digoal/blog/blob/master/201412/20141211_01.md">《PostgreSQL 事件觸發器 - DDL審計 , DDL邏輯複制 , 打造DDL統一管理入》</a>

<a href="https://github.com/digoal/blog/blob/master/201303/20130313_01.md">《PostgreSQL 事件觸發器 - PostgreSQL 9.3 Event Trigger》</a>

<a href="https://www.postgresql.org/docs/9.6/static/functions-event-triggers.html">https://www.postgresql.org/docs/9.6/static/functions-event-triggers.html</a>