Unstable after a while, connection to Postgresql fails

[thomastvedt]

We have an issue where our mosquitto isntance using mosquitto-go-auth becomes unstable after a while (around 45 days ). The only way to resolve it is to restart the service, which is not ideal.

Log from mosquitto:

time="2021-11-25T14:11:09Z" level=debug msg="checking user thomas with backend Postgres"
time="2021-11-25T14:11:09Z" level=debug msg="PG get user error: write tcp 10.163.16.138:45996->104.44.44.123:5432: write: connection reset by peer"
time="2021-11-25T14:11:09Z" level=error msg="write tcp 10.163.16.138:45996->104.44.44.123:5432: write: connection reset by peer"
time="2021-11-25T14:11:13Z" level=debug msg="checking auth cache for thomas"
time="2021-11-25T14:11:13Z" level=debug msg="to auth record: [97 -- removed -- 7 9]\n"
time="2021-11-25T14:11:13Z" level=debug msg="checking user thomas with backend Postgres"
time="2021-11-25T14:11:13Z" level=debug msg="PG get user error: write tcp 10.163.16.138:45996->104.44.44.123:5432: write: connection reset by peer"
time="2021-11-25T14:11:13Z" level=error msg="write tcp 10.163.16.138:45996->104.44.44.123:5432: write: connection reset by peer"
time="2021-11-25T14:11:14Z" level=debug msg="checking auth cache for thomas"
time="2021-11-25T14:11:14Z" level=debug msg="to auth record: [97 --removed--- 7 9]\n"
time="2021-11-25T14:11:14Z" level=debug msg="checking user thomas with backend Postgres"
time="2021-11-25T14:11:14Z" level=debug msg="PG get user error: write tcp 10.163.16.138:45996->104.44.44.123:5432: write: connection reset by peer"
time="2021-11-25T14:11:14Z" level=error msg="write tcp 10.163.16.138:45996->104.44.44.123:5432: write: connection reset by peer"

Nothing wrong with cpu or memory usage:

mosquitto.conf:

log_dest stdout
		listener 1883
		listener 8883
		protocol mqtt
		cafile /mosquitto/tls/ca.crt
		keyfile /mosquitto/tls/tls.key
		certfile /mosquitto/tls/tls.crt
		allow_anonymous false
		persistence true
		persistence_location /mosquitto/data/
		auth_plugin /mosquitto/go-auth.so
		auth_opt_backends postgres
		auth_opt_pg_host databasename.postgres.database.azure.com
		auth_opt_pg_port 5432
		auth_opt_pg_user dbuser@my-pgsql
		auth_opt_pg_password dbpassword
		auth_opt_pg_dbname mqtt
		auth_opt_pg_sslmode require
		auth_opt_pg_userquery select password_hash from \"user\" where username = $1 and is_active = true limit 1
		auth_opt_pg_superquery select count(*) from \"user\" where username = $1 and is_active = true and is_admin = true
		auth_opt_pg_aclquery select topic from \"acl\" join \"user\" on \"user\".id = acl.user_id where username = $1 and rw = $2
		auth_opt_cache true
		auth_opt_cache_refresh true
		auth_opt_cache_type default
		auth_opt_auth_cache_seconds 30
		auth_opt_acl_cache_seconds 30
		auth_opt_auth_jitter_seconds 3
		auth_opt_acl_jitter_seconds 3
		auth_opt_hasher pbkdf2
		auth_opt_log_level debug

It looks like something's up with the connection to Postgresql?

Do you have any idea on what's going on here? Any way to troubleshoot this?

Our instance can be idle for a long time.

[iegomez]

I'm not sure this is related to the plugin, Go, or the DB itself.

That said, I found this SO topic suggesting to set db.SetConnMaxLifetime(time.Something) to force reconnections after a long idle period. Could you try and patch the backend and see if that works?

[iegomez]

The PR has been merged to master so I'm closing this.
Thanks again!