Recorder system health does not support slashes (/) in database password

[Lyokovic]

The problem

The system health part or the recorder fails to extract database name when there are / characters in the database password.

What version of Home Assistant Core has the issue?

2022.8.2

What was the last working version of Home Assistant Core?

2022.5.5

What type of installation are you running?

Home Assistant OS

Integration causing the issue

recorder

Link to integration documentation on our website

No response

Diagnostics information

No response

Example YAML snippet

No response

Anything in the logs that might be useful for us?

Traceback (most recent call last):
  File "/usr/local/lib/python3.10/site-packages/sqlalchemy/engine/base.py", line 1819, in _execute_context
    self.dialect.do_execute(
  File "/usr/local/lib/python3.10/site-packages/sqlalchemy/engine/default.py", line 732, in do_execute
    cursor.execute(statement, parameters)
psycopg2.errors.InvalidCatalogName: database "<part_of_password>@postgres.lan/hass" does not exist


The above exception was the direct cause of the following exception:

Traceback (most recent call last):
  File "/usr/src/homeassistant/homeassistant/components/system_health/__init__.py", line 70, in get_integration_info
    data = await registration.info_callback(hass)
  File "/usr/src/homeassistant/homeassistant/components/recorder/system_health/__init__.py", line 67, in system_health_info
    db_stats = await instance.async_add_executor_job(
  File "/usr/local/lib/python3.10/concurrent/futures/thread.py", line 58, in run
    result = self.fn(*self.args, **self.kwargs)
  File "/usr/src/homeassistant/homeassistant/components/recorder/system_health/__init__.py", line 40, in _get_db_stats
    and (db_bytes := get_size(session, database_name))
  File "/usr/src/homeassistant/homeassistant/components/recorder/system_health/postgresql.py", line 11, in db_size_bytes
    session.execute(
  File "/usr/local/lib/python3.10/site-packages/sqlalchemy/orm/session.py", line 1712, in execute
    result = conn._execute_20(statement, params or {}, execution_options)
  File "/usr/local/lib/python3.10/site-packages/sqlalchemy/engine/base.py", line 1631, in _execute_20
    return meth(self, args_10style, kwargs_10style, execution_options)
  File "/usr/local/lib/python3.10/site-packages/sqlalchemy/sql/elements.py", line 332, in _execute_on_connection
    return connection._execute_clauseelement(
  File "/usr/local/lib/python3.10/site-packages/sqlalchemy/engine/base.py", line 1498, in _execute_clauseelement
    ret = self._execute_context(
  File "/usr/local/lib/python3.10/site-packages/sqlalchemy/engine/base.py", line 1862, in _execute_context
    self._handle_dbapi_exception(
  File "/usr/local/lib/python3.10/site-packages/sqlalchemy/engine/base.py", line 2043, in _handle_dbapi_exception
    util.raise_(
  File "/usr/local/lib/python3.10/site-packages/sqlalchemy/util/compat.py", line 208, in raise_
    raise exception
  File "/usr/local/lib/python3.10/site-packages/sqlalchemy/engine/base.py", line 1819, in _execute_context
    self.dialect.do_execute(
  File "/usr/local/lib/python3.10/site-packages/sqlalchemy/engine/default.py", line 732, in do_execute
    cursor.execute(statement, parameters)
sqlalchemy.exc.ProgrammingError: (psycopg2.errors.InvalidCatalogName) database "<part_of_password>@postgres.lan/hass" does not exist

[SQL: select pg_database_size(%(database_name)s);]
[parameters: {'database_name': '<part_of_password>@postgres.lan/hass'}]
(Background on this error at: https://sqlalche.me/e/14/f405)

Additional information

This line seems to be the culprit : https://github.com/home-assistant/core/blob/38c57944fa1353e6bb1f0e3ba3187b86010f1e9d/homeassistant/components/recorder/system_health/init.py#L62

It seems to try to extract the database name by looking at the string after the first /, but it gets the whole remaining URI after the first / in the password.

Everything else in the recorder works fine, and this password is used since at least one year without any issue.

[Lyokovic]

I just found this other issue that seems related / the same : #73189

[probot-home-assistant[bot]]

recorder documentation recorder source _{^{(message by IssueLinks)}}

[probot-home-assistant[bot]]

Hey there @home-assistant/core, mind taking a look at this issue as it has been labeled with an integration (recorder) you are listed as a code owner for? Thanks! _{^{(message by CodeOwnersMention)}}

[bdraco]

https://docs.sqlalchemy.org/en/14/core/engines.html#escaping-special-characters-such-as-signs-in-passwords

[github-actions[bot]]

There hasn't been any activity on this issue recently. Due to the high number of incoming GitHub notifications, we have to clean some of the old issues, as many of them have already been resolved with the latest updates. Please make sure to update to the latest Home Assistant version and check if that solves the issue. Let us know if that works for you by adding a comment 👍 This issue has now been marked as stale and will be closed if no further activity occurs. Thank you for your contributions.