osquery displaying wrong serial number info for apple certificates stored in hosts' keychain

[PezHub]

Fleet version: 4.65 osquery version: 5.16.0

Web browser and operating system: any

---

💥  Actual behavior

When querying the certificates table on a macOS host the results for serial number are not accurate for these apple certs:

• com.apple.systemdefault
• com.apple.kerberos.kdc

🧑‍💻  Steps to reproduce

1. Enroll a macOS host to Fleet

2. Run the following query SELECT * FROM certificates; or SELECT * FROM certificates WHERE common_name LIKE '%%com.apple%%';

3. Filter the two certs mentioned above and make note of the serial numbers

4. Open keychain on the macOS

5. Open the two certs and make note of the serial numbers

6. observe they do not match

7. 🕯️ More info (optional)

This was discovered when we added Cert information to the Fleet UI as part of this story #23235

[sgress454]

Reproduced 👍

The issue here is that the serials are being reported in hex notation (base 16). This is likely an osquery issue; it needs to translate the hex bytes into integers. For example in the original report, 303598C6 is 808818886 in base 16.

[sgress454]

Opened https://github.com/osquery/osquery/pull/8608

[JordanMontgomery]

I came across this issue because some of our cert renewal work over on MDM has us thinking about certificate serial numbers as well. I discovered a somewhat related issue today where serial numbers for certificates used in some of our unit tests, as well as those coming from iOS/iPadOS are always base 10 and never hex due to this line https://github.com/fleetdm/fleet/blob/main/server/fleet/host_certificates.go#L72

IMHO changing what OSQuery returns and what we store in the DB, especially in a conditional manner like this which is potentially ambiguous for certain values, may be the wrong direction. We will soon be managing certificates on Windows and Linux hosts and I think ideally we should try to standardize on a single representation in the DB, whether that is storing the raw byte representation of the certificate or always storing it as base 10 encoded as a string or hex encoded as a string and then add some formatting on top of it in the UI if we want conditional formatting, otherwise we run the risk of having to come up with some really convoluted fixes later if we need to use the serial. We(MDM team) aren't currently using the serial other than for display however there are certain usecases such as the DigiCert Renewal endpoint which require us to use the serial and there may be others and we are actively using the data returned by OSQuery to "fill in" the missing details for certificates like serial, notBefore, notAfter such as those from Custom SCEP servers.

ccing @rachaelshaw since this might need some attention from design. Happy to bring my concerns to a design review if you need to chat.

[sgress454]

+1 after discussing w/ @JordanMontgomery I don't think my current osquery PR is a "fix", or that this is even necessarily broken. At most we might consider a new field in the certificates table like serial_display to display a decimal version of the serial if it's <= 8 bytes. But as far as I can tell, only Keychain Access does this on Mac; Chrome certificate viewer always shows hex bytes.

[JordanMontgomery]

Opened this frontend PR to display the decimal representation of the serial in addition to hex for small values: https://github.com/fleetdm/fleet/pull/28732

[PezHub]

QA Test Results

Confirmed we now see a Hex and Decimal version of the serial number and it matches the keychain

[fleet-release]

Apple certs' numbers wrong, Fleet's truth in the cloud thrives, Clarity now belongs.