Grdb.swift: Writes to db file in a sandboxed Mac app may fail due to journal file creation failure

[imabuddha]

What did you do?

I tried to update a record in a db file on MacOS. The DatabaseQueue was open, not in readonly mode, and querying was working properly.

What did you expect to happen?

The row associated with the record to be updated in the db file.

What happened instead?

Got a sqlite 14 error. Turns out it was unable to open the journal file due to sandboxing. The user (me) had given permission for the db file, but not the dir it was in. 🙀 For completeness, here's the info from the dbg console:

2018-12-01 20:46:34.702906+0900 Go Problems DB[2994:370019] [logging-persist] cannot open file at line 42249 of [95fbac39ba]
2018-12-01 20:46:34.702985+0900 Go Problems DB[2994:370019] [logging-persist] os_unix.c:42249: (0) open(/Users/john/Documents/work/SmartGo/problems/gop.sqlite-journal) - Undefined error: 0

Environment

*GRDB flavor= GRDB
*GRDB version: 3.5
*Installation method: CocoaPods
*Xcode version: 10.1 (10B61)
*Swift version: 4.2
*Platform(s) running GRDB: macOS
**macOS version running Xcode: 10.14 (18A389)

Demo Project

Not the whole project, but here's the fix:
Add the following before doing any writes unless of course the user has given permission for the dir in which the db file resides:

            try dbQueue.write { db in
                try! db.execute("PRAGMA journal_mode=MEMORY")
            }

I just wanted to let you know about it because if you mentioned the possibility of this happening somewhere in the docs it might help someone else in the future.

Thanks for making this awesome project; it's really helping me out! 🙇🏻

[groue]

Hello @imabuddha,

Thanks for sharing this story! MacOS sandboxing is not easy.

The documentation for PRAGMA journal_mode says:

PRAGMA journal_mode = DELETE | TRUNCATE | PERSIST | MEMORY | WAL | OFF
[...]
The MEMORY journaling mode stores the rollback journal in volatile RAM. This saves disk I/O but at the expense of database safety and integrity. If the application using SQLite crashes in the middle of a transaction when the MEMORY journaling mode is set, then the database file will very likely go corrupt.

So the PRAGMA journal_mode=MEMORY command you are suggesting is quite dangerous.

Are you sure it is the only way to be granted read/write access to a sandboxed database?

[imabuddha]

Yes, setting journal_mode=MEMORY is risky, and I wouldn't recommend it for most end-user apps. My current app is an internal tool working on a simple db whose main table isn't linked to others, and because of its usage patterns & my multiple-backup-paranoia the risk is minimal.

If one has a sandboxed Mac app that must access db files outside of the app sandbox my recommendation would be to have the user first select the dir containing the file, thus giving the app the required access rights to create the journal file.

Another possibility would be a way to set the dir where journal files would be created. I don't know sqlite well enough to know if that's doable.

Essentially I think mentioning the possibility of journal creation failure in sandboxed Mac apps somewhere in the docs would be a good idea.

[groue]

Thanks for those added details. Context is important when one suggests a technique which comes with important caveats.

For document-based applications, I wonder if wrapping databases in Document Packages could provide a better workaround. Since packages are directories, I expect the app to be able to be able to write in the database without this unsafe journal_mode.

Another possibility would be a way to set the dir where journal files would be created. I don't know sqlite well enough to know if that's doable.

Most Temporary Files Used By SQLite are in the same directory as the main database. I suggest you read this documentation.

Essentially I think mentioning the possibility of journal creation failure in sandboxed Mac apps somewhere in the docs would be a good idea.

I'm not sure we're ready yet. On the other side, opening this exploratory issue was a very good idea. Google will find, it, and we may eventually come up with a solid piece of advice.

[groue]

I close this issue, @imabuddha. It is there, archived, searchable, and available for all users who face the same SQLite error 14 (SQLITE_CANTOPEN) in their sandboxes macOS app. Feel free to update it with new information, and to reopen it if you think it is necessary! Happy GRDB!