Hacker News Reader

Comment by Snawoot | Hacker News Reader

There is no point to use sqlite3 in default journaling mode. I bet results may be even better than dbm if you use

  PRAGMA journal_mode=WAL;

View on HN

> There is no point to use sqlite3 in default journaling mode.

There are situations where you wouldn't want to, but they are probably very uncommon outside of the embedded computing world. Copying 3 files vs 1 is not a gigantic deal. Most of the time you aren't even moving a SQLite database around.

You can still copy just the primary file and not the WAL or any other files if you want to live dangerously and possibly lose transactions if there are write operations in parallel.

Of course, you'd be better off with WAL-based replication tech like litestream instead of plain file copies if you are truly worried about parallel operations during your file copies.

You can even distribute individual WAL frames and arrange distributed writes with raft consensus and a time lease. I never formally modelled this but it seemed to work perfectly: the approach only lacked checkpointing the Wal file and synchronising the database file across raft nodes.

There's never a need to copy 3 files anyway. If the database is closed cleanly then the WAL file and lock file are deleted. If not (either it's still open or not closed cleanly) then I think any half-finished transactions will be discarded if you copy all files to a new location. Certainly safest not to copy the 2 extra files in any case.

more info https://sqlite.org/wal.html

That only works if all processes accessing it share a memory namespace (aka, are not in containers/jails/VMs and on the same physical machine).

WAL works for different processes accessing the same SQLite database. It's meant to improve concurrency, in fact.

Yes, but the processes still need to share a memory namespace. In WAL mode, SQLite creates a shared memory file which gets mapped into any process trying to access it (that’s why WAL mode creates additional .shm files).

But that only works if all given processes run on the same physical machine, and not in containers/jails/VM.

I dunno what SQLite is doing specifically but you can certainly mmap a file across containers and it behaves like you’d think. SELinux and other hardening options might interfere though, because shared mmaps should imho be viewed as a very likely vector for cross-process exploitation.

There isn’t really a thing called a memory namespace, in Linux or the BSDs at least. In Linux there are cgroups that deal with accounting and you can share VM space among cloned processes (which we then typically refer to as threads). Neither of these affect the ability to share memory among processes.

The only namespace that matters here is the filesystem/mount namespace.

There is no reason you can’t access the same shared SQLite database on a common volume between containers on Linux for instance.

If you're using a system with SELinux, your containers won't be able to use the same shared memory file. Which is relatively common, as CoreOS nowadays has SELinux by default.

> If you're using a system with SELinux, your containers won't be able to use the same shared memory file.

That is false. Whether you mmap the same file is dependent on the specific SELinux policy - which is by design, highly configurable.

I am also surprised/skeptical that the default configuration for those OSes for a shared volume/mount point would disallow mmaping by default (which is all that is required) - since what is the attack vector they’re preventing? If you can exfiltrate via memory you could just do so via read/write.

You are probably thinking of issues with networked filesystems like nfs, 9p, or vboxsf where you can't mmap a file and actually share memory. Basically any other real filesystem that 2 processes actually open the same file on will allow shared memory.

Which is relatively common if you're running a service on your k8s cluster with networked storage and SELinux enabled for your containers (CoreOS now has SELinux per default).

You'll either have to reduce safety and usability, or drop SQLite.

Reply on news.ycombinator.com