This morning, a user asked in the MySQL Community Slack if somebody had an idea why the error log was filled up continuously with a warning messages like this one:
2020-07-24T06:54:00.877128Z 46 [Warning] [MY-013360] [Server] Plugin sha256_password reported: ''sha256_password' is deprecated and will be removed in a future release. Please use caching_sha2_password instead'
The context was that the user upgraded from MySQL 5.7 to MySQL 8.0 and changed the authentication plugin of all users to caching_sha2_password.
As the warning message was written to the error log almost twice per second, I suspected a monitoring tool. The user said, that no application, no monitoring was using this server yet.
Of course we verified that all users had the plugin changed.
This is important when helping people (friends, colleagues, customers, users) to never trust them and ask for evidences. And sometimes just by asking evidences and let people tell the full process will help them and/or you to figure out what is the problem.
So, in this case all users had the new authentication plugin and the default (default_authentication_plugin) was also set to caching_sha2_password.
We also tried to block connections to the IP and 127.0.0.1 for the mysql port but the log was constantly filled with the same warning message.
I checked with the security developers when this deprecated warning was thrown:
- user creation
- connection attempt
So we were back to the connection attempts. As the IP and 127.0.0.1 were not used that could have been the socket.
In fact, when a user name is not found, MySQL assigns an authentication plugin randomly and proceed with authentication, to finally deny it.
So how could we resolve this issue ?
In MySQL there is a very nice but still unknown plugin that is the perfect solution to prove my point: Connection-Control Plugins !
We started by installing it:
mysql> INSTALL PLUGIN CONNECTION_CONTROL -> SONAME 'connection_control.so'; Query OK, 0 rows affected (0.15 sec) mysql> INSTALL PLUGIN CONNECTION_CONTROL_FAILED_LOGIN_ATTEMPTS -> SONAME 'connection_control.so'; Query OK, 0 rows affected (0.04 sec)
And we could immediately see what was the problem:
The user used that server to test PMM and installed the pmm-agent. Then forgot about it 😉
Aaaaah the monitoring tool was indeed the culprit !
Of course the credentials used by pmm were not present in the new installed database. The problem was also happening on 5.7 but without warning message about the deprecated authentication plugin.
Also check: https://mysqlserverteam.com/the-connection_control-plugin-keeping-brute-force-attack-in-check/
Great investigation!
This incident is also a great opportunity to improve the server response in such situation.
1. issuing a warning for every attempt is just playing in the hands of would be DOS attackers. The server should refrain from filling its own log with uselessly repetitive messages.
2. The information was insufficient to find the problem without the assistance of Chief Inspector Descamps. The wording of the warning makes users think that they are dealing with registered accounts, while in fact this was an account unknown to the system. Perhaps a hint in that direction would have helped to speed up the detection.
3. The warning was not designed for security, but only to promote the newest authentication plugin. Without the plugin mismatch, thousands of failed connection would have gone unnoticed and this is worrying. A log entry saying “there were 86,400 failed attempts today” would have been more helpful than the current message.
The development team involved in this investigation, is already aware of that and are discussing on how to improve the situation if possible.