caching_sha2_password: mysql-monitor_* credential cannot authenticate on the Admin interface (native-only scramble)

[renecannao]

Task: fix mysql-monitor_* Admin authentication under caching_sha2_password

FIRST: Git workflow (do this before reading anything else)

• Create branch fix/5985-monitor-caching-sha2 from v3.0
• PR target: v3.0
• If upstream changes are needed: git rebase, NOT git merge

Context

Fixes #5363. Parent issue: #5985 (Finding 2); also answers the unanswered follow-up on #4845.

Reproduced and confirmed — see test/repro/reg_test_5363_admin_monitor_caching_sha2.bash, which reproduces this deterministically on three consecutive cold starts with no mysql_users rows and no same-named admin credential, and includes a control proving an ordinary admin credential authenticates under the identical settings.

With mysql-default_authentication_plugin='caching_sha2_password', the credential in
mysql-monitor_username / mysql-monitor_password cannot authenticate on the Admin interface
(:6032). The same credential works under mysql_native_password. Kubernetes liveness/readiness
probes and metrics exporters that connect to :6032 as the monitor user fail with
Access denied for user 'monitor'@'127.0.0.1'.

This is not a general "Admin does not support caching_sha2" problem. Ordinary
admin-admin_credentials authenticate correctly under caching_sha2_password, on both plaintext
and TLS connections, because they are stored in cleartext and take the fast-auth path in
PPHR_6auth2(). Only the monitor credential is broken, because it is special-cased in a different
function that was never updated for caching_sha2_password.

Scope is deliberately limited to that function plus one diagnostic improvement. The credential-store
redesign and RSA public-key support are tracked separately.

Research: root cause

lib/MySQL_Protocol.cpp:2052 — MySQL_Protocol::PPHR_5passwordFalse_0() is the only code path that
authenticates mysql_thread___monitor_username. It is called from lib/MySQL_Protocol.cpp:2838,
inside the vars1.password == NULL branch of PPHR_verify_password() (i.e. the username is not
present in GloMyAuth), and only for PROXYSQL_SESSION_ADMIN / _STATS / _SQLITE sessions.

It is hardcoded to the mysql_native_password scramble:

if (strcmp((const char *)vars1.user, mysql_thread___monitor_username)==0) {
    proxy_scramble(reply, (*myds)->myconn->scramble_buff, mysql_thread___monitor_password);  // SHA1
    if (memcmp(reply, vars1.pass, SHA_DIGEST_LENGTH)==0) {                                   // 20 bytes

Under caching_sha2_password the client's fast-auth response is a 32-byte SHA256-derived value, so
this comparison can never succeed. The pre-existing comment on line 2053 —
// FIXME: does this work only for mysql_native_password ? — is exactly this bug.

The correct caching_sha2_password fast-auth verification already exists in the codebase, at
lib/MySQL_Protocol.cpp:2171 PPHR_6auth2():

a = SHA256(password)
b = SHA256(a)
d = SHA256(b || scramble_buff[20])
e = a XOR d
success iff memcmp(e, client_response, SHA256_DIGEST_LENGTH) == 0

It is simply unreachable for the monitor credential, because that path requires
vars1.password != NULL.

Second-order detail the fix must handle. The PPHR_6auth2() call site at
lib/MySQL_Protocol.cpp:2900-2906 emits fast_auth_success{0x03} before the OK packet when
switching_auth_stage == 0:

PPHR_6auth2(ret, vars1);
if (ret == true) {
    if ((*myds)->switching_auth_stage == 0) {
        const unsigned char fast_auth_success = '\3';
        generate_one_byte_pkt(fast_auth_success);
    }
}

The PPHR_5passwordFalse_0() call site at line 2838 does not. A successful caching_sha2 fast
auth that omits that byte will not be accepted by the client. Do not skip this.

Why TLS is not a complication. PPHR_5passwordFalse_0() sets
default_hostgroup = STATS_HOSTGROUP (-3, include/MySQL_Thread.h:28) on success, so the
default_hostgroup < 0 && session_type == ADMIN/STATS clause of the gate at
lib/MySQL_Session.cpp:6567 passes for both encrypted and unencrypted connections. The
client_myds->encrypted == false monitor clause at lib/MySQL_Session.cpp:6579 is not
load-bearing here. Add an assertion for the TLS case so this stays true.

Deliverables

• Modified: lib/MySQL_Protocol.cpp — extract the SHA256 fast-auth verification out of
  PPHR_6auth2() into a reusable helper; make PPHR_5passwordFalse_0() dispatch on
  auth_plugin_id; emit fast_auth_success{0x03} on the caching_sha2 success path.
• Modified: include/MySQL_Protocol.h — only if the helper is added as a member function
  rather than a file-static.
• Modified: lib/MySQL_Protocol.cpp — named error for the unsupported RSA request (see below).
• New file: test/tap/tests/reg_test_5363_admin_monitor_caching_sha2-t.cpp
• Modified: test/tap/groups/groups.json — register the new test in group no-infra-g1.
• New file: test/repro/reg_test_5363_admin_monitor_caching_sha2.bash — standalone reproduction, already written and verified against v3.0. Must go green (exit 0) once this fix lands; today it exits 1 with two [BUG #5363] assertions.
• New file: test/repro/reg_test_5985_admin_caching_sha2_full_auth.bash — companion showing Admin full auth already works; lands with this PR for context.
• New file: test/repro/README.md — state that these are developer-facing reproductions, that the TAP tests are the CI artifact, and document the destructive teardown.

Rework the repro scripts before landing them. As written they destroy the ProxySQL instance for $INFRA_ID (including its persisted proxysql.db) unconditionally, which is fine for a scratch investigation and hostile in a committed artifact. Make the teardown opt-in via an explicit flag/env var and fail with a clear message when a cold cache is required but not available, rather than silently producing a meaningless result.

Implementation details

1. Extract the fast-auth verifier. Add a helper next to PPHR_6auth2(); suggested signature:

// Returns true when 'client_response' (SHA256_DIGEST_LENGTH bytes) is the
// caching_sha2_password fast-auth response for 'cleartext_password' under 'scramble'.
static bool caching_sha2_fast_auth_verify(
    const char* cleartext_password,
    const unsigned char* scramble,      // 20 bytes
    const unsigned char* client_response
);

Rewrite PPHR_6auth2() to call it, so there is exactly one implementation of the algorithm. Do not
copy-paste the SHA256 block.

2. Dispatch in PPHR_5passwordFalse_0(). Keep the existing outer
strcmp(vars1.user, mysql_thread___monitor_username) == 0 guard and the existing success block
(the default_hostgroup/default_schema/vars1.password assignments) unchanged and shared across
branches. Replace only the verification:

• auth_plugin_id == AUTH_MYSQL_NATIVE_PASSWORD — existing proxy_scramble + 20-byte memcmp.
• auth_plugin_id == AUTH_MYSQL_CACHING_SHA2_PASSWORD and switching_auth_stage == 0 — call the
  new helper against mysql_thread___monitor_password; on success emit fast_auth_success{0x03}.
• auth_plugin_id == AUTH_MYSQL_CACHING_SHA2_PASSWORD and switching_auth_stage == 5 — the client
  has already been driven through a full-auth round trip by some other path (e.g. pass-through
  auth), so vars1.pass is the cleartext: compare it directly with
  mysql_thread___monitor_password. Defensive branch; keep it.
• any other auth_plugin_id — leave ret = false and emit a proxy_debug line naming the plugin
  id. Do not assert(0); a client can request an arbitrary plugin.

Also delete the now-answered // FIXME: does this work only for mysql_native_password ? comment.

3. Named error instead of an unexpected packet. Today a plaintext caching_sha2 client that
answers perform full authentication with the request_public_key packet (single byte 0x02)
has that byte consumed as if it were the cleartext password, at lib/MySQL_Protocol.cpp:1685
PPHR_1(), which then fails verification and produces a generic error. Clients report the
unhelpful unexpected resp from server for caching_sha2_password, perform full authentication.

In PPHR_1(), when switching_auth_stage == 5 and the payload is exactly one byte equal to 0x02,
generate an explicit generate_pkt_ERR() with error code 1045, SQL state 28000, and a message
naming the real cause, e.g.:

ProxySQL Error: caching_sha2_password RSA public key exchange is not supported; connect using TLS

This is a diagnostic change only — it must not attempt to serve a key. RSA support is tracked
separately, and that issue will replace this branch.

Build & verification

make clean
PROXYSQL31=1 make debug -j$(nproc)          # must exit 0
make build_tap_test_debug                    # must exit 0

WORKSPACE=$(pwd) INFRA_ID=dev-$USER TAP_GROUP=no-infra-g1 \
  test/infra/control/ensure-infras.bash

WORKSPACE=$(pwd) INFRA_ID=dev-$USER TAP_GROUP=no-infra-g1 \
  TEST_PY_TAP_INCL="reg_test_5363_admin_monitor_caching_sha2-t" \
  test/infra/control/run-tests-isolated.bash   # all assertions must pass

Test requirements

The new test needs no MySQL backend — it exercises Admin authentication only. It must assert this
matrix on :6032 as mysql-monitor_username / mysql-monitor_password:

mysql-default_authentication_plugin	plaintext	TLS
mysql_native_password	connects	connects
caching_sha2_password	connects	connects

and must confirm the session lands on the stats hostgroup (e.g. the connection can run a
stats_mysql_global query but not an admin-only statement).

The test must restore mysql-default_authentication_plugin to its entry value and run
LOAD MYSQL VARIABLES TO RUNTIME before exiting, including on the failure paths — no-infra-g1 is
a shared instance and a leaked global will corrupt the other tests in the group.

DO NOT

• Do not modify the CLIENT_SSL forcing at lib/MySQL_Protocol.cpp:1083. Forcing CLIENT_SSL on
  when the default plugin is caching_sha2_password is intentional and is what makes a completion
  path possible at all. The reporter explicitly asks for it to be kept.
• Do not add RSA public-key generation, serving, or decryption. Separate issue; it carries new
  config and packaging surface that does not belong in a v3.0 patch release.
• Do not change how admin/stats credentials are stored in GloMyAuth. Separate issue.
• Do not "fix" a link error like undefined reference to mysql_thread___ffto_max_buffer_size by
  dropping PROXYSQL31=1. That error means stale objects from a different feature tier — run
  make clean and rebuild with the flag. See CLAUDE.md.
• Do not create Docker networks or start containers by hand, and do not invent a new TAP group to
  isolate the test. Use ensure-infras.bash plus the TEST_PY_TAP_INCL regex.

Reference files

• lib/MySQL_Protocol.cpp:2171 PPHR_6auth2() — the caching_sha2 fast-auth algorithm to extract.
• lib/MySQL_Protocol.cpp:2900-2906 — the fast_auth_success{0x03} emission to mirror.
• lib/MySQL_Session.cpp:6560-6590 — the post-auth session-type / hostgroup gate.
• test/tap/tests/test_passthrough_auth_admin-t.cpp — Admin-only TAP test in no-infra-g1; follow
  its structure and its variable save/restore discipline.
• test/tap/tests/reg_test_4935-caching_sha2-t.cpp — existing caching_sha2 TAP coverage.

Acceptance criteria

• PROXYSQL31=1 make debug -j$(nproc) exits 0.
• The new TAP test passes all assertions under the command above.
• reg_test_4935-caching_sha2-t and test_auth_methods-t still pass (no regression on the
  existing caching_sha2 paths).
• grep -c "SHA256(c, SHA256_DIGEST_LENGTH+20, d)" lib/MySQL_Protocol.cpp returns 1 — the
  algorithm exists in exactly one place.
• grep -n "does this work only for mysql_native_password" lib/MySQL_Protocol.cpp returns
  nothing.
• A plaintext caching_sha2_password client that requests the RSA public key receives an error
  whose text names the cause, not unexpected resp from server.

Ready-made prompt for the executing agent

Read issue #5985 and this issue in full before writing code.

1. git checkout v3.0 && git pull && git checkout -b fix/5985-monitor-caching-sha2
2. Read lib/MySQL_Protocol.cpp lines 2040-2210 and 2820-2910. Understand why
   PPHR_5passwordFalse_0 is only reached when vars1.password == NULL.
3. Extract the SHA256 fast-auth verification from PPHR_6auth2 into a single
   helper. Rewrite PPHR_6auth2 to call it. Build and confirm no behaviour change.
4. Make PPHR_5passwordFalse_0 dispatch on auth_plugin_id per the implementation
   details above. Remember the fast_auth_success{0x03} byte on the caching_sha2
   success path.
5. Add the named-error branch in PPHR_1 for the one-byte 0x02 request_public_key
   packet at switching_auth_stage == 5.
6. Write test/tap/tests/reg_test_5363_admin_monitor_caching_sha2-t.cpp covering
   the 2x2 matrix, register it in no-infra-g1 in test/tap/groups/groups.json.
7. Build and run per the Build & verification section. Paste the TAP output into
   the PR description.
8. Open a PR targeting v3.0 that closes #5363 and references #5985.

[coderabbitai]

⚠️ Possible Duplicate Issue(s)

• Monitoring failing after enabling caching_sha2_password globally #5363

🔗 Related PRs

#5485 - MySQL/PgSQL Authentication unit tests (Phase 2.2) [merged]
#5538 - CI test fixes: cluster sync, plan counts, test group assignments, and diagnostics [merged]
#5744 - fix(mysql): advertise CLIENT_PLUGIN_AUTH_LENENC_CLIENT_DATA in server greeting [merged]
#5809 - Integration batch 2026-05-23: bundle of 6 fixes [closed]
#5810 - feat(passthrough-auth): pass-through authentication (spec + Phase 1, WIP) [merged]
#5865 - SCRAM verifier & md5 credential storage with SCRAM/md5 backend pass-through - PostgreSQL [open]
#5944 - test(passthrough-auth): adversarial pool-reuse (force-new probe) test [merged]
#5956 - Add dedicated TLS for admin interfaces [open]

---

📝 Issue Planner

_{Check the box below or use the @coderabbitai plan command to generate an implementation plan and prompts that you can use with your favorite coding assistant.}

• Create Plan

---

🧪 Issue enrichment is currently in open beta.

You can configure auto-planning by selecting labels in the issue_enrichment configuration.

To disable automatic issue enrichment, add the following to your .coderabbit.yaml:

issue_enrichment:
  auto_enrich:
    enabled: false

💬 Have feedback or questions? Drop into our discord!

[renecannao]

Implemented and merged in #5991.

MySQL_Protocol::PPHR_5passwordFalse_0() was hardcoded to the mysql_native_password scramble, so the 32-byte caching_sha2_password fast-auth response could never match and mysql-monitor_* was rejected outright — with no 'perform full authentication' sent, which is why TLS was not a workaround either. It now dispatches on auth_plugin_id and handles native, caching_sha2 (fast auth, plus the cleartext case at switching_auth_stage == 5) and clear_password, emitting the required fast_auth_success marker for caching_sha2.

Not gated — this landed on v3.0 and applies to the stable tier.

Shipped with it:

• test/tap/tests/reg_test_5363_admin_monitor_caching_sha2-t.cpp (group no-infra-g1), asserting errno 1040 (authenticated, locality reject) vs 1045 (auth failed) across both plugins × TLS
• test/repro/reg_test_5363_admin_monitor_caching_sha2.bash, which exits 0 once fixed and distinguishes 'bug reproduced' from 'environment broken'
• a log fix so a client requesting the RSA public key is no longer reported as 'disconnecting during switch auth'

Note the TAP test is registered in no-infra-g1, which is not yet wired into any GitHub Actions workflow, so it does not currently run in CI. Tracked separately.

This also fixes #5363 and answers the follow-up on #4845.