MaxScale blocks direct connect to database

146
February 07, 2018, at 3:09 PM

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.

READ ALSO
Can't get natural join to work with given expression? MySQL

Can't get natural join to work with given expression? MySQL

PC and Laptop are tables with relations (model, speed) : str, int

76
Tinymce PHP CRUD mysql

Tinymce PHP CRUD mysql

I'm using Tinymce along with PHP and mysql

159
Inner Join only if satisfies If condition

Inner Join only if satisfies If condition

How do I minimize the Stored Procedure

59
How to do CRUD operations in google chrome extension?

How to do CRUD operations in google chrome extension?

Can anybody help me by showing en example code or tutorial about how to do CRUD operations in Google Chrome Extension development by using MYSQL as the databaseI searched it for 2 days, as I am a novice programmer I couldn't get the whole idea by reading...

134