User acces rights with MySQL router

[Silvio Schloeffel]

Hello,

I'm not sure if this is the right forum, could also be an admin issue.

We would like to realise access to our Innodb cluster with several external routers. This means that no adjustments to the software running on the distributed servers are necessary and the connection to the respective read/write systems can be realised more easily.

The aim is that the users of the respective software can only access the router from localhost.

With a ‘normal’ DB server, I would therefore use users of the type ‘user’@‘ip_or_hostname’ to restrict access.

I can't realise this with the router(s).
For testing I created 5 users with different rules -> test_nutzer_xxx

'test_nutzer'@'%';
'test_nutzer_1'@'localhost';
'test_nutzer_2'@'router-0%';
'test_nutzer_3'@'10.234.16.40';
`test_nutzer_4`@`router-01`;

My router server has the ip 10.234.16.40 and is know as router-01.

Test 1 with test_nutzer:
router local:
[xxx@router-01 ~]$mysql -u test_nutzer -p -h 127.0.0.1 -P 6446
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 2464332
Server version: 8.0.39 MySQL Community Server - GPL

connect to router remote system:
xxx@fedora:~$ mysql -u test_nutzer -p -h 10.234.16.46 -P 6446
Enter password:
Welcome to the MariaDB monitor. Commands end with ; or \g.
Your MySQL connection id is 2464506
Server version: 8.0.39 MySQL Community Server - GPL

-> works as expected


Test 2 test_nutzer_1:
router local:
[xxx@router-01 ~]$ mysql -u test_nutzer_1 -p -h 127.0.0.1 -P 6446
Enter password:
ERROR 1045 (28000): Access denied for user 'test_nutzer_1'@'router-01' (using password: YES)

connect to router remote system:
xxx@fedora:~$ mysql -u test_nutzer_1 -p -h 10.234.16.46 -P 6446
Enter password:
ERROR 1045 (28000): Access denied for user 'test_nutzer_1'@'router-01' (using password: YES)

-> also as expected because we do not connect from localhost of db server

Test 3 test_nutzer_2:
router local:
[xxx@router-01 ~]$ mysql -u test_nutzer_2 -p -h 127.0.0.1 -P 6446
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 2465339
Server version: 8.0.39 MySQL Community Server - GPL

connect to router remote system:
xxx@fedora:~$ mysql -u test_nutzer_2 -p -h 10.234.16.46 -P 6446
Enter password:
Welcome to the MariaDB monitor. Commands end with ; or \g.
Your MySQL connection id is 2465305
Server version: 8.0.39 MySQL Community Server - GPL

-> first thing I do not understand:
router local is ok the rule allow connects from user@router-0%
BUT
why can I can connect from a external system?
My thinking is that the reason is the router is rewriting the source address.

Test 4 test_nutzer_3:
router local:
[xxx@router-01 ~]$ mysql -u test_nutzer_3 -p -h 127.0.0.1 -P 6446
Enter password:
ERROR 1045 (28000): Access denied for user 'test_nutzer_3'@'router-01' (using password: YES)

connect to router remote system:
xxx@fedora:~$ mysql -u test_nutzer_3 -p -h 10.234.16.46 -P 6446
Enter password:
ERROR 1045 (28000): Access denied for user 'test_nutzer_3'@'router-01' (using password: YES)

-> only hostname based auth possible?

Test 5 test_nutzer_4 -> only to check a single hostname and not % like test 3
router local:
[xxx@router-01 ~]$ mysql -u test_nutzer_4 -p -h 127.0.0.1 -P 6446
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 2466379
Server version: 8.0.39 MySQL Community Server - GPL

connect to router remote system:
xxx@fedora:~$ mysql -u test_nutzer_4 -p -h 10.234.16.46 -P 6446
Enter password:
Welcome to the MariaDB monitor. Commands end with ; or \g.
Your MySQL connection id is 2466357
Server version: 8.0.39 MySQL Community Server - GPL

-> result is the same as test 3 - connect possible but also from a external system.
The user from the external system is detected as:
MySQL [(none)]> SELECT USER(), CURRENT_USER();
+-------------------------+-------------------------+
| USER() | CURRENT_USER() |
+-------------------------+-------------------------+
| test_nutzer_4@router-01 | test_nutzer_4@router-01 |
+-------------------------+-------------------------+

Questions:
- How to create a user with limited host rights (router localhost)?
- Is the only hostname based auth in combination with a router "normal"?
- Where is the error in my thinking regarding user generation for this use case?

Thanks for your answers and ideas

Best

Silvio