Problem accessing database

quetebe · May 23, 2024, 8:20am

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

Hi, I had a computer with passbolt that has died, I have been able to save the files on the disk, I have the database and everything, I have done on another computer a clean install with the same server name and same mails, and I have replaced your mysql database by the dead computer, but it gives me an error when trying to start the passbolt, I do not know if I would have to do something else or pass some more file to work, if not possible, is there any way I can access the data from the database? I need to see the passwords to save them.

Steph · May 23, 2024, 8:27am

Hello @quetebe !

As per the error message, your passbolt instance can’t connect to your database instance.

First thing to test IMO is to check:

if the database server is running
if you could access your database manually via the command line and so on.

Also to bring more details, could you provide us with the current OS you are using, your passbolt server version?

quetebe · May 23, 2024, 8:45am

Hi @Steph

The database is up and running and I can log in through the command line, I am using ubuntu 22.04.4 and the passbolt version is 4.8.

Steph · May 23, 2024, 8:48am

Where you able to try with the passbolt credentials from the configuration? I suppose they are different than the one shown here.

quetebe · May 23, 2024, 8:56am

To access the mysql command line I have used the user that I put at the beginning that is in the file passbolt.php, if I try to access with a user of the passbolt itself with an email it does not let me.

antony · May 23, 2024, 9:02am

I don’t understand when you say “if I try to access with a user of the passbolt itself with an email it does not let me.”

Are you trying to logging to MySQL via an email? e.g. mysql -u admin@passbolt.com -p passboltdb ?

I saw in the screenshot that to previously logging to MySQL you used the user: quetebe as well as a password associated, this raise two questions in my mind:

Is quetebe the user that is used in the datasources credentials inside /etc/passbolt/passbolt.php ?
Does he have enough permissions on your passbolt table? (most likely passboltdb)

quetebe · May 23, 2024, 9:05am

Yes, excuse my lack of knowledge, I am very novice in the matter, I have accessed in principle with the user that is in /etc/passbolt/passbolt.php , and without problems, then I tried to access with a user of passbolt through the mail as you say, that has not gone, if I enter with the user that is in /etc/passbolt/passbolt.php could see all the passwords?

antony · May 23, 2024, 9:14am

No worries, we are here to help.

I am trying to understand the issues there and the possible steps you may have already taken:

Since you are on Ubuntu and you mentioned a migration, have you followed this documentation?
- This is really important, have you followed every steps?
You confirm that the credentials in datasources (username, password) from /etc/passbolt/passbolt.php works?
- Once logged in, can you share the output of SHOW DATABASES;

My assumptions here is that you migrated to a new server, and the only thing that you’ve migrated is the database

quetebe · May 23, 2024, 9:20am

I have only copied the database, I can access the files on the dead computer, but I could not do the migration well because I could no longer access passbolt and I had no backup, only the files.

antony · May 23, 2024, 10:41am

I have tried to investigate and I am not able to reproduce the issues, here is what I have done:

Create a server on Debian 12
a. Create 3 users
b. Create 3 groups
c. Create 5-10 resources per user
d. Create 2-4 folders per users
e. Share some folders with groups, individual users
d. Share some secrets with groups, individual users
I did a mysqdump of the database
I created a Ubuntu 22.04 server
Installed a fresh instance on it
a. Completing the installation through the server wizard
b. Completing the installtion through /install browser page
c. Completing the admin account confiugration
Imported the mysqldump.sql to the current database using mysql -u PASSBOLT_DATABASE_USER -p PASSBOLT_DATABASE < mysqldump.sql
Ensuring that the credentials in /etc/passbolt/passbolt.php are correct

The errors I have is mostly due to the difference between the server keys that prevent some decryption with what has been encrypted with the old server key (e.g. SMTP →
[FAIL] SMTP Setting errors: The OpenPGP server key cannot be used to decrypt the SMTP settings stored in database. To fix this problem, you need to configure the SMTP server again. Decryption failed. decrypt failed

I also tried to update the database credentials from /etc/passbolt/passbolt.php with wrong credentials OR wrong database name and I am not able to have the same error message, here is mine

WRONG database name:

2024-05-23 10:34:51 error: [Cake\Database\Exception\MissingConnectionException] Connection to Mysql could not be established: SQLSTATE[HY000] [1044] Access denied for user 'passboltadmin'@'localhost' to database 'passsboltdb' in /usr/share/php/passbolt/vendor/cakephp/cakephp/src/Database/Driver.php on line 144
Caused by: [PDOException] SQLSTATE[HY000] [1044] Access denied for user 'passboltadmin'@'localhost' to database 'passsboltdb' in /usr/share/php/passbolt/vendor/cakephp/cakephp/src/Database/Driver.php on line 136
2024-05-23 10:34:51 error: Could not connect to Database.

WRONG credentials:
2024-05-23 10:36:00 error: [Cake\Database\Exception\MissingConnectionException] Connection to Mysql could not be established: SQLSTATE[HY000] [1045] Access denied for user 'passboltadmin'@'localhost' (using password: YES) in /usr/share/php/passbolt/vendor/cakephp/cakephp/src/Database/Driver.php on line 144
Caused by: [PDOException] SQLSTATE[HY000] [1045] Access denied for user 'passboltadmin'@'localhost' (using password: YES) in /usr/share/php/passbolt/vendor/cakephp/cakephp/src/Database/Driver.php on line 136
2024-05-23 10:36:01 error: Could not connect to Database.

This is why I think that the issues may be somewhere else, it might be not enough permissions.

From the MySQL CLI, could you run:

GRANT ALL PRIVILEGES ON databaseName.* TO ‘DATABASE_USERNAME’@‘’ IDENTIFIED BY ‘USER_DATABASE_PASSWORD’;
FLUSH PRIVILEGES;

Also, if the issues persist, could you share some logs from MySQL?

tail -n 100 /var/log/mysql/error.log

Termindiego25 · May 23, 2024, 10:43am

I don’t know if this is useful, but I think the dead computer is related to this previous post we were trying to solve yesterday

If so, he can’t run any MySQL commands

antony · May 23, 2024, 10:45am

Hello @Termindiego25 , thanks for hoping in it looks like @quetebe have been able to go further on the issues based on his screenshot I am able to see the mysql CLI

quetebe · May 23, 2024, 11:05am

Yes sorry, it’s me, I managed to start the mysql server and give it more space, but as I got another problem I opened another post, I do not know if it is what I should have done, the truth is that this is being a nightmare, sorry for the inconvenience.

To give me that error what I did was to change the database of the new machine for the old one, which was called the same and all, I just did that, I opened the mysql folder and replaced the database files QuetebePassboltdb, I do not know if I should have done something else to make it work well or something, I have a snapshot of the machine before I made the change, in case you think it’s better if I go back and do it another way, I tried to do what you told me but I get a syntax error, I put GRANT ALL PRIVILEGES ON QuetebePassboltdb TO ‘user@mail’ IDENTIFIED BY ‘password’;
Using the passbolt user with his password.

And this is the log

antony · May 23, 2024, 1:29pm

All right, thanks for the screenshot.

Just to confirm, to perform the database backup you didn’t use:

sudo su -s /bin/bash -c "/usr/share/php/passbolt/bin/cake passbolt mysql_export" www-data

Which method did you use instead? (ideally the whole detailed steps so it’s easier to reproduce and potentially find a solution)

quetebe · May 23, 2024, 2:09pm

It tells me that the user quetebe does not have privileges, but I have been able to log in before and see all the tables.

Steph · May 23, 2024, 2:18pm

According to what I see in your error messages, I’m pretty sure you copy pasted the raw files from the old computer to the new one but you didn’t run the command @antony showed right?

In that case, did you follow the link mentioned in the error message to help recovering the database? Unfortunatelly Mysql or MariaDB can’t just be copy pasted like that, there are extra procedure to be done.

quetebe · May 23, 2024, 2:22pm

Sorry if I don’t express myself well sometimes, English is not my native language, yes, that’s what I did, I just copied and pasted the database after doing a clean install, but because I couldn’t do a backup or anything because I couldn’t access the computer where the database came from, as I mentioned before, I have a snapshot from before I pasted the database and replaced it with the blank one, if necessary or easier I can go back before replacing it, but the problem would be the same, no?

clayton · May 23, 2024, 2:34pm

What issue did you have before you copied the files?

To recover here you will likely need to:

Restore the snapshot
Take a backup, you have 3 options depending on the state of your system
- Follow our guide using a passbolt command
- Follow our backup blog post
  - It uses the command from the other page and collects more files too
- Take a manual backup
  - mysqldump -u username -p databasename > passbolt_backup.sql
  - You’ll also need to collect the other files mentioned in the backup documentation

Once you have done that it’ll be a lot easier to restore on a new instance

quetebe · May 24, 2024, 6:09am

The problem I had was that the computer where I had passbolt died, the system was damaged and it was impossible to start it, so I could not make a backup, the only thing I could do is save the files, including the database. What I did was on a new computer, do a clean install of passbolt with the same users and settings I had before, but I need to recover the passwords I had. I have the database of the dead computer as I said and all the files, so if I restore the snapshot as you say, what I understand, is that if I make a backup will do it from the empty database, the new one I created, but the one I need to restore is the computer that does not start and of which I only have the raw data, as I say I can access all the data but not start it.
If I try to make the backup as I have the system now I get the following error “Please ensure on MySQL that the user has PROCESS privileges”.

clayton · May 24, 2024, 6:25am

So just to be completely clear, the snapshot is not of the old working instance, but is of the new empty one?

Unfortunately saving just the files of the database from a crashed system is not the way to restore them. Did you have any backups from when it was running that are a bit older?