Auditing DDL Operations

you will track and document all schema changes using administrative event triggers.

In this lesson, you will learn how to capture and document structural database changes in real-time by leveraging PostgreSQL administrative event triggers.

While tools like pgaudit are excellent for logging query activity, they are often too verbose for tracking schema evolution. When you need to maintain an immutable log of "who changed the database schema and when," administrative event triggers are the most precise tool for the job.

The Mechanism

An event trigger behaves differently than a standard table trigger. Instead of firing on INSERT or UPDATE commands, it fires on DDL commands like CREATE, ALTER, or DROP. To track these changes, we create a specialized table to store the audit trail and a function that parses the event metadata.

Implementation Example

First, we create a table to hold our audit history:

CREATE TABLE ddl_audit_log (
    id SERIAL PRIMARY KEY,
    event_type TEXT,
    object_type TEXT,
    object_identity TEXT,
    executed_by TEXT,
    ts TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

Next, we create the handler function using the built-in pg_event_trigger_ddl_commands() function, which provides details about the object being modified:

CREATE OR REPLACE FUNCTION log_ddl_changes()
RETURNS event_trigger AS $$
BEGIN
    INSERT INTO ddl_audit_log (event_type, object_type, object_identity, executed_by)
    SELECT tg_tag, object_type, object_identity, session_user
    FROM pg_event_trigger_ddl_commands();
END;
$$ LANGUAGE plpgsql;

Finally, we link this to the ddl_command_end event, which ensures the log is captured only after the DDL operation successfully completes:

CREATE EVENT TRIGGER capture_ddl ON ddl_command_end
EXECUTE FUNCTION log_ddl_changes();

By isolating DDL operations in this way, you create a dedicated security and compliance audit trail that is separate from your standard query logs. This is essential for environments where schema integrity is paramount, allowing you to reconstruct the timeline of architectural changes whenever an unexpected alteration occurs.

Key Takeaway: Administrative event triggers provide a lightweight, automated mechanism to capture and store DDL activity, ensuring a reliable audit trail for all structural changes within your database schema.