PostgreSQL Event Triggers

Summary: in this tutorial, you’ll learn how to use the PostgreSQL event triggers to monitor and control Data Definition Language (DDL) operations.

Overview of PostgreSQL Event Triggers #

Regular triggers are associated with a table and respond to events that occur on the table, such as INSERT, UPDATE, DELETE, and TRUNCATE.

Unlike regular triggers, event triggers are not associated with a table but a database. Event triggers respond to the database schema change events such as adding a column, dropping a table and so on.

In PostgreSQL, event triggers can track the following events:

• ddl_command_start: fires at the start of any DDL command.
• ddl_command_end: fires at the end of any DDL command.
• sql_drop: fires after objects are dropped.
• table_rewrite: fires when a change in the table’s structure occurs.

Creating Event Triggers #

You can create event triggers using the CREATE EVENT TRIGGER statement:

CREATE EVENT TRIGGER trigger_name
ON event_name
WHEN condition
EXECUTE FUNCTION function_name();Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

In this syntax:

• trigger_name: The event trigger name.
• event_name: The DDL event to monitor, such as ddl_command_start.
• condition: Optional condition to filter events.
• function_name: The function to execute when the event occurs.

In practice, you’ll find event triggers useful for the following scenarios:

• Auditing database schema changes.
• Enforcing specific database policies.
• Preventing unauthorized database schema modifications.
• Automating notifications or logs for administrative purposes.

Example of Event Triggers #

First, create a table called event_logs to store the logs of all database structure modifications:

CREATE TABLE event_logs (
    id INT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    event_type VARCHAR NOT NULL,
    command_tag VARCHAR NOT NULL,
    object_id OID NOT NULL,
    object_name VARCHAR NOT NULL,
    schema_name VARCHAR NOT NULL,
    user_name VARCHAR NOT NULL,
    event_time TIMESTAMPTZ NOT NULL DEFAULT NOW()
);Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Try it

Second, create a function that inserts DDL events into the event_logs table:

CREATE OR REPLACE FUNCTION log_events()
RETURNS EVENT_TRIGGER
AS 
$$
BEGIN
    INSERT INTO event_logs (
        event_type,
        command_tag,
        object_id,
        object_name,
        schema_name,
        user_name
    )
    SELECT
        tg_event,
        tg_tag,
        objid,
        object_identity,
        schema_name,
        current_user
    FROM pg_event_trigger_ddl_commands();
END;
$$ LANGUAGE plpgsql;Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Try it

Third, create an event trigger and associate it with the log_events function:

CREATE EVENT TRIGGER log_schema_changes
ON ddl_command_end
EXECUTE FUNCTION log_events();Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Try it

Fourth, modify the products table by adding a new column called net_weight with the type decimal:

ALTER TABLE products
ADD COLUMN net_weight DEC;Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Try it

The ALTER TABLE statement will fire the log_schema_changes trigger that inserts data into the event_logs table.

Finally, retrieve data from the event_logs table:

SELECT
  event_type,
  command_tag,
  object_name,
  user_name
FROM
  event_logs;Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Try it

Output:

   event_type    | command_tag |   object_name   | user_name
-----------------+-------------+-----------------+-----------
 ddl_command_end | ALTER TABLE | public.products | postgresCode language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Listing Event Triggers. #

To get all event triggers, you can query from the pg_event_trigger in the PostgreSQL system catalogs:

SELECT * FROM pg_event_trigger;Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Try it

Output:

  oid  |      evtname       |    evtevent     | evtowner | evtfoid | evtenabled | evttags
-------+--------------------+-----------------+----------+---------+------------+---------
 46763 | log_schema_changes | ddl_command_end |       10 |   46762 | O          |Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Disabling Event Triggers #

To turn off an event trigger, you use the ALTER EVENT TRIGGER statement:

ALTER EVENT TRIGGER event_trigger_name
DISABLE;Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

For example, the following statement turns off the log_schema_changes event trigger:

ALTER EVENT TRIGGER log_schema_changes
DISABLE;Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Try it

Enabling Event Triggers #

To enable an event trigger, you also use the ALTER EVENT TRIGGER statement but with the ENABLE option:

ALTER EVENT TRIGGER event_trigger_name
ENABLE;Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

For example, the following statement re-enables the log_schema_changes event trigger:

ALTER EVENT TRIGGER log_schema_changes
ENABLE;Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Try it

Removing Event Triggers #

If you no longer use an event trigger, you can drop it using the DROP EVENT TRIGGER statement:

DROP EVENT TRIGGER event_trigger_name;Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

For example, you can use the following statement to drop the log_schema_changes event trigger:

DROP EVENT TRIGGER log_schema_changes;Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Try it

Preventing unauthorized schema changes #

The following example shows how to use an event trigger to prevent unauthorized users from modifying the database structure:

Step 1. Create a function that only allows the user postgres to modify the database structure:

CREATE FUNCTION stop_ddl () 
  RETURNS EVENT_TRIGGER 
AS 
$$
BEGIN
   IF current_user <> 'postgres' THEN
      RAISE EXCEPTION 'The user % does not have the authorization to change the database structure', current_user;
   END IF;
END;
$$ 
LANGUAGE plpgsql;Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Try it

The stop_ddl function raises an exception if the user is not postgres.

Step 2. Create a event trigger authorize_schema_change that executes the stop_ddl() function whenvever a DDL event occurs:

CREATE EVENT TRIGGER stop_executing_ddl 
ON ddl_command_start
EXECUTE FUNCTION stop_ddl();Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Try it

Only the user postgres can execute DDL commands, while others will receive an exception.

Summary #

• PostgreSQL event triggers are database-level triggers.
• Use PostgreSQL event triggers for monitoring and controlling database structure changes.
• Use the CREATE EVENT TRIGGER statement to create a new event trigger.
• Use the ALTER EVENT TRIGGER ... DISABLE statement to disable an event trigger.
• Use the ALTER EVENT TRIGGER ... ENABLE statement to enable an event trigger.
• Use the DROP EVENT TRIGGER statement to remove an event trigger from a database.