Unable to run CI tests on password-protected DB

[ltalirz]

It seems something changed in the postgresql configuration on github actions:
https://github.com/aiidateam/aiida-core/pull/3760/checks?check_run_id=448911931#step:8:32

E.g. one might expect something like this, if the configuration in the pg_hba.conf was changed from "trust" to "md5" (see also here ).

[ltalirz]

@sphuber One question:

I see that we are using both this;

aiida-core/.github/workflows/ci.yml

Lines 99 to 104 in 12f9641

	- uses: harmon758/postgresql-action@v1
	with:
	postgresql version: '11'
	postgresql db: test_${{ matrix.backend }}
	postgresql user: 'postgres'
	postgresql password: ''

and this

aiida-core/.github/workflows/ci.yml

Line 118 in 12f9641

sudo apt install postgresql postgresql-server-dev-all postgresql-client rabbitmq-server graphviz

Is both necessary?

[ltalirz]

One simple fix might just be to switch to using a (hardcoded) password here:

aiida-core/.github/workflows/ci.yml

Line 104 in 12f9641

postgresql password: ''

Will give this a try

[ltalirz]

• when explicitly setting a password for the test profile, django tests pass
  but sqlalchemy hangs at one particular test
  • The last tests that pass:
    tests/cmdline/commands/test_setup.py::TestVerdiSetup::test_help <- tests/utils/configuration.py PASSED
    tests/cmdline/commands/test_setup.py::TestVerdiSetup::test_quicksetup <- tests/utils/configuration.py PASSED
  • => inside tests/cmdline/commands/test_setup.py but don't know which one
    Tests:
    test_help
    test_quicksetup
    => test_quicksetup_from_config_file
    test_quicksetup_wrong_port
    test_setup
• when switching to the fixtures to set up a postgresql cluster
  • 2 django tests fail:
    TestVerdiDaemon.test_daemon_start_number
    TestVerdiDaemon.test_daemon_start:
    (error: daemon not running)
    https://github.com/ltalirz/aiida-core/commit/e75b1b4e30a8ce708832dec941131330903f5e8b/checks?check_suite_id=466345135#step:9:280
  • the sqlalchemy tests still hang at the same point:
    https://github.com/ltalirz/aiida-core/runs/453628084#step:9:119
    • this seems to suggest that something in the sqlalchemy codepath unsets the password
      (which would also affect connection to the temporary profile)
• when I run only the test file tests/cmdline/commands/test_run.py , then the tests for sqlalchemy run fine
  • if the "hang" indeed happens in this file (to be seen), this would seem to suggest that something is being changed in the sqlalchemy codepath that causes a try of accessing the cluster without password

[sphuber]

Should this have been closed by #3763?

[ltalirz]

No, there are a couple of underlying issues we should still investigate & fix:

• It should be possible to run the test suite on a postgresql installation that requires a password
• The behavior under django and sqlalchemy should be the same in this respect
• The daemon tests should not fail when switching to a temporary profile (seen failing on django)

[CasperWA]

* The django tests should not fail when switching to a temporary profile

*SQLAlchemy

[ltalirz]

I've updated that point - it was not very clear

[sphuber]

What is the status of this? Is this still an active problem or can this be closed?

[ltalirz]

The remaining issues are described here
I've changed the title now and I think we should keep this open.
This should not just be relevant for running on CI but also for running tests locally (e.g. when postgresql is running inside a container).

[CasperWA]

So, I have a feeling we may solve this by moving to GitHub Action's services feature (see here). However, I haven't had time to investigate this in practice.

[ltalirz]

I've had another look at setting a password on the database for the aiida test profile in #5776

We're now down to this error:

FAILED tests/manage/configuration/test_config.py::test_delete_profile - Conne...
= 1 failed, 2386 passed, 29 skipped, 147 deselected, 121 warnings in 605.05s (0:10:05) =

which fails at

tests/manage/configuration/test_config.py:417: 
_ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ 
aiida/manage/configuration/config.py:365: in delete_profile
    if postgres.db_exists(profile.storage_config['database_name']):
aiida/manage/external/postgres.py:146: in db_exists
    return bool(self.execute(_CHECK_DB_EXISTS_COMMAND.format(dbname)))
_ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ 

self = <aiida.manage.external.postgres.Postgres object at 0x7f401ba8c220>
command = "SELECT datname FROM pg_database WHERE datname='to-be-deleted'"
kwargs = {}
dsn = {'database': 'template1', 'host': 'localhost', 'password': None, 'port': 5432, ...}

This test already fails at the existence check, which has been noticed in #5592 under different circumstances.
The problem in both cases is that AiiDA tries to log in as the postgres superuser, for which it lacks the credentials.

The existence check could be fixed by switching to the aiida db user credentials here, but deleting the database will inevitably fail [1].

Todo:

• The verdi profile delete test should be moved to the system tests, since it involves deleting the database. Once this has been done, this issue can be closed.
• The problem of providing the correct superuser credentials for verdi profile delete can be fixed in Profile deletion fails instead of asking for sudo password #5592

[1] Why does the test work with the current trust settings, then?
Authentication works because with trust, the postgres user can authenticate even if the password is not known.
What I don't quite understand is why the following tests continue to work... somewhere it seems the test_aiida database is magically recreated?

[sphuber]

What I don't quite understand is why the following tests continue to work... somewhere it seems the test_aiida database is magically recreated?

This is because the test does not operate on the test profile that is being used for the test run, but rather only a completely separate and independent config+profile that is created especially for the test. Maybe this is related to the wrong user being passed and the test failing?

[sphuber]

If I understand correctly, this issue was related to a change in the CI. But since things have been running fine for a long time (and we have changed the CI setup significantly with regards to PostgreSQL) I am closing this. If it is still a problem, please feel free to reopen and update the problem description.