PostgreSQL: Script to generate audit trail tables

Introduction
I created a php script to generate all tables/functions and triggers needed to audit trail as shown in the article Audit Trail for Postgres

The script

These script will connect to your database and write out the sql commands to create the tables/functions and triggers needed for audit of all tables. Adapt the script to your needs.

<?php
try {
        // configure database access parameters
        $host = "";
        $dbname = "";
        $user = "";
        $pass = "";
        $dbh = new PDO("pgsql:host=$host;dbname=$dbname", $user, $pass);

        $table_list = get_all_table_list();
        foreach($table_list as $table) {
                echo "--- TABLE $table";
                echo PHP_EOL;
                echo PHP_EOL;
                echo gen_create_table_audit($table);
                echo gen_create_function_audit($table);
                echo gen_create_trigger_audit($table);
                echo PHP_EOL;
        }

} catch (PDOException $e) {
        print "Error!: " . $e->getMessage() . "<br/>";
        die();
}

/**
 * Returns all table list from database
 */
function get_all_table_list() {
        global $dbh;

        $sql = "SELECT relname FROM pg_class
                 WHERE relname !~ '^(pg_|sql_)' AND relname !~ '_audit$'
                   AND relkind = 'r'";

        $table_list = array();
        foreach($dbh->query($sql) as $row) {
                array_push($table_list, $row['relname']);
        }
        return $table_list;
}

/**
 * generate create table for audit table
 */
function gen_create_table_audit($tablename) {
        global $dbh;

        $sql = "SELECT ordinal_position,
                 column_name,
                 data_type,
                 column_default,
                 is_nullable,
                 character_maximum_length,
                 numeric_precision
            FROM information_schema.columns
           WHERE table_name = '$tablename'
        ORDER BY ordinal_position";

        $tablename_audit = "{$tablename}_audit";

        $s = "--- Create table $tablename_audit" . PHP_EOL;
        $s .= "CREATE TABLE {$tablename_audit} (" . PHP_EOL;
        $s .= "taudit_id serial PRIMARY KEY," . PHP_EOL;
        $s .= "toperation char(1) NOT NULL," . PHP_EOL;
        $s .= "tstamp timestamp NOT NULL," . PHP_EOL;
        $s .= "tdbuser text NOT NULL," . PHP_EOL;

        foreach($dbh->query($sql) as $row) {
                extract($row);
                $character_maximum_length = trim($character_maximum_length);
                if (!empty($character_maximum_length)) {
                        $size = "(" . $character_maximum_length . ")";
                } else {
                        $size = "";
                }

                $null = ($is_nullable == "YES") ? "NULL" : "NOT NULL";
                $s .= "t$column_name {$data_type}{$size} $null," . PHP_EOL;
        }

        $s = substr($s, 0, -2) . PHP_EOL;
        $s .= ");" . PHP_EOL;
        return $s;
}

/**
 * Generate create function for audit table
 */
function gen_create_function_audit($tablename) {

        $tablename_audit = "{$tablename}_audit";
        $functionname = $tablename_audit;

        $s = "--- Create function $functionname
CREATE OR REPLACE FUNCTION $functionname() RETURNS TRIGGER AS $audit$
    BEGIN
        IF (TG_OP = 'DELETE') THEN
            INSERT INTO $tablename_audit VALUES (DEFAULT, 'D', now(), user, OLD.*);
            RETURN OLD;
        ELSIF (TG_OP = 'UPDATE') THEN
            INSERT INTO $tablename_audit VALUES (DEFAULT, 'U', now(), user, NEW.*);
            RETURN NEW;
        ELSIF (TG_OP = 'INSERT') THEN
            INSERT INTO $tablename_audit VALUES (DEFAULT, 'I', now(), user, NEW.*);
            RETURN NEW;
        END IF;
        RETURN NULL; -- result is ignored since this is an AFTER trigger
    END;
$audit$ LANGUAGE plpgsql;";
        $s .= PHP_EOL;
        return $s;
}

/**
 * Generate create trigger for audit table
 */
function gen_create_trigger_audit($tablename) {
        $tablename_audit = "{$tablename}_audit";
        $triggername = $tablename_audit . "t";
        $functionname = $tablename_audit;

        $s = "--- Create trigger $triggername" . PHP_EOL;
        $s .= "CREATE TRIGGER $triggername AFTER INSERT OR UPDATE OR DELETE ON $tablename FOR EACH ROW EXECUTE PROCEDURE $functionname();";
        $s .= PHP_EOL;
        return $s;
}

Example of how to use the script:

$ php audit.php > audit.sql

Example Output:

--- TABLE minuser

--- Create table minuser_audit
CREATE TABLE minuser_audit (
        audit_id serial PRIMARY KEY,
        operation char(1) NOT NULL,
        stamp timestamp NOT NULL,
        dbuser text NOT NULL,
        user_id uuid NOT NULL,
        user_name character varying(50) NOT NULL,
        user_password character varying(50) NOT NULL,
        user_email character varying(50) NULL,
        user_role character varying(50) NULL
);
--- Create function minuser_audit
CREATE OR REPLACE FUNCTION minuser_audit() RETURNS TRIGGER AS $audit$
    BEGIN
        IF (TG_OP = 'DELETE') THEN
            INSERT INTO minuser_audit VALUES (DEFAULT, 'D', now(), user, OLD.*);
            RETURN OLD;
        ELSIF (TG_OP = 'UPDATE') THEN
            INSERT INTO minuser_audit VALUES (DEFAULT, 'U', now(), user, NEW.*);
            RETURN NEW;
        ELSIF (TG_OP = 'INSERT') THEN
            INSERT INTO minuser_audit VALUES (DEFAULT, 'I', now(), user, NEW.*);
            RETURN NEW;
        END IF;
        RETURN NULL; -- result is ignored since this is an AFTER trigger
    END;
$audit$ LANGUAGE plpgsql;
--- Create trigger minuser_auditt
CREATE TRIGGER minuser_auditt AFTER INSERT OR UPDATE OR DELETE ON minuser FOR EACH ROW EXECUTE PROCEDURE minuser_audit();

Deixe um comentário

Follow

Get every new post on this blog delivered to your Inbox.

Join other followers: