CODEWITHSUNDEEP
mariadb-10.4maxscale
CrazyRabbit
CrazyRabbit

Reputation: 307

How to setup MySQL users with Maxscale

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?

Upvotes: 0

Views: 1074

Answers (1)

markusjm
markusjm

Reputation: 2562

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.

Upvotes: 1

Related Questions