I set up MaxScale so I tried to test whether it work or not. I did tried to access from application server using this command below
$ mysql --host=192.168.30.145 --user=client --password -P 3309
and then, I got this error
ERROR 1045 (28000): Access denied for user 'client'@'::ffff:192.168.30.161' (using password: YES)
and this is an error messege in /var/log/maxscale/maxscale.log
warning: (2) [MariaDBProtocol] Authentication failed for user 'client'@[::ffff:192.168.30.161] to service 'Write-Service'. Originating listener: 'Write-Listener'. MariaDB error: 'Access denied for user 'client'@'::ffff:192.168.30.161' (using password: YES)'.
Here is the information of the MaxScale and MariaDb I set up.
・masterDB:192.168.30.140
・replicaDB1:192.168.30.141
・replicaDB2:192.168.30.142
・replicaDB3:192.168.30.143
・maxscale:192.168.30.145
・application server:192.168.30.161
on masterDB, I made user account such as maxscale@%, monitor@%, and [email protected] As for each replicaDB, I made [email protected]
For now, I did Systemctl stop ufw on all servers, and
changed bind-address = 127.0.0.1 to bind-address = 0.0.0.0 on all DB
[maxscale]
threads=auto
[MariaDB-Monitor]
type=monitor
module=mariadbmon
servers=masterDB, replicaDB1, replicaDB2, replicaDB3
user=monitor
password=alj123
monitor_interval=2s
[masterDB]
type=server
address=192.168.30.140
port=3306
protocol=MariaDBBackend
[replicaDB1]
type=server
address=192.168.30.141
port=3306
protocol=MariaDBBackend
[replicaDB2]
type=server
address=192.168.30.142
port=3306
protocol=MariaDBBackend
[replicaDB3]
type=server
address=192.168.30.143
port=3306
protocol=MariaDBBackend
[Write-Service]
type=service
router=readconnroute
router_options=master
servers=masterDB
user=maxscale
password=alj123
[Read-Service]
type=service
router=readconnroute
router_options=slave
servers=replicaDB1, replicaDB2, replicaDB3
user=read
password=alj123
[Write-Listener]
type=listener
service=Write-Service
protocol=MariaDBClient
port=3309
[Read-Listener]
type=listener
service=Read-Service
protocol=MariaDBClient
port=3310
users on masterDB
+----------------------------------------------------------------------------------------------------------------------------------+
| Grants for maxscale@% |
+----------------------------------------------------------------------------------------------------------------------------------+
| GRANT SHOW DATABASES, BINLOG MONITOR ON *.* TO `maxscale`@`%` IDENTIFIED BY PASSWORD '*9639F24DBDAFA8F106318B335C271B581801E286' |
| GRANT SELECT ON `mysql`.`db` TO `maxscale`@`%` |
| GRANT SELECT ON `mysql`.`proxies_priv` TO `maxscale`@`%` |
| GRANT SELECT ON `mysql`.`columns_priv` TO `maxscale`@`%` |
| GRANT SELECT ON `mysql`.`roles_mapping` TO `maxscale`@`%` |
| GRANT SELECT ON `mysql`.`user` TO `maxscale`@`%` |
| GRANT SELECT ON `mysql`.`tables_priv` TO `maxscale`@`%` |
| GRANT SELECT ON `mysql`.`procs_priv` TO `maxscale`@`%` |
+----------------------------------------------------------------------------------------------------------------------------------+
+--------------------------------------------------------------------------------------------------------------------------------+
| Grants for monitor@% |
+--------------------------------------------------------------------------------------------------------------------------------+
| GRANT RELOAD, SUPER, BINLOG MONITOR ON *.* TO `monitor`@`%` IDENTIFIED BY PASSWORD '*9639F24DBDAFA8F106318B335C271B581801E286' |
+--------------------------------------------------------------------------------------------------------------------------------+
+---------------------------------------------------------------------------------------------------------------------------------------------+
| Grants for [email protected] |
+---------------------------------------------------------------------------------------------------------------------------------------------+
| GRANT SELECT, INSERT, UPDATE, DELETE ON *.* TO `client`@`192.168.30.145` IDENTIFIED BY PASSWORD '*9639F24DBDAFA8F106318B335C271B581801E286' |
+---------------------------------------------------------------------------------------------------------------------------------------------+
user on each replicaDB
+-------------------------------------------------------------------------------------------------------------------+
| Grants for [email protected] |
+-------------------------------------------------------------------------------------------------------------------+
| GRANT SELECT ON *.* TO `read`@`192.168.30.145` IDENTIFIED BY PASSWORD '*9639F24DBDAFA8F106318B335C271B581801E286' |
+-------------------------------------------------------------------------------------------------------------------+
user on application sever
+---------------------------------------------------------------------------------------------------------------------------------------------+
| Grants for [email protected] |
+---------------------------------------------------------------------------------------------------------------------------------------------+
| GRANT SELECT, INSERT, UPDATE, DELETE ON *.* TO `client`@`192.168.30.161` IDENTIFIED BY PASSWORD '*9639F24DBDAFA8F106318B335C271B581801E286' |
+---------------------------------------------------------------------------------------------------------------------------------------------+
・did grant ALL PRIVILEGES [email protected], then I didn't get the error but I could INSERT, UPDATE, DELETE on replicaDB. I wanna allow client to do only SELECT on replicaDB
・changed client's host from 192.168.30.145 to %, then I didn't get the error but I don't wanna allow client to access DB from servers other than maxscale server(192.168.30.145)
Could anyone help me, please?
The reason why it failed is because you defined the user with one specific IP address ([email protected]) and you need to define it twice (unless you use proxy_protocol): once for the client IP and another time for the MaxScale IP. This is mentioned in the MaxScale Troubleshooting article as well as the MaxScale tutorial.
Instead of defining the users twice or using a wildcard to match all IPs, the best way to solve this is to add the MaxScale IP to proxy_protocol_networks in MariaDB and turn on proxy_protocol for all servers in MaxScale.
This makes MaxScale a trusted proxy for authentication that then allows MaxScale to relay the original IP to the MariaDB server to be used for the final authentication. This essentially makes MaxScale transparent to the whole authentication process which is what you usually want from a proxy.