SQLite File Placement: Permissions and Avoiding /tmp

SQLite is a file. Every ops failure that looks mysterious is usually a path failure: the process opened a different file than you think, a sidecar landed on a different filesystem, or the directory vanished because it lived in /tmp. Placement and permissions are not housekeeping. They are the availability story.

A VPS reboot, a systemd PrivateTmp setting, a container recreate, or a deploy rsync that deletes unknown files will all destroy a database that sits in the wrong directory. The fix is boring and specific.

Where the file should live

Pick a directory whose only job is application state, outside the deploy tree.

• /var/lib/myapp/data/app.db for a system service
• /home/myapp/data/app.db if you run the app under a dedicated user without writing to /var/lib
• A dedicated disk mount if you separate OS and data, still under a stable path, never under /mnt/tmp or a USB-style automount

The parent directory should be owned by the service user, mode 750. The database file should be mode 640: owner read/write, group read, no world access. The -wal and -shm sidecars inherit the directory and umask; they must remain on the same filesystem as app.db. SQLite uses them for locking and recovery. If they land on another device because you symlinked only the main file, you will get errors that look like corruption.

Never put the database in:

• /tmp or /var/tmp — tmpwatch, systemd tmpfiles, and PrivateTmp will delete or hide it
• the git working copy — the next deploy will overwrite or skip it, and you will commit a copy by accident
• a world-writable shared folder — every other unit on the host can lock or truncate it
• NFS or other remote filesystems — SQLite locking is local; remote locks are how people lose data

Permissions, user, and systemd

Create the user, the directory, and the unit so the path cannot drift.

sudo useradd --system --home /var/lib/myapp --shell /usr/sbin/nologin myapp
sudo mkdir -p /var/lib/myapp/data
sudo chown -R myapp:myapp /var/lib/myapp
sudo chmod 750 /var/lib/myapp /var/lib/myapp/data

Point the service at that path with an environment variable, not a relative filename. Relative paths resolve against WorkingDirectory, which is easy to get wrong after a packaging change.

[Service]
User=myapp
Group=myapp
WorkingDirectory=/var/lib/myapp
Environment=APP_DB=/var/lib/myapp/data/app.db
ExecStart=/usr/local/bin/myapp
UMask=0077
ReadWritePaths=/var/lib/myapp/data

UMask 0077 makes new files mode 600. ReadWritePaths (when used with ProtectSystem=strict) stops the process writing anywhere else, which is how stray databases in /tmp get created: a fallback path in code, not a planned location.

After first start, verify the three files and the owner:

sudo -u myapp sqlite3 /var/lib/myapp/data/app.db "PRAGMA journal_mode = WAL; CREATE TABLE IF NOT EXISTS ping(id INTEGER);"
ls -l /var/lib/myapp/data

Owner myapp, no other-read on the db if your umask is tight, -wal and -shm beside the main file. If you see a file in /proc/PID/cwd named app.db, the process ignored APP_DB and opened a relative path. Fix the code, do not chmod the accident.

Why /tmp keeps happening

Developers use tempfile.gettempdir() or /tmp/app.db because it always exists and needs no sudo. On a laptop that is harmless. On a VPS, /tmp is a shared scratch space, often a tmpfs, often cleaned at boot. You lose the database on reboot and you share the directory with every other process. PrivateTmp=true in systemd gives the service a private /tmp, which is even worse if you thought you were persisting data there: the files vanish when the unit stops.

The same class of bug is a Docker volume that mounts the app directory but not the data directory, or a bind mount of app.db as a file when SQLite needs to create app.db-wal next to it. Bind-mount the directory, not the single file.

High-Speed SSD on a Netbay VPS in Lucknow DC01 is local disk. Use that. Do not hide the database on a RAM disk for speed; WAL already batches writes, and a RAM disk is /tmp with extra steps.

Put the database in a dedicated directory, lock down the owner, and treat any file in /tmp as already gone. You can spin up an Ubuntu 24.04 instance on Netbay in under 60 seconds and follow along — netbayhosts.in.