New install workaround for 7.x-3.x

Please review the documentation updates in #2888521: Document SecureApt

Gitlab is now installing on 16.04 for every commit ... https://gitlab.com/aegir/provision/-/jobs/22325249

Please let me know if there are more changes we should make.

Hello

Not entirely sure this is the best place to report this - sorry if it is not.

I had exactly the same installation error ("[0] => Unable to grant privileges to database users." then "Could not find the alias @hostmaster") when installing Aegir 3.11.2 on Ubuntu 16.04 with mysql 5.7.19:

...
Generated config in write(): web server configuration file           [success]
(/var/aegir/config/server_master/apache.conf)
Caught drush error, ending drush_provision_hostmaster_install        [error]
Array                                                                [error]
(
    [PROVISION_GRANT_DB_USER_FAILED] => Array
        (
            [0] => Unable to grant privileges to database users.
        )

)
.....
Enabling hosting-queued daemon
Could not find the alias @hostmaster                                 [error]
dpkg: error processing package aegir3-hostmaster (--configure):
 subprocess installed post-installation script returned error exit status 1
dpkg: dependency problems prevent configuration of aegir3:
 aegir3 depends on aegir3-hostmaster (>= 3.11.2); however:
  Package aegir3-hostmaster is not configured yet.

dpkg: error processing package aegir3 (--configure):
 dependency problems - leaving unconfigured
Setting up php7.0 (7.0.22-0ubuntu0.16.04.1) ...
Processing triggers for libc-bin (2.23-0ubuntu9) ...
No apport report written because the error message indicates its a followup error from a previous failure.                                                                                                          
Processing triggers for libapache2-mod-php7.0 (7.0.22-0ubuntu0.16.04.1) ...
Processing triggers for systemd (229-4ubuntu19) ...
Processing triggers for ureadahead (0.100.0-19) ...
Errors were encountered while processing:
 aegir3-hostmaster
 aegir3
E: Sub-process /usr/bin/dpkg returned an error code (1)

But I traced it to having secured MYSQL with a password validation policy set to MEDIUM. If I revert to policy LOW, then installation works fine.

So I assume that AEGIR tries to create a user-password that is not secure enough for the validation policy?!

Not sure if that qualifies as a bug?

Current workaround is to set password validation policy to LOW before installing AEGIR:

$ mysql -u root -p
mysql> set global validate_password_policy=LOW;

Hope this helps. Cheers.

As a follow up to my previous post.

I ran into the same problem again when updating AEGIR (from 3.121 to 3.130, on Ubuntu 16.04 using apt-get upgrade).

$ sudo apt-get upgrade
...
Aegir frontend (@hostmaster) site detected in /var/aegir/hostmaster-7.x-3.121
upgrading the frontend from /var/aegir/hostmaster-7.x-3.121 to /var/aegir/hostmaster-7.x-3.130
...
Could not create database user XXXXXXXXX                      [error]
...
Failed to revoke user privileges                                     [warning]
...
dpkg: error processing package aegir3-hostmaster (--configure):
 subprocess installed post-installation script returned error exit status 1
dpkg: dependency problems prevent configuration of aegir3:
 aegir3 depends on aegir3-hostmaster (>= 3.130); however:
  Package aegir3-hostmaster is not configured yet.

dpkg: error processing package aegir3 (--configure):
 dependency problems - leaving unconfigured
No apport report written because the error message indicates its a followup error from a previous failure.
                       Errors were encountered while processing:
 aegir3-hostmaster
 aegir3
E: Sub-process /usr/bin/dpkg returned an error code (1)

.
The MYSQL password policy was set to MEDIUM at the time:

$ mysql -u root -p

mysql> show variables like 'validate_password%';
+--------------------------------------+--------+
| Variable_name                        | Value  |
+--------------------------------------+--------+
| validate_password_check_user_name    | OFF    |
| validate_password_dictionary_file    |        |
| validate_password_length             | 8      |
| validate_password_mixed_case_count   | 1      |
| validate_password_number_count       | 1      |
| validate_password_policy             | MEDIUM |
| validate_password_special_char_count | 1      |
+--------------------------------------+--------+
7 rows in set (0.00 sec)

.
Once again, the upgrade worked fine once I downgraded MYSQL password validation policy to LOW like this:

mysql> set global validate_password_policy=LOW;
Query OK, 0 rows affected (0.00 sec)

mysql> show variables like 'validate_password%';
+--------------------------------------+-------+
| Variable_name                        | Value |
+--------------------------------------+-------+
| validate_password_check_user_name    | OFF   |
| validate_password_dictionary_file    |       |
| validate_password_length             | 8     |
| validate_password_mixed_case_count   | 1     |
| validate_password_number_count       | 1     |
| validate_password_policy             | LOW   |
| validate_password_special_char_count | 1     |
+--------------------------------------+-------+
7 rows in set (0.00 sec)

.
So the *permanent* workaround to allow AEGIR installation and/or subsequent upgrades on Ubuntu 16.04 is to set MYSQL password validation policy to LOW permanently by forcing it in mysqld.cnf as follows:

$ sudo pico /etc/mysql/mysql.conf.d/mysqld.cnf 
[--- edit to add 4 lines as shown below ---]

$ tail -4 /etc/mysql/mysql.conf.d/mysqld.cnf 
# set password validation policy. Options: LOW, MEDIUM, HIGH
# Needs to be set to LOW during aegir install
# After changing, run $ sudo service mysql restart (reload is NOT sufficient)
validate_password_policy=LOW

$  sudo service mysql restart

.
So:
Could newer versions use for the database user a password strong enough to comply with MYSQL strongest possible password validation policy?

Are there any other implications of switching this to LOW? I'm a little bit worried given that the default is MEDIUM.

I have just checked on a clean install. In Ubuntu 16.04.3 by default the MYSQL validate password plugin is NOT installed so AEGIR installs and updates fine until you run mysql_secure_installation and choose to setup the VALIDATE PASSWORD plugin and set it to anything else that LOW.

The possible settings are:
LOW: Length >= 8
MEDIUM: Length >= 8, numeric, mixed case, and special characters
STRONG: Length >= 8, numeric, mixed case, special characters and dictionary file

But running mysql_secure_installation and setting up the VALIDATE PASSWORD plugin to at least MEDIUM sounds like a good idea, so the AEGIR install should probably agree with that.

But has anyone else reported that problem or been able to replicate it?

@colan - Should we consider this a bug and/or a security risk?
I assume the fix would be very easy (but I may be wrong): having hostmaster generate a stronger password for the database user (Length >= 8, numeric, mixed case, special characters and dictionary file)?

I'm not a huge fan of special chars in passwords, but even less of lowering default security settings ;)

Please create a new issue where we can work on the provision_password() function from provision.inc.

Ok. Done: https://www.drupal.org/project/hostmaster/issues/2942248

Hopefully I did it right - was my first time.

Thanks, that summarizes it well.

I think we can close this one...

The fix described above (setting validate_password_policy=LOW in mysqld.cnf) worked fine until I needed to upgrade MYSQL (from 5.7.21 to 5.7.22).

MYSQL upgrade failed with an error:

Setting up mysql-server-5.7 (5.7.22-0ubuntu0.16.04.1) ...
ERROR: Unable to start MySQL server:
2018-04-29T10:31:10.968083Z 0 [ERROR] unknown variable 'validate_password_policy=LOW'
2018-04-29T10:31:10.978267Z 0 [ERROR] Aborting

New fix is to disable mysql password validation plugin altogether:

$ mysql -u root -p
mysql> uninstall plugin validate_password;
Query OK, 0 rows affected (0.03 sec)
mysql> exit