This was previously discussed with @bdarnell and @catj-cockroach .

This issue describes two simultaneous issues, that should probably see a single solution:

1. The "certificate manager" is too opinionated about file locations.

   Currently, CockroachDB's "certificate manager" is designed around the idea of a single "certificate directory" where all files have a fixed, flat filename structure. This makes it possible to e.g. compare the stated purpose of a cert (encoded in its filename) with its internal fields, and report possible misconfigurations early.

   However, meanwhile this is not the right mental model that people use.

   • all the SQL client commands support a "connection URL" which can specify cert/key files in separate directories. This flexibility is desirable, e.g. to list a CA cert that's system-wide or shared across multiple clusters. However the fact the SQL client commands need that flexibility means they cannot use the existing cert manager / cert loader code. This results in undesirable complexity, currently explained as a big fat comment in cli/client_url.go.
   • we would like to offer users the ability to store certs for multiple clusters, possibly with different CA chains, in a single directory. Currently this is simply not possible.

2. The "certificate manager" preload all files when initialized, instead of when needed.

   This incurs various UX problems:

   • client commands fail with cryptic errors if launched on a machine with stray server-side certs that are irrelevant to the client command.
   • server commands fail with cryptic errors if launched on a machine with stray client-side certs.

To solve these problems, we should do away with the current overall structure of "certificate manager" and move to an architecture where the cert locations are configurable at a finer grain (but with backward-compatible defaults), and only used when strictly needed by a client or server command.

Jira issue: CRDB-16199