PostgreSQL: pgAudit?

No really an Xojo question, but the Xojo DB app I am writing is going to need audit trail capability…

I created a trigger based approach to log all CRUD operations on the tables to a single audit train table… But it would not be easy for an auditor to do audit on, and I don’t know if it would be sufficient…

There is an open source module pgAudit that has been developed specifically to meet regulatory requirements… But being an Xojo “citizen developer”, I don’t have the knowledge to compile it myself and integrate it into the DB…

One can easily download compiled installers for PostgresSQL itself for which one does not need to know much to install and get running on the platforms I care about (server will be either Mac or Windows- won’t know until last minute)

So does anyone know if there are current precompiled versions of pgAudit (I’m use PostgreSQL 14.1) available somewhere fo Mac and Windows that can be integrated into an installed Postgre server?

Failing that, does anyone know of instructions for dummies for compiling and integrating it into an installed PostgreSQL server, for these platforms?

On the Mac I assume Xcode could do it (I don’t know Xcode but I know it’s free)…and hopefully there would be free compiler to use for Windows (I don’t know VS either or if the free version would be enough)

Thanks for any info.

-Karen

BTW Here is ho I implemented the auditing (based on things I found on the web)


CREATE TABLE IF NOT EXISTS audit.AuditTable (
	Key0             		BIGSERIAL PRIMARY KEY,
	schema_name 		text not null,
	table_name 		text not null,
	SysUserName		text,
	SysOID			OID,
	ActionType 		TEXT NOT NULL CHECK (ActionType IN ('I','D','U', 'T')),
 	ActionDate 		TIMESTAMP WITH TIME ZONE NOT NULL,
	AppName 			Text,
	ClientAddress 		inet,
	Command 			text,
	OldData			JSONB,
	NewData 			JSONB); 

CREATE INDEX logged_Table_idx ON audit.AuditTable(table_name);
CREATE INDEX logged_Date_idx ON audit.AuditTable(ActionDate);
CREATE INDEX logged_actionstype_idx ON audit.AuditTable(ActionType);

--------------------------------------------------------------------------------------------------------------------------
	
CREATE FUNCTION audit.LogAudit () RETURNS trigger AS $LogAudit$
BEGIN
        INSERT INTO audit.AuditTable (
      	  	schema_name,
		table_name,
   	     	SysUserName,
   	     	SysOID,
   	     	ActionType,
    	    	ActionDate,
   	     	AppName,
     	   	ClientAddress,
    	    	Command,
    	    	OldData,
   	 	NewData)
	VALUES(
		TG_TABLE_SCHEMA::TEXT,
		TG_TABLE_NAME::TEXT,
		session_user::TEXT,
		to_regrole( session_user )::OID,
        	substring(TG_OP,1,1),
        	now(),
        	current_setting('application_name'),
        	inet_client_addr(),
        	current_query(), 
        	to_JSONB(OLD),
        	to_JSONB(NEW) );

	RETURN NULL;
END;
$LogAudit$ LANGUAGE plpgsql SECURITY DEFINER;

And create the triggers on each table as :

CREATE OR REPLACE TRIGGER  TableName_audit
	AFTER INSERT OR UPDATE OR DELETE ON TableName
	FOR EACH ROW EXECUTE PROCEDURE audit.LogAudit();

I have no clue if that is enough… I could also look into what DB Logging Postage has…

But how to get the data into a form that an auditor (who is not an IT person) could read and find acceptable, is not clear to me, and potentially a lot of work I don’t have time for!

Advice is welcome!
-Karen