Chirpstack to Postgresql -- connection refused

Tezla_LLC · August 12, 2024, 5:09pm

Hello.
I have tried all I can think of at this end and still i cannot get postgresql to link. I am running all from docker container and am using an env file which is located in same directory as docker-compose.yml. My chirpstack.toml is located relative to the docker-compose.yml at ./configuration/chirpstack. The following lines of code related to postgres are in files. I am intentionally mapping system port 5433 as my systemctl shows that postgresql already is open at boot and using 5432. If I try 5432 I cannot get postgresql to open.
Any ideas please?

Chirpstack.toml (excuse indentation in toml / yml due to pasting…indentation is correct)

[postgresql]
 dsn="postgres://chirpstack:chirpstack@$POSTGRESQL_HOST:5433/chirpstack?sslmode=disable"

[integration]
  enabled = ["mqtt", "postgresql"]
  [integration.mqtt]
    server = "tcp://${MQTT_BROKER_HOST}:183/"
    json = true

  [integration.postgresql]
 dsn="postgres://chirpstack:chirpstack@$POSTGRESQL_HOST:5433/chirpstack?sslmode=disable"

docker-compose.yml

  chirpstack:
    container_name: network_app_server
    image: chirpstack/chirpstack:4
    command: -c /etc/chirpstack
    restart: unless-stopped
    volumes:
      - ./configuration/chirpstack:/etc/chirpstack
    depends_on:
      - postgres
      - mosquitto
      - redis
    environment:
      - MQTT_BROKER_HOST=${MQTT_BROKER_HOST}
      - MQTT_UNAME=${MQTT_UNAME}
      - MQTT_PWD=${MQTT_PWD}
      - POSTGRESQL_HOST=${POSTGRESQL_HOST}
      - POSTGRES_USER=${POSTGRES_USER}
      - POSTGRES_PASSWORD=${POSTGRES_PASSWORD}
      - POSTGRES_DB=${POSTGRES_DB}
      - REDIS_HOST=redis
    env_file:
      - .env
    ports:
      - 8080:8080

  postgres:
    container_name: dB
    image: postgres:14-alpine
    ports:
      - 5433:5432
    restart: unless-stopped
    environment:
      - POSTGRES_USER=${POSTGRES_USER}
      - POSTGRES_PASSWORD=${POSTGRES_PASSWORD}
      - POSTGRES_DB=${POSTGRES_DB}
    volumes:
      - ./configuration/postgresql/initdb/postgres-steve-login.sh:/docker-entrypoint-initdb.d/postgres-steve-login.sh
      - postgresqldata:/var/lib/postgresql/data

.env file:

MQTT_BROKER_HOST=mosquitto
MQTT_UNAME=mqtt
MQTT_PWD=mqtt

POSTGRESQL_HOST=chirpstack
POSTGRES_USER=chirpstack
POSTGRES_PASSWORD=root
POSTGRES_DB=chirpstack

postgresql docker error log: (continually repeats)

2024-08-12T17:05:16.798159Z  INFO chirpstack::cmd::root: Starting ChirpStack LoRaWAN Network Server version="4.8.1" docs="https://www.chirpstack.io/"
2024-08-12T17:05:16.798195Z  INFO chirpstack::storage: Setting up PostgreSQL connection pool
2024-08-12T17:05:16.798426Z  INFO chirpstack::storage: Applying schema migrations
Error: Error occurred while creating a new object: error connecting to server: Connection refused (os error 111)

Caused by:
    error connecting to server: Connection refused (os error 111)

Liam_Philipp · August 12, 2024, 6:10pm

This is the line in the default chirpstack.toml:

  # Format example: postgres://<USERNAME>:<PASSWORD>@<HOSTNAME>/<DATABASE>?sslmode=<SSLMODE>.
  #
  dsn="postgres://chirpstack:chirpstack@$POSTGRESQL_HOST/chirpstack?sslmode=disable"

These are the default env variables for Chirpstack:

  - MQTT_BROKER_HOST=mosquitto
  - REDIS_HOST=redis
  - POSTGRESQL_HOST=postgres

It may have been a typo but there are [integration] sections in the .yml you shared that I assume are from your chirpstack.toml. You also don’t share how the env file is referenced in your docker-compose, or the chirpstack service which defines the variables that are passed to the container.

What it looks like the main issue would be is that your $POSTGRESQL_HOST is incorrect. The variable passed to chirpstack is postgres, not chirpstack (as shown above). That and removing the port from the .toml might fix it.

Also renaming the container to dB may cause issues with resolving “postgres” as the host, but that I’m unsure of.

The use of a .env file itself seems strange as only 3 variables need to be passed to chirpstack:

    environment:
      - MQTT_BROKER_HOST=mosquitto
      - REDIS_HOST=redis
      - POSTGRESQL_HOST=postgres

And only 1 needs to be passed to postgres:

    environment:
      - POSTGRES_PASSWORD=root

Tezla_LLC · August 12, 2024, 8:32pm

@Liam_Philipp
Yes I messed up that post quite bad…Went back and fixed it adding chirpstack in docker yaml. Thanks.

I want the env file bcz my hope is to get away with the default naming and name databases, passwords, usernames and tables to what works best for me. I am starting with the defaults that were in the github repo, hence root, chirpstack, etc.

According to everything I read you can define in env file like I have then use ${environment variable} as such which is working. Working in that containers show the correct environment variables when commands issued to check them.

As far as postgres only needing root I have a script under the volumes section which runs and checks if the table name and role have been created or not (ie the env file). If it has do NOT recreate, else create it. This is the path moving forward as I said earlier moving away from chirpstack table and username. Unless someone hard coded something in binaries I think this should be possible.

I changed your recommendation to postgres from chirpstack but still get same error. I can inspect the environment variables of the chirpstack container

 docker inspect network_app_server --format '{{json .Config.Env}}'

and it reveals they all look as they should

["MQTT_BROKER_HOST=mosquitto","MQTT_UNAME=mqtt","MQTT_PWD=mqtt","POSTGRESQL_HOST=postgres","POSTGRES_USER=chirpstack","POSTGRES_PASSWORD=root","POSTGRES_DB=chirpstack","REDIS_HOST=redis","PATH=/usr/local/sbin:/usr/local/bin:/usr/sbin:/usr/bin:/sbin:/bin"]

Just for the heck of it I renamed dB to postgres but this as I expected had no effect. Also removed the toml integration section of postgres …strictly mqtt now but still same issue
Any other ideas?

Liam_Philipp · August 12, 2024, 8:54pm

Two other things come to mind:

POSTGRES_PASSWORD=root

root is the postgres password for postgres (i.e the db admin user). Chirpstack will use password chirpstack to connect. It doesn’t look like your chirpstack.toml even calls that variable though so I think thats irrelevant.

Do you know why another postgres instance is blocking port 5432? If not, I would hunt down whats going on there as that is not typical behaviour from default Chirpstack and could be causing the issues.

As an aside it is possible to easily change the passwords of the users with psql commands from inside the container, and then just change the dsn line to match.

dsn="postgres://chirpstack:*NEW PASSWORD*@$POSTGRESQL_HOST/chirpstack?sslmode=disable"

As for changing the databases and tables names I have little knowledge on this but I would wager it would get extremely messy as Chirpstack expects certain tables to be there, but perhaps you have some insight I don’t.

Tezla_LLC · August 12, 2024, 9:07pm

@Liam_Philipp …
I am running on an RPi5 which came with postgresql…At boot it starts up and runs on 5432…per your recommendation I stopped the service and changed my toml and yml to 5432… BUT you bring up a good point (although that didn’t work either) in that the toml for chirpstack wants to see the virtual port which is 5432 and I had that set to 5433…Get the same error initially then a different one…The second error moving forward repeats.

2024-08-12T21:05:21.076370Z  INFO chirpstack::cmd::root: Starting ChirpStack LoRaWAN Network Server version="4.8.1" docs="https://www.chirpstack.io/"
2024-08-12T21:05:21.076512Z  INFO chirpstack::storage: Setting up PostgreSQL connection pool
2024-08-12T21:05:21.076706Z  INFO chirpstack::storage: Applying schema migrations
Error: Error occurred while creating a new object: error connecting to server: Connection refused (os error 111)

Caused by:
    error connecting to server: Connection refused (os error 111)
2024-08-12T21:05:22.643498Z  INFO chirpstack::cmd::root: Starting ChirpStack LoRaWAN Network Server version="4.8.1" docs="https://www.chirpstack.io/"
2024-08-12T21:05:22.643544Z  INFO chirpstack::storage: Setting up PostgreSQL connection pool
2024-08-12T21:05:22.643821Z  INFO chirpstack::storage: Applying schema migrations
Error: Error occurred while creating a new object: db error: FATAL: password authentication failed for user "chirpstack"

Liam_Philipp · August 12, 2024, 9:21pm

Ok well now you are actually connecting to the database, still some password issue though. Chirpstack should be connecting with user: chirpstack, pass: chirpstack.

Try connecting with that username password yourself in the postgres container and see if it works.

Tezla_LLC · August 12, 2024, 9:26pm

The following command at prompt allowed me connection…never asked me for password…

Liam_Philipp · August 12, 2024, 9:29pm

try with -W flag to force it to ask password: Top psql commands and flags you need to know | PostgreSQL

psql -U <username> -W

Or else once your logged in just try resetting chirpstack password to chirpstack again.

Or from a separate container/machine that can do psql try the following:

psql -h <db-address> -d <db-name> -U <username> -W

Tezla_LLC · August 12, 2024, 10:04pm

Yup #1 works …ie -W…

Liam_Philipp · August 12, 2024, 10:08pm

Then I don’t know what else to say besides ensure that chirpstack is connecting using chirpstack:chirpstack and postgres is connecting using postgres:root. I’m sure you already know that RPI5 is not supported because of openWRT issues, I have no clue whether that would play a part here or not but something to consider.

Tezla_LLC · August 12, 2024, 10:11pm

I had it working at one time without the postgresql integration so that much should work at least (just don’t know what is broke)…

Anything in the postgresql.conf file that might raise a flag?

Tezla_LLC · August 12, 2024, 11:57pm

Here is the latest I am finding wrt to the issue…
If I look at docker logs dB I get: (notice line 100 scram-sha-256)

2024-08-12 23:17:09.444 UTC [1] LOG:  listening on IPv4 address "0.0.0.0", port 5432
2024-08-12 23:17:09.444 UTC [1] LOG:  listening on IPv6 address "::", port 5432
2024-08-12 23:17:09.456 UTC [1] LOG:  listening on Unix socket "/var/run/postgresql/.s.PGSQL.5432"
2024-08-12 23:17:09.485 UTC [72] LOG:  database system was shut down at 2024-08-12 23:17:09 UTC
2024-08-12 23:17:09.495 UTC [1] LOG:  database system is ready to accept connections
2024-08-12 23:17:09.779 UTC [79] FATAL:  password authentication failed for user "chirpstack"
2024-08-12 23:17:09.779 UTC [79] DETAIL:  Connection matched pg_hba.conf line 100: "host all all all scram-sha-256"

If I then proceed to find the mount point
docker inspect volume postgresqldata I get

"Mountpoint": "/var/lib/docker/volumes/postgresqldata/_data",

which is empty???

In docker-compose.yml the mapping to the virtual folder on the container is

docker exec -it dB bin/sh 
cd /var/lib/postgresql/data
cat pg_hba.conf -n

and at line 100 this shows

My questions:

where is it getting the var/lib/postgresql/data/pg_hba.conf file from? A search on my system shows the only location that file exists is

/etc/postgresql/15/main

and that pg_hba.conf file looks like this

I tried adding it to volumes

    volumes:
      - ./configuration/postgresql/initdb/postgres-steve-login.sh:/docker-entrypoint-initdb.d/postgres-steve-login.sh
      - postgresqldata:/var/lib/postgresql/data
      - /etc/postgresql/15/main:/etc/postgresql/15/main

removing postgresqldata and recreating it but the mount point says the same thing and further inspection within the container shows the file still is using the one above with METHOD trust yet the error says scram-sha-256.

Would love to hear peoples thoughts here because something is off??

system · November 10, 2024, 11:57pm

This topic was automatically closed 90 days after the last reply. New replies are no longer allowed.