Bug #93959 validate_password plugin didn't keep modification after mysql restart
Submitted: 17 Jan 9:47 Modified: 17 Jan 13:31
Reporter: D BA Email Updates:
Status: Not a Bug Impact on me:
None 
Category:MySQL Server: Pluggable Authentication Severity:S2 (Serious)
Version:5.7.24 OS:Ubuntu
Assigned to: CPU Architecture:Any
Tags: password, policy

[17 Jan 9:47] D BA
Description:
When I modify the validate_password_policy or any validate_password_* variable, the plugin applies the modification.
But after a mysql server restart, every configuration of validate_password_* are reset to MEDIUM policy.

When I add validate_password_policy parameter on my.cnf (after [mysqld]), MySQL refuses to start.
[ERROR] unknown variable 'validate_password_policy=0'
[ERROR] unknown variable 'validate_password_policy=LOW'

I didn't find any default parameter on mysql configuration files, to reset in MEDIUM.
I didn't find system parameter add on the Ubuntu process starting file.
I have the same behavior with mysql-server package from Official repository and from Ubuntu repository.

How to repeat:

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 |
+--------------------------------------+--------+

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

mysql> set global validate_password_policy=0; 
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 | 4 |
| 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.01 sec) 

Confirmation on the error log :
2019-01-16T12:32:32.160144Z 10 [Warning] Plugin validate_password reported: 'Effective value of validate_password_length is changed. New value is 4'
(bug: https://bugs.mysql.com/bug.php?id=81523) 

# sudo service mysql restart

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 |
+--------------------------------------+--------+

Suggested fix:
1/ Keep persistence of variable value for the plugin validate_password after MySQL restart

2/ Fix the unknown variable 'validate_password_policy' error on MySQL start
[17 Jan 10:58] Umesh Shastry
Hello!

Thank you for taking the time to write to us, but this is not a bug. Please double-check the documentation available at https://dev.mysql.com/doc/refman/5.7/en/validate-password.html and https://dev.mysql.com/doc/refman/5.7/en/using-system-variables.html

## I quickly confirmed that it works
Please note that to make a global system variable setting permanent so that it applies across server restarts, you should also set it in an option file.
Also, if validate_password is not installed, the validate_password_xxx system variables are not available.

cat my93959.cnf
[mysqld]
plugin-load-add=validate_password.so

rm -rf 93959/
bin/mysqld --defaults-file=./my93959.cnf --initialize --basedir=$PWD --datadir=$PWD/93959 --log-error-verbosity=3
bin/mysqld --defaults-file=./my93959.cnf --basedir=$PWD --datadir=$PWD/93959 --core-file --socket=/tmp/mysql_ushastry.sock --port=3333 --log-error=$PWD/93959/log.err --log-error-verbosity=3 2>&1 & 

: bin/mysql -uroot -S /tmp/mysql_ushastry.sock -p                                                                       Enter password:
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.7.24 MySQL Community Server (GPL)

Copyright (c) 2000, 2018, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

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.01 sec)

mysql> \q
Bye
bin/mysqladmin -uroot -S /tmp/mysql_ushastry.sock shutdown -p                                                         Enter password:
[1]+  Done                    bin/mysqld --defaults-file=./my93959.cnf --basedir=$PWD --datadir=$PWD/93959 --core-file --socket=/tmp/mysql_ushastry.sock --port=3333 --log-error=$PWD/93959/log.err --log-error-verbosity=3 2>&1

- modify conf and restart server

: vi my93959.cnf
: cat my93959.cnf
[mysqld]
plugin-load-add=validate_password.so
validate_password_policy=0
: bin/mysqld --defaults-file=./my93959.cnf --basedir=$PWD --datadir=$PWD/93959 --core-file --socket=/tmp/mysql_ushastry.sock --port=3333 --log-error=$PWD/93959/log.err --log-error-verbosity=3 2>&1 &
[1] 16811

: bin/mysql -uroot -S /tmp/mysql_ushastry.sock -p
Enter password:
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 3
Server version: 5.7.24 MySQL Community Server (GPL)

Copyright (c) 2000, 2018, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

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)

thanks,
Umesh
[17 Jan 11:07] Umesh Shastry
Starting with MySQL 8.0, SET PERSIST Statement introduced i.e  SET can also be used to persist certain system variables to the mysqld-auto.cnf file in the data directory, to affect server operation for subsequent startups.

https://dev.mysql.com/doc/refman/8.0/en/set-variable.html
https://mysqlserverteam.com/mysql-8-0-persisting-configuration-variables/
[17 Jan 13:31] D BA
Sorry for this ticket. 

I test it and it's work.

For my point of view, documentation is not clear about "plugin-load-add=validate_password.so": Plugin was installed from mysql_secure_installation script or with INSTALL PLUGIN.

https://dev.mysql.com/doc/refman/5.7/en/validate-password-installation.html is more clear but less visible.

mysql_secure_installation behavior is also not clear. During execution of the script, we can activate the plugin and define a policy, but this policy is not record on configuration files... 

Well, thanks and sorry for the lost of time