MaxScale blocks direct connect to database

Question

I have a 2 nodes mariadb cluster with one maxscale load balancer.

maxscale blocks connection if i want to connect directly to a database: so for example:

mysql -h 35.300.208.100 -u finn -p works and if i then do a USE test i can do everything with the database "test". so the rights are correct.

but if a do a mysql -h 35.300.208.100 -u finn -p test i get the error:

ERROR 1045 (28000): Access denied for user 'finn'@'188.68.43.150' (using password: YES) to database 'test'

So if i do the same on the nodes with localhost, everything works fine.

This is my maxscale.cnf

Answer 1

Most of the time you will receive ERROR 1045 (28000): Access denied when the grants on the database do not contain matching grants for both the client IP and the MaxScale IP.

The usual way of solving this is to:

1. Execute SHOW GRANTS on the database from the client server
2. Execute SHOW GRANTS on the database from the MaxScale server
3. Compare the output of the two queries and make sure they are identical

This is usually enough to spot any problems with grants in the database.

Another way to resolve these sorts of errors is to execute the query that MaxScale uses to load the database users. The exact SQL for these queries can be found on the MaxScale wiki on GitHub. For MaxScale 2.1 and newer, this would be:

SELECT u.user, u.host, d.db, u.select_priv, u.password
    FROM mysql.user AS u LEFT JOIN mysql.db AS d
    ON (u.user = d.user AND u.host = d.host)
UNION
SELECT u.user, u.host, t.db, u.select_priv, u.password
    FROM mysql.user AS u LEFT JOIN mysql.tables_priv AS t
    ON (u.user = t.user AND u.host = t.host);

This should return a result set containing the authentication data that MaxScale uses. Here's an example of what the query can return:

+---------------+-----------+------+-------------+-------------------------------------------+
| user          | host      | db   | select_priv | password                                  |
+---------------+-----------+------+-------------+-------------------------------------------+
| root          | localhost | NULL | Y           |                                           |
| maxuser       | 127.0.0.1 | NULL | Y           | *5EDBD32E469DAE0CE10E6999C3899DEFCB9F12E0 |
| root          | %         | NULL | Y           |                                           |
| maxuser       | %         | NULL | Y           | *5EDBD32E469DAE0CE10E6999C3899DEFCB9F12E0 |
| skysql        | 127.0.0.1 | NULL | Y           | *85058F5DEAD82AE3507664C2C11BDA7B1827B80D |
| skysql        | %         | NULL | Y           | *85058F5DEAD82AE3507664C2C11BDA7B1827B80D |
| test          | %         | NULL | Y           | *94BDCEBE19083CE2A1F959FD02F964C7AF4CFC29 |
| stackoverflow | %         | test | N           | *5635C63172887A7D7C0828876228A5E4DC523969 |
| stackoverflow | %         | NULL | N           | *5635C63172887A7D7C0828876228A5E4DC523969 |
+---------------+-----------+------+-------------+-------------------------------------------+

The select_priv tells whether the user is allowed to connect with any default database. If it is set to N, then the value in the db column is the only database that the client is allowed to use. As we can see from the example result, the 'stackoverflow'@'%' user is either allowed to connect without a default database or with the test default database.