Request: Encrypted at Rest (Adiantum, VFS) Example

[MadManMcIvor]

Hi there,
Would any one be able to share a simple example of this SQlite package using the Adiantum VFS?

I'm newer to go (mostly work on Java at my job) so this is probably a me issue, but I cannot for the life of me seem to wrap my head around how to implement the encrypted at rest vfs.

I'm working on a Wails project (Journaling app) for fun and feel like I should have an encrypted db for it.

I'd gotten it working a bit but it seems to store the encryption key with the database file (ex. database.db?vfs=adiantum&key=baa17dfb854a4b063bdb0588330e97b0441d255eeed429a62c442666fcd14ddd), which I'd think defeats the point of encrypting it. So I must be missing something.

Any help would be appreciated!

[ncruces]

Did you mean to close this?

The key is not stored in the database, that's why you need to supply it with the database name in the file: URI.

Either as a hexkey or textkey (“password”): file:database.db?vfs=adiantum&textkey=correct+horse+battery+staple

Importing package github.com/ncruces/go-sqlite3/vfs/adiantum and using the URI like the above is all you need to do.

Key management is up to you.

[MadManMcIvor]

Thanks for writing back so quickly! I did mean to close it as I thought I'd figured it out - essentially copying from your https://github.com/ncruces/go-sqlite3/blob/main/vfs/adiantum/example_test.go... but even then what I was doing was way over complicating it. It really is this easy:

package main

import (
	"log"
	"os"


	"github.com/ncruces/go-sqlite3"
	_ "github.com/ncruces/go-sqlite3/vfs/adiantum"
	_ "github.com/ncruces/go-sqlite3/embed"
	
)

func main() {
	db, err := sqlite3.Open("file:demo.db?vfs=adiantum" +
		"&textkey=correct+horse+battery+staple")
	if err != nil {
		log.Fatal(err)
	}
	defer os.Remove("./demo.db")
	defer db.Close()
}

I'm embarrassed with how simple it ends up being and how much more I was doing. Thanks again!

[ncruces]

Glad it helped. I'm reopening to make the discussion more visible; this way it helps others. Thanks!

[freb]

@ncruces Perhaps this is a separate question, but what does key rotation look like? I assume you need to re-encrypt the entire database, but would that need to be an in-Go manual process, or is there something that could help with that?

[ncruces]

The SQLite Encryption Extension (with which most SQLite encryption solutions try to be API compatible) has a PRAGMA rekey.

I did not implement this, because as a pure VFS, I am not sure how I could make it both secure (doesn't leak data) and crash safe (doesn't loose data) under all journal modes.

Unless someone comes up with a detailed proposal on how to achieve this, and I find it worth the complexity, my advice is that you use VACUUM INTO to rekey the database into a new file. Alternatively, you could use the Backup API for the same purpose, but if you're recreating the entire file, VACUUM might be a good idea.

Unfortunately, rekeying in place is not possible.