Command deletion

[4e554c4c]

Currently none of the storage backends delete enqueued commands, even after a acknowledge/error response. Apple states that commands only need to be kept on the queue until one of these positive responses has been received. When operating at scale, these commands (especially those with large payloads) begin to take up incredible amounts of space which hinders maintainability and resource efficiency. I propose that we delete every command as soon as the last machine has acknowledged it (commands can be sent to multiple machines, so care must be taken to not delete them too eagerly).

This can be done in one of several fashions:

1. We could update StoreCommandReport in the storage drivers to check for any remaining references to a command, and then delete it if none exist
2. We could add a new DeleteCommand or CleanupCommand method to the interface

I'm not sure if I like the first option, because it adds additional side effects to StoreCommandReport when deletions should be treated specially (and opted-into). Of course the second is more work, but I'm willing to figure out what it takes to do this.

I'm opening this up to discussion to see if there are any better options and how y'all feel about cleaning up already ack'd commands.

[4e554c4c]

Another thing to discuss is what else we should delete. For example the MySQL backend stores every single command request in three places:

Table	reason	size
commands	delivering commands	one command with payload per command issued
enrollment_queue	delivering commands	one entry per device (fixed size)
command_results	reporting results	one entry per device, fixed size, plus some error info

should be be cleaning up enrollment_queue too? and should results be stored in the production database or reported elsewhere (or should this be configurable)? I am of the opinion that we should keep the production database as small as possible in order to operate efficiently. For example, we could remove the active column in enrollment_queue and use it only as a queue, and give an option for deleting non-NotNow entries in the results table.

[jessepeterson]

Yes, I agree an option to delete commands is worth supporting. But there's a few caveats I want to note:

This can be done in one of several fashions:

I'd lean very heavily towards your suggestion #1: implementing this inside of the mysql storage backend's StoreCommandReport. For a couple reasons:

I think this feature (of command deletion) should be entirely the discrection of a storage backend. Your suggestion #2 to add storage interface methods of, say, DeleteCommand or CleanupCommand I'd like to avoid so as to (attempt) to keep the storage interface as lean as possible. This in part to promote development of other storage backends (such as #5) by contributors by making it that little bit simpler for implementers. The existing file backend shouldn't have any expectation to be used in a production environment and so I don't think is worth the time to implement this deletion feature there.

Something that's been discussed in #6 is the idea of having API access to the existing command queue including command completion status, full result retrieval, and to the point in #6 — the ability to resend webhook events. That's an idea that's been around for a while with MicroMDM as well. The problem there is that the Webhook is not guaranteed for anything. Basically: it's an HTTP request fired off asynchronously. So in order to combat the issue of unreliable HTTP having retroactive access to the command queue and thus (possibly) webhook events seems beneficial. That said other ideas to solve this problem are integrating with proper message queueing system like, say, NSQ and many others. Anyway I mention this to just bolster that this would definitely want to be an option and not a default configuration.

As far as "configurability" — the mysql storage backend already has the familiar Go With... option pattern where a simple WithCommandDeletion() function could set a bool for the service to optionally perform these operations. There is the question of how this gets passed on from the CLI UX. I've been thinking perhaps adding a third option parameter to -storage and -dsn (docs), say -storage-opts which could contain, I'm not sure, colon (:) separated options to configure the storage (or, really, how the options are interpreted are up to the storage backend — similar to the DSN). But a third option has been in consideration for a little bit. Much of the heavy lifting of NanoMDM is done in the storage backends so having their own separate config mechanism makes sense. So for example -storage mysql -dsn nanomdm-database -storage-opts delete-cmds or something like that. I imagine we'd adapt that flag to the actual storage init options here:

nanomdm/cmd/cli/cli.go

Lines 61 to 64 in 56c9b7e

	case "mysql":
	mysqlStorage, err := mysql.New(
	mysql.WithDSN(dsn),
	mysql.WithLogger(logger.With("storage", "mysql")),

---

As far as an actual implementation goes in the mysql storage backend I have a few thoughts as well. Essentially I imaging it working something like this:

Assuming the above mentioned boolean is turned on for the storage, then every time we're called to store command results for an Acknowledge or Error:

1. Skip storing the Acknowledge or Error result (i.e. creating a command_results table row).
2. DELETE FROM command_results this device's command UUID (i.e. for any previous NotNow results)
3. DELETE FROM enrollment_queue this device's command UUID
4. Count the enrollment_queue for this command's UUID (any device). If it's 0 (meaning there are no other outstanding command's in any device's queue) then we can go ahead and delete the command. I think this avoids race conditions — except perhaps in the case where the DELETES from other devices happen before our final count — but their instance of this step should tidy that up. I think. ;P We could wrap all this up in a transaction if we wanted (we do this for command queuing for what it's worth). We don't currently support a way to add additional device "queue-ings" to an existing command.

Just a note that in NanoMDM (this open source project) we have full referential integrity in the SQL schema so deleting a command from the table will delete all of it's enqueued instances (enrollment_queue table) and command results (command_results table) automagically. That may have implications for the above ordering.

One other note is that I recently added a mysql storage command queue integration test in cbda10f. It is default-off (gated away by a Go build tag of integration). It uses the same queue test code that the file backend has been using for some time. Just an FYI.

---

I hope that helps. Thanks!

[4e554c4c]

Skip storing the Acknowledge or Error result (i.e. creating a enrollment_queue table row).

err, do you mean command_results row?

other than that this is exactly what I was thinking

[jessepeterson]

Yes, apologies I did mean that. :)

[jessepeterson]

FYI Implemented in PR #48.