Modern SQLite: Secure delete

This post is part of the Modern SQLite series, where I write about handy SQLite features you might not have heard about.

This feature is available since 00s, but few people know of it.

If you work with sensitive data, and want to be 100% sure that there is no trace of the old data after it has been updated or deleted — SQLite has you covered. The secure_delete pragma (off by default) causes SQLite to overwrite deleted content with zeros.

When secure_delete is off:

-- using the data.db file

create table messages(id integer primary key, value text);
insert into messages(id, value) values (42, 'helloworld');
delete from messages;

-- The file data.db still contains
-- the string 'helloworld' after deletion.

When secure_delete is on:

-- using the data.db file
pragma secure_delete = on;

create table messages(id integer primary key, value text);
insert into messages(id, value) values (42, 'helloworld');
delete from messages;

-- The file data.db does not contain
-- the string 'helloworld' after deletion.

As you'd expect, secure_delete=on results in more CPU and I/O usage. There is also secure_delete=fast (3.20+), which only increases CPU (not I/O), but does not scrub the deleted data from the freelist pages (not sure what the point of this mode is, since it's not actually secure).

Note that when using the WAL journal mode, even the secure_delete=on does NOT zero out the old content in the database file until the WAL checkpoint occurs. And the old content may remain in the WAL file even after the checkpoint (until it's truncated).

-- using the data.db file
pragma journal_mode = wal;
pragma secure_delete = on;

create table messages(id integer primary key, value text);
insert into messages(id, value) values (42, 'helloworld');
pragma wal_checkpoint;

delete from messages;
-- Both data.db and data.db-wal files contain
-- the string 'helloworld' after deletion.

pragma wal_checkpoint;
-- Now data.db does not contain 'helloworld',
-- but data.db-wal still does.

So it's safer to use journal_mode=delete (or memory, or maybe even off) if you want truly secure deletes.

Also, secure_delete=on does not scrub deleted content from virtual tables (like the full-text search shadow tables, although you can change this with the secure-delete FTS5 option).

Documentation:

──

P.S. Like SQLite? Check out Redka — Redis re-implemented with SQLite.

★ Subscribe to keep up with new posts.