How to setup MySQL users with Maxscale

Question

I'm setting up a 3 nodes (MariaDB 10.4) Galera cluster with 2 frontal servers.

Inside each of these frontal servers there is also a Maxscale 6 daemon.

[listener]
type=listener
service=readwritesplit
protocol=MariaDBClient
address=127.0.0.1
port=3306

All of these servers are inside the 10.1.0.0/24 network.

At MariaDB, I've created users like CREATE USER 'user'@'10.1.0.%' but these can't authenticate.

Authentication failed for user 'user'@[127.0.0.1] to service 'readwritesplit'. Originating listener: 'listener'. MariaDB error: 'Access denied for user 'user'@'127.0.0.1' (using password: YES)'.

So I've RENAME 'user'@'10.1.0.%' TO 'user'@'127.0.0.1'. Another authentication error.

maxscale[1109436]: Authentication to 'node1' failed: 1045, #28000: Access denied for user 'user'@'frontal1' (using password: YES)

I ended up to RENAME 'user'@'10.1.0.%' TO 'user'@'%'. All fine here of course.

As long as all servers are on the 10.1.0.% network, why users 'user'@'10.1.0.%' can't authenticate?

What version of MaxScale are you using? – markusjm Nov 16 '21 at 12:02 — markusjm, Nov 16 '21 at 12:02
@markusjm As specified in main thread, Maxscale 6 (6.1.4). – CrazyRabbit Nov 16 '21 at 12:03 — CrazyRabbit, Nov 16 '21 at 12:03
Ah, my apologies: I am blinder than usual :) – markusjm Nov 16 '21 at 12:05 — markusjm, Nov 16 '21 at 12:05

score 1 · Accepted Answer · answered Nov 16 '21 at 12:15

If you want to access from the local host (i.e. 127.0.0.1), you either need two separate users ('user'@'127.0.0.1' and 'user'@'10.1.0.%') or a grant that covers them both ('user'@'%'). A more detailed description of this can be found in the MaxScale tutorial.

You can make authentication handling with MaxScale significantly simpler by using the proxy_protocol feature in MaxScale. This would allow you to define users only once with the exact IP address they come from and let the proxy-protocol-networks variable in MariaDB control which MaxScale instances are allowed to act as proxies.

The error 'user'@'127.0.0.1' means that the client was attempting to connect from the local host, i.e. by connecting to the loopback address. This isn't covered by the 10.1.0.% host which explains why it failed.

Having converted the user to 'user'@'127.0.0.1', the local login between the client and MaxScale worked but then the backend server rejected the authentication as the MaxScale host no longer matches the grant's network (127.0.0.1).

Using the % host causes all addresses to match which explains why it solved the problem.

Thanks, but can `proxy_protocol` be used here? As maxscale resides on the same host than the client (this is why it is listening at `127.0.0.1`), won't the passed IP be `127.0.0.1`? If yes, can in this case the `'user'@'127.0.0.1'` be used? — CrazyRabbit, Nov 16 '21 at 13:35
Yes, you can still use `proxy_protocol` even if you log in from the localhost. It simply removes the need to create a second user with the MaxScale IP address. You do still need to create the `'user'@'127.0.0.1'´ user to be able to log in. — markusjm, Nov 17 '21 at 14:38

How to setup MySQL users with Maxscale

1 Answers1