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

> On Apr 13, 2018, at 10:48 AM, Jonathan Morgan <jonathan(dot)morgan(dot)007(at)gmail(dot)com> 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.

Just "securely" deleting the files won't help much, as you'll leave data in spare space on the filesystem, in filesystem journals and so on.

Maybe put the transient tables an indexes in their own tablespace on their own filesystem, periodically move them to another tablespace and wipe the first one's filesystem (either physically or forgetting the key for an encrypted FS)? That'd leave you with just the WAL data to deal with.

Seems like a slightly odd requirement, though. What's your threat model?

Cheers,
Steve