feat: caching_sha2_password: RSA public-key support for plaintext clients, and Admin credential usability

[ademidoff]

Summary

With mysql-default_authentication_plugin=caching_sha2_password, ProxySQL sends the
perform full authentication marker but implements neither of the two ways the MySQL
protocol allows a client to answer it:

1. No RSA public-key exchange. When a client on a plaintext connection sends the
   request_public_key packet (0x02), ProxySQL replies with something that is not
   AuthMoreData, so the client aborts. MySQL server answers this request
   (caching_sha2_password_auto_generate_rsa_keys=ON by default), which is why the same clients
   work against MySQL without TLS.
2. Cleartext-over-TLS is rejected on the Admin interface. ProxySQL does force CLIENT_SSL
   on for this plugin, so TLS is available and negotiates fine — but when the client then sends
   the cleartext password that full auth requires, the Admin module answers Access denied for
   credentials whose stored form is a caching_sha2_password hash, and for the
   mysql-monitor_* credential.

Net effect: for the affected credentials there is no working path at all — plaintext fails
with perform full authentication, TLS fails with Access denied.

Client-side error text (go-sql-driver/mysql v1.10.0, but any client hits the same wire
behaviour):

unexpected resp from server for caching_sha2_password, perform full authentication

Environment

• ProxySQL 2.7.0 (proxysql/proxysql:2.7.0) and 3.0.10 (proxysql/proxysql:3.0.10,
  admin-version = 3.0.10-426-gf5f1e14) — every result below reproduces identically on both,
  including the CLIENT_SSL/have_ssl behaviour and all six rows of the Finding 3 matrix
• mysql-have_ssl=false, matching how ProxySQL ships in the Percona XtraDB Cluster Operator
• Client: github.com/go-sql-driver/mysql v1.10.0; cross-checked with the MariaDB mysql CLI in
  the image

Finding 1 — Admin interface: a mysql_users row silently breaks a valid Admin credential

This one is fully deterministic. monitor is a valid admin-admin_credentials entry stored in
cleartext; adding a mysql_users row of the same name holding a caching_sha2 hash flips the
Admin login onto the full-auth path, which can then never complete:

SET mysql-default_authentication_plugin='caching_sha2_password';
SET mysql-have_ssl='false';
LOAD MYSQL VARIABLES TO RUNTIME;
SET admin-admin_credentials='admin:admin;monitor:monitorpass';
LOAD ADMIN VARIABLES TO RUNTIME;
INSERT INTO mysql_users(username,password,default_hostgroup,frontend,backend)
  VALUES('monitor', CACHING_SHA2_PASSWORD('monitorpass'),0,1,1);
LOAD MYSQL USERS TO RUNTIME;

Connecting to :6032 as monitor/monitorpass:

connection	result
plaintext	unexpected resp from server for caching_sha2_password, perform full authentication
tls=skip-verify	ERROR 1045 (28000): ProxySQL Error: Access denied for user 'monitor'@'127.0.0.1'

Remove the mysql_users row and both succeed. Switch the plugin to mysql_native_password and
both succeed with the row in place. Note a mysql_users credential is never valid for the Admin
interface on its own (it returns plain Access denied) — yet its mere presence changes the Admin
handshake for a same-named Admin credential.

Finding 2 — the mysql-monitor_* credential stops working on the Admin interface

mysql-monitor_username/mysql-monitor_password authenticates on :6032 under
mysql_native_password and is rejected under caching_sha2_password, with the password stored
in cleartext exactly as documented:

default plugin	monitor → :6032, plaintext
mysql_native_password	OK
caching_sha2_password	Access denied

This is what #5363 reports (k8s liveness/readiness probes failing with
Access denied for user 'monitor'@'127.0.0.1', open since February with no reply) and what the
follow-up comment on #4845 asks about. #4845 has the maintainer statement that
mysql-monitor_password must be cleartext — it is cleartext here, and it still fails.

Finding 3 — full matrix, Admin interface :6032

All rows with caching_sha2_password and mysql-have_ssl=false:

credential	plaintext	TLS
in admin-admin_credentials, no same-named mysql_users row	OK	OK
in admin-admin_credentials, shadowed by a hashed mysql_users row	full-auth error	Access denied
mysql_users only, caching_sha2 hash	full-auth error	Access denied
mysql_users only, cleartext password	Access denied	Access denied
dedicated name in admin-stats_credentials	OK	OK
(mysql_native_password) shadowed Admin credential	OK	OK

The frontend (:6033) also produces the full-auth error for these credentials, but in our
testing the outcome there varied between otherwise-identical instances, so we're only asserting
the Admin results.

Reproducer: a self-contained script that spins up a fresh container per assertion (ProxySQL
auth state turned out to be order-dependent, so shared instances give inconsistent results) and
asserts every row above — 13 checks, all passing on both 2.7.0 and 3.0.10:
(attach or link the script)

Root cause in source

CLIENT_SSL is forced on whenever the default plugin is caching_sha2_password, independent of
have_ssl — lib/MySQL_Protocol.cpp:1036-1043 at tag v2.7.0, unchanged at
lib/MySQL_Protocol.cpp:1083-1090 at tag v3.0.10 and on the v3.0 branch:

if (mysql_thread___have_ssl==true || mysql_thread___default_authentication_plugin_int==2) {
    // we enable SSL for client connections for either of these 2 conditions:
    // - have_ssl is enabled
    // - default_authentication_plugin=caching_sha2_password
    mysql_thread___server_capabilities |= CLIENT_SSL;
} else {
    mysql_thread___server_capabilities &= ~CLIENT_SSL;
}

==2 is caching_sha2_password (lib/MySQL_Thread.cpp:1854-1863 at v2.7.0,
lib/MySQL_Thread.cpp:2366 at v3.0.10). The Admin interface emits its greeting through this
same function (lib/ProxySQL_Admin.cpp:5590 at v2.7.0, lib/ProxySQL_Admin.cpp:2235 at
v3.0.10), which is why :6032 behaves like :6033 here. There is no code path that serves a server RSA public key or decrypts an
RSA-encrypted password.

To be explicit: forcing CLIENT_SSL is the right call and we are not asking for it to be
reverted — it is what makes a completion path possible at all. The gap is that neither
completion path actually works.

How MySQL differs (8.0.46, verified with the same client)

MySQL 8.0.46	greeting	caching_sha2 user, plaintext connection
TLS on (default)	CLIENT_SSL=true	OK — RSA full auth, cache cleared beforehand
TLS off (--tls-version=, have_ssl=DISABLED)	CLIENT_SSL=false	OK — no TLS involved at any point

MySQL also implements the cache the plugin is named for, so full auth only happens on first
connect, after FLUSH PRIVILEGES, or after a password change.

Worth pre-empting one thing: the ERROR 2061 ... Authentication requires secure connection
people quote from the mysql CLI is not server-side TLS enforcement — it is the client
declining to fetch a public key it wasn't told to trust:

mysql --ssl-mode=DISABLED                          -> ERROR 2061 ... requires secure connection
mysql --ssl-mode=DISABLED --get-server-public-key  -> connected

Asks, in order of preference

1. Serve the RSA public key and accept an RSA-encrypted password, matching MySQL's
   --get-server-public-key / --server-public-key-path flow. This fixes non-TLS clients
   generally.
2. Fix the Admin module's full-auth completion so a cleartext password received over TLS is
   verified against the stored credential — including admin-*_credentials and
   mysql-monitor_*. This alone would make TLS a genuine workaround, which it currently isn't.
3. Stop a mysql_users row from shadowing a same-named Admin credential during the Admin
   handshake (Finding 1).
4. Failing all of the above: document the limitation — which credential classes can
   authenticate to the Admin interface under caching_sha2_password, that hashed mysql_users
   entries cannot, and that TLS does not help — and return an error naming the real cause instead
   of an unexpected packet.

Impact

caching_sha2_password is the MySQL 8.0+ default and mysql_native_password was removed in
MySQL 9.0, so users are increasingly provisioned with caching_sha2 credentials. Today the only
reliable workarounds are to keep ProxySQL's frontend on the deprecated mysql_native_password,
or to give each admin-side client a dedicated admin-*_credentials name that appears nowhere in
mysql_users. Monitoring is hit first — health probes (#5363) and metrics exporters connecting
to :6032 — but any client of the Admin interface is affected.

Related

• Monitoring failing after enabling caching_sha2_password globally #5363 — same symptom, open since 2026-02-10, no response
• ProxySQL monitor password; caching_sha2_password or mysql_native_password ? #4845 — maintainer confirms mysql-monitor_password must be cleartext; the follow-up question
  about Admin/SHA-2 support is unanswered
• Not able to use hashed password for monitor_user #4786 — hashed monitor_user password not supported (closed as "not possible at the moment")
• feat(passthrough-auth): pass-through authentication (spec + Phase 1, WIP) #5810 — pass-through authentication (merged to v3.0) intercepts the cleartext password during
  caching_sha2 full auth; it appears TLS-gated and frontend-only, so it looks like it leaves both
  gaps above open

[coderabbitai]

🔗 Related PRs

#5651 - Plugin chassis ABI + mysqlx plugin baseline [merged]
#5717 - feat(mysqlx): consolidated MySQL Router parity work + observability + behavioural validation + ASAN/TSAN plumbing [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!

[renecannao]

Hi @ademidoff .
Amazing analysis, thank you!
We clearly have actionable items!

[renecannao]

We've traced all three findings to specific code paths and split the work into tracking issues:

• caching_sha2_password: mysql-monitor_* credential cannot authenticate on the Admin interface (native-only scramble) #5986 — mysql-monitor_* on the Admin interface (your Finding 2, and Monitoring failing after enabling caching_sha2_password globally #5363). Root cause: MySQL_Protocol::PPHR_5passwordFalse_0() is the only path that authenticates the monitor credential, and it is hardcoded to the mysql_native_password SHA1 scramble with a 20-byte compare. Under caching_sha2_password the client sends a 32-byte response, so it can never match. Targets v3.0.

  Worth noting for your matrix: ordinary admin-admin_credentials are not broken under caching_sha2_password — they're stored cleartext and complete via fast auth in PPHR_6auth2(), which matches rows 1 and 5 of your Finding 3 table. The monitor credential is the only Admin-specific auth gap.

• Give Admin/stats credentials their own scope, lifting the "cannot also be in mysql_users" restriction #5987 — a mysql_users row shadowing a same-named Admin credential (your Finding 1). Admin/stats credentials and mysql_users rows share a single GloMyAuth frontend map, and MySQL_Authentication::add() overwrites both password and default_hostgroup on a name collision. We're giving Admin/stats credentials their own store rather than just reordering the inserts. Your report also led us to a second, unreported direction: SET admin-admin_credentials calls GloMyAuth->del() unconditionally by username, so changing it removes a same-named frontend user from the runtime auth map until the next LOAD MYSQL USERS TO RUNTIME. Both directions are covered. Targets v3.0.

• Feature request: serve the RSA public key so plaintext caching_sha2_password clients can complete full authentication #5988 — serving the RSA public key (your ask 1). This is the general fix, but it brings keypair generation and persistence, new global variables, a cluster-propagation decision, and packaging changes, so it's targeted at the Innovative tier (3.1.x) rather than a 3.0.x patch. In the meantime caching_sha2_password: mysql-monitor_* credential cannot authenticate on the Admin interface (native-only scramble) #5986 replaces the current unexpected-packet behaviour with an error that names the actual cause, so people stop landing on unexpected resp from server.

On CLIENT_SSL forcing — agreed, it stays as-is.

If you're able to share the reproducer script it would be useful for cross-checking, but it isn't blocking: the write-up was detailed enough for us to build the TAP coverage from it directly.

[renecannao]

@ademidoff — we worked through every finding here empirically rather than by code reading, on a debug build of v3.0 (89a29ec91), and the results split three ways. Thank you for the detail in the report; it took a couple of hours to get to the bottom of and that would not have been possible without your matrix.

Summary up front: Finding 2 is a real bug and we are fixing it. Finding 1 is documented behaviour. Ask 2 turns out to describe something that already works. There is also a genuine defect underneath all this that nobody had spotted — in CACHING_SHA2_PASSWORD() itself.

---

Finding 2 is real — #5363, fix tracked in #5986

Confirmed and reproduced 3× on consecutive cold starts. mysql-monitor_* authenticates on :6032 under mysql_native_password and is rejected under caching_sha2_password, on both plaintext and TLS, with no mysql_users row anywhere.

Root cause: that credential never reaches the code you were looking at. It is not in GloMyAuth, so it is handled solely by MySQL_Protocol::PPHR_5passwordFalse_0() (lib/MySQL_Protocol.cpp:2052), hardcoded to the native SHA1 scramble with a 20-byte memcmp. Under caching_sha2 the client sends 32 bytes, so it can never match — and it returns false without ever sending 0x04, which is why TLS does not help. Full detail in #5363.

---

Ask 2 — Admin full-auth completion already works

Fix the Admin module's full-auth completion so a cleartext password received over TLS is verified against the stored credential

We tested this directly. With a caching_sha2-hashed credential in admin-admin_credentials, a cold instance (persisted proxysql.db deleted), and zero mysql_users rows:

ok - cs2adm authenticated on :6032 over TLS against a $A$ hash
ok - Client_Connections_sha2cached still 0 -> that was a FULL AUTH, not a cache hit
ok - wrong password rejected over TLS
ok - second TLS connection succeeded; counter rose to 2 -> repeat used the cache

The counter is what makes this conclusive. ProxySQL caches the cleartext recovered by a successful full auth and reuses it; every cache hit increments stats_mysql_global.Client_Connections_sha2cached. A login that succeeds while that counter is still 0 cannot have come from the cache, so it must have completed a full auth. PPHR_sha2full() verified the cleartext against sha256_crypt_r() and returned true.

That cache also explains the one thing you could not pin down — "the outcome there varied between otherwise-identical instances". The first connection after a LOAD MYSQL USERS TO RUNTIME requires full auth; once it succeeds the cleartext is cached and later connections take the fast-auth path, including on plaintext. Instances in different cache states give different answers. It is not nondeterminism.

So the Access denied you saw over TLS was not an authentication failure. Authentication succeeded and the connection was rejected afterwards by the hostgroup check in MySQL_Session.cpp — the effective default_hostgroup was the mysql_users row's 0 rather than ADMIN_HOSTGROUP. Authorization, not authentication.

---

Finding 1 — documented behaviour

A mysql_users row and an admin credential of the same name is a configuration that is explicitly not supported:

• admin-admin_credentials and admin-stats_credentials: "users defined in admin-admin_credentials or admin-stats_credentials cannot be used also in mysql_users table."
• mysql-monitor_username: "The user specified in mysql-monitor_username CANNOT be used in mysql_users."
• mysql_users: "Note: users in mysql_users shouldn't be used also for admin-admin_credentials and admin-stats_credentials."

Both credential classes share one GloMyAuth frontend map keyed by username, which is why the row overwrites the admin credential's password and hostgroup. That said — we agree the failure mode is unfriendly, and rather than just police the restriction we are going to remove it: #5987 now tracks giving admin/stats credentials their own scope so the collision becomes impossible in both directions.

Given Findings 1 and 2, most of the Finding 3 matrix follows from those two causes rather than from six independent behaviours.

---

Ask 1 — RSA public key: agreed, but a feature request

Your analysis is correct and we are not disputing any of it. There is no RSA path, and a plaintext client that answers 0x04 with request_public_key has that byte consumed as if it were the password. Tracked in #5988 as a feature request rather than a bug, targeted at the Innovative tier — it brings keypair generation and persistence, new global variables, a cluster-propagation decision and packaging changes, which do not belong in a stable-tier patch release.

Also confirming your aside: the ERROR 2061 ... Authentication requires secure connection is indeed the client refusing, not server-side enforcement. We see exactly the split you describe between --ssl-mode=DISABLED and --ssl-mode=DISABLED --get-server-public-key.

---

Ask 4 — already documented

Beyond the links above, mysql-default_authentication_plugin documents that setting it to caching_sha2_password enables mysql-have_ssl, which is the CLIENT_SSL behaviour you traced. We will improve the error text so the RSA dead end names its cause instead of surfacing as an unexpected packet.

---

The defect underneath: CACHING_SHA2_PASSWORD()

Worth reporting back, because it is the one thing that genuinely makes caching_sha2 Admin credentials unreliable, and it is not what anyone was looking for.

CACHING_SHA2_PASSWORD() draws its 20-byte salt from an unrestricted byte range. Measured over 3000 distinct hashes:

salt contains	share
;	14.1%
:	14.9%
; or :	26.9%

admin-admin_credentials is a ;-separated, :-delimited string, so roughly 27% of generated hashes are silently split into a bogus credential at LOAD ADMIN VARIABLES TO RUNTIME. The variable still shows all 70 bytes; nothing errors; the login just fails with a generic Access denied. Same command, run twice, gives a working credential and a broken one depending on the random salt.

Tracked separately. Existing stored hashes keep verifying, since verification never inspects the salt.

---

Where this leaves the issue

	outcome
Finding 1	documented behaviour; restriction to be lifted via #5987
Finding 2	real bug → #5363, fix in #5986
Finding 3	follows from the above
Ask 1	feature request → #5988
Ask 2	already works (evidence above)
Ask 3	documented; being lifted → #5987
Ask 4	largely documented; error text to improve

Both reproductions exist as standalone, self-verifying scripts and will land under test/repro/ so anyone can re-run them.

Your report was careful and it found a real bug that had been open since February with no reply. The parts that turned out differently were reasonable readings of genuinely confusing behaviour — particularly the cleartext cache, which is invisible from the outside and which we should document.

[renecannao]

For reference, the CACHING_SHA2_PASSWORD() salt defect described above is now tracked as #5989.