Audit with flyway and postgresql

audit_trigger.sql

CREATE OR REPLACE FUNCTION audit.if_modified_func() RETURNS TRIGGER AS $body$
DECLARE
    v_old_data json;
    v_new_data json;
BEGIN
    /*  If this actually for real auditing (where you need to log EVERY action),
        then you would need to use something like dblink or plperl that could log outside the transaction,
        regardless of whether the transaction committed or rolled back.
    */
 
    /* This dance with casting the NEW and OLD values to a ROW is not necessary in pg 9.0+ */
 
    IF (TG_OP = 'UPDATE') THEN
        v_old_data := row_to_json(OLD);
        v_new_data := row_to_json(NEW);
        INSERT INTO audit.logged_actions (schema_name,table_name,user_name,action,original_data,new_data,query) 
        VALUES (TG_TABLE_SCHEMA::TEXT,TG_TABLE_NAME::TEXT,session_user::TEXT,substring(TG_OP,1,1),v_old_data,v_new_data, current_query());
        RETURN NEW;
    ELSIF (TG_OP = 'DELETE') THEN
        v_old_data := row_to_json(OLD);
        INSERT INTO audit.logged_actions (schema_name,table_name,user_name,action,original_data,query)
        VALUES (TG_TABLE_SCHEMA::TEXT,TG_TABLE_NAME::TEXT,session_user::TEXT,substring(TG_OP,1,1),v_old_data, current_query());
        RETURN OLD;
    ELSIF (TG_OP = 'INSERT') THEN
        v_new_data := row_to_json(NEW);
        INSERT INTO audit.logged_actions (schema_name,table_name,user_name,action,new_data,query)
        VALUES (TG_TABLE_SCHEMA::TEXT,TG_TABLE_NAME::TEXT,session_user::TEXT,substring(TG_OP,1,1),v_new_data, current_query());
        RETURN NEW;
    ELSE
        RAISE WARNING '[AUDIT.IF_MODIFIED_FUNC] - Other action occurred: %, at %',TG_OP,now();
        RETURN NULL;
    END IF;
 
EXCEPTION
    WHEN data_exception THEN
        RAISE WARNING '[AUDIT.IF_MODIFIED_FUNC] - UDF ERROR [DATA EXCEPTION] - SQLSTATE: %, SQLERRM: %',SQLSTATE,SQLERRM;
        RETURN NULL;
    WHEN unique_violation THEN
        RAISE WARNING '[AUDIT.IF_MODIFIED_FUNC] - UDF ERROR [UNIQUE] - SQLSTATE: %, SQLERRM: %',SQLSTATE,SQLERRM;
        RETURN NULL;
    WHEN OTHERS THEN
        RAISE WARNING '[AUDIT.IF_MODIFIED_FUNC] - UDF ERROR [OTHER] - SQLSTATE: %, SQLERRM: %',SQLSTATE,SQLERRM;
        RETURN NULL;
END;
$body$
LANGUAGE plpgsql
SECURITY DEFINER
SET search_path = pg_catalog, audit;

AuditCallback.java

package test;

import java.sql.Connection;

import org.flywaydb.core.api.MigrationInfo;
import org.flywaydb.core.api.callback.FlywayCallback;

public class AuditCallback implements FlywayCallback {

	@Override
	public void beforeClean(Connection connection) {
	}

	@Override
	public void afterClean(Connection connection) {
	}

	@Override
	public void beforeMigrate(Connection connection) {
	}

	@Override
	public void afterMigrate(Connection connection) {

		try {
			System.out.println("");
			System.out.println("Updating audit info");
			System.out.println("");

			new AuditUtil().updateAuditInformation(connection);

		} catch (Exception e) {
			throw new RuntimeException("Error al intentar realizar la migracion", e);
		}
	}

	@Override
	public void beforeEachMigrate(Connection connection, MigrationInfo info) {
	}

	@Override
	public void afterEachMigrate(Connection connection, MigrationInfo info) {
	}

	@Override
	public void beforeValidate(Connection connection) {
	}

	@Override
	public void afterValidate(Connection connection) {
	}

	@Override
	public void beforeBaseline(Connection connection) {
	}

	@Override
	public void afterBaseline(Connection connection) {
	}

	@Override
	public void beforeInit(Connection connection) {
	}

	@Override
	public void afterInit(Connection connection) {
	}

	@Override
	public void beforeRepair(Connection connection) {
	}

	@Override
	public void afterRepair(Connection connection) {
	}

	@Override
	public void beforeInfo(Connection connection) {
	}

	@Override
	public void afterInfo(Connection connection) {
	}

}

AuditUtil.java

package test;

import java.io.IOException;
import java.nio.file.Files;
import java.nio.file.Path;
import java.nio.file.Paths;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Arrays;
import java.util.List;

public class AuditUtil {

	//@formatter:off
	private static final String QUERY_TABLES = 
			" SELECT table_schema || '.' || table_name AS table"
			+ " FROM information_schema.tables" 
			+ " WHERE table_schema NOT IN ('pg_catalog', 'information_schema')";
	
	
	private static final String QUERY_ADD_TRIGGER = 

			  " DROP TRIGGER IF EXISTS ##TABLE##_audit ON ##SCHEMA##.##TABLE##; \n" 
			+ " CREATE TRIGGER ##TABLE##_audit"
			+ " AFTER INSERT OR UPDATE OR DELETE ON ##SCHEMA##.##TABLE##"
			+ " FOR EACH ROW EXECUTE PROCEDURE audit.if_modified_func();";
	//@formatter:on

	private static final List<String> IGNORED_TALBES = Arrays.asList("audit.logged_actions", "public.schema_version");

	public void updateAuditInformation(Connection con) throws IOException, SQLException {

		updateTrigger(con);
		for (String table : getAllTables(con)) {
			addTriggerATable(con, table);
		}
	}

	public List<String> getAllTables(Connection con) throws SQLException {

		List<String> toRet = new ArrayList<>();
		ResultSet rs = con.prepareStatement(QUERY_TABLES).executeQuery();

		while (rs.next()) {
			String tableName = rs.getString(1);
			if (IGNORED_TALBES.contains(tableName)) {
				continue;
			}
			toRet.add(tableName);
		}

		rs.close();
		return toRet;
	}

	public void addTriggerATable(Connection con, String table) throws SQLException {

		String schemaName = table.split("\\.")[0];
		String tableName = table.split("\\.")[1];

		String query = QUERY_ADD_TRIGGER.replaceAll("##TABLE##", tableName).replaceAll("##SCHEMA##", schemaName);

		con.prepareStatement(query).execute();
	}

	public void updateTrigger(Connection con) throws IOException, SQLException {

		Path path = Paths.get("src/main/resources/db/util/audit_trigger.sql");

		String query = new String(Files.readAllBytes(path));

		con.prepareStatement(query).execute();

	}

}

pom.xml

<?xml version="1.0" encoding="UTF-8"?>
<project xmlns="http://maven.apache.org/POM/4.0.0" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
	xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/xsd/maven-4.0.0.xsd">
	
	

	<dependencies>
	

		<!-- Flyway -->
		<dependency>
			<groupId>org.flywaydb</groupId>
			<artifactId>flyway-core</artifactId>
			<version>3.2.1</version>
		</dependency>



	</dependencies>

	<build>
		<plugins>
			<plugin>
				<groupId>org.flywaydb</groupId>
				<artifactId>flyway-maven-plugin</artifactId>
				<version>3.2.1</version>
				<configuration>
					<url>jdbc:postgresql://localhost:5432/database</url>
					<callbacks>
						<callback>py.com.cds.framework.audit.AuditCallback</callback>
					</callbacks>

				</configuration>
				<dependencies>
					<dependency>
						<groupId>postgresql</groupId>
						<artifactId>postgresql</artifactId>
						<version>9.1-901-1.jdbc4</version>
					</dependency>
				</dependencies>
			</plugin>
		</plugins>
	</build>
</project>

result.md

schema_name	table_name	user_name	action_tstamp	action	original_data	new_data
public	rol	postgres	2015-10-19 17:37:21.859658-03	I		{"id":1,"nombre":"rol 1","descripcion":"el esl rol numero 1"}
public	usuario	postgres	2015-10-19 17:42:49.63219-03	I		{"id":1,"nombre":"avolpe"}
public	usuario_rol	postgres	2015-10-19 17:42:49.63219-03	I		{"id_usuario":1,"id_rol":1}

Have you been able to get dblink to work within a flyway migration script?