Subscribe DDL Commands

PostgreSQL provides event triggers. Event triggers are global to a particular database and are capable of capturing DDL events occured in the database. Redrock Postgres makes the following improvements to PostgreSQL so that users can subscribe DDL commands with event triggers:

• In Redrock Postgres, roles and tablespaces are database-level objects. Event triggers also support DDL commands for role and tablespace objects. For details, see Event Trigger Firing Matrix.
• Support capturing events of dropping objects at the end of a command (corresponding to the ddl_command_end event).
• Add a function pg_ddl_command_deparse to deparse a command from internal format of type pg_ddl_command, and returns a SQL statement in textual representation of type text.

The following example shows how to subscribe and replicate DDL commands occured on the publisher side. You can modify the commands in the example according to the actual situation.

We need to record DDL commands in a log table with event trigger on the publisher side, and publish the log table. Before proceeding, make sure the value of the configuration parameter wal_level on the publisher side is logical, and the database server needs to be restarted to take effect after the parameter is modified. Then, you can login to the database using a superuser or database owner and do the following:

Creating a log table to record DDL commands:

CREATE SCHEMA IF NOT EXISTS audit;
REVOKE ALL ON SCHEMA audit FROM public;

-- create table for ddl record
CREATE TABLE IF NOT EXISTS audit.ddl_history(
  ord             int,
  event_time      timestamp,
  username        text,
  object_type     text,
  schema_name     text,
  object_identity text,
  command_tag     text,
  command         text
);

-- grant privileges to all user
GRANT INSERT ON TABLE audit.ddl_history TO PUBLIC;

The table audit.ddl_history includes the following columns:

Publish the log table:

CREATE PUBLICATION ddl_publication FOR TABLE ONLY audit.ddl_history;

-- create function for event triggers
CREATE OR REPLACE FUNCTION audit.ddl_pub_trigger_func()
  RETURNS event_trigger AS $$
BEGIN
  INSERT INTO audit.ddl_history
    SELECT ordinality, now(), current_user,
           object_type, schema_name, object_identity,
           command_tag, pg_ddl_command_deparse(command)
      FROM pg_event_trigger_ddl_commands() WITH ORDINALITY;
END;
$$ LANGUAGE plpgsql;

-- create ddl_command_end event trigger
CREATE EVENT TRIGGER ddl_event_trigger
  ON ddl_command_end
  EXECUTE PROCEDURE audit.ddl_pub_trigger_func();

After the preceding commands are executed, your DDL commands will be recorded in the table audit.ddl_history.

You can configure the recording and publishing of DDL commands by modifying the DDL event trigger function definition. Referring to the definition of the table audit.ddl_history in above, you can configure the recording and publishing of DDL commands by adding filter conditions to the definition of DDL event trigger functions based on the user who executed the DDL command, the type of object affected, the schema the object belongs in, the object name, and the command tag. For example, we can modify the definition of the above event trigger function audit.ddl_pub_trigger_func to limit the schemas and object types involved in the recorded and published DDL commands as follows:

CREATE OR REPLACE FUNCTION audit.ddl_pub_trigger_func()
  RETURNS event_trigger AS $$
BEGIN
  INSERT INTO audit.ddl_history
    SELECT ordinality, now(), current_user,
           object_type, schema_name, object_identity,
           command_tag, pg_ddl_command_deparse(command)
      FROM pg_event_trigger_ddl_commands() WITH ORDINALITY
      WHERE schema_name IN ('testschema', 'myschema')
        AND object_type IN ('table', 'index');
END;
$$ LANGUAGE plpgsql;

In the example above, we limit the schemas involved in DDL commands to testschema and myschema, and limit the object types to table and index.

On the publisher side, we recorded the executed DDL commands in the log table audit.ddl_history, and published the table. Subscribers can replicate and execute the DDL commands in the log table. Login to the database using a superuser or database owner and do the following:

On the subscriber side, create a DDL history table same as the table on the publisher side:

CREATE SCHEMA IF NOT EXISTS audit;
REVOKE ALL ON SCHEMA audit FROM public;

-- create table for ddl record
CREATE TABLE IF NOT EXISTS audit.ddl_history(
  ord             int,
  event_time      timestamp,
  username        text,
  object_type     text,
  schema_name     text,
  object_identity text,
  command_tag     text,
  command         text
);

-- grant privileges to all user
GRANT INSERT ON TABLE audit.ddl_history TO PUBLIC;

Subscribe the log table:

CREATE SUBSCRIPTION ddl_subscriptin CONNECTION
  'host=192.168.1.50 port=5432 dbname=testdb user=testuser password=pgpass'
  PUBLICATION ddl_publication;

You need to modify the access information of the publisher in the above SQL command according to the actual deployment environment.

Create a trigger for the table audit.ddl_history on the subscriber side to replicate and execute DDL commands.

CREATE OR REPLACE FUNCTION audit.ddl_sub_trigger_func()
  RETURNS trigger AS $$
BEGIN
  EXECUTE NEW.command;
  RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER ddl_sub_trigger
  AFTER INSERT ON audit.ddl_history
  FOR EACH ROW
  EXECUTE PROCEDURE audit.ddl_sub_trigger_func();

You can configure the replication of DDL commands by modifying the table level trigger function definition. Referring to the definition of the table audit.ddl_history in above, you can configure the replication of DDL commands by adding filter conditions to the definition of DDL event trigger functions based on the user who executed the DDL command, the type of object affected, the schema the object belongs in, the object name, and the command tag. For example, we can modify the definition of the above table level trigger function audit.ddl_sub_trigger_func to limit the schemas and object types involved in the replicated DDL commands as follows:

CREATE OR REPLACE FUNCTION audit.ddl_sub_trigger_func()
  RETURNS trigger AS $$
BEGIN
  IF NEW.schema_name IN ('testschema', 'myschema') AND
     NEW.object_type IN ('table', 'index')
  THEN
    EXECUTE NEW.command;
  END IF;
  RETURN NEW;
END;
$$ LANGUAGE plpgsql;

In the example above, we limit the schemas involved in DDL commands to testschema and myschema, and limit the object types to table and index.