Unable to synch Alpha and Beta database in a DR setup

Installation Issues
1

Checklist
I have read intro post: About the Installation Issues category
I have read the tutorials, help and searched for similar issues
I provide relevant information about my server (component names and versions, etc.)
I provide a copy of my logs and healthcheck
I describe the steps I have taken to trouble shoot the problem
I describe the steps on how to reproduce the issue

Hello everyone,

My plan is to set up two passbolt servers, alpha and beta, so that the database of the first replicates on the second. To do this, I followed the tutorial below:

First, I successfully configured the alpha server, then I registered a few passwords on it. Finally, I followed official Passbolt tutorial to include a self-signed certificate and unlock HTTPS functionality.

I then configured the beta server. I followed the steps in the clustering tutorial mentioned at the beginning of this post (importing .pem keys and backing up the alpha server database). However, when it came to synchronizing the beta server database with that of the alpha server, I got the following error message:

Error connecting to source ‘replication_user@xxxxxxxxxxxx’. This was attempt 5262/86400, with a delay of 10 seconds between attempts. Message: Authentication plugin ‘caching_sha2_password’ reported error: Authentication requires secure connection.

In response, I imported alpha’s HTTPS certificate on beta, and retried database synchronization on both servers, without success.

Alpha & beta servers information:

Ubuntu 22.04LTS

Nginx

MySQL 8.0.34

PHP 8.1.2

Passbolt CE 4.3.0

Environment
PHP version 8.1.2-1ubuntu2.14.
PCRE compiled with unicode support.
The temporary directory and its content are writable and not executable.
The logs directory and its content are writable.
GD or Imagick extension is installed.
Intl extension is installed.
Mbstring extension is installed.
L'accès SSL est activé.
Config files
The application config file is present
The passbolt config file is present
Core config
Debug mode is off.
Cache is working.
Unique value set for security.salt
Full base url is set to https://xxxxxxxxxxxxx
App.fullBaseUrl validation OK.
/healthcheck/status is reachable.
Base de données
The application is able to connect to the database
32 tables found
Some default content is present
The database schema up to date.
Configuration GPG
PHP GPG Module is installed and loaded.
The environment variable GNUPGHOME is set to /var/lib/passbolt/.gnupg.
The directory /var/lib/passbolt/.gnupg containing the keyring is writable by the webserver user.
The server OpenPGP key is not the default one
The public key file is defined in /etc/passbolt/passbolt.php and readable.
The private key file is defined in /etc/passbolt/passbolt.php and readable.
The server key fingerprint matches the one defined in /etc/passbolt/passbolt.php.
The server public key defined in the /etc/passbolt/passbolt.php (or environment variables) is in the keyring.
There is a valid email id defined for the server key.
The public key can be used to encrypt a message.
The private key can be used to sign a message.
The public and private keys can be used to encrypt and sign a message.
The private key can be used to decrypt a message.
The private key can be used to decrypt and verify a message.
The public key can be used to verify a signature.
The server public key format is Gopengpg compatible.
The server private key format is Gopengpg compatible.
Application configuration
Using latest passbolt version (4.3.0).
Passbolt is configured to force SSL use.
App.fullBaseUrl is set to HTTPS.
Selenium API endpoints are disabled.
Search engine robots are told not to index content.
The deprecated self registration public setting was not found in /etc/passbolt/passbolt.php.
Host availability checking is disabled.
Serving the compiled version of the javascript app.
Some email notifications are disabled by the administrator.

Any ideas?

Thanks in advance!

2

When you say that do you mean you imported it into the trust store or used the same exact cert on both?

3

Hello and thank you for your answer !

I used the same exact cert on both alpha and beta.

4

Alright, so since you are on MySQL 8 they changed the default authentication which is why you have this error and I didn’t when setting up the blog post. I believe this is the MySQL documentation you’ll need to take a look at to sort this out

5

Thank you for your help. I have read the documentation but I still have some doubts.

Following the Passbolt tutorial, I created a mysql user named “replication_user” for the database replication task from the alpha server to beta. If I understand correctly, I need to create a new user to perform this task in this way?

CREATE USER 'sha2user'@'localhost'
IDENTIFIED WITH caching_sha2_password BY 'password';

Also, do I need to create this user on both my servers?

Secondly, it looks like I need to add the following lines to the mysql configuration file:

[mysqld]
default_authentication_plugin=caching_sha2_password

Is the configuration file /etc/mysql/my.cnf?

I’ve seen that it’s possible to generate RSA keys using the caching_sha2_password protocol. Is this necessary for my needs, i.e. simply to synchronize a database from an alpha server to a beta server?

Thank you very much for your help!

Translated with DeepL Translate: The world's most accurate translator (free version)

6

Just a reminder the blog post was more meant as a sample of how you could do it and not a step by step instruction guide to follow. Also that post was using MariaDB and you are using MySQL 8 so it’ll be a bit different.

The equivalent MySQL replication documentation will probably be better to follow in your case regarding the user configuration.

As for which authentication method to use here that is going to be determined by your needs. If I am not mistaken you can override the default authentication with the mysql_native_password option but you’d have to decide if that fits what you want to do.

7

Hello and thank you for your reply.

While searching the internet I finally found a way to get around the error message I was encountering, you’ll find the solution in the last post of this link:

Hopefully this will help others!