Re: postgres zeroization of dead tuples ? i.e scrubbing dead tuples with sensitive data.

On Wed, Nov 18, 2015 at 12:45 PM, Day, David <dday(at)redcom(dot)com> wrote:

> Hi,
>
>
>
> One of my co-workers came out of a NIST cyber-security type meeting today
> and asked me to delve into postgres and zeroization.
>
>
>
> I am casually aware of mvcc issues and vacuuming
>
>
>
> I believe the concern, based on my current understanding of postgres
> inner workings, is that when a dead tuple is reclaimed by vacuuming: Is
> that reclaimed space initialized in some fashion that would shred any
> sensitive data that was formerly there to any inspection by the
> subsequent owner of that disk page ? ( zeroization )
>
>
>
> Not sure that is the exact question to ask but hopefully you get a feel
> for the requirement is not to leave any sensitive data laying about for
>
> recovery by a hacker, or at least minimize the places it could be
> obtained without actually being able to log into postgres or having raw
> disk access privileges.
>
>
>
> Thanks for any comments/instruction/links on the matter.
>
>
>
​http://www.postgresql.org/docs/devel/static/sql-vacuum.html​

​"""
​
Plain VACUUM (without FULL) simply reclaims space and makes it available
for re-use. This form of the command can operate in parallel with normal
reading and writing of the table, as an exclusive lock is not obtained.
However, extra space is not returned to the operating system (in most
cases); it's just kept available for re-use within the same table. VACUUM
FULL rewrites the entire contents of the table into a new disk file with no
extra space, allowing unused space to be returned to the operating system.
This form is much slower and requires an exclusive lock on each table while
it is being processed.
​"""​

​So:

1) Does VACUUM FULL perform any post-rewrite action to obliterate previous
disk file?
2) Does the ready to be reused space get initialized to "zeros" during a
normal VACUUM or do the previous tuple contents exist there until they are
next overwritten?

Unfortunately I do not know the answers and don't wish to hazard a guess.

I'm not certain what mechanics you envision that would allow one to access
this dead space without having raw disk access privileges. In the case of
VACUUM FULL PostgreSQL gives back control of the relevant file to the O/S
and supposedly cannot regain access to it in any reliable (i.e.,
interpretable as PostgreSQL data) sense.

David J.

​