Summary:
YB supports multiple password based auth mechanisms: Plaintext, MD5, SCRAM. However, conn mgr supports only plaintext and MD5 at the moment.

This diff adds functionality to support SCRAM_SHA_256 in conn mgr for both, the auth backend and auth passthrough modes and merges the control flow for all 3 password based options in the auth backend flow. Includes a slight clean-up of existing relevant code in auth.c.
Note that supporting SCRAM-SHA-256-PLUS is not trivial with conn mgr. Further investigation is needed to either find a solution or completely rule out the possibility of supporting it with conn mgr.

Jira: DB-15176

Test Plan: Jenkins: all tests

Reviewers: skumar, rbarigidad, mkumar, arpit.saxena, asrinivasan

Reviewed By: rbarigidad

Subscribers: yql, rbarigidad

Differential Revision: https://phorge.dev.yugabyte.com/D44648