Configuring SHA authentication method in MySQL

As of MySQL version 8 the hashing of database user passwords has been changed by default to caching_sha2_password. This method encompasses both obsolete protocols (SHA 256), as well as current and future protocols, combined with the caching capability of the entire encryption mechanism and valid comparison of user credentials.

On a new MySQL 8 installation this is the default method, however, MySQL servers that have upgraded from previous versions retain the previous method called mysql_native_password. This consideration prevents old users from losing remote connection because, on the client side, the connection and authentication process must be updated and configured.

In Pandora FMS the connection between the Web Console and the PFMS server (monolithic environment) is done through a Unix type connection file (Unix socket-file) and shared-memory protocol, which are considered protected and secure in a local environment, so in this particular case the protocol used for the encryption, encoding and transmission of passwords and data is indistinct.

In view of this situation, and in order to keep security best practices up to date, it is recommended to change the encryption protocol from mysql_native_password to caching_sha2_password, for the default user pandora, with this tutorial.

You must confirm if you have installed a version equal or later than MySQL 8.0.4 with the following command (in its long version):

mysql --version

Or in its short version:

mysql -V

You will get something similar to this:

mysql  Ver 8.0.39-30 for Linux on x86_64 (Percona Server (GPL), Release '30', Revision '41ebc5d9')

cat /var/www/html/pandora_console/include/config.php | grep dbuser
cat /var/www/html/pandora_console/include/config.php | grep dbpass

• Stop PFMS server.

It must be executed in a terminal window:

systemctl stop pandora_server

• Active user check.

It is necessary to check if the active user for database connection (by default pandora) uses mysql_native_password. To do this, log in to MySQL as root and execute the following query:

SELECT USER, host, plugin FROM mysql.user WHERE USER = 'pandora';

You will get an answer similar to:

• Change password type.

The authentication method is changed and a password is defined with the following statement:

ALTER USER 'pandora'@'%' IDENTIFIED WITH caching_sha2_password BY 'Pandor4!';

• Checking the change.

When running again:

SELECT USER, host, plugin FROM mysql.user WHERE USER = 'pandora';

The following should be obtained:

Once the change has been verified at the server level, close the MySQL interface with the exit; command and proceed to the next step at the client level.

As explained in introduction, on the client side the connection must be updated and configured according to the type of operating system used: EL 8 /EL 9 or Ubuntu.

You must have the mariadb-connector-c package installed, which is installed by default in most EL8 and EL9 environments.

The file is checked for existence with:

ls -l /usr/lib64/mariadb/plugin/caching_sha2_password.so

A positive response similar to the following will be obtained:

The next step is to create a symbolic link to that file with the command:

ln -s /usr/lib64/mariadb/plugin/caching_sha2_password.so /usr/lib64/mysql/plugin/caching_sha2_password.so

The link made with is checked:

ls -l /usr/lib64/mysql/plugin/caching_sha2_password.so

The following positive response was obtained:

With all steps successfully completed, only the PFMS server restart remains.

The libmariadb3 package must be installed. Depending on the version installed it may not come with the operating system and must be installed with the command:

apt-get install libmariadb3

It is verified that the required file has been generated with the installation of the package, caching_sha2_password.so:

ls -l /usr/lib/x86_64-linux-gnu/libmariadb3/plugin/caching_sha2_password.so

The destination folder /usr/lib64/mysql/plugin/ must be created with the command:

mkdir -p /usr/lib64/mysql/plugin/

Now the symbolic link must be created:

ln -s /usr/lib/x86_64-linux-gnu/libmariadb3/plugin/caching_sha2_password.so /usr/lib64/mysql/plugin/caching_sha2_password.so

With these steps the server will be able to use SHA2 authentication to connect to the database. Go to next step (restart Pandora FMS server) to finish the configuration.

To be executed:

systemctl restart pandora_server

After a few moments, the status must be confirmed via the Web Console. (menú Management → Servers → Manage servers):

Back to Pandora FMS Documentation Index