RE: Complete data erasure

From: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
> Up to now, we've sort of looked the other way with respect to failures
> of file unlinks post-commit, reasoning that the worst that will happen
> is disk space leakage from no-longer-referenced files that we failed to
> unlink. (Which is bad, certainly, but not catastrophic; it's immaterial
> to database semantics.) This patch basically needs to raise the level of
> guarantee that exists in this area, or it won't do what it says on the
> tin. But I've not seen any indication that we know how to do that in a
> workable way.

Hmm, the error case is a headache.Even if the bgworker does the erasure, it could hit the same error repeatedly when the file system or disk is broken, causing repeated I/O that may hamper performance.

Do we have no good choice but to leave it up to the user to erase the file content like the following?

https://docs.oracle.com/en/database/oracle/oracle-database/19/asoag/general-considerations-of-using-transparent-data-encryption.html#GUID-F02C9CBF-0374-408B-8655-F7531B681D41
--------------------------------------------------
Oracle Database
Advanced Security Guide
7 General Considerations of Using Transparent Data Encryption

Managing Security for Plaintext Fragments

You should remove old plaintext fragments that can appear over time.

Old plaintext fragments may be present for some time until the database overwrites the blocks containing such values. If privileged operating system users bypass the access controls of the database, then they might be able to directly access these values in the data file holding the tablespace.

To minimize this risk:

1.Create a new tablespace in a new data file.

You can use the CREATE TABLESPACE statement to create this tablespace.

2.Move the table containing encrypted columns to the new tablespace. You can use the ALTER TABLE.....MOVE statement.

Repeat this step for all of the objects in the original tablespace.

3.Drop the original tablespace.

You can use the DROP TABLESPACE tablespace INCLUDING CONTENTS KEEP DATAFILES statement. Oracle recommends that you securely delete data files using platform-specific utilities.

4.Use platform-specific and file system-specific utilities to securely delete the old data file. Examples of such utilities include shred (on Linux) and sdelete (on Windows).
--------------------------------------------------

Regards
Takayuki Tsunakawa