Filler 1


So I’ve been pretty slack about posting dev stuff as of late. I can blame most of my silence on being busy writing code, but I won’t have that excuse pretty soon. With any luck we’ll be able to slow down a bit after July (watch for the alpha release!) and I’ll get to do some brain dumping here.

To get back into the swing of posting development updates I thought I’d put up something that we’re using in the Open-ILS database. Like all big projects that deal with user transactions we require an amount of accountability because of shared access to customer/patron data. The normal approach to this when using an RDBMS is to create an audit table for each production table you want to track. Normally one would have to create each audit table by hand, but since we’re using Postgres we get a little help. Postgres has a table creation option called LIKE that will create all columns from the named LIKE table in addition to any named in the CREATE list. Wrap that up in a UDF that creates the audit table and add a couple of triggers, and voila!


BEGIN;

-- Create a schema to contain all of our audit tables and the creator function
CREATE SCHEMA auditor;

-- The function that does the heavy lifting of creating audit tables
-- and the triggers that will populate them.
CREATE FUNCTION auditor.create_auditor ( sch TEXT, tbl TEXT ) RETURNS BOOL AS $creator$
BEGIN

	-- Create the audit table : auditor.{schema}_{table}_history
	EXECUTE $$
		CREATE TABLE auditor.$$ || sch || $$_$$ || tbl || $$_history (
			audit_time	TIMESTAMP WITH TIME ZONE	NOT NULL,
			audit_action	CHAR(1)				NOT NULL,
			LIKE $$ || sch || $$.$$ || tbl || $$
		);
	$$;

	-- Create the function that will update the new table
	EXECUTE $$
		CREATE FUNCTION auditor.audit_$$ || sch || $$_$$ || tbl || $$_func ()
		RETURNS TRIGGER AS $func$
		BEGIN
			INSERT INTO auditor.$$ || sch || $$_$$ || tbl || $$_history
				SELECT now(), SUBSTR(TG_OP,1,1), OLD.*;
			RETURN NULL;
		END;
		$func$ LANGUAGE 'plpgsql';
	$$;

	-- Create the trigger to run the function after a
	-- successful update or delete to the table.
	EXECUTE $$
		CREATE TRIGGER audit_$$ || sch || $$_$$ || tbl || $$_update_trigger
			AFTER UPDATE OR DELETE ON $$ || sch || $$.$$ || tbl || $$ FOR EACH ROW
			EXECUTE PROCEDURE auditor.audit_$$ || sch || $$_$$ || tbl || $$_func ();
	$$;
	RETURN TRUE;
END;
$creator$ LANGUAGE 'plpgsql';

COMMIT;

Now it’s a simple matter of choosing the tables you’d like to audit and running the auditor.create_auditor() function with the schema and table names.

SELECT auditor.create_auditor( 'actor', 'usr' );
SELECT auditor.create_auditor( 'biblio', 'record_entry' );
SELECT auditor.create_auditor( 'asset', 'copy' );

Any changes made to actor.usr, biblio.record_entry or asset.copy will be recorded with the change type (‘U’ for update and ‘D’ for delete) and the time of the change. If you record the user id of the editor in the record, you then have accountability. You can also travel back in time to remove bad edits or resurrect deleted rows.


One thought on “Filler

  • Bricklen

    Thanks for the code! It works well as is, obviously, but I’ve made a few modifications to it.

    Changes:
    – logs the SQL statement that was issued, if you have installed dblink from contrib
    – logs the old and new rows
    nb: this code has not been tested thoroughly, but the set of tests that I ran all appeared to work correctly – delete, insert, update, transactions, etc. The client ip has yet to show a value, but that may be because I’m only testing via local connection(?)

    Here is the revised function:

    CREATE OR REPLACE FUNCTION auditor.create_auditor ( sch TEXT, tbl TEXT ) RETURNS BOOL AS $$
    BEGIN
    EXECUTE
    ‘CREATE TABLE auditor.’ || sch || ‘_’ || tbl || ‘_audit (
    audit_id BIGINT NOT NULL,
    row_type TEXT,
    audit_time TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    audit_action TEXT,
    user_ip INET,
    LIKE ‘ || sch || ‘.’ || tbl || ‘,
    query TEXT);’;

    EXECUTE
    ‘CREATE FUNCTION auditor.audit_’ || sch || ‘_’ || tbl || ‘_func ()
    RETURNS TRIGGER AS $func$
    DECLARE
    v_row_id BIGINT := nextval(”auditor.audit_id_seq”);
    v_now TIMESTAMPTZ := now();
    v_user TEXT := session_user;
    v_user_ip INET := inet_client_addr();
    v_query TEXT;
    BEGIN
    if (not (select case when count(*)>0 then true else false end from pg_proc where proname ilike ”%dblink_current_query%”)) then
    v_query := ”[WARNING] – Must create dblink_current_query() to see SQL issued.”;
    ELSE
    v_query := dblink_current_query();
    END IF;
    IF tg_op = ”INSERT” THEN
    INSERT INTO auditor.’ || sch || ‘_’ || tbl || ‘_audit
    VALUES (v_row_id,null,v_now, SUBSTR(TG_OP,1,1),v_user_ip,NEW.*,v_query);
    ELSIF tg_op = ”UPDATE” THEN
    INSERT INTO auditor.’ || sch || ‘_’ || tbl || ‘_audit
    SELECT v_row_id,”original row”,v_now,null,v_user_ip,OLD.*,null;
    INSERT INTO auditor.’ || sch || ‘_’ || tbl || ‘_audit
    SELECT v_row_id,”new row”,v_now, SUBSTR(TG_OP,1,1),v_user_ip, NEW.*,v_query;
    ELSIF tg_op = ”DELETE” THEN
    INSERT INTO auditor.’ || sch || ‘_’ || tbl || ‘_audit
    SELECT v_row_id,”original row”,v_now,SUBSTR(TG_OP,1,1),v_user_ip,OLD.*,null;
    END IF;
    RETURN NULL;
    END;
    $func$ LANGUAGE plpgsql;’;

    EXECUTE ‘
    CREATE TRIGGER audit_’ || sch || ‘_’ || tbl || ‘_update_trigger
    AFTER UPDATE OR DELETE OR INSERT ON ‘ || sch || ‘.’ || tbl || ‘ FOR EACH ROW
    EXECUTE PROCEDURE auditor.audit_’ || sch || ‘_’ || tbl || ‘_func ();’;
    RETURN TRUE;
    END;
    $$ LANGUAGE plpgsql;

Comments are closed.