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

I'm not sure if this would be helpful or not, but before deleting the .sqlite3 file I tried downloading Tweetbot 3 for Twitter from the Mac App Store, the app was just updated yesterday so it is not an old app or an old version of the app. When I launched the app it crashed immediately then I deleted .sqlite3 and the app worked, then I restarted the computer and tried launching the app, but it crashed so I removed .sqlite3 again and tried launching the app again and it worked.

So as a temporary solution you can delete .sqlite3 every time you want to run an app that has been downloaded from the Mac App Store.

Tired one suggestion, rename the valid.sqlite3 file. trustd immediately seemed to recreate it, but the broken apps ran. Had to go to the market, **** down, when I got back, boom, back to bad signing. If I am not mistaken, doesn't FrancoisOC's automator script just edit one entry in that db?

More sauce for my supposed conversation with them tomorrow... assuming they make the call!

@FrancoisQC Gotta admire your tenacity! Here is some more background... this all started well after 11/11 or 11/12. Wanted to get boot-rom to 144.0.0 so took many extra steps. Striped my machine, put back OEM GPU, had one spinner with a fairly clean Sierra install. i HAD previously downloaded the HS installed AFTER the expiration date issue was fixed. It gave me an odd build number AND nVidia did not recognize that build, checked App store updates, sure enough a 005 security update. Took the half hour for that, NOW she had a more logical build number AND nVidia recognized it and d/led the correct drivers. BUT I saw a red marker on my App store icon, crap, NOW there an 006 security update. Did THAT one, yes nVidia liked it and gave me new drivers.

Put in my 980 (Metal capable) so that I could run the Mojave installer and get the final boot rom upgrade to 144.0.0 and THAT went smoothly. Returned all my drives. My boot drive is an array, so no updating it... so I updated my clone which was on a spinner. Given the experience I had by using an installer I had downloaded a while back, I got a fresh version from the app store. Crap, it was not in any way changed, got the odd build on reboot, still had to separately install both security updates. Then I cloned it back to my boot array... and she worked fine except... for this issue!

Noticed the app that gives me a temp in my menu bar seems not working. Got a fresh different one one from the app store, that didn't work either. Not sure why, but I ran BlackMagic speed test and IT crashed with the code sign issue. THAT led me here.

Tried the "delete valid.sqlite3." It created a very small one AND what would not run, now ran. BUT on a reboot, none worked and I see that db file had grown up to 17.3MBs. Not a solution. Tried your automator script, boom it worked AND while I shut the machine down last night, today a fresh boot AND the fix is still in effect.

My expectation is within the next few days this fix won't work anymore. Th9ng is I think it address a symptom but does not cure the disease. Later today I will give up x minutes watching my football games to have this "phone call" happen. My hope is that I can get it sent to a Tier 3 engineer... those guys usually are on top of the game... my hope being that they KNOW there is an issues AND are working on a solution. If he plays dumb, I will be majorly disappointed... knowing my beloved cMP is in great hardware shape (a very long story in and of itself, but I have a new CPU tray with all the chips running very cool) and will still live a long life as a winblowzen machine... I did get some juices flowing over a mini-tower, but if I get blown off over this issue, I will never again place the trust I have in apple since May 1984 when I bought my first Mac.

Edited, my previous message was clipped... trustd seems to be getting CRLs from

http://crl.apple.com/appleserverauthca1.crl

http://crl.apple.com/root.crl

http://crl.apple.com/codesigning.crl

http://www.apple.com/appleca/root.crl

On Windows, just double-click the CRL file downloaded and you will see the serial numbers that have been revoked. But the thing is, most are empty, or do NOT contain the serial numbers of the certificates revoked (or SN of the issuer).

For me, System Version shows macOS 10.13.6 (17G14042)

Both the 2020-005 and 2020-006 security updates have been installed.

The source of the bad data coming back may be here:

$ sqlite3 /System/Library/Security/Certificates.bundle/Contents/Resources/valid.sqlite3

SQLite version 3.32.2 2020-06-04 12:58:43

Enter ".help" for usage hints.

sqlite> SELECT hex(serial) from serials where hex(serial) IN ("04CA81F77D5E33F7", "0EEB5787E79E098D");

04CA81F77D5E33F7

0EEB5787E79E098D

sqlite>

hum..

date;sqlite3 /System/Library/Security/Certificates.bundle/Contents/Resources/valid.sqlite3  "select s.groupid, hex(serial), flags, format, data from serials s, groups g where hex(serial) in (\"04CA81F77D5E33F7\", \"0EEB5787E79E098D\") and s.groupid = g.groupid "

Mon 30 Nov 2020 16:29:24 EST
2587|04CA81F77D5E33F7|16|1|
2587|0EEB5787E79E098D|16|1|


date;sqlite3 /System/Library/Security/Certificates.bundle/Contents/Resources/valid.sqlite3  "select * from admin";
Mon 30 Nov 2020 16:33:17 EST
db_version|5|
db_format|3|
version|42|
db_source|0|valid.apple.com
check_again|0|J?cK?K?A

I'll see what happens if I just delete that one in Certificate.bundle and let trustd recreate from scratch. 

johnno_uk wrote:

Clearing out the database should also work too as this should just build the DB but if the code is buggy, it might also rebuilt it wrong. It doesn't just downlaod the database as a db file though in this instance I don't think. I think it builds them from those compact binaries like https://valid.apple.com/g3/v146. That's still more to go wrong than just taking a donor database to get a machine up to speed.

So I think the best solution was already found and there won't be anything better :-)

Thanks johnno

Most of that is completely over my head. I just wanted to make sure you knew that on my pre-existing High Sierra install with the 005 security update applied, if I delete the working /Library/Keychains/crls/valid.sqlite3 (6.4mb) and allow it to rebuild a new one (~17mb), the new one is broken. So I don't think it's something specific to recent High Sierra installers.

You probably understood that already but this is getting to be a really long thread, and it's a lot to expect someone to examine the whole thing.

Johnno_uk was right, the file "/System/Library/Security/Certificates.bundle/Contents/Resources/valid.sqlite3" does get used in rebuilding the file "/Library/Keychains/crls/valid.sqlite3", as when I removed the former (had to boot from USB image) and rebooted, the new file "/Library/Keychains/crls/valid.sqlite3" is definitely messed up. Doing a "select * from admin" does not show a version or db_version, only the "check_again" row even after few hours.

So I guess what we'd need is that someone shares a DB from a working HS that is at version of 145. We can then test if placing the file in both locations, "/System/Library/Security/Certificates.bundle/Contents/Resources/valid.sqlite3" and "/Library/Keychains/crls/valid.sqlite3", will fix the issue.

And Johnno_uk, where did you get those URLs from?

https://valid.apple.com/g3/v42

https://valid.apple.com/g3/v146

FrancoisQC wrote:

Riverside_Guy wrote:

"Wait until you see version 146• (sqlite3 /Library/Keychains/crls/valid.sqlite3 "select * from admin")

i.e. how do I see this?

Open a Terminal, and run

sqlite3 /Library/Keychains/crls/valid.sqlite3 "select * from admin"

...

I AM operating with a db file that was "edited" by your automator

With everything found so far, I would not recommend my script anymore. Not that it breaks something, but more that it does not address the realy issue. The automator script I created deletes the serial number of one certificate, so that since trustd does not find it in its disk cache, it somewhat assumes it isi valid, or does some other checks.

But I created that script while we were all staring at version 145 which was marking the issuer of that certificate with Flag 0.

Now in version 146, the issuer Flag is 16, which makes more sense and trustd properly launches apps which could not be launched with a default version 145.

That leads me to think that Apple is slowly pushing some updates, but they have not fixed everything.

And there is also the problem of an "updated version 146" which seems to be around 17MB, and a "fresh from scratch version 146" which is 5.8MB.

In my pre-existing HS system, I have two copies of the db, one a backup of the working db and one the current version of same (NOT a rebuilt one, note the filesizes). In case it's of any help, here's what your terminal command yields on both.

Zzyzx2015MBP-4:~ k$ sqlite3 /Library/Keychains/crls/valid\ copy\ 2.sqlite3 "select * from admin"

db_version|5|

db_format|3|

db_source|0|valid.apple.com

version|145|

Zzyzx2015MBP-4:~ k$ sqlite3 /Library/Keychains/crls/valid.sqlite3 "select * from admin"

db_version|5|

db_format|3|

db_source|0|valid.apple.com

version|146|

The hashes are not certificate hashes but literally of the db itself and given the updates are empty this could be a temporary stop the bleeding type update. E.g if the latest version is always fetched rather than the latest full version or walking through versions. What this might do is make new installs in effect just keep the database they already have and label it as version v146.

Here's where it gets clever though. I think there will be some hash checking code that simply does not exist in High Sierra so it will just say, OK version 146 it is then. Later versions of the OS will just take this update and see it and move to version 146 too if the hashes match, which they will if they were on 145 before.

So in effect what I think Apple have done is press the pause button for new installations of High Sierra by keeping them on v42 but labelling it as v146 by exploiting known differences in trustd versions.

This would explain why some people may be seeing fresh installs as OK now. I expect they'll be missing later revocations but at least apps will run. Obviously I'd need to reset back to v42 and see if the database updates but only updates the version number and nothing more to v146 to prove this is the intention of an [non]update like this.

Something interesting after I tried Johnno_uk's steps. When I tried a "build from scratch" approach, I had renamed /System/Library/Security/Certificates.bundle/Contents/Resources/valid.sqlite3 to /System/Library/Security/Certificates.bundle/Contents/Resources/valid.sqlite3.ori to see if trustd would create an empty (clean) database.

And when I tried the steps above, I realized that when manually starting the trustd process, it complained about valid.sqlite3 missing.

And when the file is present, the console warning does not show up.

This means when trustd starts, it somewhat read (and rely on?) the file /System/Library/Security/Certificates.bundle/Contents/Resources/valid.sqlite3

I would be curious to place a file older than verson 42 in there, see what happens.

As anticipated, today I see the "fix" using Unrevoke Apple Certificate.app no longer is effective, ran it, everything is OK for the time being. My understanding is that this app edits what I'd call the public facing DB (in the cls folder). I further seem to understand that said public facing one CAN be updated, edited. Yes it's a kind of hack that must be reapplied every few days.

HOWEVER, I also see in another Francoise post that there is a version of this buried away inside some framework... that I guess is NOT updateable as time marches on, but IS used as the "base" for the public facing one, i.e. the one that the PS uses to make sure there IS a public facing version. AND that one from the gitgo is one that provides us with oh so many code signing issues.

So logically, if we modify THAT version (i.e. the one inside the framework) of the file the OS can't make a public facing version that has the issue. Does this make any sense?

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.

The timestamp will be present in the CMS blob signature with an OID of SEC_OID_PKCS9_TIMESTAMP_TOKEN as shown in https://opensource.apple.com/source/Security/Security-57740.1.18/libsecurity_smime/lib/SecCmsBase.h.auto.html

The easiest way to find the an Apple CMS timestamp might be to write a program that uses the open source code from Apple to do it. It may be possible to view the signature with command line tools perhaps after shaving some bytes off.

In either will certainly be possible to grab timestamps of when each update was generated in Apple CMS system of updates to stuff. This way we can say, on time X there was an update which revoked a lot of certificates and on time Y there was an update (v126 in this case) which un revoked them.

Those pieces of data will show that Apple did issues updates to all Macs on High Sierra upwards saying their own certificates were revoked at time X and then not at time Y. I need to check for update X first which I suspect will

be v125.

We must then ask why did Apple do this ? Answers might be.

a) By mistake

b) There was a cyber attack against Apple perhaps which worked by sending poisoned certificate transport logs to them

c) Something else.

Anyhow the incident appears to be very short lived so Apple should be commended for handling it sharply. The only piece of fallout still on going appears to be issue in this thread, is that the error+correction is not handled correctly on new installs of High Sierra.

Anyhow one thing about donor database to note, is that a version at 126 or further from a working Mac is probably a good idea as that will be well past this incident.

Well Apple break stuff and then people have to fix it ;-)

I've come back after an evening out and double checked my bgrep. I saw that the WWDR cert does appear pretty much in all updates but only at the end which doesn't really do anything. So I changed it to bgrep CE057691D730F89CA25E916F7335F4C8A15713DCD273A658C024023F8EB809C2 v* | grep ': 0000' to find stuff near the start.

It's not until v46 where it starts to appear. So we can presume that all way back ago in v46 was when Apple noticed that this update mechanism didn't really work. It looks like the plan was to keep their certs in the db as they are there in v42 but as we've seen from FrancoisQC's digging they can get their flags mangled. So this problem is likely a bug that has been known about for ages and the fix was just to send updates which delete them like the way FrancoisQC's automator script does.

So right now a new install will do this:

v42 -> v43 -> v146 (which will just be v43 renamed of all intents and purposes).

I'm presuming the delete commands (big plist picture from earlier posts) it needs for App Store Apps to works appear between v46 and v126 from my rough bgreps (although better than last time).

So some known bug which has been in High Sierra for years will probably trigger (let's call it the flag clearing bug) but those special deletes from v46 to v126 won't be there to save the day as they'll all have been leap-frogged.

So it looks like Apple tries to fix software bugs by fiddling with data updates. Let's presume they already had one of these data fixes in place since v46. They may by v127 have removed that data fix perhaps having wondered why it was there or seen High Sierra mentioned in a comment the code that generates the fix and just purged it.

Nobody has yet found out why many Apple certs gets its flags set to 0 which is when everything starts to go wrong so some kind of link with iCloud/keychain is a possibility.

This is where I started first myself, thinking that disabling certs was deliberate because the Apple didn't want apps to run on a certain machine and that this was all a kludge to make that happen. Given though to make things run again all that is needed is to delete some rows from a database I reached the conclusion that such a protection mechanism that could be so easily thwarted wouldn't make sense.

There are other mechanisms and nicer error message for the case where an app is copied from another machine for example.

There is however other funny stuff though involving the keychain such as when particular certs are put into it the error status of another changes and the like.

I'd leave the VM up and running for a while if not already the case just to be certain it doesn't regress and report back if it lasted the course as that's an interesting data point.