MacOS app with user-selected file read-write entitlement rejected from Mac App Store because SQLite creates a temp file when writing to database

Question

I have a MacOS app that I want to put on the Mac App Store so it must be sandboxed. With the app the user creates multiple SQLite database files storing information they create, somewhat analogous to Excel Spreadsheet files but I am leveraging SQLite in my app to create, read, and edit the files. They store these files in the Documents folder on their mac. I added an entitlement for this: com.apple.security.files.user-selected.read-write. The app can read these database files fine, but if I try to write to them it fails.

Per the Mac Console app's log the error is:

Sandbox: MyApp Helpe(16378) deny(1) file-write-create /Users/steve/Documents/myFile1.sqlite-journal

Violation: deny(1) file-write-create /Users/steve/Documents/myFile1.sqlite-journal

So it is failing because SQLite, behind the scenes, creates a temporary file with the same name as the one accessed but changes the extension by appending "-journal" to it. So if the user opens a file named myFile1.sqlite it opens and reads fine, but if they try to write to it, SQLite will create a temporary file named myFile1.sqlite-journal as part of the process, then deletes it. But because the user did not open or save a file named myFile1.sqlite-journal, it is not in the sandbox and is denied.

I confirmed this is the problem by creating an empty file in Finder named myFile1.sqlite-journal and opened it from my app (thus adding it as a user selected file to the sandbox), and was then able to write to myFile1.sqlite.

This is a known issue and there seems to be a solution per the docs by using "Related Items": https://developer.apple.com/library/archive/documentation/Security/Conceptual/AppSandboxDesignGuide/AppSandboxInDepth/AppSandboxInDepth.html.

Below is the relevant text, but this is my first mac app and these instructions are clear as mud. Been fooling around with them for days and still have no idea what I'm supposed to do. The below mentions extensions but sqlite does not have a set extension. You just use whatever extension you want (let's say .sqlite). Can someone explain what properties I need to add to the info.plist.

RELATED ITEMS:

The related items feature of App Sandbox lets your app access files that have the same name as a user-chosen file, but a different extension. This feature consists of two parts: a list of related extensions in the application’s Info.plist file and code to tell the sandbox what you’re doing.

There are two common scenarios where this makes sense:

Scenario 1: (Not Relevant to my issue)

Scenario 2: Your app needs to be able to open or save multiple related files with the same name and different extensions (for example, to automatically open a subtitle file with the same name as a movie file, or to allow for a SQLite journal file).

To gain access to that secondary file, create a class that conforms to the NSFilePresenter protocol. This object should provide the main file’s URL as its primaryPresentedItemURL property, and should provide the secondary file’s URL as its presentedItemURL property.

After the user opens the main file, your file presenter object should call the addFilePresenter: class method on the NSFileCoordinator class to register itself.

Note: In the case of a SQLite journal file, beginning in 10.8.2, journal files, write-ahead logging files, and shared memory files are automatically added to the related items list if you open a SQLite database, so this step is unnecessary.

In both scenarios, you must make a small change to the application’s Info.plist file. Your app should already declare a Document Types (CFBundleDocumentTypes) array that declares the file types your app can open.

For each file type dictionary in that array, if that file type should be treated as a potentially related type for open and save purposes, add the key NSIsRelatedItemType with a boolean value of YES.

Answer 1

Use a Document Package

Your app can register a document file type that is presented as a regular file in Finder, but actually a directory. When the user selects the new location to save the file, the sandbox gives your app permission to write multiple files (including sqlite's temporary files) to the location selected by the user (and any sub-directories you want to create starting with that URL).

So if the user selects ~/Desktop/Untitled.appDocExtension to save their file, your app can write now write the following files: ~/Desktop/Untitled.appDocExtension/myFile1.sqlite and also ~/Desktop/Untitled.appDocExtension/myFile1.sqlite-journal.

GarageBand (.band) files would be an example of this type of document. You can tell a Finder file icon represents a document package if you right-click a document in Finder and you can select "Show Package Contents". (Basically like an App Bundle, of course.)

How to set this up:

1. Add the user-selected-file read-write entitlement to your app: com.apple.security.files.user-selected.read-write
2. Add a new Document Type to your app's Info.plist, including a unique filename extension and also make sure to add the LSTypeIsPackage key:

<key>CFBundleDocumentTypes</key>
    <array>
        <dict>
            <key>CFBundleTypeExtensions</key>
            <array>
                <string>appDocExtension</string>
            </array>
            <key>CFBundleTypeName</key>
            <string>MyDocTypeName</string>
            <key>CFBundleTypeRole</key>
            <string>Editor</string>
            <key>LSHandlerRank</key>
            <string>Default</string>
            <key>LSItemContentTypes</key>
            <array>
                <string>com.mydomain.typeidentifier</string>
            </array>
            <key>LSTypeIsPackage</key>
            <true/>
        </dict>
    </array>

3. Add an Exported Type Declaration to your app's Info.plist (credit here).

<key>UTExportedTypeDeclarations</key>
    <array>
        <dict>
            <key>UTTypeConformsTo</key>
            <array>
                <string>com.apple.package</string>
                <string>public.composite-content</string>
            </array>
            <key>UTTypeDescription</key>
            <string>My Doc type description</string>
            <key>UTTypeIcons</key>
            <dict/>
            <key>UTTypeIdentifier</key>
            <string>com.mydomain.typeidentifier</string>
            <key>UTTypeTagSpecification</key>
            <dict>
                <key>public.filename-extension</key>
                <array>
                    <string>appDocExtension</string>
                </array>
            </dict>
        </dict>
    </array>

To save the document at run-time:

Present an NSSavePanel to the user:

        let savePanel = NSSavePanel()
        savePanel.canCreateDirectories = true
        savePanel.allowedFileTypes = ["appDocExtension"]
        savePanel.begin { response in
            guard response == .OK else {return}
            guard let url = savePanel.url else {return}
            DispatchQueue.main.async {[weak self] in
                // this directory will look like a single file in Finder
                try! FileManager.default.createDirectory(at: url, withIntermediateDirectories: false)
                // create a location under this directory to save your sqlite db:
                let dbURL = url.appendingPathComponent("myFile1.sqlite")
                // ... and now you can open and write to the db at dbURL
            }
        }

If the user wants to eventually get direct access to the raw sqlite file, they can:

• Use the "Show Package Contents" command in Finder.
• or Navigate to the file in Terminal.
• or use an Export command that you provide in your app to copy the .sqlite file to a new location.

You can store whatever you want in these packages, so if you later want to add metadata or sidecar files, like images or other media, you can package them all together.