Bug #107291 Access denied when connecting over socket
Submitted: 13 May 2022 15:43 Modified: 1 Jul 2022 7:25
Reporter: Michal Kralik Email Updates:
Status: Verified Impact on me:
None 
Category:MySQL Router Severity:S2 (Serious)
Version:8.0.29 OS:CentOS (7)
Assigned to: CPU Architecture:x86
Tags: access denied, socket

[13 May 2022 15:43] Michal Kralik
Description:
A new user cannot connect over socket, but can connect over TCP.
After connecting via TCP, the socket connection starts working.
The socket connection for read-only mode does not work at all.

Restarting MySQL Router does not help.

MySQL 8.0.29
MySQL Router 8.0.29
CentOS 7 (3.10.0-1160.21.1.el7.x86_64)

MySQL running in InnoDB Cluster with 2 replicas

{
    "clusterName": "mysql1",
    "defaultReplicaSet": {
        "name": "default",
        "primary": "1.2.3.4:3320",
        "ssl": "REQUIRED",
        "status": "OK_NO_TOLERANCE",
        "statusText": "Cluster is NOT tolerant to any failures.",
        "topology": {
            "1.2.3.4:3320": {
                "address": "1.2.3.4:3320",
                "memberRole": "PRIMARY",
                "mode": "R/W",
                "readReplicas": {},
                "replicationLag": null,
                "role": "HA",
                "status": "ONLINE",
                "version": "8.0.29"
            },
            "5.6.7.8:3320": {
                "address": "5.6.7.8:3320",
                "memberRole": "SECONDARY",
                "mode": "R/O",
                "readReplicas": {},
                "replicationLag": null,
                "role": "HA",
                "status": "ONLINE",
                "version": "8.0.29"
            }
        },
        "topologyMode": "Single-Primary"
    },
    "groupInformationSourceMember": "1.2.3.4:3320"
}

MySQL Router config

[DEFAULT]

user=mysqlrouter
keyring_path=/var/lib/mysqlrouter/keyring
master_key_path=/etc/mysqlrouter/mysqlrouter.key
connect_timeout=5
read_timeout=30
dynamic_state=/var/lib/mysqlrouter/state.json
client_ssl_cert=/var/lib/mysqlrouter/router-cert.pem
client_ssl_key=/var/lib/mysqlrouter/router-key.pem
client_ssl_mode=PREFERRED
server_ssl_mode=AS_CLIENT
server_ssl_verify=DISABLED
unknown_config_option=error
max_total_connections=30000

[logger]
level=INFO

[metadata_cache:bootstrap]
cluster_type=gr
router_id=3
user=router1
metadata_cluster=mysql1
ttl=0.5
auth_cache_ttl=-1
auth_cache_refresh_interval=2
use_gr_notifications=0

[routing:bootstrap_rw]
bind_address=0.0.0.0
bind_port=6446
socket=/run/mysqlrouter/mysql.sock
destinations=metadata-cache://mysql1/?role=PRIMARY
routing_strategy=first-available
protocol=classic

[routing:bootstrap_ro]
bind_address=0.0.0.0
bind_port=6447
socket=/run/mysqlrouter/mysqlro.sock
destinations=metadata-cache://mysql1/?role=SECONDARY
routing_strategy=round-robin-with-fallback
protocol=classic

[routing:bootstrap_x_rw]
bind_address=0.0.0.0
bind_port=6448
socket=/run/mysqlrouter/mysqlx.sock
destinations=metadata-cache://mysql1/?role=PRIMARY
routing_strategy=first-available
protocol=x

[routing:bootstrap_x_ro]
bind_address=0.0.0.0
bind_port=6449
socket=/run/mysqlrouter/mysqlxro.sock
destinations=metadata-cache://mysql1/?role=SECONDARY
routing_strategy=round-robin-with-fallback
protocol=x

How to repeat:
1. Connect to MySQL Router on port 6446
2. Create a new user
CREATE USER IF NOT EXISTS 'connecttest'@'%' IDENTIFIED BY 'asdasdasd1';

3. Connect via socket (the password is correct)
# mysql -u connecttest -p -S /run/mysqlrouter/mysql.sock
Enter password:
ERROR 1045 (28000): Access denied for user 'connecttest'@'host' (using password: YES)

4. Connect via TCP
# mysql -u connecttest -p -h localhost -P 6446
Enter password:
Welcome to the MySQL monitor.  Commands end with ; or \g.

5. Connect via socket - at this point the connection starts working
# mysql -u connecttest -p -S /run/mysqlrouter/mysql.sock
Enter password:
Welcome to the MySQL monitor.  Commands end with ; or \g.

6. Connect via ro socket - this never works
# mysql -u connecttest -p -S /run/mysqlrouter/mysqlro.sock
Enter password:
ERROR 1045 (28000): Access denied for user 'connecttest'@'host' (using password: YES)
[20 May 2022 13:42] MySQL Verification Team
Hi Mr. Kralik,

Can you please give us the reasons why are you trying to connect our client CLI to the read-only socket of the router.  You have to use options that would change nothing at all, which is not how our CLI works by default.

Next, please check the privileges of that user that is attempting this connection ....

Last, but not least, describe to us how is router connected to the cluster and are all replicas configured for read and write operations.
[21 Jun 2022 1:00] Bugs System
No feedback was provided for this bug for over a month, so it is
being suspended automatically. If you are able to provide the
information that was originally requested, please do so and change
the status of the bug back to "Open".
[24 Jun 2022 7:04] Michal Kralik
Sorry, for some reason I did not receive email notification for your follow up.

We use MySQL router to connect to an InnoDB Cluster.
We'd like to use a socket to connect to MySQL router instead of TCP connections.

The user has proper privileges since you can connect through TCP with the same user.

I don't understand your question regarding "connect our CLI to the read-only socket of router".
In the example I demonstrate connection with "mysql" cli to the router either via socket or TCP where the socket connection does not work but TCP does. I does not seem this is expected.

My understanding of the read-only socket of MySQL Router is that it connects you to a read-only replica of the InnoDB Cluster.

Router is connected to the cluster as described in the MySQL Router config I attached. It uses TCP connections. Let me know what debugging information you'd need here.

One replica is R/W and the other is R/O - as described in the output from MySQL replica config.
[1 Jul 2022 7:25] MySQL Verification Team
Hello Michal Kralik,

Thank you for the report and feedback.
Verified as described.

regards,
Umesh
[1 Jul 2022 7:27] MySQL Verification Team
Test details

Attachment: 107291.results (application/octet-stream, text), 14.73 KiB.

[16 Aug 2022 15:49] Jan Kneschke
Posted by developer:
 
Workaround
==========

1. caching the router's "server_ssl_mode" from "AS_CLIENT" to "PREFERRED" or,
2. start mysql client with "--ssl-mode=REQUIRED"

Root Cause
==========

1. the default authentication method caching_sha2_password requires an
   encrypted connection for the first login of an account. It creates
   fast-authentication cache entry for following logins of the same
   account which also works over plaintext connections.
   (The cache is reset with FLUSH PRIVILEGES or server restarted).

2. mysql --socket=... does NOT encrypt the connection by default,
   mysql --host=... does encrypt the connection by default.

3. router's "server_ssl_mode=AS_CLIENT" opens an encrypted connection
   to the server if the client opened an encrypted connection.

Behaviour
=========

Putting it all together,

At first login:

- "mysql --socket=..." connects to the router without encryption
- router's "server_ssl_mode=AS_CLIENT" therefore connects to the server without encryption
- server requires a secure connection as the login didn't succeed before and fails the auth

Via TCP:

- "mysql --host=..." connects to the router with encryption
- router's "server_ssl_mode=AS_CLIENT" therefore connects to the server with encryption
- server requires a secure connection and login succeeds

After a successful login via tcp:

- "mysql --socket=..." connects to the router without encryption
- router's "server_ssl_mode=AS_CLIENT" therefore connects to the server without encryption
- server finds the cached auth-data and can authenticate without encryption