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

From: Jonathan Morgan <jonathan(dot)morgan(dot)007(at)gmail(dot)com>
To: pgsql-general(at)lists(dot)postgresql(dot)org
Subject: how to securely delete the storage freed when a table is dropped?
Date: 2018-04-13 17:48:02
Message-ID: CAK5U2OK5Ox_XVo_Gf2WKSSMVDYBy6j7wYW3YY00bS7syxeAXJw@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

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.

Thanks,

Jonathan Morgan

--
"The man with the new idea is a Crank until the idea succeeds."
- Mark Twain, from 'Following the Equator: A Journey Around the World'

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Tim Smith 2018-04-13 18:15:00 Re: Recursive CTE for building menus
Previous Message Paul Jungwirth 2018-04-13 15:04:37 Re: Recursive CTE for building menus