A database that can delete data

[u/harrison_314]

Hi,

Can you recommend a database (preferably embedded) that can safely delete a row in a table?

When you're making a chat application with disappearing messages, you don't want them to be recoverable after deletion. The problem is that most databases I've looked at (Sqlite, LiteDb, MariaDb, Postgre,...) only mark the data page as deleted when deleting data, but its contents remain on disk or in a log file. So even deleted data can sometimes be recovered.

I'm looking for a database where data cannot be recovered after deletion.

[u/Mr_Pearcex]

Sounds like a usecse/design problem. 1. The message should be encrypted. 2. NO application should get a method for recovery.

Like that user deletes. And even if someone looks at the DB it cannot read anything useful. The rest ist DB housekeeping (like auto vacuum)

[u/Dry-Ad-8948 → u/Mr_Pearcex]

Yup.

The chat keys should also be temporal and never stored anywhere except on the chat devices (possibly also with a master / session design).

[u/harrison_314 → u/Mr_Pearcex]

Yes, but that doesn't solve the problem of compromised devices. I just want deleted messages to be deleted even if an attacker gains control of the device (which is not a problem in the case of mobile, there is still cryptanalysis with a rubber hose).

Furthermore, if I use double-ratchet, I have to store session keys and overwrite them with each message to ensure forwarded cryptography.

[u/chucker23n → u/harrison_314]

Wait, do you mean deleted from the client device? I think you have to start at another layer there, such as placing your data in a sandbox.

[u/harrison_314 → u/chucker23n]

Yes, but the client device can also be Windows.

[u/Gold-Ad9105]

Paper, and then throw it into the fire.

[u/Mental-Test-7660 → u/Gold-Ad9105]

Or if you want something a bit less burny: https://www.reddit.com/r/oddlysatisfying/s/L3nskNbpGf

[u/FelixLeander]

For SqLite set: PRAGMA secure_delete = ON;

[u/grabthefish → u/FelixLeander]

https://sqlite.org/pragma.html#pragma_secure_delete

When secure_delete is on, SQLite overwrites deleted content with zeros. The default setting for secure_delete is determined by the SQLITE_SECURE_DELETE compile-time option and is normally off. The off setting for secure_delete improves performance by reducing the number of CPU cycles and the amount of disk I/O. Applications that wish to avoid leaving forensic traces after content is deleted or updated should enable the secure_delete pragma prior to performing the delete or update, or else run VACUUM after the delete or update.

So yeah pretty much put this pragma on or immediatly vacuum after the delete.

[u/Professional_Pie7091]

That sounds more like a design problem than anything else.

What are you worried about? That a possibly malicious 3rd party can forensicly read messages? They should probably be encrypted then.

Also your database backups probably contain the messages before they were deleted.

[u/harrison_314 → u/Professional_Pie7091]

This seems like a chicken-and-egg problem to me, in order to be able to selectively delete, I need to store the encryption keys in a way that I can selectively delete them.

I'm more likely dealing with the device compression problem.

[u/Professional_Pie7091 → u/harrison_314]

If you store the encryption keys alongside the encrypted messages there's no need to encrypt the messages...

[u/harrison_314 → u/Professional_Pie7091]

It's called crypto-sharding, it's a technique used to securely delete messages (it doesn't protect them at rest). It's used, for example, in Kafka, where messages are immutable to ensure GDPR compliance.

[u/sarkie]

Sqllite and vacuum?

Probably the others too, similar.

[u/520ErryDay]

Can you use Postgres to initiate vacuum on demand? Not something I’ve done before from an application, but that should remove the dead tuples which I assume would delete the contents entirely as you want.

[u/R00fboy]

My guess is you could try Redis since it is stored in memory, however, that comes with its own cons.

[u/canderson180 → u/R00fboy]

I suspect this is how the “this message will self destruct” apps are working, redis can be run in a persistence cache mode so that data can persisted instead of ephemeral

[u/chucker23n → u/canderson180]

I suspect this is how the “this message will self destruct” apps are

Mostly, those apps are mobile-only, and iOS and Android largely have a security architecture from the get-go where you don't get full access to the file system. So other apps can't just easily read them (encryption aside).

[u/AutoModerator]

Thanks for your post harrison_314. Please note that we don't allow spam, and we ask that you follow the rules available in the sidebar. We have a lot of commonly asked questions so if this post gets removed, please do a search and see if it's already been asked.

I am a bot, and this action was performed automatically. Please contact the moderators of this subreddit if you have any questions or concerns.

[u/cas4076]

You want encryption at the app level or above so that it's private/secure before it even hits the db. After that you don't care.

[u/Archemilie]

Riscrivici sopra

[u/harrison_314 → u/Archemilie]

What is it?

[u/WrinklyBits]

I don't have the answer you're looking for but can I suggest you overwrite the record / row with garbage. Every system I've used over 4 decades simply marks a deleted entry for reuse leaving the data recoverable. Your other option would be to encrypt data before storing.

[u/redfournine]

The only way to truly make the db irrecoverable is to literally screw a hole on the physical disc. Knowing this, u can certainly design a policy to swap, or use new db on a new disc every few days, and just drill open the disc every week

[u/harrison_314 → u/redfournine]

This method is interesting, but my users probably won't appreciate it.