Syspass: Lost connection to database after reboot

[bmols]

sysPass Version
3

Describe the question
After my VM/Containers being rebooted my syspass-app has lost connection to the syspass-db

Platform (please complete the following information):

• OS: Ubuntu 18.04
• Browser Vivaldi

Additional context
I noticed my port numbers are changing. Initially it was 32768 & 32769 forwarding to the app and 32770 to the database. Now its 32771 and 32772 forwarding to the app, and 32773 to the database. When I go into the shell of the container and type 'ping syspass-db' it returns successfully and with the right IP address. Also when I go through the steps to install a database it tells me one already exists. So I know they can communicate, but seems like something got changed and the app is looking in the wrong place for the database.

[nuxsmin]

Hello, this is a known behavior while running on Docker environment. Please read the Docker documentation to avoid this issue.

Regards

[bmols]

I figured it out. When I made the syspass docker I was still fresh to containers. I've learnt quite a bit since then and this could have been avoided had I known what I was doing.

Thanks for the work you've put into this!

[nuxsmin]

@bmols Thanks for the feedback!

[mattarc]

I figured it out. When I made the syspass docker I was still fresh to containers. I've learnt quite a bit since then and this could have been avoided had I known what I was doing.

Thanks for the work you've put into this!

@bmols Could you share your findings please? I have run into the same problem, have set it up using docker-compose and can no longer reconnect to the db after a reboot. I set the ports myself using Kitematic for both the app and the db, and they haven't changed as far as a I can tell, but nevertheless I am still not able to connect.

I have looked at the Docker documentation but am not entirely sure what I'm looking for to be honest...

Thanks

EDIT--
I now understand that the problem is not ports but to do with the IP address that docker assigns to the app and the db. I also now understand that docker re-assigns these at restart.
I've looked into controlling the order in which the containers start to try and influence the IP addresses that way, and have tried using 'depends_on' in the yaml file, however I also realise that it already has a link tag which effectively does the same thing.

As a temporary fix, if the IPs are assigned the wrong way round (always either 172.18.0.2 or 172.18.0.3) then I can manually shut down the containers and then restart them in order myself, which results in the IP addresses being correct, after which the app works again.

Is there a way to automate this? Or a better method to achieve the same goal?

All Google is revealing to me is that docker containers should refer to each other by name and not rely on an IP, so that it doesn't matter if the IP changes. I used 'syspass-db' as the database address when first installing, but this problem still exists...

Thanks again in advance for any help offered.

[9numbernine9]

Hey @mattarc - I ran into the same issue as you with docker-compose, and, like you, I can't find any relevant section of the Syspass docs that mentions this 'gotcha.' As a workaround (for you or anyone else that finds this issue), I'm using this setup to ensure that the syspass container's IP address remains consistent:

• Create a .env file in the same directory as the docker-compose.yml that contains the following variables. SUBNET is the specification of a network that docker-compose can use when creating the default network for these containers (change the value to something else if you are already using this network range on your host machine), and SYSPASS_IP is the IP address _within this subnet's range_ that the syspass container will be assigned (ensure that the last octet in this address (.10 in my example) is large enough that docker-compose won't try to use it for some other container in our Syspass setup.)

SUBNET=172.20.0.0/24
SYSPASS_IP=172.20.0.10

• Add a definition for the default network in the docker-compose.yml file that tells docker-compose to use the subnet we've specified for our containers:

networks:
  default:
    ipam:
      driver: default
      config:
      - subnet: ${SUBNET}

• Finally, in the docker-compose.yml file's syspass-app container section, explicitly set the IP address of the syspass-app container:

services:
  app:
    container_name: syspass-app
    networks:
      default:
        ipv4_address: ${SYSPASS_IP}

These changes should ensure that your syspass-app gets a consistent IP address between executions of docker-compose down and docker-compose up!

With all of the above said: this seems like more of a workaround than necessary. Maybe @nuxsmin can help us better understand why changing the IP address of the container breaks things in the first place? :)

[nuxsmin]

Hello,

sysPass shouldn't rely on IP addresses, so you should set the database container name instead.

Another question is about the database users, since sysPass will follow the least privilege access pattern, so it creates a random user tied to the hostname from which you made the installation (ie. the app container), so this feature would break the database access if the ip address/hostname of the sysPass container is changed. Please be aware that MariaDB/MySQL user authorization is built by username@hostname so this is the key point about access denied issue...

Regards

[nuxsmin]

And many thanks for your contribution @mattarc & @9numbernine9 !!

[9numbernine9]

@nuxsmin Thank you for taking the time to respond - I appreciate it!

Although I realize that the database user being created in MariaDB isn't _explicitly_ tied to the IP address, it seems to behave like it is. I've been playing with trying to explicitly specify the hostname of the Syspass container (instead of the IP address like I was doing here) and I haven't been able to make it work.

I should preface this with: based on this docker-compose issue, it seems like assigning a fixed hostname for a container created by docker-compose isn't easy. What I've _tried_ to do is use a docker-compose.yml file like this (instead of my previous example with the IP address shenanigans):

version: '3.7'
services:
  application:
    container_name: syspass
    image: syspass/syspass:3.1.2
    restart: always
    hostname: syspass
    networks:
      default:
        aliases:
          - syspass
    expose:
      - "443"
    volumes:
      - syspass-config:/var/www/html/sysPass/app/config
      - syspass-backup:/var/www/html/sysPass/app/backup
    environment:
      - VIRTUAL_PORT=443
      - VIRTUAL_PROTO=https
      - USE_SSL=yes
    env_file:
      - ./syspass/syspass-variables.env

  database:
    container_name: mariadb
    restart: always
    image: mariadb:10.3
    expose:
      - "3306"
    volumes:
      - syspass-db:/var/lib/mysql
    env_file:
      - ./mariadb/mariadb-variables.env

  proxy:
    container_name: nginx
    restart: always
    image: nginx:alpine
    ports:
      - "80:80"
      - "443:443"
    volumes:
      - nginx-conf:/etc/nginx/conf.d
      - nginx-certs:/etc/nginx/certs
      - nginx-vhost:/etc/nginx/vhost.d
      - nginx-html:/usr/share/nginx/html

  proxy-generator:
    container_name: nginx-proxy-gen
    restart: always
    image: jwilder/docker-gen
    depends_on:
      - proxy
    volumes:
      - /var/run/docker.sock:/tmp/docker.sock:ro
      - nginx-conf:/etc/nginx/conf.d
      - nginx-certs:/etc/nginx/certs
      - nginx-vhost:/etc/nginx/vhost.d
      - nginx-html:/usr/share/nginx/html
      - ./nginx/templates:/etc/docker-gen/templates
    command: "-notify-sighup nginx -watch /etc/docker-gen/templates/nginx.tmpl /etc/nginx/conf.d/default.conf"

  certificates:
    container_name: nginx-letsencrypt
    restart: always
    image: jrcs/letsencrypt-nginx-proxy-companion
    depends_on:
      - proxy
      - proxy-generator
    volumes:
      - /var/run/docker.sock:/var/run/docker.sock:ro
      - nginx-certs:/etc/nginx/certs
      - nginx-vhost:/etc/nginx/vhost.d
      - nginx-html:/usr/share/nginx/html
    environment:
      - NGINX_PROXY_CONTAINER=nginx
      - NGINX_DOCKER_GEN_CONTAINER=nginx-proxy-gen

volumes:
  syspass-config: {}
  syspass-backup: {}
  syspass-db: {}
  nginx-conf: {}
  nginx-certs: {}
  nginx-vhost: {}
  nginx-html: {}

You'll notice that I've added a hostname: syspass entry and a network: aliases: default entry. The first one assigns the hostname syspass inside the container itself, and I can verify that it works:

root@myhost:~# docker exec syspass hostname
syspass

Unfortunately, this alone doesn't allow the mariadb container to resolve the hostname 'syspass' - that's where the network: aliases: default entry comes in. It's harder to verify that this works (the mariadb Docker image doesn't contain any useful DNS tools, e.g. nslookup, dig, etc.) but we _can_ verify it with some Bash hackery:

root@myhost:~# docker exec mariadb bash -c '(echo >/dev/tcp/syspass/80) &>/dev/null && echo "open" || echo "closed"'
open

Now we know the mariadb contain can reach the syspass container with the hostname syspass. Great! Unfortunately, after doing docker-compose down and docker-compose up, we're back to the situation where Syspass can't connect to the database again: Unable to connect to DB Please contact to the administrator.

Looking at the user information in the MariaDB database, here are the users:

root@myhost:# docker exec -it mariadb mysql -u root -p
Enter password: 
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 15
Server version: 10.3.23-MariaDB-1:10.3.23+maria~bionic mariadb.org binary distribution

Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

MariaDB [(none)]> select user, host from mysql.user;
+------------------+------------+
| user             | host       |
+------------------+------------+
| root             | %          |
| sp_5ef5022dc9bb2 | 172.21.0.2 |
| root             | localhost  |
| sp_5ef5022dc9bb2 | syspass    |
+------------------+------------+
4 rows in set (0.000 sec)

Because I did docker-compose down and docker-compose up, 172.21.0.2 is no longer the IP address of the Syspass container, so that's not going to work. As for the other one, I have _no_ idea. It seems like it _should_ work, but... it doesn't?

---

All of this to say: _if_ there's a way to set up the docker-compose file so that I _don't_ need to hardcode IP/network addresses _and_ Syspass will continue to be able to log in to the database after doing docker-compose down and docker-compose up, I would like to see it. :-)

[nuxsmin]

@9numbernine9 thanks for taking your time in researching and testing the docker-compose based deployment.

I'll try to find out how to avoid such issue, since using the normal behavior of the docker-compose stack it works in some other projects that I've developed with a bunch of services within the stack.

It seems that mariadb tries to resolve the hostname but it would point to the last IP address assigned to the app container.

I'll be back with the find outs :wink:

[BerMan7328]

I'm having the same issue here.
@nuxsmin have some updates for this?
Im using the version 3.2.2

I tried to force mariadb to not skip name resolve, but didn't work
https://github.com/MariaDB/mariadb-docker/issues/280