There is no "homeassistant" local user in the database

Madivad · June 12, 2020, 8:14am

I have been struggling with this all day and I’m really getting nowhere, although I’m understanding the problem, I just don’t know how to fix it.

I have found countless references and just about every time people talk about blowing out the system and starting again (uninstalling and reinstalling) and while I’ve done that with MQTT, I haven’t done it to HA itself.

I’m using Home Assistant in the Synology Package which is basically a HassIO / HassOS with the add-on packages.

The only packages I’ve added on are File Editor, MQTT, and TasmoAdmin.

I have uninstalled and reinstalled MQTT numerous times (at least 6). I have had login as [] and also included users and passwords. I have tried it with anonymous set to True and False, always restarting MQTT, often restarting HA as well. The MQTT installation itself doesn’t seem to be the problem, all my devices are seeing each other and talking (I can view everything perfectly in MQTT Explorer).

What is not happening though, is Home Assistant can’t use MQTT.

MQTT logs:

[17:16:47] INFO: Setup mosquitto configuration
[17:16:47] WARNING: SSL not enabled - No valid certs found!
[17:16:47] INFO: No local user available
[17:16:47] INFO: Initialize Hass.io Add-on services
[17:16:47] INFO: Initialize Home Assistant discovery
[17:16:48] INFO: Start Mosquitto daemon
1591946208: mosquitto version 1.6.3 starting
1591946208: Config loaded from /etc/mosquitto.conf.
1591946208: Loading plugin: /usr/share/mosquitto/auth-plug.so
1591946208: |-- *** auth-plug: startup
1591946208:  ├── Username/password checking enabled.
1591946208:  ├── TLS-PSK checking enabled.
1591946208:  └── Extended authentication not enabled.
1591946208: Opening ipv4 listen socket on port 1883.
1591946208: Opening ipv6 listen socket on port 1883.
1591946208: Opening websockets listen socket on port 1884.
1591946208: Warning: Mosquitto should not be run as root/administrator.
1591946208: New connection from 172.30.32.1 on port 1883.
[INFO] found madmqtt on Home Assistant
1591946209: New client connected from 172.30.32.1 as mqtt-explorer-61cfc303 (p2, c1, k60, u'madmqtt').
1591946213: New connection from 172.30.32.1 on port 1883.
1591946213: New client connected from 172.30.32.1 as DVES_428AAA (p2, c1, k30, u'madmqtt').
1591946213: New connection from 172.30.32.1 on port 1883.
1591946213: New client connected from 172.30.32.1 as DVES_CA6F7B (p2, c1, k30, u'madmqtt').
1591946230: New connection from 172.30.32.1 on port 1883.
[WARN] Not found homeassistant on local database
1591946230: Socket error on client <unknown>, disconnecting.
1591946350: New connection from 172.30.32.1 on port 1883.
1591946350: Socket error on client <unknown>, disconnecting.
1591946470: New connection from 172.30.32.1 on port 1883.
1591946470: Socket error on client <unknown>, disconnecting.
1591946507: New connection from 172.30.32.1 on port 1883.
1591946507: Socket error on client <unknown>, disconnecting.
1591946508: New connection from 172.30.32.1 on port 1883.
1591946508: Socket error on client <unknown>, disconnecting.
1591946510: New connection from 172.30.32.1 on port 1883.
1591946511: Socket error on client <unknown>, disconnecting.
1591946515: New connection from 172.30.32.1 on port 1883.
1591946515: Socket error on client <unknown>, disconnecting.
1591946523: New connection from 172.30.32.1 on port 1883.
1591946523: Socket error on client <unknown>, disconnecting.
1591946539: New connection from 172.30.32.1 on port 1883.
[WARN] Not found homeassistant on local database
1591946539: Socket error on client <unknown>, disconnecting.

As can be seen, other things are interacting with the broker, just not HA.

Developer Tools Logs

2020-06-12 17:21:47 ERROR (Thread-5) [homeassistant.components.mqtt] Unable to connect to the MQTT broker: Connection Refused: not authorised.
2020-06-12 17:21:47 WARNING (Thread-5) [homeassistant.components.mqtt] Disconnected from MQTT server (5)
2020-06-12 17:21:48 ERROR (Thread-5) [homeassistant.components.mqtt] Unable to connect to the MQTT broker: Connection Refused: not authorised.
2020-06-12 17:21:48 WARNING (Thread-5) [homeassistant.components.mqtt] Disconnected from MQTT server (5)
2020-06-12 17:21:51 ERROR (Thread-5) [homeassistant.components.mqtt] Unable to connect to the MQTT broker: Connection Refused: not authorised.
2020-06-12 17:21:51 WARNING (Thread-5) [homeassistant.components.mqtt] Disconnected from MQTT server (5)

configuration.yaml

is basically blank, and nothing to do with MQTT

# Configure a default setup of Home Assistant (frontend, api, etc)
default_config:

# Text to speech
tts:
  - platform: google_translate

# All these files are empty
group: !include groups.yaml
automation: !include automations.yaml
script: !include scripts.yaml
scene: !include scenes.yaml

switch:
  - platform: orvibo
    switches:
     -  host: 192.168.1.82
        mac: ac:cf:23:****
        name: "Room1"

     -  host: 192.168.1.81
        mac: ac:cf:23:24***
        name: "Room2"

I’m drawing a blank, any help appreciated

edit: this is my MQTT config from the Supervisor > MQTT > Configuration

logins: []
anonymous: true
customize:
  active: false
  folder: mosquitto
certfile: fullchain.pem
keyfile: privkey.pem
require_certificate: false

As mentioned, I have tried this with logins provided (for madmqtt and others) and with anonymous true and false

Madivad · June 12, 2020, 8:24am

Something I do think is ODD and I’m not sure how it is there, and I can’t seem to remove it, but in MQTT Explorer I am seeing a range of messages that could be in conflict. I am not 100% sure where the “homeassistant” topic is coming from:

Madivad · June 12, 2020, 9:21am

I managed to delete those homeassistant entries, so they are gone, but I am still getting

1591952698:  └── Extended authentication not enabled.
1591952698: |-- *** auth-plug: startup
1591952698: Opening ipv4 listen socket on port 1883.
1591952698: Opening ipv6 listen socket on port 1883.
1591952698: Opening websockets listen socket on port 1884.
1591952698: Warning: Mosquitto should not be run as root/administrator.
1591952699: New connection from 172.30.32.1 on port 1883.
[INFO] found madmqtt on local database
1591952699: New client connected from 172.30.32.1 as mqtt-explorer-61cfc303 (p2, c1, k60, u'madmqtt').
1591952704: New connection from 172.30.32.1 on port 1883.
1591952704: New client connected from 172.30.32.1 as DVES_CA6F7B (p2, c1, k30, u'madmqtt').
1591952706: New connection from 172.30.32.1 on port 1883.
[WARN] Not found homeassistant on local database
1591952707: Socket error on client <unknown>, disconnecting.
1591952827: New connection from 172.30.32.1 on port 1883.
1591952827: Socket error on client <unknown>, disconnecting.

The problem being 4th from the bottom

[WARN] Not found homeassistant on local database

172.30.32.1 is HASSIO (just for info)

Screenshot from 2020-06-12 19-12-04

I’m going to take a break, if I have no success I will blow this out and rebuilt it

francisp · June 12, 2020, 11:03am

The ‘homeassistant’ topic is coming from discovery messages. From the content, I suspect Tasmota discovery messages. They should be there if you use auto-discovery on mqtt, so quite normal.

The fact that HA can’t connect to mqtt is more concerning.

Madivad · June 12, 2020, 12:03pm

You’re telling me! LOL

I’ve just about finished a re-install, I’m sure it’ll be fine then

Madivad · June 12, 2020, 6:49pm

And as expected, a fresh reinstall and it just works…

this is something that very much saddens me and I’ve seen that I’m not the only one who has experienced this, but the only way that seems to fix some of the problems with HA is to do open heart surgery and/or rebuild the monster.

roberthenderson · June 17, 2020, 11:51pm

For those troubleshooting this issue, what likely has happened is you uninstalled and reinstalled the Mosquitto MQTT Broker without removing its corresponding Integration. If you uninstall Mostquitto, you need to remove the integration. This will also remove all your Tasmota devices and entities from HA.

Once you reinstall/reconfigure/start Mosquitto, after having done the above, you should see a successful connection in the logs. Now go to your configurations and there should be an MQTT one discovered. You can now add the integration. Once done, restart all your Tasmota devices (or do setoption19 1 again) and home assistant will re-add them.

Your existing automation and cards, etc should all work again as long as you didn’t change the ids.

bph · June 19, 2020, 4:46am

Ding! Ding! Ding!

The missing piece for the last two hours. Thank you!

(For searching: Not found homeassistant on local database )

Madivad · June 20, 2020, 3:24pm

I cannot confirm nor deny this information as in the end there were other problems and I blew it out and started a whole new install.

Having said that though, it does sounds pretty much like what I’d done. I’d definitely uninstalled it, but I might not have removed the integration. It didn’t feel like a necessary step.

I’m going to mark that as the solution and hopefully this doesn’t happen again and so I never have to find out! Although it does appear it has helped someone already, that’s great!

pyramidak · March 19, 2021, 6:04pm

On my side this was bad cofiguration that does the problems with users and broken MQTT.
If anybody uses auth_providers, must be added -type: homeassistant.

homeassistant:
  auth_providers:
    - type: homeassistant

What is the reason that this is not done automatically in code behind and must be done manually?