Bug #93959 validate_password plugin didn't keep modification after mysql restart

Submitted: 17 Jan 2019 9:47 Modified: 17 Jan 2019 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 2019 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 2019 10:58] MySQL Verification Team
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 2019 11:07] MySQL Verification Team
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 2019 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

Portions of this website are copyright © 2001, 2002 The PHP Group

Page generated in 0.054 sec. using MySQL 8.4.7-u6-cloud

Timestamp references displayed by the system are UTC.
Content reproduced on this site is the property of the respective copyright holders.
It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.