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

Hello

2011/10/22 Charles Waweru <cwaweru@systempartners.biz>:

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

Regards

Pavel Stehule

On Oct 22, 2011, at 1:23, "Charles Waweru" <cwaweru@systempartners.biz> 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.

Charles

You have to manually save the row to an archive table prior to deleting it. Once you have deleted a row from a table you cannot get it back.

There are multiple options to choose from to implement such an archive process.

David J.

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

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