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

aiida-core/aiida/manage/external/postgres.py

Lines 201 to 210 in 408efa7

	@property
	def host_for_psycopg2(self):
	"""Return correct host for psycopg2 connection (as required by regular AiiDA operation)."""
	host = self.dsn.get('host')
	if self.connection_mode == PostgresConnectionMode.PSQL:
	# If "sudo su postgres" was needed to create the DB, we are likely on Ubuntu, where
	# the same will *not* work for arbitrary database users => enforce TCP/IP connection
	host = host or 'localhost'
	return host

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.

[ltalirz]

After some investigation, it transpired that the postgresql configuration on the VM in question (ubuntu 20.0After some investigation, it transpired that the postgresql configuration on the VM in question (ubuntu 20.04 on Azure) contained the line:

local all postgres trust

This allows all local UNIX users to log into the postgresql server as the postgres database superuser (and thus, creating databases via psycopg2 works).

However, since the database user for the aiida database is not postgres, the remaining configuration

local   all             all                                     peer
# IPv4 local connections:
host    all             all             127.0.0.1/32            md5
# IPv6 local connections:
host    all             all             ::1/128                 md5

ends up requiring that users use the "host" connection method with password.

There are two things to address here:

1. This configuration does not seem very sensible to me. If local Unix users can log in as the postgres database superuser, they can anyhow do anything they want in the database. It does not make sense to make access to the database for other database users more restrictive
2. The assumption in the quicksetup code that, if the database could be created with a local connection, then also access should work this way, can obviously be violated. Instead of assuming which method is correct, we should try both