[FEATURE] Add support for refreshing credentials (either periodically, or upon a detected login failure)

[andycoates]

Summary

We currently use OCI Vault to store database credentials and it has support via OCI Functions to rotate a DB credential whilst syncing that new credential to OCI Vault.

As it is good practice to rotate credentials, it would therefore be great if we can somehow help the Exporter realise its credentials need rotating, as it seems right now the Exporter only reads credentials on initial startup.

Motivation

Right now if credentials are rotated the Exporter ends up locking its account (as it retries with the old password), which can lead to downtime in metric collection until the account is unlocked and the Exporter restarted. When using multi-db, restarting the Exporter also incurs risk to missed metric collection/issues for the other DBs that may have been fine, so ideally just the DB that got rotated can have its connection reset with new credentials.

Proposed Solution

I think a config option would help make this optional, but the config could either a) control how often it checks for new credentials itself (catch an update before its connections start failing) b) indicate that after a successful connection, if it then sees a ORA login failure, it fetches new credentials and tries again.

I'm not a Golang or Oracle DBA, but my guess is this would rely on re-creating the DB connection (since I don't believe you can update credentials of an existing connection?) so that it starts a new pool (just for the DB that failed).

Oracle DB also has a grace period for old passwords, and existing connections aren't affected by a password change (unless a DBA kills the sessions), so there might be a potential to use that knowledge in determining the most appropriate way of detecting/updating its credentials without affecting current queries etc.

Alternatives Considered

Restarting the Exporter obviously re-reads the credentials, but that has a big impact in multi-db setups, but even restarting the Exporter doesn't avoid locking its account prior to restart etc.

[anders-swanson]

Great suggestion @andycoates. We'll look at getting this implemented. This is what I'm thinking for the implementation:

• each DB target manages its own credential lifecycle and pool; no global restart.
• add a per-db ConnectionManager interface:
  • Create initial pool from credentials.
  • Detect credential changes (poll or watch).
  • React to auth failures by refreshing credentials and rebuilding the pool.
  • Holds a hash of the current creds
• config changes:

credential-refresh:
  enabled: true|false
  auth-failure-codes: ["ORA-01017"]
  auth_failure_backoff: 30s
  auth_failure_backoff_multiplier: 2

When auth fails due to one of the auth-failure-codes , the ConnectionManager reloads credentials (from file, vault, or wherever specified by the current DB instance. If the credentials are different than the stored hashed value, restart the DB connection pool.

If the credentials are the same as the hashed value, log an error and start the backoff.

Note: since we are using godror as the primary Oracle connection driver, this all has to be synchronous and would block any other database from creating connections. However, it should be fine if the other database connection pools are currently "warmed up". Godror only allows connections to be opened synchronously, so we have to code defensively around this.

[anders-swanson]

I've also noticed that if the godror driver initializes a connection pool with invalid credentials (say the user isn't created yet), that. connection pool needs to be refreshed once the user is created.