>> I am looking for a tool in the PostgreSQL database that can help to audit >> fraudulent activities on a PostgreSQL database. Of particular interest is >> how to retrieve say from a PostgreSQL sales database all the deleted rows. >> > > you can use triggers > > PostgreSQL has no other internal mechanism, but triggers and well > access rights are enough for this purposes
I can't help with past forensics, but for the future… My blog has a post showing a PL/pgSQL routine called from a table's trigger to record all Create, Update, and Delete actions. There is a tricky hack required to get around weakness in PL/pgSQL on order for the routine to work generically. Automatically Record History of Field Changes in Postgres (Dynamic Triggers in PL/pgSQL) http://crafted-software.blogspot.com/2011/05/hoorah-i-was-able-to-complete-my.html Another idea: On some tables in some databases, I never delete records. I have a boolean column that marks if a row is in the imaginary garbage can, and thereafter ignored in all queries. Any true deletes can therefore trigger a "Red Alert". --Basil Bourque -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
