Chapter 19 · Security, Deployment, Reliability Boundaries, and When Not to Use SQLite
File Permissions, Process Identity, Secrets, and Encryption Reality
Understand SQLite security at the operating-system file boundary, apply least privilege to database/directories/backups, and separate encryption/key-management choices from ordinary core SQLite behavior.
Learning outcomes
SQLite is a library, not an authorization server. It does not place a network login layer between an application and its database file. The process that opens the file runs with the operating-system identity and permissions of that process. Security therefore depends heavily on filesystem permissions, directory ownership, backup handling, process isolation, and—when required—an explicitly chosen encryption design.
Explain why SQLite has no server-side user/role/GRANT security boundary around a local database file.
Review database-file, parent-directory, WAL/journal, and backup permissions using least privilege.
Distinguish application process identity from end-user identity inside the application.
Explain that public-domain core SQLite does not transparently encrypt database files by default.
Separate whole-database encryption products/extensions, OS/device encryption, and field-level cryptography by threat model.
Keep encryption keys/secrets outside the database and outside unprotected backups/logs.
The SQLite authority boundary is the file
A client/server database can authenticate a network connection and enforce server-owned roles. Core SQLite instead opens a file using the host process’s OS privileges. If another process can read that file, it can usually copy and inspect the SQLite contents with its own SQLite library. If it can modify the file/directory, it may be able to alter or replace the database, subject to OS locking while the live application is using it.
A desktop or mobile application may implement users, permissions, or roles in its own tables and code. Those rules govern behavior through that application. They do not cryptographically prevent a separate process with filesystem access from opening the same unencrypted database directly.
Protect the directory, not only the main .db file
SQLite may create neighboring files such as rollback journals, WAL files, shared-memory files, temporary output, or new databases created through operations such as ATTACH. A deployment review therefore starts at the directory and process identity.
| Object | Why permissions matter |
|---|---|
| Database file | Contains persistent tables, indexes, schema, and application metadata. |
| Parent directory | SQLite may need to create/delete journal/WAL/temp/backup files next to the database. |
| -wal / -shm / rollback journal | Can contain live/recent database state and must not be exposed more broadly than the database. |
| Backups / VACUUM INTO copies / dumps | Often bypass application access controls and may live much longer than the primary file. |
| Logs / crash dumps | Can accidentally contain SQL parameters, keys, or application secrets. |
| Migration/import directories | An attacker who can replace migration or input files may influence what the privileged process writes. |
Process identity and least privilege
Run the application under an OS identity that has the minimum file and directory rights required. A read-only reporting process does not need write access. A service that owns only one application database should not have broad write permission across unrelated application directories.
# Observe a disposable deployment directory. Do not point this at system paths.mkdir -p fieldnotes-permission-reviewprintf '' > fieldnotes-permission-review/fieldnotes.sqlitels -ld fieldnotes-permission-reviewls -l fieldnotes-permission-review/fieldnotes.sqlite# Also record the service identity that would run the application:id# Use a disposable folder, not Program Files/System32 or other critical paths.New-Item -ItemType Directory -Force .\fieldnotes-permission-review | Out-NullNew-Item -ItemType File -Force .\fieldnotes-permission-review\fieldnotes.sqlite | Out-NullGet-Acl .\fieldnotes-permission-review | Format-ListGet-Acl .\fieldnotes-permission-review\fieldnotes.sqlite | Format-List[System.Security.Principal.WindowsIdentity]::GetCurrent().NameThe lab is observational. Production ACL design depends on whether the application is single-user, per-device, a local service, a containerized workload, or a managed desktop/mobile package.
Backups have the same confidentiality problem
Chapter 16 taught how to make consistent backups. A backup that is technically correct but world-readable is still a security failure. Apply retention, ACLs, destination isolation, transport protection, and deletion policy to physical backups, dumps, and recovery artifacts. A copied database can be queried without the live application’s UI authorization layer.
Encryption reality: no magic core PRAGMA
The public-domain core SQLite library does not transparently encrypt an ordinary database by default. SQLite.org provides the separately licensed SQLite Encryption Extension (SEE) as one optional product; third-party encrypted SQLite variants also exist. Operating systems may provide full-disk/device/filesystem encryption. These choices protect different boundaries and have different packaging, licensing, interoperability, performance, backup, and key-management implications.
| Mechanism | Protects mainly against | Important limitation/question |
|---|---|---|
| OS full-disk/device encryption | Offline theft of the device/storage when keys are unavailable. | Data is normally plaintext to an authorized running process. |
| Encrypted filesystem/container | Offline access to selected storage area. | Mount/unlock policy and backup handling become part of security. |
| Whole-database SQLite encryption extension/product | Direct reading of DB/WAL/journal without key. | Library compatibility, licensing, key lifecycle, recovery, and deployment must be designed. |
| Field-level application encryption | Exposure of selected sensitive values through some storage paths. | Application must manage keys, indexing/search limitations, formats, rotation, and authenticated encryption. |
| Password hashing | Credential verification without storing reversible password text. | Use a dedicated password-hashing design/library; this is not SQLite file encryption. |
A blog may show PRAGMA key or similar commands from a specific encrypted SQLite distribution. Those are not portable core-SQLite features. First identify the exact library/product, its documentation, license, algorithms, key-storage model, and backup/recovery procedure.
Secrets and keys are separate assets
Do not store the sole encryption key beside the encrypted database in an equally readable configuration file. Key management may use OS credential stores, platform keychains/keystores, hardware-backed keys, environment/injected secrets, or managed secret services depending on the platform. The database migration and backup runbooks must say how keys are backed up, rotated, revoked, and recovered without copying them indiscriminately into dumps or logs.
Data asset: fieldnotes.sqliteProcess identity: ______________________DB directory owner/ACL: _______________Read principals: ______________________Write principals: _____________________Backup destination + ACL: _____________Dump/export destination + ACL: ________Encryption mechanism (if required): ___Key owner/store: ______________________Key backup/recovery owner: ____________Crash/log redaction policy: ___________Off-device transfer protection: _______Restore drill includes key recovery? __Lab: deployment-permission review
Create a disposable directory and database, then record—not guess—the effective identity, permissions, writable parent directory, and backup location. Use Python to inspect the paths without changing critical system directories.
from pathlib import Pathimport os, sqlite3, stat, tempfilewith tempfile.TemporaryDirectory() as td: root = Path(td) / "fieldnotes" root.mkdir() db = root / "fieldnotes.sqlite" con = sqlite3.connect(db) con.execute("create table t(id integer primary key, value text)") con.execute("insert into t(value) values (?)", ("local-only",)) con.commit(); con.close() for p in (root, db): mode = stat.filemode(p.stat().st_mode) print(p.name, "mode=", mode, "readable=", os.access(p, os.R_OK), "writable=", os.access(p, os.W_OK)) backup = root / "fieldnotes-backup.sqlite" backup.write_bytes(db.read_bytes()) print("backup permissions:", stat.filemode(backup.stat().st_mode))# Interpret results according to your OS identity/ACL model.# os.access() is an observation aid, not a security proof.Checkpoint
What does SQLite itself authorize?
Separate database-library behavior from operating-system policy.
- Does a table of application roles prevent another OS process with read access from opening an unencrypted DB copy?
- Why should the parent directory have a permission review?
- Does core SQLite automatically encrypt ordinary .db files?
- Why is an encrypted database not sufficient if its key is stored beside it in plaintext?
- Why must backup permissions be reviewed separately?
Review the answers
Application roles govern behavior through that application, not raw file access. SQLite creates companion files and needs directory operations, so directory permissions matter. Public-domain core SQLite does not transparently encrypt ordinary files; encryption requires OS/platform mechanisms or an explicitly selected extension/product. A plaintext co-located key collapses the protection boundary. Backups are separate copies that may bypass application controls and persist in different locations.
Bridge to hostile inputs
File permissions decide who can supply or alter database files, but applications sometimes intentionally open files from outside their normal trust domain: imports, document formats, plugins, user-selected databases, forensic tools. Lesson 3 treats the database schema itself as potentially untrusted input and layers SQLite’s defensive controls accordingly.