Thread: Accessing dead rows within postgresql
Hi there, My name is Aaron and i am new to this forum. I am after a couple of things but would like to take it step by step. Firstly i have never used PostgreSQL which complicates matters, i have been assigned a project which involves me investigating the deletion process within PostgreSQL. I would like to ask how do i work around this, Basically from my research i have found out that PostgreSQL does not securely delete data from its tables, rows, columns or database. Instead it is deleted from the user interface and kept hidden from the user till it is overwritten by bigger bytes of data. What i would like to achieve or do is to delete data and locate it using the source code or any other means as when data is deleted it still remains in parts of the DBMS. I have been looking through the expriement papers and it says thatPostgreSql keeps 100% of its expired records in the DB-slack (database slack) and its trend line is superimposed on that of the expired record. I guess what am trying to do is to input a couple of records delete this records and try to get into the db-slack before carrying out a vacuum to see if i can retrieve the deleted data. Please if anyone knows or understands what am talking please can you advise on how i can achieve this. I have not installed any version of PostgreSQL so does not really matter which version i would use till i get a way to carry out this investigation process. Cheers Aaron -- View this message in context: http://postgresql.1045698.n5.nabble.com/Accessing-dead-rows-within-postgresql-tp3791817p3791817.html Sent from the PostgreSQL - novice mailing list archive at Nabble.com.
On Wed, Mar 16, 2011 at 11:19:38AM -0700, aaronenabs wrote: > I would like to ask how do i work around this, Basically from my research i > have found out that PostgreSQL does not securely delete data from its > tables, rows, columns or database. Instead it is deleted from the user > interface and kept hidden from the user till it is overwritten by bigger > bytes of data. > > What i would like to achieve or do is to delete data and locate it using the > source code or any other means as when data is deleted it still remains in > parts of the DBMS. Yes, old versions of modified data still exist on disk, until vacuum reclaims the pages and they're re-used for new data. PostgreSQL won't let you get to that data once a newer version is committed, so in order to see it you'll need to modify postgres, or write your own program to find it. It's interesting you should describe this as "securely delet[ing]" the data. If someone can get at the old versions of data, they have access to your data files, and you've probably got bigger security problems than someone finding old data. -- Joshua Tolley / eggyknap End Point Corporation http://www.endpoint.com
Attachment
Hi thanks for your reply At the min, its a research am carrying out, from what you stated, "so in order to see it you'll need to modify postgres, or write your own program to find it. " How would you advise i modifty postgres, or what kind of program do you suggest i create, as i described am new to postgresql so have little knowledge on the source code or how to modify it. Thanks for your help. -- View this message in context: http://postgresql.1045698.n5.nabble.com/Accessing-dead-rows-within-postgresql-tp3791817p3904296.html Sent from the PostgreSQL - novice mailing list archive at Nabble.com.
well the project is about accessing the deleted rows not protected them. So what i would really like to achieve is to access data that i have removed from the database without the vacuum procedure carried out. Cheers -- View this message in context: http://postgresql.1045698.n5.nabble.com/Accessing-dead-rows-within-postgresql-tp3791817p3907518.html Sent from the PostgreSQL - novice mailing list archive at Nabble.com.
On Thu, Mar 17, 2011 at 03:17:04PM -0700, aaronenabs wrote: > well the project is about accessing the deleted rows not protected them. So > what i would really like to achieve is to access data that i have removed > from the database without the vacuum procedure carried out. Well, presumably the code you'd need to look at lives in src/backend/access/heap somewhere, or even just changing the HeapTupleSatisfiesVisibility() macro in src/include/utils/tqual.h always to return true. If you were to write your own app, after doing the above you'd need to strip the heap access stuff out of postgres and build your app around it. I think. -- Joshua Tolley / eggyknap End Point Corporation http://www.endpoint.com
Attachment
Joshua Tolley, thanks for that. I heard it is possible to set the HeapTupleSatisfiesVisibility() to true and that shows the dead rows along side the live rows. I intend to carry this out and test this, Do you know any guide for navigating within the source code so i can find its location. Again what kind of app would i be looking to build or would it be a script. Thanks for you help. Aaron -- View this message in context: http://postgresql.1045698.n5.nabble.com/Accessing-dead-rows-within-postgresql-tp3791817p4154557.html Sent from the PostgreSQL - novice mailing list archive at Nabble.com.