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} |
Last active
August 25, 2017 06:40
-
-
Save aVolpe/f12566b5ec7266144354 to your computer and use it in GitHub Desktop.
Audit with flyway and postgresql
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
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; |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
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) { | |
} | |
} |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
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(); | |
} | |
} |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
<?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> |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment
Have you been able to get dblink to work within a flyway migration script?