Create a gist now

Instantly share code, notes, and snippets.

Embed
Audit with flyway and postgresql
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;
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) {
}
}
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();
}
}
<?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>
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}
@bradyclifford

This comment has been minimized.

Show comment
Hide comment
@bradyclifford

bradyclifford Aug 25, 2017

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

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

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment