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

On 13 April 2018 at 18:48, 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.

Can you encrypt the data in the application, above the DB level ? That
would be cleaner if you can.

If not, you'll have to worry about both the DB's data files themselves
and the WAL files in pg_xlog/ which hold copies of the recently
written data. Even if you securely scrub the deleted parts of the
filesystems after dropping the table, there could still be copies of
secret table data in WAL files that haven't yet been overwritten.

One way to scrub deleted files would be to use ZFS and have an extra
disk. When it's time to scrub, "zpool attach" the extra disk to your
zpool, which will cause ZFS to copy over only the files that haven't
been deleted, in the background. When that's finished you can detach
the original disk from the zpool and then do a low-level overwrite of
that entire disk. For extra security points use encrypted block
devices underneath ZFS, and instead of scrubbing the disk just destroy
the encryption key that you were using for it.