Postgres update from 10 to 15 failed (with fix for: User "dsmrreader" does not have a valid SCRAM secret)

gctwnl commented 9 months ago

Support guidelines

[X] I've read the support guidelines

I've found an issue and checked that ...

[X] ... the documentation does not mention anything about my problem
[X] ... there are no open or closed issues that are related to my problem

Description

I have followed the instructions in the READM.md to update my setup. I have used the route:

docker exec -t dsmrdb pg_dumpall -c -U dsmrreader > dsmrreader.sql
# move old Postgres 10.5 database, create empty new one with Postgres 15.5:
cat dsmrreader.sql | docker exec -i dsmrdb psql -U dsmrreader

But this fails on first attempt of dsmr app to conect to the db:

Attaching to dsmr, dsmrdb
dsmrdb  | 
dsmrdb  | PostgreSQL Database directory appears to contain a database; Skipping initialization
dsmrdb  | 
dsmrdb  | 2024-01-07 00:24:12.225 CET [1] LOG:  starting PostgreSQL 15.5 on x86_64-pc-linux-musl, compiled by gcc (Alpine 13.2.1_git20231014) 13.2.1 20231014, 64-bit
dsmrdb  | 2024-01-07 00:24:12.225 CET [1] LOG:  listening on IPv4 address "0.0.0.0", port 5432
dsmrdb  | 2024-01-07 00:24:12.225 CET [1] LOG:  listening on IPv6 address "::", port 5432
dsmrdb  | 2024-01-07 00:24:12.231 CET [1] LOG:  listening on Unix socket "/var/run/postgresql/.s.PGSQL.5432"
dsmrdb  | 2024-01-07 00:24:12.235 CET [25] LOG:  database system was shut down at 2024-01-07 00:22:28 CET
dsmrdb  | 2024-01-07 00:24:12.241 CET [1] LOG:  database system is ready to accept connections
dsmr    | [s6-init] making user provided files available at /var/run/s6/etc...exited 0.
dsmr    | [s6-init] ensuring user provided files have correct perms...exited 0.
dsmr    | [fix-attrs.d] applying ownership & permissions fixes...
dsmr    | [fix-attrs.d] done.
dsmr    | [cont-init.d] executing container initialization scripts...
dsmr    | [cont-init.d] 10-adduser: executing... 
dsmr    | usermod: no changes
dsmr    | 
dsmr    | -------------------------
dsmr    | User UID: 803
dsmr    | User GID: 803
dsmr    | -------------------------
dsmr    | 
dsmr    | [cont-init.d] 10-adduser: exited 0.
dsmr    | [cont-init.d] 20-set-app-defaults: executing... 
[ INFO ] DSMR Reader Reader release: 5.10.4
[ INFO ] DSMR Reader Reader Docker release: 2023.11.01
[ INFO ] Ensure permissions...
[ INFO ] Setting architecture requirements...
[ INFO ] X64 Architecture
[ INFO ] Verifying if the DSMR Reader web credential variables have been set...
[ INFO ] Verifying database connectivity to host: dsmrdb with port: 5432...
[ INFO ] Database connectivity successfully verified!
[ INFO ] Running post configuration...
dsmr    | /app/dsmr_parser/parsers.py:41: SyntaxWarning: invalid escape sequence '\d'
dsmr    |   """
dsmrdb  | 2024-01-07 00:24:19.399 CET [30] FATAL:  password authentication failed for user "dsmrreader"
dsmrdb  | 2024-01-07 00:24:19.399 CET [30] DETAIL:  User "dsmrreader" does not have a valid SCRAM secret.
dsmrdb  |   Connection matched pg_hba.conf line 100: "host all all all scram-sha-256"
dsmr    | Traceback (most recent call last):
dsmr    |   File "/usr/local/lib/python3.12/site-packages/django/db/backends/base/base.py", line 219, in ensure_connection
dsmr    |     self.connect()
dsmr    |   File "/usr/local/lib/python3.12/site-packages/django/utils/asyncio.py", line 33, in inner
dsmr    |     return func(*args, **kwargs)

I was able to fix this by:

Stop everything
Empty the postgres database folder
create and start dsmrdb (creates a basic db)
stop dsmrdb
change sha-256 to md5 (what it was when I used 10.5) in pg_hba.conf
start dsmrdb
cat dsmrreader.sql | docker exec -i dsmrdb psql -U dsmrreader
docker compose down and docker compose up -d

The only problem left is that I got 4 warnings about 46 minutes lost, but these disappeared later. I am creating the issue here so that it may help others who move from Postgres 10 to 15

Expected behaviour

Database update instructions worked

Actual behaviour

Database update instructions were invalid for Postgres versions where the previous mechanism was md5 while the new one is sha-256

Steps to reproduce

Update a Postgres 10.5 version (which I installed last year when I started to use it)

Docker info

root@snape:/mnt/ServerData/dsmrreader# docker info
Client: Docker Engine - Community
 Version:    24.0.7
 Context:    default
 Debug Mode: false
 Plugins:
  buildx: Docker Buildx (Docker Inc.)
    Version:  v0.11.2
    Path:     /usr/libexec/docker/cli-plugins/docker-buildx
  compose: Docker Compose (Docker Inc.)
    Version:  v2.21.0
    Path:     /usr/libexec/docker/cli-plugins/docker-compose

Server:
 Containers: 17
  Running: 15
  Paused: 0
  Stopped: 2
 Images: 26
 Server Version: 24.0.7
 Storage Driver: overlay2
  Backing Filesystem: extfs
  Supports d_type: true
  Using metacopy: false
  Native Overlay Diff: true
  userxattr: false
 Logging Driver: json-file
 Cgroup Driver: systemd
 Cgroup Version: 2
 Plugins:
  Volume: local
  Network: bridge host ipvlan macvlan null overlay
  Log: awslogs fluentd gcplogs gelf journald json-file local logentries splunk syslog
 Swarm: inactive
 Runtimes: io.containerd.runc.v2 runc
 Default Runtime: runc
 Init Binary: docker-init
 containerd version: 3dd1e886e55dd695541fdcd67420c2888645a495
 runc version: v1.1.10-0-g18a0cb0
 init version: de40ad0
 Security Options:
  apparmor
  seccomp
   Profile: builtin
  cgroupns
 Kernel Version: 5.15.0-91-generic
 Operating System: Ubuntu 22.04.3 LTS
 OSType: linux
 Architecture: x86_64
 CPUs: 2
 Total Memory: 11.68GiB
 Name: snape
 ID: NUB7:HRXS:UK5U:3GV3:EF7Z:MYM5:Z6VU:QQAJ:VMQB:LNYK:DOY6:ZPNW
 Docker Root Dir: /var/lib/docker
 Debug Mode: false
 Experimental: false
 Insecure Registries:
  127.0.0.0/8
 Live Restore Enabled: false

Version

docker-compose is not installed on my system. I use 'docker compose' uname -a: Linux snape 5.15.0-91-generic #101-Ubuntu SMP Tue Nov 14 13:30:08 UTC 2023 x86_64 x86_64 x86_64 GNU/Linux

Docker compose

docker-compose is not installed on my system

Container logs

See description.

Additional info

My system works again (though logging in to dsmrreader I have yet to solve)

Current docker-compose.yml:

version: '3'

services:
  dsmrdb:
    image: postgres:15-alpine
    container_name: dsmrdb
    env_file:
      - postgres.env
    volumes:
      - /mnt/ServerData/dsmrreader/postgres:/var/lib/postgresql/data
      - /mnt/ServerData/dsmrreader/backups:/dsmr/backups
    restart:  unless-stopped
    labels:
      - traefik.enable=false

  dsmr:
    image: xirixiz/dsmr-reader-docker:5.10.4-2023.11.01
    container_name: dsmr
    env_file:
      - dsmr.env
    depends_on:
      - dsmrdb
    cap_add:
      - NET_ADMIN
    links:
      - dsmrdb
    restart: unless-stopped
    ports:
      - 7777:80
      - 7779:443
    devices:
      - /dev/ttyUSB0:/dev/ttyUSB0
    labels:
      - traefik.enable=false

gctwnl commented 9 months ago

I suppose this issue can be closed as I included the fix.

gctwnl commented 9 months ago

I found the errors in the GUI I lost earlier after I had fixed it

   Process behind schedule: Generate day and hour statistics   (43 minutes ago)
   Process behind schedule: Calculate quarter hour electricity peaks   (43 minutes ago)
   Process behind schedule: Weather update   (43 minutes ago)
   Process behind schedule: Generate consumption data   (46 minutes ago)

xirixiz commented 9 months ago

@gctwnl too bad it caused issues, but good that you were able to fix it, and for sharing this here.

degrashopper commented 8 months ago

@gctwnl Thanks for your description on how you fixed it. I also needed to upgrade from 10.1 to 15.5 and could not get it to work untill i found your post.

mrjmg commented 8 months ago

I just upgraded to v16 and ran into the same.

Actual fix: nano/vi pg_hba.conf change this line: host all all all scram-sha-256 into: host all all all md5

Here some background info: https://www.postgresql.org/docs/14/auth-password.html

edit: this is the default and changed as per the default too. If you have more users, probably good to change db & user dsmrreader to md5 only, while keeping the rest on scram-sha-256.

xirixiz commented 7 months ago

Hi I've created a S6 v3 overlay Docker image. I`m currently not using DSMR reader myself, so I'm unable to do some proper testing. Would you mind testing ghcr.io/xirixiz/dsmr-reader-docker:development and share the output?

It's on the pre_release branch now. If everything is working fine, I`ll merge the changes to the main branch

xirixiz / dsmr-reader-docker