Re: is there a function in postgresql that can be used for retrieving deleted rows

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 raw data 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 going on.

The raw data files may have old rows in them, if they've not been overwritten yet. pg_filedump might help you there, depending on the version of postgresql you're running. (Most row updates are done by deleting an old row and creating a new one, 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