Remote mysql db connection with docker-compose

danclarke_2000 · December 2, 2022, 2:59pm

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

Hi,

I am using docker-compose, but instead of the local docker mysql db I want to use an existing db cluster which is setup for backup.

To this end I modified the docker-compose file; copied below

remove the db service and its dependency under passbolt
updated the environment variables as below

The following behaviour happens

docker-compose ps gives me status = running for passbolt.
docker compose logs gives me only 1 line; waiting for timeout. I set the DEBUG variable to true and still only 1 line.
no tables get created in the db so passbolt is probably not connecting
the exec command to setup first user gives me output “Exception: SQLSTATE[42S02]: Base table or view not found: 1146 Table ‘passbolt.users’ doesn’t exist”
i checked that if i connect to mysql from the same host where I have passbolt, copy/pasting the values from docker-compose-ce.yaml. I then check that user can see that database and create a test table.
curl to the mapped ports (localhost:8090, localhost:8091) fails

Notes:

this is running on amazon linux x86_64, using docker-compose
i have not set the env variables for the SMTP; but i expect to be able to start the service without the email configuration and do the email part later so i dont think this is the first problem to solve
I tried the DATASOURCES_DEFAULT_HOST with and without the mysql prefix; both “mysql://url.amazonrds.com” and “url.amazonrds.com”
the value for APP_FULL_BASE_URL points to a load balancer, so if passbolt tries to request to that value it will get an error until the target becomes healthy
I havent created a SSL cert (but i’d expect the http endpoint to still send back something)

What i expected

curl to get a 200 or 302 response
tables to get created in the backend database
more logs
the exec command that creates the first user password to succeed (see command below)

[passpolt@ip-aaa ~]$ cat docker-compose-ce.yaml
version: '3.9'
services:
  passbolt:
    image: passbolt/passbolt:latest-ce
    restart: unless-stopped
    environment:
      APP_FULL_BASE_URL: https://mydomain.com
      DATASOURCES_DEFAULT_HOST: "mysql://auroramysql.rds.amazonaws.com"
      DATASOURCES_DEFAULT_USERNAME: "passbolt"
      DATASOURCES_DEFAULT_PASSWORD: “xxx”
      DATASOURCES_DEFAULT_DATABASE: "passbolt"
    volumes:
      - gpg_volume:/etc/passbolt/gpg
      - jwt_volume:/etc/passbolt/jwt
    command: ["/usr/bin/wait-for.sh", "-t", "0", "db:3306", "--", "/docker-entrypoint.sh"]
    ports:
     - 8090:80
     - 8091:433

volumes:
  gpg_volume:
  jwt_volume:

docker-compose -f docker-compose-ce.yaml exec passbolt su -m -c "/usr/share/php/passbolt/bin/cake \
                                passbolt register_user \
                                -u user@email.com -f username -l name -s /bin/sh www-data

clayton · December 2, 2022, 3:43pm

Thanks for the detailed description of what is going on.

First thing that comes to mind is, did you create a passbolt database on your RDS instance?

If not you’ll want to log into the RDS instance and run:

CREATE DATABASE passbolt CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

This is from the CE from Source page.

After that could you run the healthcheck and migration status commands from the docker troubleshooting page and post the output?

This will give a bit more info on where the issue is.

danclarke_2000 · December 2, 2022, 6:46pm

ok thanks this has moved me forward; the healthcheck script confirms the app can connect to the database, but there are other errors

I cleared out the docker volumes and db, ran the db migration script as per the message in the healthcheckoutput

It seems like it didnt generate a self-signed cert either; so maybe some of the automatic actions that are supposed to happen on first run dnot happen or didnt happen in my case. I can create a cert and populate it via the variables and see how that helps. it looks like this is the root cause of most of the errors.

the error ‘Could not reach the /healthcheck/status with the url specified in App.fullBaseUrl’ → this will be tough to solve if there isnt a way to ignore because that URL resolves to a load balancer which only fwd’s traffic when the target group is healthy. so it will be chicken and egg. unless there’s a way to override or make that first test use a differnet url like localhost

I’ll try these things and let you know

Below are two things, the healthcheck output and also the output of the export fingerpprint command

www-data@5385af052d20:/usr/share/php/passbolt$ export PASSBOLT_GPG_SERVER_KEY_FINGERPRINT="$(gpg \
  --home $GNUPGHOME\
  --list-keys \
  ${PASSBOLT_KEY_EMAIL:-passbolt@yourdomain.com} | \
  grep -Ev "^(pub|sub|uid|^$)" | tr -d ' ')"
gpg: error reading key: No public key

www-data@5385af052d20:/usr/share/php/passbolt$ ./bin/cake passbolt healthcheck

     ____                  __          ____
    / __ \____  _____ ____/ /_  ____  / / /_
   / /_/ / __ `/ ___/ ___/ __ \/ __ \/ / __/
  / ____/ /_/ (__  |__  ) /_/ / /_/ / / /
 /_/    \__,_/____/____/_.___/\____/_/\__/

 Open source password manager for teams
-------------------------------------------------------------------------------
 Healthcheck shell......Warning Error: file_get_contents(/etc/passbolt/gpg/serverkey.asc): failed to open stream: No such file or directory
In [/usr/share/php/passbolt/src/Utility/Healthchecks/GpgHealthchecks.php, line 456]

2022-12-02 18:40:21 warning: Warning (2): file_get_contents(/etc/passbolt/gpg/serverkey.asc): failed to open stream: No such file or directory in [/usr/share/php/passbolt/src/Utility/Healthchecks/GpgHealthchecks.php, line 456]
Warning Error: file_get_contents(/etc/passbolt/gpg/serverkey_private.asc): failed to open stream: No such file or directory
In [/usr/share/php/passbolt/src/Utility/Healthchecks/GpgHealthchecks.php, line 458]

2022-12-02 18:40:21 warning: Warning (2): file_get_contents(/etc/passbolt/gpg/serverkey_private.asc): failed to open stream: No such file or directory in [/usr/share/php/passbolt/src/Utility/Healthchecks/GpgHealthchecks.php, line 458]

-------------------------------------------------------------------------------

 Environment

 [PASS] PHP version 7.4.33.
 [PASS] PCRE compiled with unicode support.
 [PASS] The temporary directory and its content are writable and not executable.
 [PASS] The logs directory and its content are writable.
 [PASS] GD or Imagick extension is installed.
 [PASS] Intl extension is installed.
 [PASS] Mbstring extension is installed.

 Config files

 [PASS] The application config file is present
 [WARN] The passbolt config file is missing in /etc/passbolt/
 [HELP] Copy /etc/passbolt/passbolt.default.php to /etc/passbolt/passbolt.php
 [HELP] The passbolt config file is not required if passbolt is configured with environment variables

 Core config

 [FAIL] Debug mode is on.
 [HELP] Set debug = false; in config/passbolt.php
 [PASS] Cache is working.
 [PASS] Unique value set for security.salt
 [PASS] Full base url is set to https://passbolt.alpsecpocs.eu
 [PASS] App.fullBaseUrl validation OK.
 [FAIL] Could not reach the /healthcheck/status with the url specified in App.fullBaseUrl
 [HELP] Check that the domain name is correct in config/passbolt.php
 [HELP] Check the network settings

 SSL Certificate

 [FAIL] SSL peer certificate does not validate
 [FAIL] Hostname does not match when validating certificates.
 [WARN] Using a self-signed certificate
 [HELP] Check https://help.passbolt.com/faq/hosting/troubleshoot-ssl

 Database

 [PASS] The application is able to connect to the database
 [PASS] 26 tables found
 [PASS] Some default content is present
 [PASS] The database schema up to date.

 GPG Configuration

 [PASS] PHP GPG Module is installed and loaded.
 [PASS] The environment variable GNUPGHOME is set to /var/lib/passbolt/.gnupg.
 [PASS] The directory /var/lib/passbolt/.gnupg containing the keyring is writable by the webserver user.
 [PASS] The server OpenPGP key is not the default one
 [FAIL] The public key file is not defined in config/passbolt.php or not readable.
 [HELP] Ensure the public key file is defined by the variable passbolt.gpg.serverKey.public in config/passbolt.php.
 [HELP] Ensure there is a public key armored block in the key file.
 [HELP] Ensure the public key defined in config/passbolt.php exists and is accessible by the webserver user.
 [HELP] See. https://www.passbolt.com/help/tech/install#toc_gpg
 [FAIL] The private key file is not defined in config/passbolt.php or not readable.
 [HELP] Ensure the private key file is defined by the variable passbolt.gpg.serverKey.private in config/passbolt.php.
 [HELP] Ensure there is a private key armored block in the key file.
 [HELP] Ensure the private key defined in config/passbolt.php exists and is accessible by the webserver user.
 [HELP] See. https://www.passbolt.com/help/tech/install#toc_gpg
 [FAIL] The server key fingerprint doesn't match the one defined in config/passbolt.php.
 [HELP] Double check the key fingerprint, example:
 [HELP] sudo su -s /bin/bash -c "gpg --list-keys --fingerprint --home /var/lib/passbolt/.gnupg" www-data | grep -i -B 2 'SERVER_KEY_EMAIL'
 [HELP] SERVER_KEY_EMAIL: The email you used when you generated the server key.
 [HELP] See. https://www.passbolt.com/help/tech/install#toc_gpg
 [FAIL] The server public key defined in the config/passbolt.php (or environment variables) is not in the keyring
 [HELP] Import the private server key in the keyring of the webserver user.
 [HELP] you can try:
 [HELP] sudo su -s /bin/bash -c "gpg --home /var/lib/passbolt/.gnupg --import /etc/passbolt/gpg/serverkey_private.asc" www-data
 [FAIL] The server key does not have a valid email id.
 [HELP] Edit or generate another key with a valid email id.

 Application configuration

 [PASS] Using latest passbolt version (3.8.3).
 [PASS] Passbolt is configured to force SSL use.
 [PASS] App.fullBaseUrl is set to HTTPS.
 [PASS] Selenium API endpoints are disabled.
 [PASS] Search engine robots are told not to index content.
 [PASS] Registration is closed, only administrators can add users.
 [PASS] Serving the compiled version of the javascript app
 [PASS] All email notifications will be sent.

 JWT Authentication

 [PASS] The JWT Authentication plugin is enabled
 [PASS] The /etc/passbolt/jwt/ directory is not writable.
 [FAIL] A valid JWT key pair is missing
 [HELP] Run the create JWT keys script to create a valid JWT secret and public key pair:
 [HELP] sudo su -s /bin/bash -c "/usr/share/php/passbolt/bin/cake passbolt create_jwt_keys" www-data

 SMTP Settings

 [PASS] The SMTP Settings plugin is enabled.
 [PASS] SMTP Settings coherent. You may send a test email to validate them.
 [WARN] The SMTP Settings source is: env variables.
 [HELP] It is recommended to set the SMTP Settings in the database through the administration section.

 [FAIL] 10 error(s) found. Hang in there!

Many thanks

clayton · December 5, 2022, 11:31am

When you cleared out the volumes and DB did you clear all of the volumes, or just specific ones?

The line:

[FAIL] Could not reach the /healthcheck/status with the url specified in App.fullBaseUrl

can be safely ignored for now, the container should still accept connections with that being the case.

The GPG issues are something to look more at though. You may need to specify some of these environment variables in docker, which the moving from one docker to a new one page might help shed some light here as well. This could explain the mismatch for the keys.