Thread: is there a function in postgresql that can be used for retrieving deleted rows
is there a function in postgresql that can be used for retrieving deleted rows
From
"Charles Waweru"
Date:
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
Re: is there a function in postgresql that can be used for retrieving deleted rows
From
Pavel Stehule
Date:
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 > > > Any suggestion please would be appreciated. > > > > Charles
Re: is there a function in postgresql that can be used for retrieving deleted rows
From
David Johnston
Date:
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.
Re: is there a function in postgresql that can be used for retrieving deleted rows
From
Steve Atkins
Date:
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
Re: is there a function in postgresql that can be used for retrieving deleted rows
From
Basil Bourque
Date:
>> 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