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

What I'm saying is I think that Apple have put in an update in place such that new installations will probably be OK.

But the only thing that is of interest here that makes an installation new enough is the copying of

/System/Library/Security/Certificates.bundle/Contents/Resources/valid.sqlite3 to /Library/Keychains/crls/valid.sqlite3 as part of the installation.

If you imagine the support narrative. Apple could say when questioned about this problem.

Yes there was was an issue in the last few weeks but re-installing a fresh copy or installing over the top (presuming this recopies the file too) will fix things. There would be no support narrative from people just finding there was no problem installing from now onward.

They'd be telling people to reinstall the whole OS just to copy one file, but that would be a very clear instruction. It would waste people's time as the security patches would be needed to be installed again too along with iTunes updates and stuff but it's still a simple story of, something was broken and now it's fixed try again.

In the meanwhile they can think of what to do next.

I'm going to test now by making that copy from /System/... to /Library/... and see what happens now v146 is out.

My expectation is that it will change the version number only.

I can set the update interval to something low I think so I don't have to wait hours to see that happen or not.

I could be wrong on all this off course but sending an empty update looks suspiciously like the first step in a plan towards fixing at least one category of user, those who install a fresh copy of High Sierra today or later on. Like I say though I think they'll bundle those that did an install before v146 into the same bucket and tell them just to reinstall because it's easier for them to do it that way.

oh, and btw, using your method (Johnno_uk), it looks like "Apple Mac OS Application Signing" cetificate is yet again marked as Revoked, because the issuer Flags is still set to 0.

And this is when there is a version 42 in /System/Library/Security/Certificates.bundle/Contents/Resources/valid.sqlite3

I think my way of pretending to be a fresh install might be wrong but can produce the bug nonetheless.

The file update-current turned out to be the same as the online update of v42 so it was almost as if it was applying v42 over v42

update caught in the act:

-rw-r--r--@ 1 johnno staff 5210256 Dec 1 23:56 update-current

-rw-r--r--@ 1 johnno staff 17645568 Dec 1 23:57 valid.sqlite3

-rw-r--r--@ 1 johnno staff 12824 Dec 1 23:56 valid.sqlite3-journal

$ diff update-current ../v42

[NO DIFF]

So it still looks like just going with a donor database is the best idea.

On my main machine I've personally kept with a script removing the 2 known certs from the db and then validates them just to ensure any other caches are refreshed. I have that setup as a LaunchDaemon so it happens when the machine boots and in root's crontab too. So all just appears normal. That's not the sort of thing any user should do if the donor database works just as well since that way their system is truly normal and funky scripts won't cause issues in the future.

I did look more into what was in v42 and did not see anything to do with issuer_hash CE057691D730F89CA25E916F7335F4C8A15713DCD273A658C024023F8EB809C2 so I'm sure there was nothing wrong with the data Apple were publishing in any way and it is all just a lurking bug in High Sierra. If a donor database catches a machine up then that's the best thing to do. If the bug triggers again but this time for all installations it will trigger for more people and something might be done about it.

Other things to try for fun are things like:

sudo sqlite3 /Library/Keychains/crls/valid.sqlite3 "update admin set ival=0 where key = 'db_version';"

This forces trustd to think the database version is too old and try to rebuild. That made apps run but

select flags from groups where groupid IN (select groupid from issuers where hex(issuer_hash) = 'CE057691D730F89CA25E916F7335F4C8A15713DCD273A658C024023F8EB809C2') still returned 0 which doesn't look right when it should be 16.

I think Apple may be trying to fix things and perhaps v146 was a failed attempt. This will all be OK in the end I'm sure.

FrancoisQC wrote:

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

The database from /Library/Keychains/crls/? (not the one from inside the bundle). Well, softmusic posted the one I used on my client's computer about a week ago https://discussions.apple.com/thread/252037712?answerId=253981944022#253981944022

The link to the file is http://www.mediafire.com/file/m2ky3mrnon6jy49/valid.sqlite3.zip/file

This is the one from my computer which is from a pre-existing High Sierra install and has been working fine. https://www.dropbox.com/s/56nzoo5xpo9tcrw/valid.sqlite3.zip?dl=0

OptimusGREEN wrote:

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

Correct, there should be no personal information in these databases. Such databases should contain only data downloaded from Apple. Apple provide the data in a wire format that is smaller and includes the ability to send incremental updates. All Macs since High Sierra have been downloading this data and updating their local databases with it. It looks though like the code in High Sierra has recently had a problem updating all the way from the base copy of the local database which does come as part of the High Sierra installer all the way up to the current update. On the other hand Macs which have been running High Sierra for a while and been regularly connected to the Internet appear to have managed to keep up with the updates and not corrupt the information in their local databases. Note that local copies from machine to machine may have different integer keys and format varies from OS version to OS version so it's not as if every Mac that's working well should have the exact same bytes in the db file but the semantic meaning of all the data should be the same unless perhaps in some enterprise installations using a different source than Apple for the updates.

The other question to ask is the opposite and that is, could a donor database from somebody I don't trust cause issues? The answer here is obviously yes. For a start they could give you a broken database just like the one you already have. The main risk though would be that they exclude some data from it that could have been used if it had remained present to help malicious applications from running, the visiting of rouge websites to be blocked etc. This untrusted provider could also perhaps make it so that the OS was blocked from getting other critical information online. So the safest place to get a donor database from is another Mac under your control that is running High Sierra, or from a backup of one that was recently. If those sources are not an option then work your way out to sources you have less knowledge or trust for. If that sounds too risky then the only option is to wait for Apple to provide what is needed or not to run a new install of High Sierra. A database has been posted to this forum and so anybody has the choice of using it or not. It will have been provided with no warranty of course. My gut feeling is that using it will be totally fine but trust is a complex thing and taking human trust and converting that into records of trust in computer systems without being cognisant of the risks involved would be foolhardy.

johnno_uk wrote:

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.

I agree with you. I decided to share this working database because personal information it may contains is not mine !

It comes from a computer that a friend gave to me without cleaning it (which was a good reason to do a fresh High Sierra install when replacing the HDD by an SSD).

Now I have to use this old database because it is the most stable fix, but I would very much prefer a clean fix from Apple. Risk is also limited, because this computer is mainly used with parental controls limiting the ability to install apps.

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. 

This database didn't exist until High Sierra I think so you'll likely not find it in backups of the system.

I think it was added as part of the improvements described in this article - https://www.thesslstore.com/blog/crypto-ssl-improvements-high-sierra-ios-11/

The source code we've seen has a copyright marker starting 2016 and the last Yosemite release was in 2015 with only security patches after that into 2017.

Thats an interesting thing to consider though that perhaps installing security patches reset this database with the expectation it will be updated online pretty soon after. You could try putting the donor database back again and see if it sticks for longer than it did the first time.

I'd be reluctant to offer solutions that block updates or run regularly to either clear keys or keep putting a donor database in place automatically as I don't really think it's a good idea to modify a machine in that way, especially somebody else's machine. It's what I did to my own machine but as soon as software is installed to fight against the OS it's treading a fine line on being malware. As a get out of jail free card though the App I've been using to get serials easily hasn't failed so far to make a non running app run again just by loading it and dragging the app onto it. https://brockerhoff.net/RB/AppCheckerLite/

That could be a a backup solution if the donor database doesn't stick and you could show the client what to do. Installing an app is also a modification of a machine to some extent but it wouldn't be installing something to fight with the OS and would only be used under the user's control and consent.

It works because this app shows both code signing certificates and App Store receipts and whether they are valid or not plus crucially the way it does that is to call APIs that make trustd make an OCSP request to Apple (this is a online check for just one certificate vs getting a set of bundled updates proactively) which will find the certificates to be good. That good state will hang about for up to 3 hours and so apps can be launched again.

Interesting... what I found, both with an installer I had from right after the date issue sprang up AND a fresh, downloaded from the app store one, was that the resulting 10.13.6 build number does not come close to following the "conventional" build numbering AND might very well be some internal, unreleased one... while there are a ton of nVidia "web" drivers for their cards (up to HS where apple cut them off at the knees) there is NONE for this particular build. I am pretty sure that if we HAD seen this build, nVidia would have tweaked their drivers for it.

The other thing is that we have 2 separate security updates that EACH have to be separately applied. Means they NEVER actually created what we used to call a combo installer, one that REALLY was up to date.

For the record, 005 was 09/24/2020, 006 11/11/2020. Not sure that we have collectively been able to pinpoint which one of those caused the issue.

Don't thank me, the problem is not resolved...

I was running under the impression that this valid.sqlite3, along with the name of its location (clr) was a cache that trustd was building / learning along the way. But its not.

Is it a trust store? just like Windows Certificate Store, where Apple could push an updated trust store file? Well, from Johnno_uk's answer, he does not believe Apple could push a "whole new file". In that case, its not a trust store either.

I understand that if this is a trust store, then replacing it altogether could break enterprise deployments, as enterprise could push their own CAs to their employee's Macs. And if Apple was to blindly replace that file, well, you break those enterprise deployments.

Seriously, I am at loss. Many people here have provided valuable info, I think we nailed down the problem (corrupted / invalid / incorrect / different valid.sqlite3) but we have no way to fit it permanently as Apple is pushing SQL updates to it, rather than sending a whole new corrected file, if it actually can as per their current trustd design.

I'm now speachless... going silent....

There will be more than meets the eye to all this. I now suspect that on 12th Nov there will be a pair of updates which revoke Apple's certificates by accident and quickly un revoke them.

Whilst the problems in this article mention an OSCP server meltdown ...

https://tidbits.com/2020/11/13/apple-network-failure-destroys-an-afternoon-of-worldwide-mac-productivity/

... Nobody appears to be asking why all those Macs were making OCSP requests in the first place. Really Apple just said, 'Yeah we do that', people said 'What about privacy', Apple said 'We'll use encryption more' and everyone said 'OK then'.

The article does have this in it though ...

"What prevented ocsp.apple.com from responding? I doubt Apple will ever share detail"

I think at least for an app launch these OSCP requests are only supposed to happen as a fallback incase the local database has an invalid revocation in it. High Sierra doesn't do this fallback (but using an app like WhatsYourSign effectively makes that happen) but I think later versions of the OS do this automatically. I don't think it's normal for every app launch (or even a launch and then cache of the result for a bit) in a scenario where certs are thought to be good locally to even make an OCSP request at all. So why were all those Macs making so many OSP requests. It would be interesting to get ISP data to see if there was a traffic spike.

So if Apple had sent a bad update revoking their own certs by mistake then that could cause a lot of Macs to DDOS their OSCPs servers as lots of Mac users launch Apps. So the root cause of the incident in the article would be the extra traffic rather than the servers being bad or under provisioned for expected load or even additional load due to lots of new OS installs of Big Sur.

If that proves to be the case, then you might ask why could they not just make those updates into do nothing updates retrospectively. This would be due to hashing to check updates follow on correctly so changing what is supposed to be immutable data that has already been processed by some systems might cause more issues.

Perhaps people running High Sierra on Nov 12th didn't have slow app launches but did see the problem new installs see (the problem in this thread) for a few hours. An issue like that would be lost in the noise of apps not running or running slow.

I'm going to work back from v145 looking for the issuer hash for WWDRCa to see if the serial for 'Mac OS Application Signing' is in there. This is partly out of stubbornness as if the 2 incidents are related then I'll look like less of an idiot on the other forum.

So to update my theory. Rather than saying that an update was mis-interpreted and caused the Apple certs to be revoked locally by mistake when going up from v42 -> vX -> vY -> v146 all in one go due to being a fresh install, I'm saying that a vX did actually ask to revoke them but perhaps a vY asking to undo that is getting ignored. Obviously if I can't find vX and vY that match my presumptions then I'm just guessing.

Note: If you're interested at 17:45 in this video here https://developer.apple.com/videos/play/wwdc2017/701/ the update mechanism that is involved with this issue is explained at a very high level. Then have a think about what is in the CT Logs and ponder on whether Apple might have been overly trusting them allowing another CA other than themselves to revoke Apple certificates.

Note that I do appreciate that many users are not interested in a root cause analysis and just want their Macs to work.

My strategy here is that if I do actually find anything definitive, then perhaps it can start a dialogue with Apple engineering to see if a patch will be issued or not. Even if the there is no response, which is most likely, it will still be possible to get the information to the right people.

Given that some Macs can't run Mojave and people reinstall their OS for many reasons, locking the users of such macs out of the App Store won't be an experience Apple will want to be in place even if these users are using very old Macs.

Oh boy.

19:25: "if the client hits a certificate that is in the list" [.. in valid.sqlite3 ..] " the client will perform OCSP to confirm it is really revoked".

I'm sorry Apple, but in all the tests I have done so far, I did not see any OCSP request leaving HS regarding Apple Mac OS Application Signing.

Having v146 in all the files may be a red herring or the key to a fix.

Every single update file currently being served as http://valid.apple.com/g3/vX reports inside a version of 146 rather than X.

It could be Apple trying to fast-foward everything to a known starting point and they'll send further updates in due course.

We really can't tell if:

a) This is normal

b) Its not normal and an accident

c) Its not normal but is part of a fix attempt by Apple.

What I think the effect will be is that no matter where you start (today) that your database will leap frog to a version 146 (There are many version 146s as a result). For fresh installs you'll miss those critical deletes I've been talking about and shown in the picture of the plist editor with lots of red text on it.

This does however lead us to a different fix for fresh HighSierra installs without involving a donor DB and just running something once which could be this:

1) sudo sqlite3 /Library/Keychains/crls/valid.sqlite3 'update admin set ival=125 where key = "version" '

2) Reboot

3) Be patient, wait a while (perhaps up to 3 hours)

4) Try to run an app store app and if it does not go back to step 3

5) Wait even longer to see if the fix holds (perhaps leave a machine running over night)

6) See if running an App store app still works.

The machine should be connected to the Internet at all times.

There are tricks to increase the update interval which I've posted before but to keep things simple I've not included them here. I've just done this and am at step 5 now and will report back if it worked.

Running this query below returns no results which is encouraging:

/usr/bin/sqlite3 /Library/Keychains/crls/valid.sqlite3 'SELECT groupid FROM serials where hex(serial) IN ("04CA81F77D5E33F7", "0EEB5787E79E098D")'

guillermo316 wrote:

Hello, i have the same problem, when I install a fresh High Sierra OS in a SSD. But I have an HDD with High Sierra upgraded from Sierra and I don't have this problem. Sadly, the command only works few hours

That puts you in a very good position as you have a good database on the machine with the HDD. Just copy the file /Library/Keychains/crls/valid.sqlite3 from the old install (HDD) to your new one (SSD) and reboot the new install and you should be good to go.

Yes, everything is v146 after an update. So your v146 in the last test is v43 in disguise.

Apple are forcing a leapfrog to a (not the) v146 for some reason. No amount of different install practices will change that right now.

It does look like v43 is the buggy update and always was even back in the day. Now though everybody with a new in stalls gets stuck with that badged as v146. That's basically it.

The mitigations start at v46 and end at v126 but are just skipped right now.