App Store apps do not install properly on a fresh install of High Sierra on internal SSD or HDD

Here is how to read those updates from valid.apple.com as a set of plists.

I used a tool https://github.com/mypalmike/csplitb to help with the splitting as my awk skills are terrible.

# 62706C697374 is just ‘bplist’ as a load of hex characters

csplitb --prefix record- --suffix .plist --number 2 62706C697374 v145

ls record-*.plist

# These files will be invalid because the size of all but the last will be 4 bytes too long.

# Whilst the last will be way too long but since there was be no marker after it csplit didn't

# know where to stop. You need to read the penultimate file to pull out what the correct size

# of the last file should be.

hexdump record-05.plist # If the last file was record-07.plist then do record-06.plist instead etc

# So the last 4 bytes were 0x00066a67 but they will be different each time

printf “%ld\n" 0x00066a67 to see in decimal

# e.g in this case it was 420455 and you need to remember that for later

# take 4 bytes off the end of every file

for i in record-*.plist; do truncate -s-4 $i; done

# then redo the last one to be the correct size

truncate -s 420455 record-06.plist # If the last file was record-07.plist then do that etc

well, even with that clean DB, there are still some things broken. People in this discussion talked about Pages, so I thought I'd give a shot at installing it.

Attempt 1: App stores tells me "you can't install that version, but an older version of Pages for your OS is available. Would you like to install it"? ... of course! ... installed then failed.

Attempt 2: Please use the Purchased tab

Attempt 3 using the Purchased tab: look like I had installed it in 2016. Ok, I'll try this one... Failed again. Let's Hide this purchase.

Attempt 4: after hiding the previous purchase, trying via the App Store, now it tells me that Pages can't be installed as it requires macOS 10.15. No option, or no prompt to download an earlier version...

:(

Ah, I shoulda realized it was a command line. Yes, it says I have 146. Yes the db is 17.7. So I should rebuild it? I recall at one point, all I did was rename that file and it got rebuilt with a new one pretty quickly. Tried that, got a new valid.sqlite3... but zero bytes. Reloaded the Finder and it's back to 17.7MB. Mmmmm, I guess boot into recovery and delete? Got the command line handy (so I don't have to pour backward in this thread)?

v146 is a very small incremental update there is very little in it.

If you look at it, it appears to be changing nothing, e.g it's almost creating fake changes of X->X, Y->Y etc in order to force some code to do something ... Looks like an attempt to fix things in that respect, though that could just be wishful thinking.

(check-agin is just 3 hours expressed as seconds if anybody is wondering).

Hey folks,

I have an old db from a timemachine backup if its any use for debugging?

Looks like its 142 and 8.1mb in size

db_version|5|
db_format|3|
db_source|0|valid.apple.com
version|142|

I'm currently doing a fresh install on a spare hdd to see if theres any change on apples end.



So are you saying I should try to blow up my iMac (again, for the 17th time) and try to re-install the OS from scratch (from USB, image downloaded from Apple Store late October, early November), and see what happens?

johnno_uk wrote:

So I just saw what softmusic@ did. I started with v42 and ended up with a v146 that was large and broken.

Catching the update in the act looked like it did for others:

• I guess I'll have to go back and see "what @softmusic did".

• Don't know if you missed it, but a while back I deleted the database, rebooted, and watch the Finder window of /Library/Keychains/crls/ while the database was rebuilt. The temporary Journal file appeared and then vanished a number of times with various sizes. I poked around in the system log to see if it was pulling pieces-parts from various different sources but I couldn't find it. It looks like you did a verbose reboot? Was that the only entry showing the temporary journal file? It happened too quickly for me to attempt to duplicate or copy them to peek inside later.

This will all be OK in the end I'm sure.

yeah, until then, we're still waiting. It is not a critical issue for me, but it does annoy me from a technical point of view.

And I still don't know if this issue is what is causing Safari to prevent me from establishing a TLS connection to idmsa.apple.com, hence to authenticate to this forum using that same Mac.

So where do we get this donor DB (I like your term btw!) ?

OK my first answer was bothering me. Looking at 2 other Macs running Catalina in this case, I counted the number of rows in tables and they were different but hardly by much.

With that in mind to be really cautious with what I'm saying, is that I did not rule out that issuer hashes of CAs and the serials of certs issued by them can't end up in this database for reasons other than updates from Apple directly.

For example when a user visits a certain site or runs a particular app, that might update this database also.

In that sense the data would be considered personal, just as much as the same type of data stored server side when such checks were made is personal and has caused Apple to say they'll take extra protections not to tie such data to users, IP addresses etc.

My observations of forcing an OCSP check for a cert through trustd have been that this database doesn't update but some cached info does (because apps start to run again) so that could just be because I'm not seeing a cache flush that should happen later but doesn't. I can't say if a theoretical cache flush on somebody else's machine would or wouldn't happen.

So a better answer would be:

Q: Im assuming there is no personal info stored in the db?

A: I don't think so but I haven't proven this definitively. If any personal data was in there it might be evaluations of use of certificates that are not normally in the updates from Apple. If somebody had a big database of issuer hashes and serials they could then perhaps match up things like which unusual (not in Apple updates) apps that were launched or sites visited perhaps. Only Apple themselves can answer this question definitively. Really they should be publishing detailed information of how this mechanism works for full transparency and increased user trust.

I still think the risk is low for anybody discovering personal information of the person donating a database since effort would be needed even if some of the information is personal it would be need to be matched up to other data to be meaningful.

I don't know if all certificate authorities publish lists of the serial numbers they've issued or not but they could probably be queried individually (my db's have only around 2K of serials in there), so gathering all the supplemental data might be hard too besides the tasks of matching it up with the donor database in the first place. Unless a donor is worried that they may have run a particular app or visited a particular site and that the recipient would have the means and knowhow, and motivation (blackmail?) to go to all the effort involved in finding something worthwhile to hold influence over the donor, then there is little to worry about.

To be super cautious, a person should never give other people data from their own computer that they don't fully understand first and never take data from anybody they don't trust or for which they don't understand the data content either. This statement is not indented as a criticism of any actions taken thus far in the thread as I believe sufficient understanding was in place for those sharing files or indeed downloading and using them.

Once again, making too definitive a statement is a common misstep so sorry about that. The answer is usually in grey area unfortunately and can often sound scary. This is why we all delegate this stuff to Apple and they're supposed to be sorting this out.

However we all take risks in life, and so hopefully this info will give people enough information to calculate what path they'd be comfortable with.

Riverside_Guy wrote:

AND after my rather discouraging conversation with the mothership I am pretty convinced they have no actual interest in really fixing this properly. OR it may be they have a very coordinated coverup going on.

I would tend toward the former opinion rather than the latter. For the same reason that I decided to trust the donor DB provided by softmusic—that those of us affected are a fairly small target population, probably not worth the effort on the part of anyone with nefarious intentions.

However, as johnno pointed out, there are gray areas. In my previous life as IT at a newspaper with about 100 users, no way would I use a publicly provided database. Responsibilities are different in a case like that.

My client who received the donor database valid.sqlite3 in /Library/Keychains/crls/ reports that maybe after the latest security update, or maybe just because of the passage of time, her Pages is no longer launching. 

This makes sense if the keychain copy of the database periodically gets updated from the one in certificates.bundle.

This was a new High Sierra install which was an upgrade from Yosemite. Will Yosemite versions of those two time machine files be compatible with High Sierra? If so, I can transplant her older ones from Time Machine into her current system. 

If that's a doubtful solution, I can use my stable HS as a donor for the two files.

It would be nice if Apple could just push a complete copy of a "clean" DB rather than pushing upgrade scripts to it...

Nice for some but nasty for others. Whilst they could push out a start from scratch type full update that we'd have to presume wouldn't trigger the same bug after testing for that, such an update would be picked up by every Mac running High Sierra or later plus every iPhone running iOS 11 or later and perhaps some Apple TVs too though not watches. Whilst a few MB is relatively small as a file on a local computer, some people might in certain places in the world be charged quite a lot by their mobile providers for such a download.

It may also be impractical to spot calls coming in from High Sierra and only handle those to avoid the problem above. The servers involved are not special servers for this purpose running a special protocol but are from a client perspective just web servers. What is special about them is where they are which is dotted around the edges of the Internet in places like ISP server racks and certainly telco racks. They hold copies of stuff that lots of people will want, and therefore by being closer to people things can be delivered faster and not incur transit costs for getting the same data over the Internet over and over. The servers belong to Akamai not Apple. Apple pay to use this CDN (Content delivery network).

So unless Apple can exploit something about High Sierra that might just be seen as an error by other OSes then fixing it via the normal update mechanism will be a no go.

A security patch with any code bugs fixes which also brings that database up to a current level may be the best thing to do.

They can also do post installer patches or even put up a new build out in an installer but that would cause other issues if the build number changed and High Sierra is so old I doubt they'd do that.

It's doubtful that Apple would have a backdoor to just do anything on any Mac in much that same way they don't want such a thing for iPhones as it means governments will lean on them to get data from phones/macs. So when an update mechanism breaks down there may not be a way to fix it in the background without users downloading and installing patches, manually or automatically but certainly knowingly.

This will explain why this particular update mechanism has been improved in later OS version.

At least for me, your automator script run every 3+ days seems to do the trick. I thought of having it in startup items, BUT am I correct in thinking there is no way to avoid the dialog that wants my system password? I even tried Script Editor to see if I could, say, add something but don't even see the sudo command line in among a load of off characters (must assume it was compiled, this uneditable)!

So can I have a script that runs the edit portion AND supplies a specific password bundled in an automator action?

BUT am I correct in thinking there is no way to avoid the dialog that wants my system password?

Correct. valid.sqlite3 is owned by root:wheel, and normal users don't have write access. The only way to modify the file is by using sudo and prompt for the user password. Unless Johnno_uk, or Google, knows of a way to automate running a task as root with elevated privileges. Probably doable, I just haven't looked into it.

tip: open the file with Automator, rather than double-clicking on it. You will see the AppleScript:

do shell script "sudo sqlite3 /Library/Keychains/crls/valid.sqlite3 'delete from serials where hex(serial) = \"04CA81F77D5E33F7\"'" with administrator privileges

You can easily create your own Automator script the same way, or get inspired by this.

Be creative, have fun!