How to safely delete a row from a database?

Question

For compliance reasons, when I delete user personal information from the database in my current project, the corresponding rows should really be permanently deleted.

The database we use is postgres 8.x,

Is there anything I can do except to execute COMPACT / VACUUM regularly?

Fortunately, our backups will be kept by others and they will be allowed to store deleted information.

Answer 1

"Unreported deletion" is harder than it sounds and goes beyond your database. For example, do you plan to go back to all previous instances of your database on a tape / backup, where this line also exists, and delete it there too?

Consider regular deletion and periodic VACUUMING, which you mentioned earlier.

Answer 2

Are you backing up your database? - If so, make sure you also delete it from the backups.

Is it because of the security risk? In this case, I would modify the data in the row and then delete the row.

Answer 3

I might be disconnected tangentially, but do you really want to delete such users? Most approaches to managing identity and access recommend supporting users, but in a state marked “as deleted” so as not to lose audit capabilities (what has this user been in the last five years)?

Removal of user information may be required for integrity purposes or for vile black hats. In any case, there is no deletion method that guarantees that traces cannot be left from the existence of the user, as was noted in other posts.

Perhaps you should clarify why such an irrevocable removal is desirable ...?

Answer 4

To execute a “D” in an ACID, relational databases use a transaction log type system to modify the database. When deletion is done, deletion is made into a copy of the data memory (buffer cache), and then it is written to the transaction log file in synchronous mode. If the database should have failed, the transaction log will be renamed to return the system to the correct state. Thus, deletion exists in several places where it will need to be deleted. Only at a later time the record is “deleted” from the actual data file on disk (and any indexes). This amount of time depends on the database.

Answer 5

This is not something that you can do on the software side. Its a hardware problem, to really remove it, you need to physically destroy the disk.

Answer 6

How to rewrite a record with random characters / dates / numbers, etc.?