This article is half-done without your Comment! *** Please share your thoughts via Comment ***
If you experienced with SQL Server, You might know about SQL Server Audit and Change Data Capture (CDC) functionality.
As per the PostgreSQL WIKI, PostgreSQL does not have such an Inbuilt functionality for auditing your user transactions.
Most of the Application requires, Database auditing like Who Inserted, When Updated kind of DML Transactions.
Here, I am sharing one demonstration about Trigger Approach, which is not always good, but for specific requirement, we can go with this approach.
I have created separate schema and table to log about DML transactions.
PostgreSQL Database Administrator can use this trigger for security purpose also.
Create a one new Schema:
|
1 |
CREATE SCHEMA AuditTable; |
Create a new table for Logged Transaction:
|
1 2 3 4 5 6 7 8 9 10 11 |
CREATE TABLE AuditTable.tbl_LoggedTransactions ( SchemaName CHARACTER VARYING ,TableName CHARACTER VARYING ,UserName CHARACTER VARYING ,DMLAction CHARACTER VARYING ,OriginalData TEXT ,ExecutedNewData TEXT ,ExecutedSQL TEXT ,RecordDateTime TIMESTAMP WITHOUT TIME ZONE DEFAULT NOW() ); |
Revoke all access of newly creatd table from PUBLIC:
|
1 |
REVOKE ALL ON AuditTable.tbl_LoggedTransactions FROM public; |
Create one trigger function for auditing DML transactions:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 |
CREATE OR REPLACE FUNCTION AuditTable.trg_AuditDML() RETURNS TRIGGER AS $BODY$ DECLARE OldData TEXT; NewData TEXT; BEGIN IF (TG_OP = 'UPDATE') THEN OldData := ROW(OLD.*); NewData := ROW(NEW.*); INSERT INTO AuditTable.tbl_LoggedTransactions ( SchemaName ,TableName ,UserName ,DMLAction ,OriginalData ,ExecutedNewData ,ExecutedSQL ) VALUES ( TG_TABLE_SCHEMA::TEXT ,TG_TABLE_NAME::TEXT ,session_user::TEXT ,substring(TG_OP,1,1) ,OldData ,NewData ,current_query() ); RETURN NEW; ELSIF (TG_OP = 'DELETE') THEN OldData := ROW(OLD.*); INSERT INTO AuditTable.tbl_LoggedTransactions ( SchemaName ,TableName ,UserName ,DMLAction ,OriginalData ,ExecutedSQL ) VALUES ( TG_TABLE_SCHEMA::TEXT ,TG_TABLE_NAME::TEXT ,session_user::TEXT ,substring(TG_OP,1,1) ,OldData ,current_query() ); RETURN OLD; ELSIF (TG_OP = 'INSERT') THEN NewData := ROW(NEW.*); INSERT INTO AuditTable.tbl_LoggedTransactions ( SchemaName ,TableName ,UserName ,DMLAction ,ExecutedNewData ,ExecutedSQL ) VALUES ( TG_TABLE_SCHEMA::TEXT ,TG_TABLE_NAME::TEXT ,session_user::TEXT ,substring(TG_OP,1,1) ,NewData ,current_query() ); RETURN NEW; ELSE RAISE WARNING '[AuditTable.trg_AuditDML] - Other action occurred: %, at %',TG_OP,now(); RETURN NULL; END IF; END; $BODY$ LANGUAGE plpgsql; |
Create a sample table:
|
1 2 3 4 5 |
CREATE TABLE public.tbl_Employees ( EmpID INT ,EmpName CHARACTER VARYING ); |
Create trigger on tbl_Employees with the use of trigger function AuditTable.trg_AuditDML:
|
1 2 3 |
CREATE TRIGGER trg_tbl_Employees_INSERT_UPDATE_DELETE AFTER INSERT OR UPDATE OR DELETE ON public.tbl_Employees FOR EACH ROW EXECUTE PROCEDURE AuditTable.trg_AuditDML(); |
Execute few sample DMLs:
|
1 2 3 4 5 6 7 8 |
INSERT INTO tbl_Employees VALUES (1,'Anvesh'),(2,'Martin'),(3,'Roy') ,(4,'Jeeny'),(5,'Kavita'),(6,'Neevan'); UPDATE tbl_Employees SET EmpName='Mahi' WHERE EmpID = 5; DELETE FROM tbl_Employees WHERE EmpID = 4; |
Check AuditTable.tbl_LoggedTransactions table for logged DML transaction:
|
1 |
SELECT *FROM AuditTable.tbl_LoggedTransactions; |
