Description of problem: mariadb complains about passwords but gives no idea about what the new "current policy" has been changed to regarding passwords. This has broken the zoneminder 'zmsetup' script as previously the password strength was not an issue. Is there a test function to check passwords to know in advance whether they will pass the mariadb "policy", or do I have to write one from scratch - assuming I can find the details of it. As far as I can tell from querying mariadb there is no password verification plugin enabled, so where has this change in cauldron come from? It's a real PITA when stuff that was working fine gets gratuitously broken for no good reason. Version-Release number of selected component (if applicable): How reproducible: Steps to Reproduce: 1. 2. 3.
Assigning to maintainer
CC: (none) => marja11Assignee: bugsquad => alien
*** Bug 19222 has been marked as a duplicate of this bug. ***
After many hours of digging I have found: MariaDB [mysql]> SHOW VARIABLES LIKE '%pass%'; +------------------------------------+-----------------------------+ | Variable_name | Value | +------------------------------------+-----------------------------+ | cracklib_password_check_dictionary | /usr/share/cracklib/pw_dict | | old_passwords | OFF | | report_password | | | strict_password_validation | ON | +------------------------------------+-----------------------------+ So why has strict_password_validation been set ON for default installations? It looks like an upstream decision as there is no reference to the variable outside the upstream tarball. It seems buggy in that it checks for dictionary words for a regular mysql user but ignores the same word for the mysql root user. Also it appears to get the character count wrong depending on the text included, however testing gets very frustrating without any written policy to test against. Anyhow, do we really need to have this feature enabled by default? If not, can it be changed in our my.cnf or somewhere? Also if a system is upgraded from Mga5 where this was not implemented, will passwords fail after upgrade? (maybe 'old_passwords' variable affects this?).
For the next version of zoneminder I have enhanced the setup wizard to check passwords before they are passed to mariadb, and also to update the weak default built-in password in the configuration. I have tested an upgrade from Mageia5 to Cauldron with the new version of zoneminder and the upgrade does go smoothly with the old mysql root password. This will fix my main reason for submitting this bug report, however it could affect other packages.
> assuming I can find the details of it. https://dev.mysql.com/doc/refman/5.7/en/validate-password-plugin.html bottom of page. > It seems buggy in that it checks for dictionary words for a regular mysql user > but ignores the same word for the mysql root user. My experience indicated it does check new root user pw. > Also if a system is upgraded from Mga5 where this was not implemented, will > passwords fail after upgrade? (maybe 'old_passwords' variable affects this?). Going to depend on what is being done. :( I saved my mythtv database. Booted Release 6. Found out about new pw requirements, finally managed to change mysql root password, modified passwords to match new pw policy, loaded my mythtv database and was no longer able to login as root. :( Solution was to change/set passwords in old database to pass new checks in the new mysql app and modify all configuration files to have default password which passes check. See bug 19127
CC: (none) => bittwister2
(In reply to Bit Twister from comment #5) > > assuming I can find the details of it. > > https://dev.mysql.com/doc/refman/5.7/en/validate-password-plugin.html > bottom of page. > Yes I read that mysql page but I don't think it applies to new mariadb. > > It seems buggy in that it checks for dictionary words for a regular mysql user > > but ignores the same word for the mysql root user. > > My experience indicated it does check new root user pw. I have found that if a (now) invalid root password exists prior to upgrade it will continue to work. Also if logged in as (mysql) root another now invalid root password may be set. It's all very confusing. > > > Also if a system is upgraded from Mga5 where this was not implemented, will > > passwords fail after upgrade? (maybe 'old_passwords' variable affects this?). > > Going to depend on what is being done. :( I did test this and it was not an issue. > > I saved my mythtv database. Booted Release 6. Found out about new pw > requirements, finally managed to change mysql root password, modified > passwords to match new pw policy, loaded my mythtv database and was no > longer able to login as root. :( > > Solution was to change/set passwords in old database to pass new checks in > the new mysql app and modify all configuration files to have default > password which passes check. > See bug 19127 "mythtv_Password" has no numerics and contains a dictionary word which should fail in new mariadb - but I have not tested it. zoneminder users are requested to always run the zmsetup script after updates which now checks user password and any new root password. If it's of any use to you I have a bash function to check passwords in zmsetup: chkpass(). It checks character types, length and uses cracklib-dicts for dictionary check (as IIANM mariadb does). It is quiet by default returning 0 or 1 but creates an error message variable $messg if it fails which may be used by the calling script later if needed.
Sorry that should have been chkpasswd() at line 65 http://svnweb.mageia.org/packages/cauldron/zoneminder/current/SOURCES/zmsetup?revision=1047873&view=markup
Seems that the relevance of the dictionary word to cracklib-dicts is also dependant on password length - using my checker I get: [baz@jackodesktop bin]$ chkpass mythtv_Password Error: No numbers [baz@jackodesktop bin]$ chkpass mythtv1_Password OK [baz@jackodesktop bin]$ chkpass mytht1_Password OK [baz@jackodesktop bin]$ chkpass myth1_Password OK [baz@jackodesktop bin]$ chkpass myt1_Password OK [baz@jackodesktop bin]$ chkpass my1_Password OK [baz@jackodesktop bin]$ chkpass m1_Password Error: Contains dictionary word Interesting ;)
Looking like either the Release Notes or Errata needs to have a mariadb section about this new "feature" requirement.
This just gets more confusing: Running a series of passwords past mariadb gives really strange results. It seems that length is checked to be min of 8 Numbers, upper case and special characters seem to not be tested for. Dictionary words seem to be checked sometimes and possibly for a limited selection of words and then only when at the start of the password? [baz@jackodesktop SOURCES]$ for p in mythtv_Password mythtv_Pass mythv_Pass mtv_Pass tv_Pass Password Password1 Password16 password17 password17~ password17~B passages17~B 17~Bpassword word17~B words17~B before17~B bedroom17~B shirts17~b passhrts 95735216; do echo; echo -n $p" "; mysql -uroot -p$mysqlpass -e "USE mysql; GRANT ALL PRIVILEGES ON zm.* TO $zm_db_user@localhost IDENTIFIED BY '$p';";echo; done mythtv_Password mythtv_Pass mythv_Pass mtv_Pass tv_Pass ERROR 1819 (HY000) at line 1: Your password does not satisfy the current policy requirements Password ERROR 1819 (HY000) at line 1: Your password does not satisfy the current policy requirements Password1 ERROR 1819 (HY000) at line 1: Your password does not satisfy the current policy requirements Password16 ERROR 1819 (HY000) at line 1: Your password does not satisfy the current policy requirements password17 ERROR 1819 (HY000) at line 1: Your password does not satisfy the current policy requirements password17~ ERROR 1819 (HY000) at line 1: Your password does not satisfy the current policy requirements password17~B ERROR 1819 (HY000) at line 1: Your password does not satisfy the current policy requirements passages17~B 17~Bpassword word17~B words17~B before17~B bedroom17~B shirts17~b passhrts 95735216 [baz@jackodesktop SOURCES]$
Just gets worse: [baz@jackodesktop SOURCES]$ for p in christine williams williams1 williams#1 21#williams anne-marie anne-~marie christ21 christ21# 21christ# 21#christ 21#christ% 2christ3 2christ#; do echo; echo -n $p" "; mysql -uroot -p$mysqlpass -e "USE mysql; GRANT ALL PRIVILEGES ON zm.* TO $zm_db_user@localhost IDENTIFIED BY '$p';";echo; done christine ERROR 1819 (HY000) at line 1: Your password does not satisfy the current policy requirements williams ERROR 1819 (HY000) at line 1: Your password does not satisfy the current policy requirements williams1 ERROR 1819 (HY000) at line 1: Your password does not satisfy the current policy requirements williams#1 ERROR 1819 (HY000) at line 1: Your password does not satisfy the current policy requirements 21#williams ERROR 1819 (HY000) at line 1: Your password does not satisfy the current policy requirements anne-marie ERROR 1819 (HY000) at line 1: Your password does not satisfy the current policy requirements anne-~marie ERROR 1819 (HY000) at line 1: Your password does not satisfy the current policy requirements christ21 ERROR 1819 (HY000) at line 1: Your password does not satisfy the current policy requirements christ21# ERROR 1819 (HY000) at line 1: Your password does not satisfy the current policy requirements 21christ# 21#christ ERROR 1819 (HY000) at line 1: Your password does not satisfy the current policy requirements 21#christ% 2christ3 ERROR 1819 (HY000) at line 1: Your password does not satisfy the current policy requirements 2christ# ERROR 1819 (HY000) at line 1: Your password does not satisfy the current policy requirements [baz@jackodesktop SOURCES]$
Glad to see that my mythtv_Password gets through. You had me worried I was losing my mind with your standalone testing showing it would fail. Just guessing but I suggest your name test shots will work if they contain an uppercase letter.
[baz@jackodesktop SOURCES]$ for p in ${passarr[@]}; do echo; echo -n $p" "; mysql -uroot -p$mysqlpass -e "USE mysql; GRANT ALL PRIVILEGES ON zm.* TO $zm_db_user@localhost IDENTIFIED BY '$p';";echo; done cHristine ERROR 1819 (HY000) at line 1: Your password does not satisfy the current policy requirements Williams ERROR 1819 (HY000) at line 1: Your password does not satisfy the current policy requirements wilLiams1 ERROR 1819 (HY000) at line 1: Your password does not satisfy the current policy requirements williamsB1 ERROR 1819 (HY000) at line 1: Your password does not satisfy the current policy requirements 21#Williams ERROR 1819 (HY000) at line 1: Your password does not satisfy the current policy requirements anne-Marie ERROR 1819 (HY000) at line 1: Your password does not satisfy the current policy requirements Anne-marie ERROR 1819 (HY000) at line 1: Your password does not satisfy the current policy requirements
Ah - been reading. MariaDB [(none)]> uninstall plugin cracklib_password_check; Now: [baz@jackodesktop SOURCES]$ passarr=(williams christ humpty baz 12345 qwertyuiop) [baz@jackodesktop SOURCES]$ for p in ${passarr[@]}; do echo; echo -n $p" "; mysql -uroot -p$mysqlpass -e "USE mysql; GRANT ALL PRIVILEGES ON zm.* TO $zm_db_user@localhost IDENTIFIED BY '$p';";echo; done williams christ humpty baz 12345 qwertyuiop [baz@jackodesktop SOURCES]$ MariaDB [(none)]> INSTALL PLUGIN cracklib_password_check SONAME 'cracklib_password_check.so'; Query OK, 0 rows affected (0.00 sec) [baz@jackodesktop SOURCES]$ for p in ${passarr[@]}; do echo; echo -n $p" "; mysql -uroot -p$mysqlpass -e "USE mysql; GRANT ALL PRIVILEGES ON zm.* TO $zm_db_user@localhost IDENTIFIED BY '$p';";echo; done williams ERROR 1819 (HY000) at line 1: Your password does not satisfy the current policy requirements christ ERROR 1819 (HY000) at line 1: Your password does not satisfy the current policy requirements humpty ERROR 1819 (HY000) at line 1: Your password does not satisfy the current policy requirements baz ERROR 1819 (HY000) at line 1: Your password does not satisfy the current policy requirements 12345 ERROR 1819 (HY000) at line 1: Your password does not satisfy the current policy requirements qwertyuiop ERROR 1819 (HY000) at line 1: Your password does not satisfy the current policy requirements [baz@jackodesktop SOURCES]$ So it can be disabled from within mariadb but will it survive a reboot I wonder?
In a word - NO
I have removed all checking other than cracklib-check and now both agree for all cases I can think up, So mariadb is using ONLY cracklib-check to check it's passwords. [baz@jackodesktop SOURCES]$ for p in ${passarr[@]}; do echo; echo "$p" | /usr/sbin/cracklib-check ; mysql -uroot -p$mysqlpass -e "USE mysql; GRANT ALL PRIVILEGES ON zm.* TO $zm_db_user@localhost IDENTIFIED BY '$p';"; done Mythetv_Password: OK zmpass: it is based on a dictionary word ERROR 1819 (HY000) at line 1: Your password does not satisfy the current policy requirements zmpassw: it is based on a dictionary word ERROR 1819 (HY000) at line 1: Your password does not satisfy the current policy requirements zmpasswd: it is based on a dictionary word ERROR 1819 (HY000) at line 1: Your password does not satisfy the current policy requirements zmpass-wd: OK james1_3: it is based on a dictionary word ERROR 1819 (HY000) at line 1: Your password does not satisfy the current policy requirements 2james#123: OK Williams: it is based on a dictionary word ERROR 1819 (HY000) at line 1: Your password does not satisfy the current policy requirements ~williams: it is based on a dictionary word ERROR 1819 (HY000) at line 1: Your password does not satisfy the current policy requirements 21christ#: OK christ21#: it is based on a dictionary word ERROR 1819 (HY000) at line 1: Your password does not satisfy the current policy requirements mftkdp: it is based on a dictionary word ERROR 1819 (HY000) at line 1: Your password does not satisfy the current policy requirements mftkdpe: OK mftkdpej: OK 123456: it is too simplistic/systematic ERROR 1819 (HY000) at line 1: Your password does not satisfy the current policy requirements 12345678: it is too simplistic/systematic ERROR 1819 (HY000) at line 1: Your password does not satisfy the current policy requirements 184629: it is based on a dictionary word ERROR 1819 (HY000) at line 1: Your password does not satisfy the current policy requirements 1846295: it is based on a (reversed) dictionary word ERROR 1819 (HY000) at line 1: Your password does not satisfy the current policy requirements 18462951: it is based on a (reversed) dictionary word ERROR 1819 (HY000) at line 1: Your password does not satisfy the current policy requirements PASS_WD: it is based on a dictionary word ERROR 1819 (HY000) at line 1: Your password does not satisfy the current policy requirements [baz@jackodesktop SOURCES]$ So whilst I disagree with some of the messages above ... echo 'yourpassword' | /usr/sbin/cracklib-check ...is all that is required to verify that a password will work with mariadb Now I know, I feel I made rather hard work of that!
I guess all we need then is to add a small explanation in errata something like: ============================================ Mariadb - In Mageia 6 mariadb-10.1.16 has strict_password_validation permanently set ON. For passwords to be accepted they need to pass the cracklib-check test. The following code snippet will do this for any intended password. $ echo 'yourpassword' | /usr/sbin/cracklib-check yourpassword: OK =========================================== Strange that one passes!
Whiteboard: (none) => FOR_ERRATA
Whiteboard: FOR_ERRATA => FOR_ERRATA6
Keywords: (none) => FOR_ERRATA6Whiteboard: FOR_ERRATA6 => (none)
(In reply to Barry Jackson from comment #17) > I guess all we need then is to add a small explanation in errata something > like: > > ============================================ > Mariadb - In Mageia 6 mariadb-10.1.16 has strict_password_validation > permanently set ON. > For passwords to be accepted they need to pass the cracklib-check test. > The following code snippet will do this for any intended password. > $ echo 'yourpassword' | /usr/sbin/cracklib-check > yourpassword: OK > =========================================== > > Strange that one passes! Thanks for the suggestion, Barry. Added.
Keywords: FOR_ERRATA6 => IN_ERRATA6
Closing then
Status: NEW => RESOLVEDResolution: (none) => WORKSFORME
See Also: (none) => https://bugs.mageia.org/show_bug.cgi?id=21472
CC: (none) => ivanaugustobd