Feature request: serve the RSA public key so plaintext caching_sha2_password clients can complete full authentication

[renecannao]

Task: implement caching_sha2_password RSA public-key exchange (target v3.1.x)

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

• Create branch feature/caching-sha2-rsa-public-key from v3.0
• PR target: v3.0, gated behind PROXYSQL31 — this feature ships in the Innovative tier (v3.1.x),
  not in a v3.0.x patch release. See "Why v3.1.x" below.
• If upstream changes are needed: git rebase, NOT git merge

Context

Parent issue: #5985, ask 1 — the general fix.

When mysql-default_authentication_plugin='caching_sha2_password' and ProxySQL cannot complete fast
auth, it sends the perform full authentication marker (0x04). The MySQL protocol allows a client
to answer that in one of two ways: send the cleartext password over TLS, or request the server's RSA
public key and send the password RSA-encrypted. ProxySQL implements only the first. A client on a
plaintext connection sends request_public_key (single byte 0x02) and gets something that is not
AuthMoreData, so it aborts.

MySQL answers this request out of the box —
caching_sha2_password_auto_generate_rsa_keys is ON by default — which is why the same clients
work against MySQL without TLS. caching_sha2_password is the MySQL 8.0+ default and
mysql_native_password was removed in MySQL 9.0, so this affects a growing share of deployments.

Sibling issues, both targeting v3.0 and both landing before this one:

• caching_sha2_password: mysql-monitor_* credential cannot authenticate on the Admin interface (native-only scramble) #5986 — mysql-monitor_* under caching_sha2 on the Admin interface
• Give Admin/stats credentials their own scope, lifting the "cannot also be in mysql_users" restriction #5987 — Admin/stats credential namespace separation

Why v3.1.x and not a v3.0.x patch

The protocol state machine change is contained (see below). What makes this an Innovative-tier
feature is everything around it: RSA keypair generation and on-disk persistence, new global
variables, a decision about ProxySQL Cluster propagation, and packaging. That is new crypto and new
configuration surface, which does not belong in a stable-tier patch release.

Do not frame this as a state-machine rewrite when scoping it. The state machine is the easy part.

Research: current behaviour

lib/MySQL_Protocol.cpp:1685 MySQL_Protocol::PPHR_1() advances
switching_auth_stage from 4 to 5 and then unconditionally treats the whole packet body as the
cleartext password:

if ((*myds)->switching_auth_stage == 4) {
    (*myds)->switching_auth_stage = 5;
}
...
vars1.pass_len = strlen((char *)pkt);
vars1.pass = (unsigned char *)malloc(vars1.pass_len+1);
memcpy(vars1.pass, pkt, vars1.pass_len);

There is no discriminator for the one-byte 0x02 request. It is consumed as a one-character
password, verification fails, and an error is returned where the client expects AuthMoreData.

#5986 adds a named error on that branch as a stopgap. This issue replaces that branch
with the real implementation — expect a small conflict there and resolve it by deleting the
stopgap.

No RSA key material exists anywhere in the tree today: grep -rn "RSA\|EVP_PKEY\|PEM_write" lib/ include/ src/ finds only an unrelated PEM_read_bio_X509 in lib/ProxySQL_Admin_Stats.cpp:2801.

The verifier at the end of the flow is already correct and needs no change:
lib/MySQL_Protocol.cpp:2309 PPHR_sha2full() at switching_auth_stage == 5 runs
sha256_crypt_r() against the stored $A$ digest. It simply never receives a usable password on
the plaintext path today.

Implementation details

1. Key material. Generate an RSA-2048 keypair alongside the existing self-signed certificate
bootstrap in src/proxy_tls.cpp:224 (which already manages proxysql-key.pem,
proxysql-cert.pem, proxysql-ca.pem in the datadir). Persist as a new pair of files in the same
directory with the same permissions discipline. Generate on first start if absent; never regenerate
silently on restart, or every client that pinned the key breaks.

2. Global variables. Mirror MySQL's names and semantics:

• mysql-caching_sha2_password_auto_generate_rsa_keys (bool, default true)
• mysql-caching_sha2_password_private_key_path (string, default the generated path)
• mysql-caching_sha2_password_public_key_path (string, default the generated path)

Register in lib/MySQL_Thread.cpp following the existing string/bool variable pattern.

3. Protocol. In PPHR_1(), before the packet is consumed as a password: when
switching_auth_stage == 5, the payload is exactly one byte, and that byte is 0x02, reply with
AuthMoreData — 0x01 followed by the PEM-encoded public key — leave the stage such that the next
client packet is understood as an RSA-encrypted password, and return "more data needed" without
advancing to verification.

This needs a new stage value (e.g. 6) to distinguish "waiting for cleartext" from "waiting for
RSA-encrypted password"; do not overload stage 5. Every switching_auth_stage == 5 comparison in
lib/MySQL_Protocol.cpp must be audited for whether it should also accept the new stage — there are
comparisons in PPHR_verify_sha2(), PPHR_sha2full(), PPHR_passthrough_init() and
PPHR_verify_password().

4. Decryption. On the packet that follows, RSA-OAEP-decrypt with the private key, then XOR-unmask
the result with (*myds)->myconn->scramble_buff (the client XORs the null-terminated password with
the scramble before encrypting), and feed the recovered cleartext into the existing stage-5
verification path. Reject oversized payloads before calling into OpenSSL.

5. Cluster. Decide and document whether the keypair propagates across a ProxySQL Cluster. It
does not have to — each node can serve its own key, since the key is per-connection and not part of
any persistent client state — but the decision must be explicit in the PR description, because
operators behind a load balancer will ask.

Security requirements

• The private key must never be logged, never exposed through any Admin table or variable getter,
  and never included in a support bundle. Only the public key is readable.
• File permissions on the private key must match what src/proxy_tls.cpp already applies to
  proxysql-key.pem.
• Use RSA-OAEP padding, matching MySQL. Reject PKCS#1 v1.5.
• Bound the encrypted payload length before decryption.
• The recovered cleartext must be zeroed after use, following the handling already applied to
  passthrough_cleartext in lib/MySQL_Protocol.cpp (search memset near passthrough_cleartext).

Build & verification

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

# also confirm the stable tier still builds with the feature compiled out
make clean
make debug -j$(nproc)                        # must exit 0

Test requirements

New TAP test covering, with mysql-have_ssl=false and
mysql-default_authentication_plugin='caching_sha2_password':

1. Plaintext connection, client requests the public key, full auth completes, session works.
2. Plaintext connection, wrong password — auth fails with a proper Access denied, not a protocol
   error.
3. TLS connection — the existing cleartext-over-TLS path still completes and is not routed through
   RSA.
4. mysql-caching_sha2_password_auto_generate_rsa_keys=false with no key files present — the server
   returns a clear error and does not crash.
5. The private key is not readable through the Admin interface.

Because the client side must exercise --get-server-public-key semantics, verify against both the
MySQL CLI and a driver that does this natively; the reporter on #5985 used
github.com/go-sql-driver/mysql v1.10.0 and their reproducer is a good starting point.

DO NOT

• Do not enable this in the stable tier. It must be behind PROXYSQL31.
• Do not change the CLIENT_SSL forcing at lib/MySQL_Protocol.cpp:1083. It stays.
• Do not modify PPHR_sha2full()'s verification logic — it is correct; it only needs to be reached.
• Do not overload switching_auth_stage == 5 for the RSA wait state.
• Do not regenerate the keypair on every start.
• Do not expose the private key through any Admin table, variable, or log line.
• Do not "fix" a link error like 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:1685 PPHR_1() — where the 0x02 discriminator goes.
• lib/MySQL_Protocol.cpp:2309 PPHR_sha2full() — the existing, correct stage-5 verifier.
• lib/MySQL_Protocol.cpp:2375 PPHR_passthrough_init() — the existing example of replying
  AuthMoreData and returning for another round trip; follow its shape.
• src/proxy_tls.cpp:224 — self-signed cert generation and datadir file handling.
• lib/MySQL_Thread.cpp — global variable registration pattern.

Acceptance criteria

• PROXYSQL31=1 make debug -j$(nproc) exits 0 and plain make debug -j$(nproc) exits 0.
• All five TAP scenarios pass.
• mysql --ssl-mode=DISABLED --get-server-public-key connects successfully against a ProxySQL
  configured with caching_sha2_password and mysql-have_ssl=false.
• The reproducer from feat: caching_sha2_password: RSA public-key support for plaintext clients, and Admin credential usability #5985 passes the plaintext rows of its matrix.
• grep -rn "private_key" lib/ProxySQL_Admin*.cpp shows no path that returns key contents to a
  client.
• The PR description states the ProxySQL Cluster propagation decision and its rationale.

[coderabbitai]

🔗 Related PRs

#5567 - Add SSL/TLS traffic decryption for PostgreSQL backend connections [merged]
#5744 - fix(mysql): advertise CLIENT_PLUGIN_AUTH_LENENC_CLIENT_DATA in server greeting [merged]
#5810 - feat(passthrough-auth): pass-through authentication (spec + Phase 1, WIP) [merged]
#5945 - feat(passthrough-auth): gate to Innovative/AI tiers (refuse enable on Stable) [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!