What is it

• It create the same schemas and tables in a audit DB.
• It add triggers in each of the tables to your DB to copy every INSERT, UPDATE or DELETE to the audit DB.

How to start

1. Copy all functions in db_init folder in your current DB.

2. Create a audit DB and copy audit_get_table_columns in the public schema.

3. It require dblink extension in order to work, so install it:

CREATE EXTENSION IF NOT EXISTS dblink;

4. Run this code. Update the dbname, user, and password (password might not be required).

SELECT dblink_connect(
	'audit_db_connection',
	'host=127.0.0.1 port=5432 dbname=audit user=root password=1234 options=-csearch_path='
);
-- Copy all your schemas and tables to the audit DB
SELECT audit_db(
	'audit_db_connection',
	'host=127.0.0.1 port=5432 dbname=audit user=root password=1234 options=-csearch_path='
);
SELECT dblink_disconnect('audit_db_connection');

4. Review your audit DB you will have the same struct of your DB. Insert something in your DB and review it in the audit DB.

What happens if I update a table or add a new column?

You just have to run this function

SELECT dblink_connect(
	'audit_db_connection',
	'host=127.0.0.1 port=5432 dbname=audit user=root password=1234 options=-csearch_path='
);
-- Update table triggers and audit table column
SELECT audit_table(
	'audit_db_connection',
	'host=127.0.0.1 port=5432 dbname=audit user=root password=1234 options=-csearch_path=',
	'my_schema',
	'my_table'
);
SELECT dblink_disconnect('audit_db_connection');

Open a new issue

If you want to fix something or improve the code. These are the steps to install it in dev env.

• Run git clone https://github.com/albertcito/postgresql-audit.git
• Run cd postgresql-audit
• Run docker-compose up -d

Review test it in PgAdmin

• Run this query function to create a copy of the public.lang table in audit db

SELECT test_table()

• Insert data in lang table

INSERT INTO public.lang(id, name, localname, active, is_blocked, created_by, updated_by, type)
VALUES ('EN', 'English', 'English', true, false, 1, 2, 'left');

• Review the table audit DB to see the same value inserted.

Run test in the terminal

• docker exec -it postgresql-audit bash
• Connect and test it
  • psql -U db_user example_db
  • SELECT test_table();