WL#11057: Make caching_sha2_password default authentication mechanism

WL#9591 introduces new authentication plugin - caching_sha2_password. This worklog switches default authentication for server/client to caching_sha2_password.

Functional Requirements

• FR#1: Default value for plugin column in mysql.user table shall be changed to 'caching_sha2_password'.
• FR#2: Default authentication plugin for MySQL server shall be changed from mysql_native_password to caching_sha2_password.
• FR#3: Default authentication plugin for libmysql shall be changed from myql_native_password to caching_sha2_password.
• FR#4: Two new columns shall be added to table mysql.slave_master_info.
  • FR#4.1 : Column Public_key_path would store path to a file containing public key of master.
  • FR#4.2 : Column Master_public_key would store preference to fetch public key from master
• FR#5: It should be possible to specify path to master's public key file and preference to fetch master key through CHANGE MASTER statement.
• FR#6: root@localhost shall be defined to use caching_sha2_password by --initialize.
• FR#7: It should be possible to consult the path to master's public key file and preference for fetching public key from master through either SHOW SLAVE STATUS or querying performance_schema.replication_connection_configuration table.
• FR#8: Group Replication should support a new plugin variables to specify public key file information
• FR#9: A new client API should be introduced to clear cached public key information in MySQL client library

Non-functional Requirements

• NFR#1: As a part of upgrade, no change should be done to a valid user account's authentication plugin.

• HLS#1: Default value of plugin column in mysql.user table will be changed to 'caching_sha2_password'.
• HLS#2: Default value for system variable : --default-authentication-plugin will be changed from 'mysql_native_password' to 'caching_sha2_password'.
• HLS#3: Default plugin for libmysql will be changed from native_password_client_plugin to caching_sha2_password_client_plugin.
• HLS#4: In mysql.slave_master_info table, two new columns will be added after column Tls_version.
  • Name of the column : Master_public_key_path
    • Type of the column : TEXT
    • Character set : utf8
    • Collation : utf8_bin
    • Value : Path to master's public key path
  • Name of the column : Get_master_public_key
    • Type of the column : Boolean
• HLS#5: During upgrade, columns, Master_public_key_path and Get_master_public_key will be added after Tls_version.

# If the order of column Public_key_path and Get_master_public_key is wrong,
# same will correct the order in slave_master_info table.
ALTER TABLE slave_master_info
 MODIFY COLUMN Master_public_key_path TEXT CHARACTER SET utf8 COLLATE utf8_bin
 COMMENT 'The file containing public key of master server.'
 AFTER Tls_version;
ALTER TABLE slave_master_info
 MODIFY COLUMN Get_master_public_key BOOLEAN NOT NULL
 COMMENT 'Preference to obtain public key from master.'
 AFTER Master_public_key_path

• HLS#6: CHANGE MASTER sql will be extended to support two new clauses.

  MASTER_PUBLIC_KEY_PATH = '<path_to_master's_public_key>'
  Get_master_public_key = ON | OFF

• HLS#7:
  • Class Master_info::Master_info() will be modified to have a new member variables to have information about master's public key : master_public_key_path and preference to obtain master public key
  • If master_public_key_path is not empty, while establishing connection with master through libmysql, MYSQL_SERVER_PUBLIC_KEY option will be set with the value of master_public_key_path.
  • If master_public_key_path is set, we will not obtain public key from master even if Get_master_public_key is set to ON
• HLS#8:
  • SHOW SLAVE STATUS will show information about Master_public_key_path and Get_master_public_key.
  • Two new column will be added in performance_schema.replication_connection_configuration.
    • Name : Master_public_key_path
      • Type : VARCHAR(512).
    • Name : Get_master_public_key
      • Type : Boolean
• HLS#9:
  • Two new options will be added to Group replication plugin
    • group_replication_recovery_public_key_path : To accept public key file information
    • group_replication_recovery_get_public_key : To accept preference about fetching public key from master
• HLS#10:
  • A new client API : mysql_reset_server_public_key() will be introduced to clear cached copy of server public key from client library.

• Notes about design
  • With respect to caching_sha2_password, requirement to provide server's public key or use of TLS material is required at following occasions.
    • First login after user creation
    • First login after FLUSH PRIVILEGES
    • First login after password change
    • First login after RENAME USER
  • Other than above mentioned cases, public key or TLS material is not required.
  • Above mentioned conditions apply to slave user when user is used for traditional replication or group replication.