6 ms·

Show HN: Roast my SQLite encryption at-rest

• justinclift 3y ago
  How do you feel yours compares with (say) SQLCipher, which is a very popular C based one, and keeps fairly close to the upstream SQLite releases? * https://www.zetetic.net/sqlcipher/ https://www.zetetic.net/sqlcipher/ * https://github.com/sqlcipher/sqlcipher https://github.com/sqlcipher/sqlcipher Their most recent release (v4.5.7) is based upon SQLite v3.45.3, which is the latest version of SQLite: * https://github.com/sqlcipher/sqlcipher/releases/tag/v4.5.7 https://github.com/sqlcipher/sqlcipher/releases/tag/v4.5.7
  • ncruces 3y ago
    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 https://www.sqlite.org/cksumvfs.html [2] https://sqlite.org/forum/forumpost/db235b3070 https://sqlite.org/forum/forumpost/db235b3070
• nemo1618 3y ago
  Hey, it's my Adiantum package! How's the performance? Revisiting the API now, I see that Encrypt and Decrypt return newly-allocated slices, which is unfortunate. Should I add Append variants that don't force an allocation?
  • ncruces 3y ago
    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 https://sqlite.org/src/file/test/speedtest1.c
    • nemo1618 3y ago
      Ha, you're right, I even have benchmarks in the README showing 0 allocs/op. Nice. (Sorry for doubting you, past-self!) In the past I've implemented a form of the write-buffering you describe -- it was not fun. But it was a lot more important in that context, because flushing a write meant uploading many MB to remote servers. With 4 KB pages flushed locally, I'd wager it's not worth it.
• deleted 3y ago
  [deleted]
• pyuser583 3y ago
  SQLite has a paid version. One of the paid features is encryption. They’re not going to make it easy.
  • ncruces 3y ago
    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).
    • cipherboy 3y ago
      Which reference AES implementation? My memory is that the one from the spec has terrible timing side channel attacks... e.g. https://www.redhat.com/en/blog/its-all-question-time-aes-timing-attacks-openssl https://www.redhat.com/en/blog/its-all-question-time-aes-tim... seems to corroborate my memory. I seem to recall this was remotely exploitable, and exploiting timing side channels has only gotten better since 2014.
      • ncruces 3y ago
        I don't have a license, so can't know for sure. But the only versions mentioned in [1] that should compile out of the box into Wasm, are the ones that say they use "the Rijndaal reference implementation." I don't think compiling OpenSSL into Wasm is tenable. But some wrappers around the Go AES implementation should work. [1] https://www.sqlite.org/see/doc/release/www/readme.wiki https://www.sqlite.org/see/doc/release/www/readme.wiki
  • _lvbh 3y ago
    That was a complete surprise to me. Here’s the relevant link: https://sqlite.org/prosupport.html https://sqlite.org/prosupport.html
• bityard 3y ago
  Have you considered targeting libSQL for this instead? https://github.com/tursodatabase/libsql https://github.com/tursodatabase/libsql SQLite is a great product and I use it a lot, but it does not have a great history of outside contributions and collaboration. If I were developing any kind of extension, I feel like I would have a better shot at getting help and feedback from a community-based project.
  • ncruces 3y ago
    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 https://github.com/ncruces/go-sqlite3
    • avinassh 3y ago
      > 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. Hey, this is v and I am an engineer at Turso. We do have some documentation and an example implementation of Virtual WAL docs: https://github.com/tursodatabase/libsql/blob/ef44612/libsql-sqlite3/doc/libsql_extensions.md#virtual-wal https://github.com/tursodatabase/libsql/blob/ef44612/libsql-... example: https://github.com/tursodatabase/libsql/blob/ef44612/libsql-sqlite3/test/rust_suite/src/virtual_wal.rs https://github.com/tursodatabase/libsql/blob/ef44612/libsql-... for an open source implementation, you may check how Bottomless works. Bottomless is another project which does back up like litestream and it internally implements a Virtual WAL. Bottomless - https://github.com/tursodatabase/libsql/tree/main/bottomless https://github.com/tursodatabase/libsql/tree/main/bottomless I am sure we can improve our docs, make it more discover-able and easy to find. I am open to feedback and suggestions!
• 580515975 3y ago
  A couple of thoughts: First, great job on the readme! One way you could improve it is by expanding on the "Caution" section. What's written is the beginnings of a threat model, but it could be improved by being more explicit about which attacks this system does/doesn't defend against. > The only security property that disk encryption (and this package) provides is that all information such an adversary can obtain is whether the data in a sector has (or has not) changed over time. I think the adversary learns a bit more than this. Randomized encryption would provide the above probably, but the _deterministic_ scheme that's used here will let the adversary learn not only whether a sector changed, but whether its value matches what it was at a previous point in time. How does this translate into the security of the database, itself? Seeing what blocks have changed might reveal information about what data has changed. Let's consider a security game where I (the adversary) get to submit sql queries, and then learn which blocks on disk has changed. After this initial phase (where I can learn where data is stored), I submit two different sql queries, you pick one of them at random and run it, and then tell me which blocks have changed. I win if I can guess which sql query you picked. Suppose I submit queries which each insert into a different table. Because the tables are stored separately on-disk, it'll probably be pretty easy for me to distinguish them. But okay, that's still count-ish/size-ish, and maybe out of scope. What if I submit two queries which each insert different values, but into the same table. Further, let's say that this table has an index. Based on which pages were written to, I can now learn something about the _values_ that were inserted, because different values will write into the index in different places. Now, it's completely valid if the threat model says, "if you can see more than two copies of the database file, then all is lost." However, I think it'd be worth translating the current write-up of the threat model into the implications for leaking the database. For more examples of attacks based on seeing what indices/sizes changed [1] and [2]. Is it valid to pad the sqlite file to a multiple of the block size? Does sqlite ever call truncate on a non-block-aligned size and expect any truncated bytes to be fully removed? What are the atomicity requirements for a sqlite VFS? SQLite, in general, is supposed to not get corrupted if power were to be yanked mid-write. However, because this VFS writes one block at-a-time, the computer dying mid-write could corrupt more bytes around the write position than would normally be corrupted if the the standard VFS was used. It's possible this is a non-issue, but it's worth considering what contract sqlite has for VFSes. [1]: https://en.wikipedia.org/wiki/CRIME https://en.wikipedia.org/wiki/CRIME [2]: https://www.usenix.org/legacy/events/sec07/tech/full_papers/wright/wright.pdf https://www.usenix.org/legacy/events/sec07/tech/full_papers/...
• deleted 3y ago
  [deleted]
• Hakeed1990 3y ago
  Thanks for sharing this.
• d-z-m 3y ago
  For your KDF, how did you select the parameters for Argon2id? How often is the KDF invoked / what are your requirements for speed in KDF'ing? Nit, but your "pepper" is confusingly named IMO, because it is hardcoded and not secret. "label" may make more sense. However, one thing to keep in mind with the use of a static(and public) Argon2 salt is that it allows an attacker to pre-compute keys. If this package were to gain adoption, I think that may become a bigger issue. I would reccommend randomly generating a 128 bit salt, similar to how you're randomly generating the key if one isn't provided.
  • ncruces 3y ago
    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 https://pkg.go.dev/golang.org/x/crypto/argon2#IDKey
    • d-z-m 2y ago
      > I used the parameters suggested in the documentation [1], which follow the RFC Where in the RFC is your parameter set mentioned? I don't see it anywhere[0]. The only parameter set I see mentioned with memory requirement as low as 64 MiB have t=3, not t=1. I believe the Go documentation might be outdated. [0]: https://datatracker.ietf.org/doc/rfc9106/ https://datatracker.ietf.org/doc/rfc9106/ > 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? If it's tweakable at build, perhaps pepper makes more sense.
• slifin 3y ago
  Why do you need this in built into your database? Surely you turn on disk encryption on your drive then if an attacker yanks the drive and tries to read your database without your password they fail?
  • deskr 2y ago
    Data is stolen much more often by copying is instead of yanking the drive out.
  • dig1 3y ago
    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.
    • GauntletWizard 3y ago
      Where are the encryption keys stored? Does the client have to type in a password every time? Userspace encryption of user data has been almost universally rejected because there's no reasonable attack on it - Any attacker that has access to the data also has access to the encryption key.
      • dig1 3y ago
        > Where are the encryption keys stored? Does the client have to type in a password every time? It depends on the use case. Typing a password to unlock the database when the app is started is a popular approach (e.g., keepassx does this), but you can also hardcode/obfuscate it, fetch it remotely, etc. > Userspace encryption of user data has been almost universally rejected Any kind of encryption is better than none. However, an encrypted drive will add zero value if your data and OS can be accessed remotely.
      • CRConrad 3y ago
        > Userspace encryption of user data has been almost universally rejected because there's no reasonable attack on it - Any attacker that has access to the data also has access to the encryption key. So don't you mean there's no reasonable defense against an attack on it? If the "attack" is to just decrypt it with the decryption key the attacker also finds, that seems very reasonable. :-)
• Hakeed1990 3y ago
  [dead]