A user with just EXECUTE on PROCEDURE privileges to the database, failing to connect via maxscale with database name mentioned.

This is with the MaxScale 2.4.13/2.5.5 and MariaDB server 10.5.5, a user with only EXECUTE to PROCEDURE privileges unable to connect via maxscale with database name mentioned explicitly.

Same connection to the Direct MariaDB server IP, is able to access and execute the SP.

The customer reported this saying after upgrading to the latest MaxScale 2.4.13, their application running on Python/Nodejs are not able to connect the database via MaxScale. They have the below mentioned user with similar privileges of EXECUTE to PROCEDURE only and in their application uses the database name explicitly while making the connection.

As the connection to direct MariaDB server is working fine, raised this bug to fix the case, so that via maxscale as well it should connect the database.

Here are the repro steps:

1. Setup MaxScale 2.4.13/2.5.5 and MariaDB server 10.5.5, create following database/table/SP

create database db1;
use db1
CREATE TABLE foo (a int);
DELIMITER //
CREATE PROCEDURE foobar()
BEGIN
SELECT * FROM foo;
END //
DELIMITER ;

2. At the MariaDB server, create a user with below privileges

create user 'user1'@'%' identified by 'suresh';
create role user1_role;
grant user1_role to 'user1'@'%';
GRANT EXECUTE ON PROCEDURE `db1`.`foobar` TO `user1_role`;
SET DEFAULT ROLE user1_role FOR 'user1'@'%';

Or (without role)
create user 'user1'@'%' identified by 'suresh';
GRANT EXECUTE ON PROCEDURE `db1`.`foobar` TO 'user1'@'%';

3. Try to connect the database via MaxScale with database name specified

mysql -h<maxscale ip> -P <port> -u user1 -p suresh <database name> ## This gives error as access denied.

4. Try to connect the database directly to the server with database name specified

mysql -h<server ip> -P <port> -u user1 -p suresh <database name> ## This connects and can execute SP.

Note: We can connect via MaxScale as well without mentioning the database name

mysql -h<maxscale ip> -P <port> -u user1 -p suresh ## Database name not specified while connecting, it works.

Test Results:
=============
[root@node4 ~]# maxctrl --version
2.5.5

[root@node4 ~]# maxctrl list servers
┌────────┬────────────────┬──────┬─────────────┬─────────────────────────┬──────┐
│ Server │ Address │ Port │ Connections │ State │ GTID │
├────────┼────────────────┼──────┼─────────────┼─────────────────────────┼──────┤
│ pidb01 │ 192.168.81.193 │ 3306 │ 0 │ Slave, Synced, Running │ │
├────────┼────────────────┼──────┼─────────────┼─────────────────────────┼──────┤
│ pidb02 │ 192.168.81.192 │ 3306 │ 0 │ Master, Synced, Running │ │
├────────┼────────────────┼──────┼─────────────┼─────────────────────────┼──────┤
│ pidb03 │ 192.168.81.194 │ 3306 │ 0 │ Slave, Synced, Running │ │
└────────┴────────────────┴──────┴─────────────┴─────────────────────────┴──────┘
[root@node4 ~]#

[root@node4 ~]# maxctrl list services
┌────────────────────┬────────────────┬─────────────┬───────────────────┬────────────────────────┐
│ Service │ Router │ Connections │ Total Connections │ Servers │
├────────────────────┼────────────────┼─────────────┼───────────────────┼────────────────────────┤
│ Read-Write-Service │ readwritesplit │ 0 │ 0 │ pidb01, pidb02, pidb03 │
└────────────────────┴────────────────┴─────────────┴───────────────────┴────────────────────────┘

[root@node4 ~]# maxctrl list listeners Read-Write-Service
┌─────────────────────┬──────┬────────────────┬─────────┬────────────────────┐
│ Name │ Port │ Host │ State │ Service │
├─────────────────────┼──────┼────────────────┼─────────┼────────────────────┤
│ Read-Write-Listener │ 3306 │ 192.168.81.152 │ Running │ Read-Write-Service │
└─────────────────────┴──────┴────────────────┴─────────┴────────────────────┘
[root@node4 ~]#

1. 1. Connection to database via MaxScale(192.168.81.152) without specifying the database name:

[root@node4 ~]# mysql -h192.168.81.152 -uuser1 -psuresh
Welcome to the MariaDB monitor. Commands end with ; or \g.
Your MariaDB connection id is 3
Server version: 10.5.5-MariaDB-log MariaDB Server

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

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

MariaDB [(none)]> use db1
Database changed
MariaDB [db1]> call foobar();
------

------

------
1 row in set (0.002 sec)

Query OK, 0 rows affected (0.002 sec)

MariaDB [db1]> exit
Bye

1. 1. Connection to database via MaxScale(192.168.81.152) specifying explicitly the database name: (Throws access denied error and maxscale logs noted user not found)

[root@node4 ~]# mysql -h192.168.81.152 -uuser1 -psuresh db1
ERROR 1044 (42000): Access denied for user 'user1'@'192.168.81.152' to database 'db1'
[root@node4 ~]#

maxscale logs:

At Maxscale 2.5.5
==
2020-11-20 06:42:17 warning: (4) [mariadbclient] Authentication failed for user 'user1'@[192.168.81.152] to service 'Read-Write-Service'. Originating listener: 'Read-Write-Listener'. MariaDB error: 'Access denied for user 'user1'@'192.168.81.152' to database 'db1''.

At Maxscale 2.4.13
===
2020-11-20 07:01:19 warning: (1) [MariaDBAuth] Read-Write-Service: login attempt for user 'user1'@[192.168.81.152]:58176 to database 'db1', authentication failed. User not found.

1. 1. Connection to database direct to the server IP (192.168.81.194) mentioning database name (Works fine, can execute the SP)

[root@node4 ~]# mysql -h192.168.81.194 -uuser1 -psuresh db1
Welcome to the MariaDB monitor. Commands end with ; or \g.
Your MariaDB connection id is 195
Server version: 10.5.5-MariaDB-log MariaDB Server

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

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

MariaDB [db1]> call foobar();
------

------

------
1 row in set (0.058 sec)

Query OK, 0 rows affected (0.058 sec)

MariaDB [db1]> exit
Bye