create_roles.sql inconsistant with instal instructions

[Window-Hero]

The install instructions provided in the main README instruct the user to create a postgres user "selene", set a password, and set an environmental variable to match the password used, however the bootstrap_mycroft_db.py script refers to create_roles.sql and overwrites this password with a predefined default "adam", but then when the bootstrap script attempts to log in as the selene user it tries to use the password defined in the environmental variable, which is no longer correct, since it has been overwritten.

To replicate: Follow the instructions in the README, attempt to run the bootstrap_mycroft_db.py script after having defined a selene user and an environmental variable for the password, the script will fail to authenticate as selene.

Proposed fixes: Under the current instructions create_roles.sql is entirely unnecessary, because all it does is overwrite the user that was already created. Either the usage of this file could be eliminated, or the file could be modified to use the password the user provides in the environmental variable.

[34will]

I have been banging my head against a wall for the past two days with password login issues for the selene user on my postgres database, and this is the cause! Why does it work like this? Bumping this to try and get it fixed

[gsaslis]

Sure enough, I'm hitting the same issue...

Adding here the error message to save someone else $hours_of_head_banging now that search will actually yield some results:

bootstrap_mycroft_db logs:

Destroying any objects we will be creating later.
Creating the mycroft database
Creating the extensions
Creating user-defined data types
Create the schemas and grant access
Creating the account schema tables
Creating the skill schema tables
Creating the geography schema tables
Creating the wake_word schema tables
Creating the device schema tables
Creating the tagging schema tables
Creating the metric schema tables
Granting access to schemas and tables
Copying template to new database.
db password: 2y5BhDZrpetiGKAG4moG22DEPjS1Re6f
Traceback (most recent call last):
  File "/opt/selene/selene-backend/db/scripts/bootstrap_mycroft_db.py", line 403, in <module>
    _populate_db(args.ci)
  File "/opt/selene/selene-backend/db/scripts/bootstrap_mycroft_db.py", line 370, in _populate_db
    mycroft_db = PostgresDB(db_name=MYCROFT_DB_NAME)
  File "/opt/selene/selene-backend/db/scripts/bootstrap_mycroft_db.py", line 112, in __init__
    self.db = connect(
  File "/root/.cache/pypoetry/virtualenvs/db-ED_YH5mr-py3.9/lib/python3.9/site-packages/psycopg2/__init__.py", line 122, in connect
    conn = _connect(dsn, connection_factory=connection_factory, **kwasync)
psycopg2.OperationalError: connection to server at "db" (192.168.96.2), port 5432 failed: FATAL:  password authentication failed for user "selene"

postgres logs:

2022-10-25 20:13:08.357 UTC [60] FATAL:  password authentication failed for user "selene"
2022-10-25 20:13:08.357 UTC [60] DETAIL:  Password does not match for user "selene".
    Connection matched pg_hba.conf line 92: "host all all all md5"