Is there a way to support encryption via SQLite3MultipleCiphers when using OPFSCoopSyncVFS?

[silenceofthewans]

OPFSCoopSyncVFS works great for my usecase of offline-first the only nice to have will be encryption since db files sit in OPFS unencrypted so I thought of doing a custom build using sqwab and SQLite3MultipleCiphers with these make arguments:

SQLITE_VERSION='version-3.51.2' WASQLITE_EXTRA_DEFINES=-DSQLITE_ENABLE_FTS5 -D__WASM__ -I. as mentioned in SQLite3MultipleCiphers docs and also added the files from their
sqlite3mc-2.2.7-sqlite-3.51.2-amalgamation.zip release like:

sqwab/
├── overlay.wa-sqlite/
│   ├── .placeholder
│   ├── sqlite3.c        <-- (renamed from sqlite3mc_amalgamation.c)
│   ├── sqlite3.h        <-- (renamed from sqlite3mc_amalgamation.h)
│   └── sqlite3ext.h     <-- (copied as is from the zip)

It does build and I can use in my app but as soon as I use the encryption related features like the PRAGMA key = "mysecretkey"; I get error: Setting key failed. Encryption is not supported by the VFS. Don't know if this error is from the SQLite3MultipleCiphers side or the OPFSCoopSyncVFS. Are these simply incompatible at the moment or has anyone successfully enabled encryption while using OPFSCoopSyncVFS ?

[rhashimoto]

I have never tried to use SQLite3MultipleCiphers in any context, but it looks like this particular error message occurs when the SQLite3MultipleCiphers VFS can't be found in SQLite's VFS stack. My guess is that you are not instantiating your wa-sqlite VFS in such a way that it also includes SQLite3MultipleCiphers. Perhaps SQLite3MultipleCiphers is looking for something in the primary wa-sqlite VFS that it isn't finding, but I think it probably would produce an error earlier if that were the case. So my guess is that this is a VFS initialization error in your code.

Now whether OPFSCoopSyncVFS (or any wa-sqlite sample VFS) will work with SQLite3MultipleCiphers is an open question AFAIK. I don't think that OPFSCoopSyncVFS peeks into the database header or page data (as other sample VFS do) so that won't be a problem, but it does purposely return some errors to handle asynchronous operations which may or may not cause problems for SQLite3MultipleCiphers.

[silenceofthewans]

after reading some info here: https://utelle.github.io/SQLite3MultipleCiphers/docs/faq/faq_overview/#how-can-i-enable-encryption-for-a-non-default-sqlite-vfs

I had to do this to make it work:

    const vfs = await OPFSCoopSyncVFS.create('MyVFS', module);
	sqlite3.vfs_register(vfs, true); // making default here didn't work here I guess

	db = await sqlite3.open_v2(dbName, flags, 'multipleciphers-MyVFS'); // prepend "multipleciphers-" to vfs name
	await sqlite3.exec(db, `PRAGMA key = 'mysecretkey';`); // make this the first command after opening

So far I can see it works since it gives "file is not a database" if I pass no PRAGMA key or wrong key and I can see encrypted file if I download from opfs. And normal usage in my app syncs, boostraps etc seems to be working fine. wa-sqlite.wasm size did went from 702kb (base + fts5) to 885kb (base + fts5 + encryption) but not too bad.

[rhashimoto]

That's great, thanks for detailing your success! My guess is that SQLite3MultipleCiphers is shimming the default VFS at the time it is loaded but doesn't track OPFSCoopSyncVFS becoming the default after that.

[SirajChokshi]

I see this is resolved, but thought it was worth sharing the fork of wa-sqlite I made for this exact purpose: https://github.com/SirajChokshi/wa-sqlite

I added some demos and has the finished build if anybody wants to use those directly or reference.

[silenceofthewans]

Great! although I think the prepend trick requires less config if you already use sqwab. Have you been able to use PRAGMA rekey = 'mynewsecretkey' to change the encrypted db's key or to make an unecrypted db with existing data encrypted? In my tests PRAGMA rekey = '' for removing encryption works fine but I haven't been able to change the encryption key or add encryption to an existing db via rekey. The first rekey call does go through but using PRAGMA key = 'mynewsecretkey' doesn't open as if wrong key is provided since it gives the "File is not a database" error even though the db is still encrypted in OPFS. I also tried using the VACUUM INTO 'file:newencrypted.db?key=mysecretkey' but same story.