Show HN: Roast my SQLite encryption at-rest

So, I haven't studied their solution much, but ISTM what they did was fork SQLite at (around) the time of the commit that removed SQLITE_HAS_CODEC, and forward port the 4 years of changes since then.

That's a bit untenable for me, since I'd rather keep as close as possible to SQLite compiled with Clang, and use the extension points already provided by SQLite (the VFS API).

Most SQLite encryption solutions (SQLite Encryption Extension, SQLCipher, SQLite3 Multiple Ciphers, sqleet) encrypt page content, and some need to reserve a few bytes of every page to do so (for nounces, MACs). This was "easy" to do with SQLITE_HAS_CODEC, but requires some "reverse engineering" to do from the VFS layer. Some of this "reverse engineering" is likely stable, because the "checksum VFS" [1] depends on it. OTOH, extension points that are not part of the "public API" have been summarily dropped in the past [2].

My scheme does not care about the SQLite file format(s) at all, because instead of encrypting just page content, it encrypts entire files. It uses 4K blocks, so setting page size to (at least) 4K is advised, but not required. The only assumption it makes is that SQLite is not sensitive to file sizes rounding up to the next block (4K) size. An assumption that holds for databases, journals and WALs.

The scheme does not try to authenticate blocks, so it doesn't try to protect against forgery. Other solutions may include MACs, but to offer random access they don't protect against reverting a page to an older version of itself, so IMO, this is of limited value.

Other schemes add a nounce to each page, which allows on disk content to change, while the decrypted content stays the same. I don't include a nounce, so if an adversary gets hold of multiple encrypted backups of the same database he knows not only which blocks couldn't possibly have changed, but also which ones definitely did.

[1] https://www.sqlite.org/cksumvfs.html

[2] https://sqlite.org/forum/forumpost/db235b3070

Hey! That's amazing that you found this.

I haven't benchmarked much, but I think I measured a 15% hit on speedtest1 [1] (with about 10% inside your library). Less if I kept temporary files in memory. Other solutions claim less of a performance hit. I'd have to measure to make sure.

There are some things I could do to improve performance. A partial block update needs a read-update-write cycle; journals and WALs do some of these. I could buffer consecutive writes in memory unencrypted and only flush them on fsync. Didn't do that as it requires some additional bookkeeping.

I don't think Encrypt and Decrypt allocate at all! They encrypt/decrypt in-place; the returned slice just aliases the input block. But thanks, it'd be pretty bad if they did.

[1] https://sqlite.org/src/file/test/speedtest1.c

That was a complete surprise to me.

Here’s the relevant link:

https://sqlite.org/prosupport.html

Of course, nor would I expect them to make it so.

In fact, I mention the SEE in my package's documentation. If you have a license to the paid extension, it should be easy to compile it and use it with my package.

It will be slow, however, because it will be running the reference AES implementation in Wasm.

That said, if anyone is interested in sponsoring a SEE license, I can look into doing the encryption Go (which uses assembly on most platforms for those bits).

I used the parameters suggested in the documentation [1], which follow the RFC.

The KDF is invoked every time a connection is opened iff you specify your key with the textkey= parameter. It is ill advised to overuse this, especially if you don't use connection pooling, as it makes opening connections slow. You can bypass the KDF by providing your own 256-bit key using either the hexkey= or key= parameters (key= cannot embed NULLs).

I agree pepper confusing (because the pepper is supposedly secret), but this is not a salt either, as a salt is supposed to be public, but unique. Do you have better naming that you can suggest?

Anyway, I forgot to do this, but the intention was for the pepper to be changeable as a build parameter. Thanks for reminding me!

[1] https://pkg.go.dev/golang.org/x/crypto/argon2#IDKey

This is very useful, for example, when you ship your application to the client(s) with SQLite as the main conf/data storage. You don't have to think about whether their drives are encrypted. Also, it assures the clients that all the data in your application is encrypted by default.

I don't know if that's his use case, but we had many users tell us they share their account with other users so they want the data to be encrypted even when logged in.

Of course if they share their computer, someone could install a keylogger and wait for them to type their passwords, but I guess that's an extra layer of security that may help a bit.

Data is stolen much more often by copying is instead of yanking the drive out.

My extension targets my Go SQLite bindings (the VFS is implemented in Go) [1].

This extension, and the wrapper, target public SQLite APIs, so if anyone wants to replace SQLite with libSQL, that should be easy.

You simply need to include a little bit of glue C code that I append to the amalgamation, and use some SQLite compile options.

I explicitly support building and providing your own, custom, Wasm SQLite "blob" (it just needs to include my glue code).

As for Adiantum encryption, as I said, reimplementing this in C/C++/Rust to make it a loadable extension is perfectly viable, and would be compatible with libSQL, again because this uses only public SQLite APIs (that's the point, basically!)

But this is predicated on it being a secure scheme (that's the feedback I was looking for this time around).

PS: I've got nothing against Turso, or libSQL. In fact I spent the last year perusing their virtual WAL API. The problem is that I found no documentation, nor any useful open source implementations of it. If there any I'd be very interested. So, thus far, I also don't have anything that drives towards libSQL.

[1] https://github.com/ncruces/go-sqlite3

Turso / libsql supports encryption, integrated with MultipleCiphers

Example and usage code is here - https://turso.tech/blog/fully-open-source-encryption-for-sql...

The threat model has to exclude:

- attacks on a running app that has the keys loaded, naturally

The threat model has to include at least:

- passive attacks against the DB itself, lacking access to the keys

The threat model really should also include:

- active attacks against the DB lacking access to the keys (e.g., replace blocks)

IMO ZFS does a pretty good job against these threats, for example, so ZFS is a good yardstick for measuring things like TFA.

However, the fact that a running system must have access to the keys means that at-rest data encryption does not buy one much protection against server compromise, especially when the system must be running much/most/all of the time. So you really also want to do the utmost to secure the server/application.

First of all, thanks for the review. I'll try to respond to all points.

Disk encryption, on which this is based, is usually deterministic in nature.

So yes, an adversary 1 that can inspect multiple versions of a database (e.g. backups) can learn exactly: which (blocks) changed, which didn't change, which have been reverted; but that is all they should learn.

An adversary 2 that can modify files, can also mix-and-match blocks between versions to produce a valid file with high probability .

And an adversary 3 that can submit changes and see their effect on the encrypted data can probably infer a lot about the database.

I'll try to make these more explicit in the README. In practical terms: adversary 1 is the one I thought I'd covered reasonably well; adversary 2 means that backups should be independently signed, and signatures verified before restoring them; adversaries 2 and 3 mean that this is ineffective against live attacks.

Security, though, is also about comparing options. Reading the documentation for alternatives (even the expensive ones) I don't see this kind of analysis. I see 2 advantages to the alternatives that encrypt page data with a nounce and a MAC. The nounce allows reverts to go unnoticed. No change, means a block definitely didn't change. But ciphertext changing doesn't necessarily mean plaintext changed. The MAC ensures blocks are valid. But they still be reverted to previous versions of themselves, mix-and-match is still possible. Do these two properties make a huge difference? Is there anything else I'm missing?

On your other points.

Yes it's always safe to round up file sizes to block size, for databases, journals and WALs (I could detail why, but the formats are documented). It may not be safe for all temporary files (I'm assuming it is), but that can be fixed for those files by remembering the file size in memory.

About atomicity, corruption, etc, the VFS is supposed to declare its characteristics [1] to SQLite. Your concerns are covered by SAFE_APPEND and POWERSAFE_OVERWRITE. See also [2]. As a wrapper VFS, I filter most of those characteristics from the underlying VFS, forcing SQLite to assume the worst.

[1] https://www.sqlite.org/c3ref/c_iocap_atomic.html

[2] https://www.sqlite.org/psow.html