Gateway and MQTT (mosquitto ) are running on my raspberry pi zero, looks like connections to that are ok.
The application service is running, however I’m getting these errors every 2 seconds:
chirpstack-application-server[290872]: time=“2021-02-01T15:45:52+11:00” level=warning msg=“storage: ping PostgreSQL database error, will retry in 2s” error=“pq: password authentication failed for user “appserver””
I know it’s connecting, because if I change the password it throw an authentication error with the chirpstack_as_events username… then if I get the username correct, it throws an error using the ‘appserver’ username.
It’s as if the appserver user has been hard coded?
Config:
I’ve tried a few combinations under the [postgresql] stanza, and also
under the [application_server.integration.postgresql] stanza.
I’ve tried using the dsn format, and also the format as below
I can confirm I can login as the chirpstack_as_events user, using the password in my dsn.
I’ll note I tried changing the user… I changed it from chirpstack_as_events to chirpstack_as_even
Feb 1 16:47:53 servername chirpstack-application-server[4097]: time=“2021-02-01T16:47:53+11:00” level=warning msg=“storage: ping PostgreSQL database error, will retry in 2s” error=“pq: role “chirpstack_as_even” is not permitted to log in”
Feb 1 16:47:56 servername chirpstack-application-server[4097]: time=“2021-02-01T16:47:56+11:00” level=warning msg=“storage: ping PostgreSQL database error, will retry in 2s” error=“pq: role “chirpstack_as_even” is not permitted to log in”
Feb 1 16:47:58 servername chirpstack-application-server[4097]: time=“2021-02-01T16:47:58+11:00” level=warning msg=“storage: ping PostgreSQL database error, will retry in 2s” error=“pq: role “chirpstack_as_even” is not permitted to log in”
Feb 1 16:48:00 servername chirpstack-application-server[4097]: time=“2021-02-01T16:48:00+11:00” level=warning msg=“storage: ping PostgreSQL database error, will retry in 2s” error=“pq: role “chirpstack_as_even” is not permitted to log in”
—>>> At this point I changed the config .toml file dsn username from chirpstack_ns_events to chirpstack_ns_even. Ensuring the password matched.
Then I did a systemctl restart chirpstack-application-server.service
The new table was blank, so it did some setup:
Feb 1 16:48:02 servername chirpstack-application-server[4097]: time=“2021-02-01T16:48:02+11:00” level=info msg=“storage: applying PostgreSQL data migrations”
Feb 1 16:48:02 servername chirpstack-application-server[4097]: time=“2021-02-01T16:48:02+11:00” level=info msg=“storage: PostgreSQL data migrations applied” count=0
Feb 1 16:48:02 servername chirpstack-application-server[4097]: time=“2021-02-01T16:48:02+11:00” level=info msg=“integration: configuring global integrations”
Feb 1 16:48:02 servername chirpstack-application-server[4097]: time=“2021-02-01T16:48:02+11:00” level=info msg=“integration/mqtt: TLS config is empty”
Feb 1 16:48:02 servername chirpstack-application-server[4097]: time=“2021-02-01T16:48:02+11:00” level=info msg=“integration/mqtt: connecting to mqtt broker” server=“tcp://192.168.50.76:1883”
Feb 1 16:48:02 servername chirpstack-application-server[4097]: time=“2021-02-01T16:48:02+11:00” level=info msg=“integration/postgresql: connecting to PostgreSQL database”
Feb 1 16:48:02 servername chirpstack-application-server[4097]: time=“2021-02-01T16:48:02+11:00” level=info msg=“integration/mqtt: connected to mqtt broker”
Feb 1 16:48:02 servername chirpstack-application-server[4097]: time=“2021-02-01T16:48:02+11:00” level=info msg=“integration/mqtt: subscribing to tx topic” qos=0 topic=application/+/device/+/command/down
Then it does it again!
Feb 1 16:48:02 servername chirpstack-application-server[4097]: time=“2021-02-01T16:48:02+11:00” level=warning msg=“integration/postgresql: ping PostgreSQL database error, will retry in 2s” error=“pq: password authentication failed for user “appserver””
Feb 1 16:48:04 servername chirpstack-application-server[4097]: time=“2021-02-01T16:48:04+11:00” level=warning msg=“integration/postgresql: ping PostgreSQL database error, will retry in 2s” error=“pq: password authentication failed for user “appserver””
Feb 1 16:48:06 servername chirpstack-application-server[4097]: time=“2021-02-01T16:48:06+11:00” level=warning msg=“integration/postgresql: ping PostgreSQL database error, will retry in 2s” error=“pq: password authentication failed for user “appserver””
It seems my issue was caused by a missing " on the postgres line in the toml file. When I tried changing the username the application server refused to start, and pointed to the postgres line on the toml file. Fixing the missing quotation, allowed the server to start correctly and the error is no longer appearing.
The question remains why the server was able to start in the beginning, but for now at least I can continue with the setup.
Nope, still didn’t have any luck getting this to work. I just put it down to the build being broken.
I did check through all the quotes, nothing like that on my end.
Strangely enough is the password to postgres is CORRECT, it shows that — authentication failed for user “appserver” — , but is the password is INCORRECT, it will show — authentication failed for user “username_i_entered”…
I think this had something to do with how finicky the .toml file format is, specifically the PostgresSQL DSN, so I deleted the old .toml file and started with a new one, I ended up using the format:
[postgresql]
dsn=“postgres://dbusername:dbpassword@localhost/chirpstack_as_events?sslmode=disable”
and this worked.
Despite the notes in the .toml file saying I could use the alternative format (which I was using), it didn’t work for me.
It looks like I’ve specified it twice, again in the
[application_server.integration.postgresql]
dsn=“postgres://dbusername:dbpassword@localhost/chirpstack_as_events?sslmode=disable”
To sort out any issues with user/passwords I ended up using pgAdmin, from memory postgres only listens on localhost by default, so there was some changes to /var/lib/pgsql/13/data/postgresql.conf file needed like the listen_addresses - and the pg_hba.conf to allow another host to connect (only if you want to use pgAdmin, I highly recommend it, pgsql on the cli isn’t incredibly intuitive.
idk what it was…
I also had such a problem “…failed for user “appserver…”
And i just reentered <dsn=…> row in cfg file, save, restart this service and it started to work.
I’m using the default settings <dsn=“postgres://chirpstack_as:dbpassword@localhost/chirpstack_as?sslmode=disable”>
*ubuntu 20.4.3 +VirtualBox