MDEV-12484 Enable unix socket authentication by default

Change the default authentication for root@localhost to

  IDENTIFIED VIA mysql_native_password USING 'invalid' OR unix_socket

which provides secure passwordless login, while still allowing
SET PASSWORD to work as expected.

Also create a second all-privilege account for the user that owns
datadir (and thus has full access to the data anyway).

Compile unix_socket plugin statically into the server.

2 files changed, 21 insertions, 13 deletions

diff --git a/scripts/mysql_install_db.sh b/scripts/mysql_install_db.sh
index 8cb55e519e7..54b5bed4546 100644
--- a/scripts/mysql_install_db.sh
+++ b/scripts/mysql_install_db.sh
@@ -37,8 +37,8 @@ force=0
 in_rpm=0
 ip_only=0
 cross_bootstrap=0
-auth_root_authentication_method=normal
-auth_root_socket_user='root'
+auth_root_authentication_method=socket
+auth_root_socket_user=""
 skip_test_db=0
 
 usage()
@@ -46,17 +46,17 @@ usage()
   cat <<EOF
 Usage: $0 [OPTIONS]
   --auth-root-authentication-method=normal|socket
-                       Chooses the authentication method for the created initial
-                       root user. The default is 'normal' to creates a root user
-                       that can login without password, which can be insecure.
-                       The alternative 'socket' allows only the system root user
-                       to login as MariaDB root; this requires the unix socket
-                       authentication plugin.
+                       Chooses the authentication method for the created
+                       initial root user. The historical behavior is 'normal'
+                       to creates a root user that can login without password,
+                       which can be insecure. The default behavior 'socket'
+                       sets an invalid root password but allows the system root
+                       user to login as MariaDB root without a password.
   --auth-root-socket-user=user
                        Used with --auth-root-authentication-method=socket. It
-                       specifies the name of the MariaDB root account, as well
-                       as of the system account allowed to access it. Defaults
-                       to 'root'.
+                       specifies the name of the second MariaDB root account,
+                       as well as of the system account allowed to access it.
+                       Defaults to the value of --user.
   --basedir=path       The path to the MariaDB installation directory.
   --builddir=path      If using --srcdir with out-of-directory builds, you
                        will need to set this to the location of the build
@@ -505,6 +505,11 @@ cat_sql()
 {
   echo "use mysql;"
 
+  # Use $auth_root_socket_user if explicitly specified.
+  # Otherwise use the owner of datadir - ${user:-$USER}
+  # Use 'root' as a fallback
+  auth_root_socket_user=${auth_root_socket_user:-${user:-${USER:-root}}}
+
   case "$auth_root_authentication_method" in
     normal)
       echo "SET @auth_root_socket=NULL;"
diff --git a/scripts/mysql_system_tables_data.sql b/scripts/mysql_system_tables_data.sql
index bf473db7527..9d0088aa333 100644
--- a/scripts/mysql_system_tables_data.sql
+++ b/scripts/mysql_system_tables_data.sql
@@ -25,7 +25,9 @@
 -- add escape character in front of wildcard character to convert "_" or "%" to
 -- a plain character
 SELECT LOWER( REPLACE((SELECT REPLACE(@@hostname,'_','\_')),'%','\%') )INTO @current_hostname;
-SELECT json_object('access',cast(-1 as unsigned)) INTO @all_privileges;
+SELECT '{"access":18446744073709551615}' INTO @all_privileges;
+SELECT '{"access":18446744073709551615,"plugin":"mysql_native_password","authentication_string":"invalid","auth_or":[{},{"plugin":"unix_socket"}]}' into @all_with_auth;
+
 
 -- Fill "global_priv" table with default users allowing root access
 -- from local machine if "global_priv" table didn't exist before
@@ -37,7 +39,8 @@ REPLACE INTO tmp_user_nopasswd SELECT @current_hostname,'root',@all_privileges F
 REPLACE INTO tmp_user_nopasswd VALUES ('127.0.0.1','root',@all_privileges);
 REPLACE INTO tmp_user_nopasswd VALUES ('::1','root',@all_privileges);
 -- More secure root account using unix socket auth.
-INSERT INTO tmp_user_socket VALUES ('localhost',IFNULL(@auth_root_socket, 'root'),json_set(@all_privileges, '$.plugin', 'unix_socket'));
+INSERT INTO tmp_user_socket VALUES ('localhost', 'root',@all_with_auth);
+REPLACE INTO tmp_user_socket VALUES ('localhost',IFNULL(@auth_root_socket, 'root'),@all_with_auth);
 IF @auth_root_socket is not null THEN
   IF not exists(select 1 from information_schema.plugins where plugin_name='unix_socket') THEN
      INSTALL SONAME 'auth_socket'; END IF; END IF;