CACHING_SHA2_PASSWORD() generates salts containing credential-string delimiters, silently breaking ~27% of Admin credentials

[renecannao]

Task: restrict the salt alphabet generated by CACHING_SHA2_PASSWORD()

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

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

Context

The CACHING_SHA2_PASSWORD() admin SQL function draws its 20-byte salt from an unrestricted byte range. Roughly 27% of the hashes it produces cannot be stored in admin-admin_credentials or admin-stats_credentials, because those variables are ;-separated and :-delimited plain strings and the salt contains one of those delimiters.

The failure is completely silent. UPDATE global_variables succeeds, LOAD ADMIN VARIABLES TO RUNTIME succeeds with no error or warning, and SELECT @@admin-admin_credentials shows all 70 bytes present and correct. ProxySQL_Admin::add_credentials() then tokenizes the value on ; and splits on :, producing a bogus credential. The only symptom is a generic Access denied at login.

Because the salt is random, the same command run twice produces a working credential and a broken one. This is what makes caching_sha2 Admin credentials look unreliable, and it was the underlying cause of confusion in #5985.

Measurements

Over 3000 distinct hashes generated by SELECT CACHING_SHA2_PASSWORD('secret') on v3.0 (89a29ec91):

salt contains	count	share
; (0x3b)	424	14.1%
: (0x3a)	446	14.9%
; or :	808	26.9%
a byte outside 0x20–0x7e	2994	99.8%

Observed salt byte range: 0x10–0x7d.

Note the last row is not by itself a defect for storage — a hash whose salt contains control bytes but no ; or : stores and authenticates correctly:

hash bytes: 24 41 24 30 30 35 24 5F 17 31 14 7B 42 59 6C 6A 2D 32 13 61
                                        ^^    ^^          ^^
login over TLS: OK

It does matter for readability, for copy/paste into a config file, and for SET admin-admin_credentials, which does not survive a control byte in the value. But the actionable defect is the delimiters.

Reproduction

-- repeat until the generated salt happens to contain ';' or ':' (~27% of the time)
UPDATE global_variables SET variable_value='admin:admin;radmin:radmin' WHERE variable_name='admin-admin_credentials';
UPDATE global_variables
   SET variable_value = variable_value || ';brk:' || CACHING_SHA2_PASSWORD('secret')
 WHERE variable_name='admin-admin_credentials';
LOAD ADMIN VARIABLES TO RUNTIME;        -- no error
-- then: mysql -u brk -psecret -P6032 --ssl-mode=REQUIRED   -> ERROR 1045 Access denied

The same hash placed in mysql_users.password authenticates correctly on :6033, which confirms the hash itself is valid and that the defect is in generation-versus-storage, not in verification.

Proposed fix

Restrict the generated salt to a safe alphabet. MySQL's own caching_sha2_password salts are printable; matching that is both compatible and sufficient. At minimum the alphabet must exclude ;, :, ', ", \ and all bytes outside 0x21–0x7e. A conservative choice such as [A-Za-z0-9./] (the crypt(3) base64 alphabet) is simplest to reason about and loses a negligible amount of entropy over 20 characters (~119 bits, well beyond what the salt needs).

Verification is unaffected. PPHR_verify_sha2() / PPHR_sha2full() (lib/MySQL_Protocol.cpp:2260, :2309) read the salt out of the stored string with substr(7,20) and pass it through to sha256_crypt_r(); they never inspect its contents. Every hash generated before this change keeps verifying exactly as it does today. This is a generation-side change only, with no migration.

Deliverables

• Modified: the implementation of the CACHING_SHA2_PASSWORD() SQL function — restrict the salt alphabet.
• New/modified unit test asserting that N generated salts contain only characters from the permitted alphabet (N large enough to be meaningful — 1000+ is cheap since no I/O is involved).
• New/modified test asserting that a hash produced by CACHING_SHA2_PASSWORD() can always be round-tripped through admin-admin_credentials and used to authenticate on :6032.
• Verify that a pre-existing hash with an out-of-alphabet salt still authenticates (backward-compatibility assertion).

Related, but deliberately out of scope

The other half of this is that admin-*_credentials silently accepts a value it cannot represent. Even with a fixed salt alphabet, a hand-written password containing ; or : breaks identically. That is worth addressing separately — either by validating and rejecting at SET/LOAD time, or as part of #5987, which removes the shared-namespace design these variables sit on.

DO NOT

• Do not change PPHR_verify_sha2() or PPHR_sha2full(). Verification is correct and format-agnostic; changing it would break every existing stored hash.
• Do not change the $A$ format, the rounds encoding, or the 20-character salt length — only which characters can appear.
• Do not "fix" undefined reference to mysql_thread___ffto_max_buffer_size by dropping PROXYSQL31=1 — that is a stale-object tier mismatch. Run make clean. See CLAUDE.md.

Reference files

• lib/MySQL_Protocol.cpp:2260 PPHR_verify_sha2() and :2309 PPHR_sha2full() — the consumers, for confirming the salt is opaque to verification.
• lib/sha256crypt.cpp:317 — SALT_LEN_MAX 20, worth knowing: ProxySQL's bundled sha256-crypt permits a 20-byte salt where standard/glibc caps at 16, so ProxySQL-generated hashes are not reproducible with standard crypt tooling.
• lib/ProxySQL_Admin.cpp add_credentials() — the tokenizer that the delimiters break.

[coderabbitai]

🔗 Related PRs

#5457 - Redact admin-admin_credentials from admin SET command logs [merged]
#5807 - fix(core): validate_charset must replace collation 255 on MariaDB backends (#5790) [merged]
#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]
#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!