On Oct 21, 2011, at 10:23 PM, Charles Waweru wrote:
> 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.
>
> Any suggestion please would be appreciated.
For forensics work after the fact the three database-specific places to look are the server logs, the WAL logs and the
rawdata files. The server logs are standard human readable logs which may or may not have anything useful in them.
Normal forensics rules - if you're not imaging the whole drive then take copies of everything first, then copies of
those,and only work on the copies of copies.
https://github.com/snaga/xlogdump can show you what's in the WAL logs, but expect to spend some time working out what's
goingon.
The raw data files may have old rows in them, if they've not been overwritten yet. pg_filedump might help you there,
dependingon the version of postgresql you're running. (Most row updates are done by deleting an old row and creating a
newone, so it doesn't make much traffic to wipe out the old rows if autovacuum is running).
Don't forget related logs and backups - logs from the front-end webserver, if any, and any backups.
Cheers,
Steve