[#MXS-4262] Maxscale fails to identify correct access permissions for the xpand user

When a user who has restricted domain/host permissions, maxscale does not identify such permissions correct and allows login. However Xpand rejects such a connection on subsequent command executions. This is without proxy protocol since that feature was not available in 6.4.1 or before.

Example:
Create a following user on Xpand with his grants. Note that this user has permission to connect only from host '10.2.14.193'.

MySQL [(none)]> grant all on *.* to t1@'10.2.14.193' identified by 'Sproutr0x#';

Query OK, 0 rows affected (0.06 sec)

From host 10.2.14.193, try a connection to Xpand via maxscale (karma195 in this case):

[root@vqc008a tools]# mysql -h karma195 -u t1 -p'Sproutr0x#'

Welcome to the MariaDB monitor.  Commands end with ; or \g.

Your MySQL connection id is 1

Server version: 5.0.45-Xpand-6.0.5

Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

MySQL [(none)]> show databases;

ERROR 2006 (HY000): MySQL server has gone away

No connection. Trying to reconnect...

Connection id:    2

Current database: *** NONE ***

ERROR 1927 (HY000): Authentication to '@@Backend-Monitor:node-1' failed: 1045, #HY000: [39936] Access denied: for user 't1'@'' (using password: YES) (@@Backend-Monitor:node-1)

MySQL [(none)]> exit

Bye

From logs its evident that Maxscale allowed this connection because it found the record for this user (With correct permission) in the system.users file:

2022-08-26 17:51:05   info   : (1) Found matching user 't1'@'10.2.14.193' for client 't1'@'10.2.14.193' with sufficient privileges.

2022-08-26 17:51:05   info   : (1) [readconnroute] (Read-Only-Service); New session for server @@Backend-Monitor:node-1. Connections : 1

2022-08-26 17:51:05   info   : (1) Started Read-Only-Service client session [1] for 't1' from 10.2.14.193

2022-08-26 17:51:05   info   : (1) Connected to '@@Backend-Monitor:node-1' with thread id 52225

Maybe it was too soon since this connection should have failed at the next check (i.e. select @@version_comment). For xpand, this connection originated from t1@'' which does not have permissions in system.users and hence it rejected the connection.

2022-08-26 17:51:05   info   : (1) (@@Backend-Monitor:node-1); Storing COM_QUERY while in state 'Authenticating': select @@version_comment limit 1

2022-08-26 17:51:05   error  : (1) Authentication to '@@Backend-Monitor:node-1' failed: 1045, #HY000: [39936] Access denied: for user 't1'@'' (using password: YES)

2022-08-26 17:51:05   info   : (1) [readconnroute] (Read-Only-Service); Server '@@Backend-Monitor:node-1' failed

2022-08-26 17:51:05   info   : Stopped Read-Only-Service client session [1]

For user, it subsequently failed when show database command arrives:

2022-08-26 17:52:27   info   : (2) Found matching user 't1'@'10.2.14.193' for client 't1'@'10.2.14.193' with sufficient privileges.

2022-08-26 17:52:27   info   : (2) [readconnroute] (Read-Only-Service); New session for server @@Backend-Monitor:node-1. Connections : 1

2022-08-26 17:52:27   info   : (2) Started Read-Only-Service client session [2] for 't1' from 10.2.14.193

2022-08-26 17:52:27   info   : (2) [readconnroute] (Read-Only-Service); Routed [COM_QUERY] to '@@Backend-Monitor:node-1' show databases

2022-08-26 17:52:27   info   : (2) (@@Backend-Monitor:node-1); Storing COM_QUERY while in state 'Handshaking': show databases

2022-08-26 17:52:27   info   : (2) Connected to '@@Backend-Monitor:node-1' with thread id 54273

2022-08-26 17:52:27   error  : (2) Authentication to '@@Backend-Monitor:node-1' failed: 1045, #HY000: [39936] Access denied: for user 't1'@'' (using password: YES)

2022-08-26 17:52:27   info   : (2) [readconnroute] (Read-Only-Service); Server '@@Backend-Monitor:node-1' failed

2022-08-26 17:52:27   info   : Stopped Read-Only-Service client session [2]

We should fix this since this does not result in a correct user behavior.

Complete maxscale logs attached.

[MXS-4262] Maxscale fails to identify correct access permissions for the xpand user Created: 2022-08-26 Updated: 2022-09-05 Resolved: 2022-09-05
Status:	Closed
Project:	MariaDB MaxScale
Component/s:	xpandmon
Affects Version/s:	6.2.4, 6.4.1
Fix Version/s:	6.4.1

[MXS-4262] Maxscale fails to identify correct access permissions for the xpand user Created: 2022-08-26 Updated: 2022-09-05 Resolved: 2022-09-05