[MXS-1510] Unable to login after SET password Created: 2017-11-02  Updated: 2018-01-02  Resolved: 2018-01-02

Status: Closed
Project: MariaDB MaxScale
Component/s: N/A
Affects Version/s: 2.1.9
Fix Version/s: N/A

Type: Bug Priority: Major
Reporter: Andrius Kulbis Assignee: Unassigned
Resolution: Not a Bug Votes: 0
Labels: galera


 Description   

I am using MaxScale v2.19 to load balance queries to 3-node gallera cluster. I decided to run phpMyAdmin connected to MaxScale for database administration.

The problem occurs when I try to change database user password via phpMyAdmin or via CLI connected to MaxScale using SET password for 'user'@'%' = PASSWORD('****');|

I get confirmation about successfully changed password, but then I am unable to connect to my database using new nor old password. Connecting to the each node directly works as expected with new password. I see that the password hash has changed in every node of cluster.

Only thing that fixes this going to one of the cluster nodes directly and running same SET password for 'user'@'%' = PASSWORD('****') query, then I am able to connect to the database via MaxScale again.

General log of each node on SET password operation:

db01-test

 
		173511 Connect	user@172.12.12.64 as anonymous on 
		173511 Query	SET CHARACTER SET 'utf8mb4'
 
		173511 Query	SET collation_connection = 'utf8mb4_unicode_ci'
 
		173511 Query	SET lc_messages = 'lt_LT'
 
		173510 Query	SELECT `plugin` FROM `mysql`.`user` WHERE `User` = "user" AND `Host` = "%" LIMIT 1
 
		173510 Query	SET `old_passwords` = 0
 
		173510 Query	SET password = PASSWORD('test123')

db02-test

 
		171527 Connect	user@172.12.12.64 as anonymous on 
		171527 Query	SET CHARACTER SET 'utf8mb4'
 
		171527 Query	SET collation_connection = 'utf8mb4_unicode_ci'
 
		171527 Query	SET lc_messages = 'lt_LT'
 
		171526 Query	SET `old_passwords` = 0
 
		171526 Query	SET password = PASSWORD('test123')
 
		171526 Query	FLUSH PRIVILEGES

db03-test

 
		171399 Connect	user@172.12.12.64 as anonymous on 
		171399 Query	SET CHARACTER SET 'utf8mb4'
 
		171399 Query	SET collation_connection = 'utf8mb4_unicode_ci'
 
		171399 Query	SET lc_messages = 'lt_LT'
 
		171398 Query	SET `old_passwords` = 0
 
		171398 Query	SET password = PASSWORD('test123')

My config:

maxscale.cnf

 
 
 
[maxscale]
 
threads=2
 
log_debug=1
 
log_auth_warnings=true
 
 
 
 
 
[db01-test]
 
type=server
 
address=172.12.12.101
 
port=3307
 
protocol=MySQLBackend
 
 
 
[db02-test]
 
type=server
 
address=172.12.12.102
 
port=3307
 
protocol=MySQLBackend
 
 
 
[db03-test]
 
type=server
 
address=172.12.12.103
 
port=3307
 
protocol=MySQLBackend
 
 
 
[Galera Monitor]
 
type=monitor
 
module=galeramon
 
servers=db01-test,db02-test,db03-test
 
user=maxscale
 
passwd=***
 
monitor_interval=10000
 
 
 
 
 
[Galera Service]
 
type=service
 
router=readwritesplit
 
servers=db01-test,db02-test,db03-test
 
user=maxscale
 
passwd=***
 
localhost_match_wildcard_host=true
 
auth_all_servers=true
 
 
 
[MaxAdmin Service]
 
type=service
 
router=cli
 
 
 
[Read-Write Listener]
 
type=listener
 
service=Galera Service
 
protocol=MySQLClient
 
port=3306
 
 
 
[MaxAdmin Listener]
 
type=listener
 
service=MaxAdmin Service
 
protocol=maxscaled
 
socket=default

I get the following error in MaxScale.log (due to a fact that I can login directly to db03-test):
[MySQLBackend] Invalid authentication message from backend 'db03-test'. Error code: 1045, Msg : #28000Access denied for user 'user'@'172.12.12.64' (using password: YES)

 Comments   
Comment by markus makela [ 2017-12-27 ]

Assuming that there are two grants, one for the client IP and another for the MaxScale IP, only the grant that matches the IP address of MaxScale will be changed. This is best explained with some example SQL.

First, execute this on the backend server.

CREATE USER 'test'@'client-ip' IDENTIFIED BY 'first-password';
 
CREATE USER 'test'@'maxscale-ip' IDENTIFIED BY 'first-password';

Then connect via MaxScale with the created credentials and execute the following.

SET password = PASSWORD('second-password');

After that, execute the following query directly on the backend server.

SELECT user, host, password FROM mysql.user WHERE user = 'test';

The result set should be close to the following one.

MariaDB [(none)]> SELECT user, host, password FROM mysql.user WHERE user = 'test';
 
+------+----------------+-------------------------------------------+
 
| user | host           | password                                  |
 
+------+----------------+-------------------------------------------+
 
| test | client-ip      | *257AE5EA13AF9CFA6154C0B24E29A820BEA5AF6B |
 
| test | maxscale-ip    | *176B2AD3EBAEA7ED057A9B0205CAA8E53D363007 |
 
+------+----------------+-------------------------------------------+

As we can see, the server does correctly change the password for the connected user but only for the user that is connected to the server, not to MaxScale. A way to work around this is to modify the password with a different user.

This can also be done through MaxScale with a compound statement (MariaDB 10.1+ only) that modifies both of the users in one query:

BEGIN NOT ATOMIC
 
  SET PASSWORD FOR 'user'@'client-ip' = PASSWORD('new-password');
 
  SET PASSWORD FOR 'user'@'maxscale-ip' = PASSWORD('new-password');
 
END

Comment by markus makela [ 2018-01-02 ]

Not a bug.

Generated at Thu Feb 08 04:07:18 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.