>> 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
imaginarygarbage can, and thereafter ignored in all queries. Any true deletes can therefore trigger a "Red Alert".
--Basil Bourque