ApsaraDB RDS for PostgreSQL allows you to use event triggers to implement features such as the DDL recycle bin, firewall, incremental synchronization. This helps you reduce maintenance costs and protect data security.
Prerequisites
Background information
You can create DDL recycle bin and firewall policies based on event triggers to ensure database security.
- Prevent against risky operations, such as DROP TABLE, DROP INDEX, and DROP DATABASE.
- Retrieve data from the recycle bin if a table is deleted by mistake.
You can use two event triggers, pg_get_ddl_command and pg_get_ddl_drop, to collect and save DDL statements in the dts_audit.dts_tb_ddl_command table. The event triggers are implemented by using the pg_func_ddl_command() function. The structure of the dts_audit.dts_tb_ddl_command table is as follows:
Column | Type | Collation | Nullable | Default | Storage | Stats target | Description
-----------------+-----------------------------+-----------+----------+--------------------+----------+--------------+-------------
event | text | | | | extended | |
tag | text | | | | extended | | Command tag
classid | oid | | | | plain | | OID of catalog the object belonged in
objid | oid | | | | plain | | OID the object had within the catalog
objsubid | integer | | | | plain | | Object sub-id (e.g. attribute number for columns)
object_type | text | | | | extended | | Type of the object
schema_name | text | | | | extended | | Name of the schema the object belonged in, if any; otherwise NULL. No quoting is applied.
object_identity | text | | | | extended | | Text rendering of the object identity, schema-qualified.
is_extension | boolean | | | | plain | | True if the command is part of an extension script
query | text | | | | extended | | sql text
username | text | | | CURRENT_USER | extended | |
datname | text | | | current_database() | extended | |
client_addr | inet | | | inet_client_addr() | main | |
crt_time | timestamp without time zone | | | now() | plain | |
The required CREATE statements are as follows:
CREATE SCHEMA IF NOT EXISTS dts_audit;
CREATE TABLE IF NOT EXISTS dts_audit.dts_tb_ddl_command ( event text,
tag text, classid oid, objid oid, objsubid int,
object_type text, schema_name text, object_identity text, is_extension bool, query text,
username text default current_user, datname text default current_database(),
client_addr inet default inet_client_addr(), crt_time timestamp default now()
);
The following section provides examples of how to implement the DDL recycle bin, firewall, incremental synchronization. You can modify the code as needed.
DDL recycle bin
DDL firewall
You can create event triggers as needed and use the ddl_command_start event to block the execution of specific DDL statements.
Incremental synchronization
On the publication side, executed DDL statements are stored in the dts_audit.dts_tb_ddl_command table. You can synchronize the records from the publication side to the subscription side.