MaxScale not authenticating database users. Please help me understand

[rafae...@gmail.com]

Hi all,

I have this odd issue with MaxScale 1.3 where I cannot authenticate some of the accounts. Let me give you an idea of my frustration and I will describe my setup below.

When I try to login from CLI or even phpMyadmin through MaxScale it fails for some accounts. For example, trying to connect a Wordpress site to my cluster fails IF the connection goes through MaxScale.

Just to convince myself there is something odd with the authentication, I setup an haproxy in the same box where MaxScale is running. No issues connecting through haproxy at all. I can authenticate all accounts just fine.

For the MaxScale USER and monitoring account I use the same account used by haproxy with not only select to mysql.* tables but actually GRANT (all permissions) to all tables.

I'm running a MariaDB 10.0.24 Galera Cluster (3 nodes), all up, sync'd and dandy. Ubuntu 14.04 LTS servers, xtrabackup-v2 and no issues authenticating when using HAPROXY in front of the cluster instead of MaxScale.

My setup:

4 Servers: NODE0, NODE1, NODE2, HAProxy/MaxScale/Arbitrator

The odd thing is that when going through MaxScale, I can authenticate to backend with pma and haproxy_root accounts. All other accounts in my databases cannot authenticate at all... not even root, or a wordpress site, or a forum database, etc. Supposedly, I should be able to connect from any host e.g: wordpress@'%' to my databases but even specifying the MaxScale host IP wordpress@'x.x.x.x as an additional user account with its respective permissions it does not allow me to connect!

root@svr-haproxy:~# for i in `seq 1 5`; do mysql -h 127.0.0.1 -u wordpress -p -e "show variables like 'server_id'"; done
ERROR 1045 (28000): Access denied for user 'wordpress'@'127.0.0.1' (using password: YES)

root@svr-haproxy:~# for i in `seq 1 5`; do mysql -h 192.168.1.223 -u wordpress -p -e "show variables like 'server_id'"; done
ERROR 1045 (28000): Access denied for user 'wordpress'@'192.168.1.223' (using password: YES)

I know all passwords are correct and all accounts have the correct permissions in the backed. Just bringing down MaxScale and firing up haproxy allows me to authenticate all accounts as expected.

Here is my MaxScale config:

[maxscale]
threads=4

[Splitter Service]
type=service
router=readwritesplit
servers=node0, node1, node02
user=haproxy_root
passwd=***********

[Splitter Listener]
type=listener
service=Splitter Service
protocol=MySQLClient
port=3306

[node0]
type=server
address=192.168.1.220
port=3306
protocol=MySQLBackend

[node1]
type=server
address=192.168.1.221
port=3306
protocol=MySQLBackend

[node2]
type=server
address=192.168.1.222
port=3306
protocol=MySQLBackend

[Galera Monitor]
type=monitor
module=galeramon
disable_master_failback=1
servers=node0, node1, node2
user=haproxy_check
passwd=
monitor_interval=10000

[MaxAdmin Service]
type=service
router=cli

[MaxAdmin Listener]
type=listener
service=MaxAdmin Service
protocol=maxscaled
address=localhost
port=6603

[Markus Mäkelä]

Hi,

Are you running MaxScale on the same machine as the user that's trying to connect? If so, you might need to add 'localhost_match_wildcard_host=true' to the service definition. This will match localhost to wildcard grants like 'wordpress'@'%'.

The reason why localhost_match_wildcard_host is requires relates to anonymous users in the database. If there are anonymous users in the backend database, wildcard will not match the 'user'@'%' grant since ''@'localhost' is more specific. In this sense, MaxScale imitates the behavior of a normal MySQL type server. Usually this is more of a hindrance than a benefit but this is the reason the localhost_match_wildcard_host exists. It should even automatically match wildcard grants to localhost if anonymous users have been removed from the backend database. If it doesn't, it looks like the automatic detection of anonymous users has a bug.

If you'd like to see what MaxScale is trying to do, you can enable the debug log and look for entries like this:

2016-03-06 20:39:18   debug  : 140737353893952 [MySQL Client Auth], checking user [maxu...@127.0.0.1] db: asdf

With these you'll see the username, IP and database the client is connecting to. In hindsight, it might be better to have these on a higher logging level than debug since problems like this happen to non-developers as well. I've created bug report MXS-604 about the logging levels.

If you still can't log in through MaxScale, please give an example of the grants given to the users who are trying to log in.

Markus

-- 
Markus Mäkelä, Software Engineer
MariaDB Corporation
t: +358 40 7740484 | Skype: markus.j.makela 

[rafae...@gmail.com]

Markus,

Thank you so much for your input. I truly appreciate it.

I have password protected the only account that had no password (haproxy_check) which was mainly used for haproxy to check the service availability. Unfortunately, things didn't change.

Let me explain my setup a bit more to see if you can help me spot the issue.

MaxScale is running in server 4 while server 1-3 are my SQL nodes.

Server 4 is also the one hosting phpMyadmin. In any case, I have taken your recommendation (since maxscale is in the same machine where the user is trying to connect from (phpMyadmin) and added 'localhost_match_wildcard_host=true' to the service definition. In fact I tried with 'localhost_match_wildcard_host=true' and also with 'localhost_match_wildcard_host=1'

From the logs, I can see that MaxScale is actially loading all users from the database on initialization:

2016-03-06 20:06:25 notice : MariaDB Corporation MaxScale 1.3.0 (C) MariaDB Corporation Ab 2013-2015
2016-03-06 20:06:25 notice : MaxScale is running in process 16913
2016-03-06 20:06:25 notice : Loaded 11 MySQL Users for service [Splitter Service].
2016-03-06 20:06:25 notice : Loaded module MySQLClient: V1.0.0 from /usr/lib/x86_64-linux-gnu/maxscale/libMySQLClient.so
2016-03-06 20:06:25 notice : Listening MySQL connections at 0.0.0.0:3306
2016-03-06 20:06:25 notice : Loaded module maxscaled: V1.0.0 from /usr/lib/x86_64-linux-gnu/maxscale/libmaxscaled.so
2016-03-06 20:06:25 notice : Listening maxscale connections at localhost:6603
2016-03-06 20:06:25 notice : MaxScale started with 4 server threads.
2016-03-06 20:06:25 notice : Started MaxScale log flusher.
2016-03-06 20:06:26 notice : Server changed state: node0[192.168.1.220:3306]: new_master
2016-03-06 20:06:26 notice : Server changed state: node1[192.168.1.221:3306]: new_slave
2016-03-06 20:06:26 notice : Server changed state: node2[192.168.1.222:3306]: new_slave

So, it looks like it does not like % regardless. For example, from terminal and phpmyadmin I can actually login with pma user. If I try another % user it fails:

root@svr-haproxy:~# mysql -h 127.0.0.1 -u pma -p -e "show variables like 'server_id'";
Enter password:
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| server_id | 1 |
+---------------+-------+

root@svr-haproxy:~# mysql -h 127.0.0.1 -u root -p -e "show variables like 'server_id'";
Enter password:
ERROR 1045 (28000): Access denied for user 'root'@'127.0.0.1' (using password: YES)

root@svr-haproxy:~# mysql -h 127.0.0.1 -u wordpress -p -e "show variables like 'server_id'";
Enter password:

ERROR 1045 (28000): Access denied for user 'wordpress'@'127.0.0.1' (using password: YES)

Checking back on the nodes I can see the grants given to the users who are trying to log in; for example root:

root@svr-demo:/home/ralphy# mysql -h 127.0.0.1 -u root -p -e "show grants for 'root'@'127.0.0.1'";
Enter password:
+----------------------------------------------------------------------------------------------------------------------------------------+
| Grants for ro...@127.0.0.1 |
+----------------------------------------------------------------------------------------------------------------------------------------+
| GRANT ALL PRIVILEGES ON *.* TO 'root'@'127.0.0.1' IDENTIFIED BY PASSWORD '*23AE809DDACAF96AF0FD78ED04B6A265E05AA257' WITH GRANT OPTION |
+----------------------------------------------------------------------------------------------------------------------------------------+
root@svr-demo:~# mysql -h 127.0.0.1 -u root -p -e "show grants for 'root'@'%'";
Enter password:
+--------------------------------------------------------------------------------------------------------------------------------+
| Grants for root@% |
+--------------------------------------------------------------------------------------------------------------------------------+
| GRANT ALL PRIVILEGES ON *.* TO 'root'@'%' IDENTIFIED BY PASSWORD '*23AE809DDACAF96AF0FD78ED04B6A265E05AA257' WITH GRANT OPTION |
+--------------------------------------------------------------------------------------------------------------------------------+
root@svr-demo:~# mysql -h 127.0.0.1 -u root -p -e "show grants for 'root'@'localhost'";
Enter password:
+----------------------------------------------------------------------------------------------------------------------------------------+
| Grants for root@localhost |
+----------------------------------------------------------------------------------------------------------------------------------------+
| GRANT ALL PRIVILEGES ON *.* TO 'root'@'localhost' IDENTIFIED BY PASSWORD '*23AE809DDACAF96AF0FD78ED04B6A265E05AA257' WITH GRANT OPTION |
| GRANT PROXY ON ''@'%' TO 'root'@'localhost' WITH GRANT OPTION |
+----------------------------------------------------------------------------------------------------------------------------------------+

Another example - Wordpress %

root@svr-demo:~# mysql -h 127.0.0.1 -u root -p -e "show grants for 'wordpress'@'%'";
Enter password:
+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Grants for wordpress@% |
+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'wordpress'@'%' IDENTIFIED BY PASSWORD '*C260A4F79FA905AF65142FFE0B9A14FE0E1519CC' |
| GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, REFERENCES, INDEX, ALTER, CREATE TEMPORARY TABLES, EXECUTE, CREATE VIEW, SHOW VIEW, CREATE ROUTINE, ALTER ROUTINE, EVENT, TRIGGER ON `wordpress`.* TO 'wordpress'@'%' |
+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+

Now, going back to the MaxScale logs...

2016-03-06 20:08:23 notice : Splitter Service: login attempt for user 'wordpress', authentication failed.
2016-03-06 20:13:26 notice : Splitter Service: login attempt for user 'root', authentication failed.

Debug shows that the authentication fails... ro...@127.0.0.1 not existent:

2016-03-06 20:28:08 debug : Receiving connection from 'root' to database ''.
2016-03-06 20:28:08 debug : 140170468775680 [MySQL Client Auth], checking user [ro...@127.0.0.1] db:
2016-03-06 20:28:08 debug : 140170468775680 [MySQL Client Auth], checking user [ro...@127.0.0.1] with wildcard host [%]
2016-03-06 20:28:08 debug : 140170468775680 [MySQL Client Auth], user [ro...@127.0.0.1] not existent

So, there is definitely something else here and I haven't been able to figure it out when using maxscale... going back to haproxy, as before, works just fine whether the monitoring account has password or not.

I'm lost here, honestly.

[Markus Mäkelä]

Hi,

The 'root' user requires 'enable_root_user' to work through MaxScale.
This is done to prevent possible misuse since the root user really
shouldn't be used. Here is the documentation for the 'enable_root_user':
https://github.com/mariadb-corporation/MaxScale/blob/1.3.0/Documentation/Getting-Started/Configuration-Guide.md#enable_root_user

I' not sure why the authentication fails but having all the logs from an
authentication attempt would make figuring this out easier. If you could
open a bug report on the MariaDB Jira over at
https://jira.mariadb.org/browse/MXS we could track this more easily and
provide a place where you can drop off logs and configurations for more
detailed analysis. I hope we can resolve this problem and improve
MaxScale to make figuring things like this a lot easier.

Markus

[rafae...@gmail.com]

Markus,

I believe I found the main problem. MaxScale is trying to authenticate via IPv6 ::1 instead of IPv4. This is strange since I do not have IPv6 enabled in any of my servers:

root@svr-haproxy:~# cat /proc/sys/net/ipv6/conf/all/disable_ipv6
1

/etc/hosts

127.0.0.1 localhost svr-haproxy

Logs:

2016-03-07 00:36:08 error : Failed to obtain address for host ::1, Address family for hostname not supported
2016-03-07 00:36:08 warning: Failed to add user root@::1 for service [Splitter Service]. This user will be unavailable via MaxScale.
2016-03-07 00:36:08 warning: Duplicate MySQL user found for service [Splitter Service]: ro...@127.0.0.1 for database: (null)
2016-03-07 00:36:08 notice : Splitter Service: login attempt for user 'wordpress', authentication failed.

The authentication never leaves the MaxScale box (no authentication is performed on the backend SQL servers) and that's when it fails.

To my surprise, enabling root login (this is a test environment hence I'm not too concerned about allowing root logins) DOES work. Now I need to figure out why MaxScale is not authenticating accounts other than root, pma or service... basically I can login with these three accounts but all other accounts are simply not authenticated at all (they never get to the backend nodes for authentication).

I'll open a bug report if I figure it out and understand that it is in fact a bug. I do not like disturbing developers with unfounded claims.

[rafae...@gmail.com]

Markus,

I have opened a bug report https://jira.mariadb.org/browse/MXS-605. I'm unable to spot a mis-configuration on my end so I hope I can receive assistance there to figure it out.

[Chandranana Naik]

Hi,

Could you please tell on which architecture you are facing this issue?

I am working on verifying maxscale 2.2 on s390x, and facing similar issue where i get an error :

 Read-Only-Service: login attempt for user 'maxscale'@[172.17.0.10]:40030, authentication failed.

 

All permissions are granted to this user, still there is authentication failure.

The same scenario works fine on x86 arch. 

Regards,

Chandranana

[Johan Wikman]

 Hi,

This is a complete longshot.

Linux on s390x is big-endian while Linux on x86 is little-endian. Since we only build and test Linux on x86, I would not be surprised if there were some endianess issues present that might or might not explain the problems you see.

Johan

--
You received this message because you are subscribed to the Google Groups "MaxScale" group.
To unsubscribe from this group and stop receiving emails from it, send an email to maxscale+unsubscribe@googlegroups.com.
For more options, visit https://groups.google.com/d/optout.