Motivation
As with most of my tutorials, topic ideas come from user inquiries. I see this question come up quite often and we don't have a documented approach to bridge the gap today. Cockroach Labs engineering is hard at work to build an API that will make this point moot but until then this can be a viable alternative. It is primarily directed at our cloud offering where we rely on password authentication today. There are also cases where password authentication serves other purposes and we need ways to automate the provisioning of passwords other than ALTER USER username WITH PASSWORD "password"; command.
You can take this approach and incorporate it into your CI/CD pipelines to onboard new users and manage their passwords in absence of certificate-based authentication and its associated revocation mechanisms or directory services and its password management capabilities.