Profile deletion fails instead of asking for sudo password

[ramirezfranciscof]

When access to postgres requires sudo privileges, verdi quicksetup will ask for the password but verdi profile delete will not and fail instead. Not serious since if your setup remembers the sudo password (on by default, reference for controlling this) one can just sudo ls and then run the verdi profile delete command and it will work.

(aiida_test) framirez@theospc1:aiida_test$ verdi profile delete quicksetup
Warning: deleting profile `quicksetup` excluding: database user.
Warning: this operation cannot be undone, are you sure you want to continue? [y/N]: y
sudo: a password is required

Unable to autodetect postgres setup.
Traceback (most recent call last):
  File "/home/framirez/miniconda3/envs/aiida_test/bin/verdi", line 10, in <module>
    sys.exit(verdi())
  File "/home/framirez/miniconda3/envs/aiida_test/lib/python3.9/site-packages/click/core.py", line 1130, in __call__
    return self.main(*args, **kwargs)
  File "/home/framirez/miniconda3/envs/aiida_test/lib/python3.9/site-packages/click/core.py", line 1055, in main
    rv = self.invoke(ctx)
  File "/home/framirez/miniconda3/envs/aiida_test/lib/python3.9/site-packages/click/core.py", line 1657, in invoke
    return _process_result(sub_ctx.command.invoke(sub_ctx))
  File "/home/framirez/miniconda3/envs/aiida_test/lib/python3.9/site-packages/click/core.py", line 1657, in invoke
    return _process_result(sub_ctx.command.invoke(sub_ctx))
  File "/home/framirez/miniconda3/envs/aiida_test/lib/python3.9/site-packages/click/core.py", line 1404, in invoke
    return ctx.invoke(self.callback, **ctx.params)
  File "/home/framirez/miniconda3/envs/aiida_test/lib/python3.9/site-packages/click/core.py", line 760, in invoke
    return __callback(*args, **kwargs)
  File "/home/framirez/miniconda3/envs/aiida_test/lib/python3.9/site-packages/aiida/cmdline/commands/cmd_profile.py", line 143, in profile_delete
    get_config().delete_profile(
  File "/home/framirez/miniconda3/envs/aiida_test/lib/python3.9/site-packages/aiida/manage/configuration/config.py", line 360, in delete_profile
    if postgres.db_exists(profile.storage_config['database_name']):
  File "/home/framirez/miniconda3/envs/aiida_test/lib/python3.9/site-packages/aiida/manage/external/postgres.py", line 146, in db_exists
    return bool(self.execute(_CHECK_DB_EXISTS_COMMAND.format(dbname)))
  File "/home/framirez/miniconda3/envs/aiida_test/lib/python3.9/site-packages/pgsu/__init__.py", line 124, in execute
    raise ConnectionError(
ConnectionError: Could not connect to PostgreSQL server using dsn={'host': 'localhost', 'port': 5432, 'user': 'postgres', 'password': None, 'database': 'template1'}.
Consider providing connection parameters via PGSU(dsn={...}).

[ltalirz]

Indeed, the problem is that the Postgres instance is not passed interactive=True when verdi profile delete is called from the command line.

One would need to give verdi profile delete a --non-interactive option and pass that through to the delete_profile function.

Note: that would only solve the interactive case. If one wants the non-interactive case to work in general (when pgsu cannot automatically figure out how to log in as the postgres superuser), we will also need to add --su-db-username and --su-db-password options.
An alternative would be to store the su-db-username and su-db-password in AiiDA's config.json when running verdi quicksetup and read it from there (if it exists) when deleting the profile but that doesn't sound like a good idea security-wise.

Todo:

• add --non-interactive, --su-db-username and --su-db-password options to verdi profile delete
• pass the value of those options down to the point where the database associated with the profile is deleted