Getting Statistics on Certs Generated/Expiration

[TheSecMaven]

We see the below list of tables in the mysql database
acme_account_orders_index
acme_accounts
acme_authzs
acme_certs
acme_challenges
acme_keyID_accountID_index
acme_orders
nonces
revoked_ssh_certs
revoked_x509_certs
ssh_certs
ssh_host_principals
ssh_hosts
ssh_users
used_ott
x509_certs

Which of these can be used to get the number of ACME certs issued? same goes for number of manually signed certs? Can we get info like expiration date for certs that we signed from this, or maybe some help in deriving that? if we had the list of certs that would work as well, as we could from there pull what we need.

[TheSecMaven]

the other thing is many of these are stored as go bytes. trying to convert them to something human readable would be helpful

[dopey]

For number of ACME certificates you can use acme_certs.

For non-acme certificates the answer is more convoluted. x509_certs contains both ACME and non-ACME (manual) certificates. In order to figure out which is which, you'd need to parse the structure of the certificate stored in the DB and parse the provisioner ID to check if it matches the ID of an ACME provisioner. :/

[dopey]

More generally, we definitely need a better way of getting reporting metrics from the DB. It's high on our list of priorities. There are a handful of factors that have kept us from implementing it thus far:

1. Time. We have a small team and we're spread REAL thin. I'm sure that comes as no surprise. We're hoping that offering a hosted version of the X.509 CA will help with prioritization.
2. As you noted the data is stored as bytes. Which is super unfriendly for querying. Our plan is to convert all storage to SQL for easier querying. Hence we're weary of spending time developing logging/reporting tooling that is going to have to be redone when we make the DB switch.

[TheSecMaven]

makes sense. any thoughts on script/example code to convert the bytes back to original format? unless something is going to come in the short term (less than 6 months) we will probably need to try to do something as a workaround until then, so any guidance would be helpful. google did not prove helpful for this topic for me.

[dopey]

which DB are you using? I'll make a note to bring this up during our next planning session. I don't think it would take me long to write something that you could then modify to your needs.

[mmalone]

Pretty sure the "bytes" in the database are just PEM-encoded certs. step certificate inspect --format json can convert a PEM-encoded certificate into a JSON object. Could possibly use that in a little script?

@mkkeffeler my understanding is that you guys are interested in open source, and less interested in commercial. That's fine, and I'm always hesitant to mix the two... however, I will note that observability and reporting features are very high priority for our commercial work. If you ever want to talk about use cases there (even if you're never gonna be a buyer) I'd love to do so. I can't remember if I've mentioned this to you in the past... apologies if I have :).

[TheSecMaven]

good to know, thanks mmalone :) and @dopey we use mysql. let me poke real fast at whether it is just a pem encoded cert