Re: how to securely delete the storage freed when a table is dropped?

On 04/13/2018 12:48 PM, Jonathan Morgan wrote:
> For a system with 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 
> any particular table likely won't be (so can't just shred the disks at 
> "completion"), 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 online 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)
>
> 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.

I'd write a program that fills all free space on disk with a specific 
pattern.  You're probably using a logging filesystem, so that'll be far from 
perfect, though.

-- 
Angular momentum makes the world go 'round.