4

For a system with sensitive information stored in a PostgreSQL 9.5 database, in which data stored in a table that is deleted must be securely deleted (like shred does to files), and where the system is persistent even though a particular data set won't be, I'm trying to figure out my options for securely deleting the underlying data files when a table is dropped.

As background, I'm not a DBA, but I am an experienced implementor in many languages, contexts, and databases. I've looked and haven't been able to find a way to ask PostgreSQL to do the equivalent of shredding its underlying files before releasing them to the OS when a table is DROPped. Is there a built-in way to ask PostgreSQL to do this? (I might just not have searched for the right thing - my apologies if I missed something and I'll gladly accept abuse along with links to relevant documentation)

A partial answer we're looking at is shredding the underlying data files for a given relation and its indexes manually before dropping the tables, but this isn't so elegant, and I'm not sure it is getting all the information from the tables that we need to delete.

We also are looking at strategies for shredding free space on our data disk - either running a utility to do that, or periodically replicating the data volume, swapping in the results of the copy, then shredding the entire volume that was the source so its "free" space is securely overwritten in the process.

Are we missing something? Are there other options we haven't found? If we have to clean up manually, are there other places we need to go to shred data than the relation files for a given table, and all its related indexes, in the database's folder? Any help or advice will be greatly appreciated.

1

drop table removes the table files from disk filesystem.

If you need them securely erased you could perhaps look for a filesystem that supports that.

On linux SSD filesystems, mounting with -O discard (or running fstrim after dropping) will hide the content of the deleted files from consumer-grade forensic tools (like photorec, dd, etc.) however nation-states with electron beam probes and the like may still be able to access the data if they can gain physical access to the media.

On magnetic disk running sfill will shred the deallocated blocks.

0

There is no failsafe mechanism to do what you want, especially in a world with SSD drives. I don't think this belongs on Database Administrators because it has nothing to do with PostgreSQL. You're just asking how do you securely delete a file. That's a really tough task with a ton of variables and it's far more suitable for Security StackExchange.

You have to decide what you're trying to protect against. If you're trying to protect against an entity with unlimited resources and access to a device full of files that have passed through unlink you're going to have to know,

  • device make/model underlying technology
  • file system
  • method of cleaning or shreding

Alas, just

Then someone can walk away with your door stop and you're good to go.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.