Uploaded image for project: 'MariaDB Server'
  1. MariaDB Server
  2. MDEV-14031

Password policy causes replication failure

Details

    • 10.2.11, 10.1.30

    Description

      simple_password_check settings on both master and slave

      [mariadb]
      plugin-load-add=simple_password_check.so
       
      simple_password_check_minimal_length    = 10
      simple_password_check_other_characters  = 1
      simple_password_check_letters_same_case = 1
      simple_password_check_digits            = 1
      

      On master side, create an user with invalid password
      CREATE USER USR_RO@'10.1.1.26' IDENTIFIED BY 'bsg9#d.cem#!85'; – No Upper Case Letter
      ERROR 1819 (HY000): Your password does not satisfy the current policy requirements

      The user was not created on master server, but for some reason binlog has written to disk.

      On slave side, replication slave stops
      Query caused different errors on master and slave. Error on master: message (format)='Your password does not satisfy the current policy requirements' error code=1819 ; Error on slave: actual message='no error', error code=0. Default database: ''. Query: 'CREATE USER USR_RO@'10.1.1.26' IDENTIFIED BY 'bsg9#d.cem#!85''

      Meanwhile, I can see the new user has been created on slave server, the behaviour is strange.

      Attachments

        Issue Links

          Activity

            Due to various considerations, some statements are written into the binary log even when they fail. In this case, CREATE USER is written into the binary log on master. The binlog event gets the error code 1819 (ER_NOT_VALID_PASSWORD). The expectation is that the slave will hit the same error while trying to execute the statement. However, you apparently don't have the simple_password_check plugin on the slave, so the password does not violate any restrictions, the statement works all right ("no error" on the slave), which causes the replication failure.

            Reasons for writing erroneous statements into the binary log are different, depending on the statement. I don't know reasoning for single CREATE USER, it looks like the change was made in the scope of MDEV-7288, so I'll assign it to bar to confirm whether it was intentional. Meanwhile, the obvious workaround seems to be to have the same configuration on the slave as the master has, including smple_password_check plugin.

            elenst Elena Stepanova added a comment - Due to various considerations, some statements are written into the binary log even when they fail. In this case, CREATE USER is written into the binary log on master. The binlog event gets the error code 1819 (ER_NOT_VALID_PASSWORD) . The expectation is that the slave will hit the same error while trying to execute the statement. However, you apparently don't have the simple_password_check plugin on the slave, so the password does not violate any restrictions, the statement works all right ("no error" on the slave), which causes the replication failure. Reasons for writing erroneous statements into the binary log are different, depending on the statement. I don't know reasoning for single CREATE USER , it looks like the change was made in the scope of MDEV-7288 , so I'll assign it to bar to confirm whether it was intentional. Meanwhile, the obvious workaround seems to be to have the same configuration on the slave as the master has, including smple_password_check plugin.

            As far as I remember, password validation is always skipped on the slave. In particular, because normally passwords are not written to binlog, the statement is rewritten to use IDENTIFIED BY PASSWORD and the password hash.

            serg Sergei Golubchik added a comment - As far as I remember, password validation is always skipped on the slave. In particular, because normally passwords are not written to binlog, the statement is rewritten to use IDENTIFIED BY PASSWORD and the password hash.

            The failed CREATE USER statement gets written into the binary log.
            This test demonstrates the problem:

            --source include/not_embedded.inc
            --source include/have_binlog_format_statement.inc
             
            if (!$SIMPLE_PASSWORD_CHECK_SO) {
              skip No SIMPLE_PASSWORD_CHECK plugin;
            }
             
            INSTALL SONAME "simple_password_check";
             
            --echo #
            --echo # Password policy causes replication failure
            --echo #
             
            SELECT PLUGIN_NAME FROM INFORMATION_SCHEMA.PLUGINS
            WHERE PLUGIN_NAME='simple_password_check';
             
            --disable_query_log
            RESET MASTER; # get rid of previous tests binlog
            --enable_query_log
             
            --error ER_NOT_VALID_PASSWORD
            CREATE USER USR_RO@'10.1.1.26' IDENTIFIED BY 'bsg9#d.cem#!85';
             
            --let $binlog_file = LAST
            source include/show_binlog_events.inc;
             
            UNINSTALL PLUGIN simple_password_check;
            

            The output is:

            INSTALL SONAME "simple_password_check";
            #
            # Password policy causes replication failure
            #
            SELECT PLUGIN_NAME FROM INFORMATION_SCHEMA.PLUGINS
            WHERE PLUGIN_NAME='simple_password_check';
            PLUGIN_NAME
            simple_password_check
            CREATE USER USR_RO@'10.1.1.26' IDENTIFIED BY 'bsg9#d.cem#!85';
            ERROR HY000: Your password does not satisfy the current policy requirements
            include/show_binlog_events.inc
            Log_name	Pos	Event_type	Server_id	End_log_pos	Info
            master-bin.000001	#	Gtid	#	#	GTID #-#-#
            master-bin.000001	#	Query	#	#	use `test`; CREATE USER USR_RO@'10.1.1.26' IDENTIFIED BY 'bsg9#d.cem#!85'
            UNINSTALL PLUGIN simple_password_check;
            binlog.binlog-AAA 'stmt'                 [ pass ]     54
            

            Notice, the CREATE USER statement.

            bar Alexander Barkov added a comment - The failed CREATE USER statement gets written into the binary log. This test demonstrates the problem: --source include/not_embedded.inc --source include/have_binlog_format_statement.inc   if (!$SIMPLE_PASSWORD_CHECK_SO) { skip No SIMPLE_PASSWORD_CHECK plugin; }   INSTALL SONAME "simple_password_check";   --echo # --echo # Password policy causes replication failure --echo #   SELECT PLUGIN_NAME FROM INFORMATION_SCHEMA.PLUGINS WHERE PLUGIN_NAME='simple_password_check';   --disable_query_log RESET MASTER; # get rid of previous tests binlog --enable_query_log   --error ER_NOT_VALID_PASSWORD CREATE USER USR_RO@'10.1.1.26' IDENTIFIED BY 'bsg9#d.cem#!85';   --let $binlog_file = LAST source include/show_binlog_events.inc;   UNINSTALL PLUGIN simple_password_check; The output is: INSTALL SONAME "simple_password_check"; # # Password policy causes replication failure # SELECT PLUGIN_NAME FROM INFORMATION_SCHEMA.PLUGINS WHERE PLUGIN_NAME='simple_password_check'; PLUGIN_NAME simple_password_check CREATE USER USR_RO@'10.1.1.26' IDENTIFIED BY 'bsg9#d.cem#!85'; ERROR HY000: Your password does not satisfy the current policy requirements include/show_binlog_events.inc Log_name Pos Event_type Server_id End_log_pos Info master-bin.000001 # Gtid # # GTID #-#-# master-bin.000001 # Query # # use `test`; CREATE USER USR_RO@'10.1.1.26' IDENTIFIED BY 'bsg9#d.cem#!85' UNINSTALL PLUGIN simple_password_check; binlog.binlog-AAA 'stmt' [ pass ] 54 Notice, the CREATE USER statement.

            ok to push, but please create a bug report for the second issue we've discussed.

            serg Sergei Golubchik added a comment - ok to push, but please create a bug report for the second issue we've discussed.

            People

              bar Alexander Barkov
              mxu Michael Xu
              Votes:
              0 Vote for this issue
              Watchers:
              4 Start watching this issue

              Dates

                Created:
                Updated:
                Resolved:

                Git Integration

                  Error rendering 'com.xiplink.jira.git.jira_git_plugin:git-issue-webpanel'. Please contact your Jira administrators.