verdi quicksetup fails with aiida 2.x but not 1.6

[ltalirz]

Describe the bug

I've encountered an issue where verdi quicksetup fails to create a new profile on Ubuntu 20.04 with AiiDA 2.

I have another, separate AiiDA 1.6.x installation on the same machine (connecting to the same Postrgres DB), where verdi quicksetup still works, including the creation of new users & databases.

Some details:

1. The new users and databases to be used by the new profile are created successfully. This means pgsu is able to figure out how to connect. The connection problem arises at a later stage.

2. The relevant part of the traceback is

Traceback (most recent call last):
  File "/ohome/shared/talirz/aiida2/aiida-core/aiida/cmdline/commands/cmd_setup.py", line 97, in setup
    storage_cls.migrate(profile)
  File "/ohome/shared/talirz/aiida2/aiida-core/aiida/storage/psql_dos/backend.py", line 62, in migrate
    cls.migrator(profile).migrate()
  File "/ohome/shared/talirz/aiida2/aiida-core/aiida/storage/psql_dos/migrator.py", line 209, in migrate
    with self._connection_context() as connection:
  File "/ohome/shared/talirz/aiida2/aiida-core/aiida/storage/psql_dos/migrator.py", line 96, in _connection_context
    raise exceptions.UnreachableStorage(f'Could not connect to database: {exception}') from exception
aiida.common.exceptions.UnreachableStorage: Could not connect to database: (psycopg2.OperationalError) connection to server on socket "/var/run/postgresql/.s.PGSQL.5432" failed: FATAL:  Peer authentication failed for user "my_aiida_db_user"

I.e. for some reason AiiDA tries to use "peer authentication" with the newly created database user. Peer authentication requires you to connect with a database user that has the same name as your operating system user, i.e. this is bound to fail.

Steps to reproduce

Inside my AiiDA 2.0 AiiDAlab container, verdi quicksetup does work - however, this is a bit of a special setup: no sudo allowed and no postgres user exists (postgresql is run by the aiida user).

I'll need to figure out what exactly the conditions are to reproduce this.

Expected behavior

verdi quicksetup should continue to work after upgrading to AiiDA 2.

Your environment

• Operating system [e.g. Linux]: Ubuntu 20.04
• Python version [e.g. 3.7.1]: python 3.9.12
• aiida-core version [e.g. 1.2.1]: 2.0.1post0
• pgsu version: 0.2.2 (in both cases)
• postgresql: 12

Additional context

After having a closer look, the culprit seems to be that calls to postgres.host_for_psycopg2 return None in AiiDA2 but 'localhost' in AiiDA 1.6

The bug seems a bit subtle. The code of the function seems unchanged https://github.com/aiidateam/aiida-core/blob/408efa71d3dfb2eff6684ca614cf239c1aa9daec/aiida/manage/external/postgres.py#L201-L210

However, in the AiiDA 1.6 case, the connection method turns out to be PostgresConnectionMode.PSQL, while in the AiiDA 2 case it is PostgresConnectionMode.PSYCOPG.

I'll need to stop here for the moment but I'll try to come back and figure out how to fix this.

Note: interestingly, AiiDA2 fails even when specifying --db-host localhost manually, i.e. it seems this information is lost along the way.