MySQL 8.0 made caching_sha2_password the default authentication plugin
and MySQL 8.4 disables mysql_native_password by default (it is removed in
9.0). None of the existing mysql-check modes works with a
caching_sha2_password check user, and each of them generates an aborted
connection on the server for every check:
- pre-41 / post-41 do not set CLIENT_PLUGIN_AUTH, so the server rejects
the handshake with error 1251 ("Client does not support
authentication protocol requested by server") and the server is
marked DOWN.
- post-80 announces mysql_native_password, so the server answers with
an AuthSwitchRequest (0xFE) to caching_sha2_password. The check
accepts it as a valid reply since it is not an error packet, and
reports the server UP. But the COM_QUIT pipelined after the handshake
response is read by the server as the auth switch reply, and the
handshake is aborted with error 1156 ("Got packets out of order").
In both cases the server counts a connection error for the HAProxy host
(Aborted_connects, and the host cache handshake errors when
skip_name_resolve is OFF, which is the MySQL default, and the HAProxy
address resolves forward and backward). Once the host reaches max_connect_errors
(100 by default), the server blocks it with ER_HOST_IS_BLOCKED (1129)
and refuses every connection from it, including proxied client traffic,
until FLUSH HOSTS is issued. With post-80 this happens while the check
reported the server as healthy.
This patch adds a "post-80-sha2" mode which sets CLIENT_PLUGIN_AUTH and
announces caching_sha2_password with an empty auth response. For a
password-less caching_sha2_password account the server authenticates in
a single round trip and replies with an OK packet, then the pipelined
COM_QUIT closes the session cleanly, so no connection error is recorded:
CREATE USER 'haproxy'@'%' IDENTIFIED WITH caching_sha2_password BY '';
option mysql-check user haproxy post-80-sha2
Tested against MySQL 8.4.13 (skip_name_resolve=OFF,
max_connect_errors=10) and Cloud SQL for MySQL 8.4.11 with a
caching_sha2_password check user, one check every 2s: with post-80-sha2
the check is L7OK and neither Aborted_connects nor the host cache error
counters move. With post-41 or post-80 every check adds one aborted
connection and one host cache handshake error, and the HAProxy host is
blocked (1129) after 10 checks, i.e. 20 seconds.
The documentation and the mysql-check regtest are updated accordingly.
Signed-off-by: Alex Joseph <[email protected]>
---
Not subscribed to the list, please keep me CC'd.
Tested on MySQL 8.4.13 (skip_name_resolve=OFF);
reg-tests/checks: 27/27 pass.
doc/configuration.txt | 19 +++++++++++-
reg-tests/checks/mysql-check.vtc | 25 +++++++++++++++
src/tcpcheck.c | 52 ++++++++++++++++++++++++++++++--
3 files changed, 93 insertions(+), 3 deletions(-)
diff --git a/doc/configuration.txt b/doc/configuration.txt
index 2698b1462..3e684fa62 100644
--- a/doc/configuration.txt
+++ b/doc/configuration.txt
@@ -11952,7 +11952,7 @@ no option logasap
logging.
-option mysql-check [ user <username> [ { post-41 | pre-41 | post-80 } ] ]
+option mysql-check [ user <username> [ { post-41 | pre-41 | post-80 |
post-80-sha2 } ] ]
Use MySQL health checks for server testing
May be used in the following contexts: tcp
@@ -11971,6 +11971,23 @@ option mysql-check [ user <username> [ { post-41 |
pre-41 | post-80 } ] ]
where the health check user is created with
mysql_native_password
authentication. Example:
CREATE USER 'haproxy'@'%' IDENTIFIED WITH
mysql_native_password BY '';
+ post-80-sha2
+ Send post v8.0 client compatible checks with CLIENT_PLUGIN_AUTH
+ capability set and caching_sha2_password as the authentication
+ plugin. Use this option when connecting to MySQL 8.0+ servers
+ where the health check user is created with
caching_sha2_password
+ authentication, which is the default plugin since MySQL 8.0 and
+ the only one enabled by default since MySQL 8.4. Example:
+ CREATE USER 'haproxy'@'%' IDENTIFIED WITH
caching_sha2_password BY '';
+
+ The authentication plugin announced by the check must match the one of the
+ check user. Otherwise the server either rejects the handshake (error 1251
+ with pre-41 and post-41) or replies with an authentication method switch
+ request that the check cannot answer (post-80, in which case the check is
+ still reported as passed). In both cases the server aborts the handshake and
+ counts it as a connection error for the HAProxy host. Once the host reaches
+ MySQL "max_connect_errors", the server refuses all connections from it,
+ including proxied client connections, until "FLUSH HOSTS" is issued.
If you specify a username, the check consists of sending two MySQL packet,
one Client Authentication packet, and one QUIT packet, to correctly close
diff --git a/reg-tests/checks/mysql-check.vtc b/reg-tests/checks/mysql-check.vtc
index f2f642800..42ebbb989 100644
--- a/reg-tests/checks/mysql-check.vtc
+++ b/reg-tests/checks/mysql-check.vtc
@@ -36,6 +36,12 @@ server s5 {
sendhex
"67000002FFE304436C69656E7420646F6573206E6F7420737570706F72742061757468656E7469636174696F6E2070726F746F636F6C20726571756573746564206279207365727665723B20636F6E736964657220757067726164696E67204D7953514C20636C69656E74"
} -start
+server s6 {
+ sendhex
"4A0000000A382E302E3139000A0000006F3C025E6249410D00FFFFFF0200FFC715000000000000000000007C182159106E2761144322200063616368696E675F736861325F70617373776F726400"
+ recv 69
+ sendhex "0700000200000002000000"
+} -start
+
syslog S1 -level notice {
recv
expect ~ "[^:\\[ ]\\[${h1_pid}\\]: Health check for server be1/srv
succeeded, reason: Layer7 check passed.+info: \"8.0.19\".+check duration:
[[:digit:]]+ms, status: 1/1 UP."
@@ -61,6 +67,11 @@ syslog S5 -level notice {
expect ~ "[^:\\[ ]\\[${h1_pid}\\]: Health check for server be5/srv failed,
reason: Layer7 wrong status.+code: 1251.+info: \"Client does not support
authentication protocol requested by server; consider upgrading MySQL
client\".+check duration: [[:digit:]]+ms, status: 0/1 DOWN."
} -start
+syslog S6 -level notice {
+ recv
+ expect ~ "[^:\\[ ]\\[${h1_pid}\\]: Health check for server be6/srv
succeeded, reason: Layer7 check passed.+info: \"8.0.19\".+check duration:
[[:digit:]]+ms, status: 1/1 UP."
+} -start
+
haproxy h1 -conf {
global
@@ -104,6 +115,12 @@ haproxy h1 -conf {
option mysql-check user pouet post-41
server srv ${s5_addr}:${s5_port} check inter 1s rise 1 fall 1
+ backend be6
+ log ${S6_addr}:${S6_port} daemon
+ option log-health-checks
+ option mysql-check user user post-80-sha2
+ server srv ${h1_mysql3_addr}:${h1_mysql3_port} check inter 1s rise 1
fall 1
+
listen mysql1
bind "fd@${mysql1}"
tcp-request inspect-delay 100ms
@@ -118,6 +135,13 @@ haproxy h1 -conf {
tcp-request content reject
server srv ${s3_addr}:${s3_port}
+ listen mysql3
+ bind "fd@${mysql3}"
+ tcp-request inspect-delay 100ms
+ tcp-request content accept if { req.len eq 69 } { req.payload(0,69) -m
bin
"3C000001008208000080000121000000000000000000000000000000000000000000000075736572000063616368696E675F736861325F70617373776F7264000100000001"
}
+ tcp-request content reject
+ server srv ${s6_addr}:${s6_port}
+
} -start
syslog S1 -wait
@@ -125,3 +149,4 @@ syslog S2 -wait
syslog S3 -wait
syslog S4 -wait
syslog S5 -wait
+syslog S6 -wait
diff --git a/src/tcpcheck.c b/src/tcpcheck.c
index 1e245e0c8..69dfe535c 100644
--- a/src/tcpcheck.c
+++ b/src/tcpcheck.c
@@ -4937,6 +4937,46 @@ static int do_parse_mysql_check_opt(char **args, int
cur_arg, struct proxy *curp
"01" /* COM_QUIT command */
};
+ /* MySQL >=8.0 client Authentication packet with CLIENT_PLUGIN_AUTH
capability
+ * and caching_sha2_password as the auth plugin. This is the default
+ * authentication plugin since MySQL 8.0, and the only one enabled by
default
+ * since MySQL 8.4 (mysql_native_password is disabled and removed in
9.0).
+ *
+ * When the client does not announce the plugin used by the account, the
+ * server answers the handshake response with an AuthSwitchRequest.
Since
+ * the COM_QUIT packet is pipelined right after the handshake response,
the
+ * server then reads it as the auth switch reply, fails the handshake
with a
+ * "packets out of order" error and counts it as a connection error for
the
+ * client host in the host cache. After max_connect_errors such checks,
the
+ * server blocks the HAProxy host (ER_HOST_IS_BLOCKED) and every
connection
+ * from it, including proxied client traffic, is refused.
+ *
+ * Announcing caching_sha2_password with an empty auth response lets the
+ * server authenticate a password-less caching_sha2_password account in
a
+ * single round trip: it answers with an OK packet and the pipelined
COM_QUIT
+ * closes the session cleanly, so no connection error is recorded.
+ *
+ * Client capabilities: 0x00088200 (little-endian: 00820800)
+ * - CLIENT_PROTOCOL_41 (0x00000200)
+ * - CLIENT_SECURE_CONNECTION (0x00008000)
+ * - CLIENT_PLUGIN_AUTH (0x00080000)
+ */
+ static char mysql80_sha2_req[] = {
+ "%[var(check.header),hex]" /* 3 bytes for the packet length
and 1 byte for the sequence ID */
+ "00820800" /* client capabilities with
CLIENT_PLUGIN_AUTH */
+ "00800001" /* max packet */
+ "21" /* character set (UTF-8) */
+ "000000000000000000000000" /* 23 bytes, all zeroes */
+ "0000000000000000000000"
+ "%[var(check.username),hex]00" /* the username */
+ "00" /* auth response length (0 = no
password) */
+ "63616368696e675f736861325f" /* auth plugin name:
"caching_sha2_password\0" */
+ "70617373776f726400"
+ "010000" /* packet length */
+ "00" /* sequence ID */
+ "01" /* COM_QUIT command */
+ };
+
struct tcpcheck *tc = &curpx->tcpcheck;
struct tcpcheck_rule *chk;
struct tcpcheck_var *var = NULL;
@@ -4986,8 +5026,13 @@ static int do_parse_mysql_check_opt(char **args, int
cur_arg, struct proxy *curp
packetlen = userlen + 7 + 27 + 22;
mysql_req = mysql80_req;
}
+ else if (strcmp(args[cur_arg+2], "post-80-sha2") == 0) {
+ /* post-80-sha2: CLIENT_PLUGIN_AUTH +
caching_sha2_password (22 bytes) */
+ packetlen = userlen + 7 + 27 + 22;
+ mysql_req = mysql80_sha2_req;
+ }
else {
- ha_alert("parsing [%s:%d] : keyword '%s' only supports
'post-41', 'pre-41' and 'post-80' (got '%s').\n",
+ ha_alert("parsing [%s:%d] : keyword '%s' only supports
'post-41', 'pre-41', 'post-80' and 'post-80-sha2' (got '%s').\n",
file, line, args[cur_arg], args[cur_arg+2]);
goto error;
}
@@ -5607,6 +5652,7 @@ int proxy_parse_mysql_check_opt(char **args, int cur_arg,
struct proxy *curpx, c
static char mysql40_rsname[] = "*mysql40-check";
static char mysql41_rsname[] = "*mysql41-check";
static char mysql80_rsname[] = "*mysql80-check";
+ static char mysql80_sha2_rsname[] = "*mysql80-sha2-check";
char *mysql_rsname = "*mysql-check";
struct tcpcheck_ruleset *rs = NULL;
int err_code = 0;
@@ -5639,8 +5685,10 @@ int proxy_parse_mysql_check_opt(char **args, int
cur_arg, struct proxy *curpx, c
mysql_rsname = mysql40_rsname;
else if (strcmp(args[cur_arg+2], "post-80") == 0)
mysql_rsname = mysql80_rsname;
+ else if (strcmp(args[cur_arg+2], "post-80-sha2") == 0)
+ mysql_rsname = mysql80_sha2_rsname;
else {
- ha_alert("parsing [%s:%d] : keyword '%s' only supports
'post-41', 'pre-41' and 'post-80' (got '%s').\n",
+ ha_alert("parsing [%s:%d] : keyword '%s' only supports
'post-41', 'pre-41', 'post-80' and 'post-80-sha2' (got '%s').\n",
file, line, args[cur_arg], args[cur_arg+2]);
err_code |= ERR_ALERT | ERR_ABORT;
goto out;
--
2.56.0.385.gac671c819a-goog
