# PostgreSQL: pgAudit?

**URL:** <https://forum.xojo.com/t/postgresql-pgaudit/67471>\
**Category:** Databases\
**Created:** [December 17, 2021, 3:48am UTC](https://forum.xojo.com/t/postgresql-pgaudit/67471 "2021-12-17T03:48:09Z")\
**Posts on this page:** 2\
**Page:** 1

<div class="post-metadata">

**Author:** ![KarenA](https://forum.xojo.com/letter_avatar_proxy/v4/letter/k/c89c15/32.png) [@KarenA](https://forum.xojo.com/u/KarenA)\
**Post date:** [December 17, 2021, 3:48am UTC](https://forum.xojo.com/t/postgresql-pgaudit/67471/1 "2021-12-17T03:48:09Z")

</div>

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

---

<div class="post-metadata">

**Author:** ![KarenA](https://forum.xojo.com/letter_avatar_proxy/v4/letter/k/c89c15/32.png) [@KarenA](https://forum.xojo.com/u/KarenA)\
**Post date:** [December 17, 2021, 4:13am UTC](https://forum.xojo.com/t/postgresql-pgaudit/67471/2 "2021-12-17T04:13:42Z")

</div>

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

```auto

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 :

```auto
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