Database issue in conda environment on Ubuntu 18.04

[kjappelbaum]

I tried to install aiida (latest stable release) with pip -e in a conda enviornment on Ubuntu 18.04. The installation outside conda worked for me. But verdi quicksetup fails in a conda environment and raises a database error (the problem also appears inside a regular virtual python environment):

$ verdi quicksetup
Profile name [quicksetup]: e
Email Address (identifies your data when sharing): e
First Name: e
Last Name: e
Institution: e
Detected no known postgres setup, some information is needed to create the aiida database and grant 
aiida access to it. If you feel unsure about the following parameters, first check if postgresql is 
installed. If postgresql is not installed please exit and install it, then run verdi quicksetup again.
If postgresql is installed, please ask your system manager to provide you with the following parameters:
postgres host [localhost]: 

[ltalirz]

we checked together and the code ends up here:
https://github.com/aiidateam/aiida_core/blob/377738b24306f321d733b2c2fc68387fec346e3c/aiida/control/postgres.py#L113

By the way, this prompt also appears when we provide the --non-interactive command line option, which seems like another bug.

[kjappelbaum]

sudo verdi quicksetup actually goes through the function whereas it does not without sudo (as mentioned above) .

The various print comments were inserted in the various if statements in the lines mentioned in print output. The end of function was just inserted before the return

https://github.com/aiidateam/aiida_core/blob/377738b24306f321d733b2c2fc68387fec346e3c/aiida/control/postgres.py#L125

The output with sudo was

Profile name [quicksetup]: F
Email Address (identifies your data when sharing): [email protected]
First Name: F
Last Name: F
Institution: F
NoneHello line 105
postgresHello line 105
postgresHello line 114
end of function
<function _pg_execute_sh at 0x7fd1751bed70>th


...for Django backend
Traceback (most recent call last):
  File "/usr/local/bin/verdi", line 9, in <module>
    sys.exit(run())
  File "/home/kevin/aiida/aiida_core/aiida/cmdline/verdilib.py", line 1056, in run
    aiida.cmdline.verdilib.exec_from_cmdline(sys.argv)
  File "/home/kevin/aiida/aiida_core/aiida/cmdline/verdilib.py", line 1041, in exec_from_cmdline
    CommandClass.run(*argv[command_position + 1:])
  File "/home/kevin/aiida/aiida_core/aiida/cmdline/verdilib.py", line 633, in run
    quicksetup.invoke(ctx)
  File "/usr/local/lib/python2.7/dist-packages/click/core.py", line 895, in invoke
    return ctx.invoke(self.callback, **ctx.params)
  File "/usr/local/lib/python2.7/dist-packages/click/core.py", line 535, in invoke
    return callback(*args, **kwargs)
  File "/usr/local/lib/python2.7/dist-packages/click/decorators.py", line 27, in new_func
    return f(get_current_context().obj, *args, **kwargs)
  File "/home/kevin/aiida/aiida_core/aiida/cmdline/verdilib.py", line 754, in quicksetup
    setup(profile_name, only_config=False, non_interactive=True, **setup_args)
  File "/home/kevin/aiida/aiida_core/aiida/cmdline/verdilib.py", line 537, in setup
    profile=gprofile)
  File "/home/kevin/aiida/aiida_core/aiida/cmdline/__init__.py", line 136, in pass_to_django_manage
    load_dbenv(profile=profile)
  File "/home/kevin/aiida/aiida_core/aiida/backends/utils.py", line 73, in load_dbenv
    return load_dbenv_django(profile=profile, *args, **kwargs)
  File "/home/kevin/aiida/aiida_core/aiida/backends/djsite/utils.py", line 25, in load_dbenv
    check_schema_version()
  File "/home/kevin/aiida/aiida_core/aiida/backends/djsite/utils.py", line 106, in check_schema_version
    if 'db_dbsetting' not in connection.introspection.table_names():
  File "/usr/local/lib/python2.7/dist-packages/django/db/backends/__init__.py", line 1374, in table_names
    with self.connection.cursor() as cursor:
  File "/usr/local/lib/python2.7/dist-packages/django/db/backends/__init__.py", line 167, in cursor
    cursor = utils.CursorWrapper(self._cursor(), self)
  File "/usr/local/lib/python2.7/dist-packages/django/db/backends/__init__.py", line 138, in _cursor
    self.ensure_connection()
  File "/usr/local/lib/python2.7/dist-packages/django/db/backends/__init__.py", line 133, in ensure_connection
    self.connect()
  File "/usr/local/lib/python2.7/dist-packages/django/db/utils.py", line 94, in __exit__
    six.reraise(dj_exc_type, dj_exc_value, traceback)
  File "/usr/local/lib/python2.7/dist-packages/django/db/backends/__init__.py", line 133, in ensure_connection
    self.connect()
  File "/usr/local/lib/python2.7/dist-packages/django/db/backends/__init__.py", line 122, in connect
    self.connection = self.get_new_connection(conn_params)
  File "/usr/local/lib/python2.7/dist-packages/django/db/backends/postgresql_psycopg2/base.py", line 130, in get_new_connection
    connection = Database.connect(**conn_params)
  File "/usr/local/lib/python2.7/dist-packages/psycopg2/__init__.py", line 130, in connect
    conn = _connect(dsn, connection_factory=connection_factory, **kwasync)
django.db.utils.OperationalError: FATAL:  password authentication failed for user "aiida_qs_root"
FATAL:  password authentication failed for user "aiida_qs_root"

[ltalirz]

@kjappelbaum the postgres package that you are using remains the same throughout all these attempts, correct (i.e. installed once via sudo apt-get install ...)?

I'll set up an ubuntu 18.04 VM to check, might take a bit

[ltalirz]

This is what I get installing aiida v0.12.0 on Ubuntu 18.04

(aiida) max@qmobile:~$ verdi quicksetup
Profile name [quicksetup]: test_profile
Email Address (identifies your data when sharing): [email protected]
First Name: Leopold
Last Name: Talirz
Institution: EPFL
[sudo] password for max:  [**Here I entered the correct sudo password **]
Detected no known postgres setup, some information is needed to create the aiida database and grant aiida access to it.
If you feel unsure about the following parameters, first check if postgresql is installed.
If postgresql is not installed please exit and install it, then run verdi quicksetup again.
If postgresql is installed, please ask your system manager to provide you with the following parameters:

Again, the following command runs through without the need for sudo privileges:

       {{ aiida_venv }}/bin/verdi setup default --non-interactive \
       --email aiida@localhost \
       --backend django \
       --db_host localhost --db_port 5432 \
       --db_name {{ aiida_postgres_db }} \
       --db_user {{ aiida_postgres_db_user }} --db_pass '{{ aiida_postgres_db_pw  }}' \
       --repo ${HOME}/.aiida/repository-aiida/

@DropD Any ideas?

[kjappelbaum]

@ltalirz yes, it was the same postgresql installation. But the difference was that for the installation which I tried in the conda environment I installed it from the github repo whereas in the case it worked for me on Ubuntu 18.04 I installed directly via pip install aiida

Hence, I purge removed now my postgresql installation, reinstalled it and then managed to install aiida directly with pip install aiida (i.e. no errors for the quicksetup) but somehow did not manage to install it by cloning the github repo and doing then pip install -e aiida_core

[DropD]

For 1.0.0a1 and higher:

• in 2ff590 the pg_execute_sh function was changed, adding the preexec_fn=os.setpgrp keyword argument: result = subprocess.check_output(sudo_su_psql, preexec_fn=os.setgrp, **kwargs)
• in 520dc os.setgrp was changed to os.setsid
  This prevents sudo from asking for the password and makes the command fail immediately. The solution might be to add -S to sudo, allowing the password to be passed through stdin.

For 0.12.2:

None of the above seems to apply. However, it seems to me from the output above that the sudo password prompt got suppressed for some reason as well. The error in sudo verdi quicksetup follows from an invalid password stored for the aiida_qs_root db user in the incomplete profile created during the first failed attempt.

[DropD]

The fix applied in #1734 is broken again, so for now sudo -E /path/to/verdi quicksetup is required again.

[ltalirz]

A few comments:

• regarding 'password authentication failed' error with sudo verdi quicksetup: This is likely because of trying to an existing DB with an existing user (set up in a previous, failed attempt)
• regarding a failing setup in a conda environment: @kjappelbaum mentions he used the repo cloned from git, which would default to the develop branch. This problem was indeed present in develop and was solved about a month ago by adding the -S flag to the sudo command. Unfortunately, there was another problem introduced by adding preexec_fn=os.setsid (only in develop).

@DropD and me are now looking into

• checking the 0.12 branch on Ubuntu 16.04 (should work) and 18.04
• checking the develop branch (need to fix preexec_fn=os.setsid)

[ltalirz]

Tests completed successfully (all inside python virtualenv):

aiida 0.12.1:

• Ubuntu 16.04, sudo required (password for sudo required)
• Ubuntu 18.04, sudo required (password for sudo required)

modified develop branch (with preexec_fn=os.setsid removed):

• Ubuntu 18.04, sudo required (password for sudo required)
• Ubuntu 18.04, sudo required (passwordless sudo)

[giovannipizzi]

Hi @ltalirz is this ok for the v1.0.0 milestone? If not, please reassign the milestone, thanks!

[ltalirz]

The issue in develop was fixed a long time ago in #1909
According to the comments above, 0.12.x seems to have been working and just got a further improvement in #2433
Closing this.