maria-developers team mailing list archive
-
maria-developers team
-
Mailing list archive
-
Message #08214
Re: passwordless mariadb root login with auht_socket in Debian
I tried to clean up a little bit the script, i inlined the diff at the
end of the mail.
There is a first problem here : the "set_mysql_rootpw()" function resets
the password and set the use of the "unix_socket" plugin to be used for
every root users, even non-local one, i modified it so it only changes
for the root@localhost user to avoid possible breaking of existing
setups (which can still occur if users use the root@localhost user with
a password on scripts for example).
Another issue is that the "INSTALL PLUGIN unix_socket SONAME
'auth_socket';" statement will fail as $MYSQL_BOOTSTRAP runs with
"--skip-grant-table" and INSTALL PLUGIN seems to require the grant table
to be usable.
If i am not mistaken, it can be replaced with "INSERT INTO mysql.plugin
(name, dl) VALUES ('unix_socket', 'auth_socket');" then the plugin will
get loaded at the next server start but i am not sure if it will load if
"--skip-grant-table" is used.
Which leads to another possible issue : the "set_mysql_rootpw()"
function doesnt check that the "unix_socket" plugin is loaded before
modifying the root user and it modifies directly the mysql.user table
while in "--skip-grant-table" which can lead to a broken server with no
root access if it didnt work as expected.
I did work around that with the same ugly prepared statement
construction i sent last time on this thread to emulate a test done on a
procedure without using one.
The statement PREPARE should return an "ERROR 1065 (42000): Query was
empty" error if the plugin is not active and execute the "UPDATE
mysql.user [...]" query if its loaded.
Yet another potential problem is that the debian.cnf file gets replaced
before the the migration to unix_socket has occured (and worked).
Rather than having to backup the existing debian.cnf, overwriting it
with the new configuration format then trying to install/configure the
unix_socket auth then ifnally rollbacking or removing the backup of the
file depending on the outcome, it would be safer to replace the file
only once the migration has worked.
If the installer is aborted in the middle of the operation, the "new"
file will be kept in place and the backup will have to be manually
restored (or maybe, debconf does take care of the
debian_old_config.XXXXXX file by itself?).
Le 02/03/2015 21:29, Otto Kekäläinen a écrit :
I'm trying to engineer a fix for this but it seems really tricky. Here
is what I've got so far:
https://github.com/ottok/mariadb-10.0/commit/26fd165625b2e840fbda05ed11e5b7c12f308fca
--- mariadb-server-10.0.postinst 2015-03-03 01:04:34.054732754 +0100
+++ mariadb-server-10.0.postinst 2015-03-03 02:17:46.753503859 +0100
@@ -29,19 +29,25 @@
mysql --no-defaults -u root -h localhost </dev/null >/dev/null 2>&1
}
-# call with $1 = "online" to connect to the server, otherwise it bootstraps
+# This function resets the root@localhost user password and enable the
+# usage of the unix_socket plugin for it.
+# Call with $1 = "online" to connect to the server, otherwise it bootstraps
set_mysql_rootpw() {
-
- tfile=`mktemp`
+ tfile="$(mktemp)"
if [ ! -f "$tfile" ]; then
return 1
fi
- # this avoids us having to call "test" or "[" on $rootpw
+ # The reset_root statement is used to verify that the
unix_socket plugin
+ # is active before resetting the root@localhost password ; if
the plugin
+ # is not active, it will fail with "ERROR 1065 (42000): Query
was empty"
+
+ # This avoids us having to call "test" or "[" on $rootpw
cat << EOF > $tfile
-USE mysql;
SET sql_log_bin=0;
-UPDATE user SET password="", plugin="unix_socket" WHERE user='root';
+SET @reset_root=IF( (SELECT 1 FROM INFORMATION_SCHEMA.PLUGINS WHERE
PLUGIN_NAME='unix_socket' AND PLUGIN_STATUS='ACTIVE' AND
PLUGIN_TYPE='AUTHENTICATION' AND PLUGIN_LIBRARY LIKE
CONCAT('auth_socket','%') )=1, "UPDATE mysql.user SET Password='',
Plugin='unix_socket' WHERE User='root' AND Host='localhost'", '');
+PREPARE reset_root FROM @reset_root;
+EXECUTE reset_root;
FLUSH PRIVILEGES;
EOF
if [ "$1" = "online" ]; then
@@ -51,7 +57,7 @@
$MYSQL_BOOTSTRAP <$tfile
retval=$?
fi
- rm -f $tfile
+ rm -f "$tfile"
return $retval
}
@@ -122,81 +128,97 @@
# As the binlog cron scripts to need at least the Super_priv, I
do first
# the old query which always succeeds and then the new which may
or may not.
- # recreate the credentials file if not present or with debian-sys-maint
+ # Recreates the credentials file if not present or with
debian-sys-maint
# still there
- dc=$mysql_cfgdir/debian.cnf;
- if [ ! -e "$dc" -o -n "`fgrep debian-sys-maint $dc 2>/dev/null`" ];
then
- if [ ! -d "$mysql_cfgdir" ]; then install -o 0 -g 0 -m 0755 -d
$mysql_cfgdir; fi
+ dc=$mysql_cfgdir/debian.cnf
+ if [ ! -e "$dc" ]; then
+ # debian.cnf does not exists
+ if [ ! -d "$mysql_cfgdir" ]; then
+ # The configuration directory does not exists
+ install -o 0 -g 0 -m 0755 -d $mysql_cfgdir
+ fi
+ oldconf=''
+ else
+ # A backup of the existing debian.cnf is done
+ # In case the migration to auth_socket would fail, it will be
restored
+ oldconf="$(mktemp --tmpdir=$mysql_cfgdir -t
debian_old_config.XXXXXX)"
+ cp "$dc" "$oldconf"
+ fi
+
+ fgrep -q debian-sys-maint $dc
+ if [ ! -e "$dc" -o $? -ne 0 ]; then
+ # debian.cnf does not exists or contains the debian-sys-maint user
if [ -e "$dc" ]; then
- oldconf=`mktemp --tmpdir=$mysql_cfgdir -t
debian_old_config.XXXXXX`
- cp $dc $oldconf
+ # A backup of the existing debian.cnf is done
+ # In case the migration to auth_socket would fail, it will
be restored
+ oldconf="$(mktemp --tmpdir=$mysql_cfgdir -t
debian_old_config.XXXXXX)"
+ cp "$dc" "$oldconf"
else
- oldconf=''
+ # There was no pre-existing debian.cnf file
+ oldconf=''
+ if [ ! -d "$mysql_cfgdir" ]; then
+ # The configuration directory does not exists
+ install -o 0 -g 0 -m 0755 -d $mysql_cfgdir
+ fi
fi
+
+ # (re)creation of the debian.cnf file
umask 066
- cat /dev/null > $dc
+ > $dc
umask 022
- echo "# Automatically generated for Debian scripts. DO NOT
TOUCH!" >>$dc
- echo "[client]" >>$dc
- echo "host =
localhost" >>$dc
- echo "user =
root" >>$dc
- echo "password =
" >>$dc
- echo "socket =
$mysql_rundir/mysqld.sock" >>$dc
- echo "[mysql_upgrade]" >>$dc
- echo "host =
localhost" >>$dc
- echo "user =
root" >>$dc
- echo "password =
" >>$dc
- echo "socket =
$mysql_rundir/mysqld.sock" >>$dc
- echo "basedir =
/usr" >>$dc
+ echo "# Automatically generated for Debian scripts. DO NOT TOUCH!
+[client]
+host = localhost
+user = root
+password =
+socket = $mysql_rundir/mysqld.sock
+[mysql_upgrade]
+host = localhost
+user = root
+password =
+socket = $mysql_rundir/mysqld.sock
+basedir = /usr" >>$dc
fi
+
# If this dir chmod go+w then the admin did it. But this file
should not.
chown 0:0 $dc
chmod 0600 $dc
- # Update privilege tables
- password_column_fix_query=`/bin/echo -e \
- "USE mysql;\n" \
- "SET sql_log_bin=0;\n" \
- "ALTER TABLE user CHANGE Password Password char(41) character
set latin1 collate latin1_bin DEFAULT '' NOT NULL;"`
-
- # Upgrade password column format before the root password gets set.
- # NOTE: Lines like this apparently really need to be formatted this way
- # for mysqld to process the correclty (;-delimiter, newlines etc)
- echo "$password_column_fix_query" | $MYSQL_BOOTSTRAP 2>&1 | $ERR_LOGGER
-
- # Replace old maintenance user with auth_socket usage if migrating
- replace_query=`/bin/echo -e \
- "USE mysql;\n" \
- "SET sql_mode='', sql_log_bin=0;\n" \
- "DROP USER 'debian-sys-maint'@'localhost';"`
- # WARNING: This line might yield "The MariaDB server is running with
- # the --skip-grant-tables option so it cannot execute this statement"
-
- # Some plugins should installed per default. The query sequence is
supposed
- # to be aborted if the CREATE TABLE fails due to an already
existent table in which case the
- # admin might already have chosen to remove one or more plugins.
Newlines are necessary.
- install_plugins=`/bin/echo -e \
- "USE mysql;\n" \
- "SET sql_log_bin=0;\n" \
- "CREATE TABLE IF NOT EXISTS plugin (name char(64) COLLATE
utf8_bin NOT NULL DEFAULT '', " \
- " dl char(128) COLLATE utf8_bin NOT NULL DEFAULT '', " \
- " PRIMARY KEY (name)) ENGINE=MyISAM DEFAULT CHARSET=utf8
COLLATE=utf8_bin COMMENT='MySQL plugins';\n" \
- "INSTALL PLUGIN unix_socket SONAME 'auth_socket';\n"`
+ # Upgrade Password column format before the root password gets set.
+ echo -e \
+ "SET sql_log_bin=0;
+ ALTER TABLE mysql.user CHANGE Password Password char(41)
CHARACTER SET latin1 COLLATE latin1_bin DEFAULT '' NOT NULL;" |
$MYSQL_BOOTSTRAP 2>&1 | $ERR_LOGGER
- # Install plugins and ignore if already there
+ # Install plugin(s) and ignore if already there
set +e
- echo "$install_plugins" | $MYSQL_BOOTSTRAP 2>&1 | $ERR_LOGGER
+ # Some plugins should be installed by default. The query sequence
is supposed to be
+ # aborted if the CREATE TABLE fails due to an already existent
table in which case the
+ # admin might already have chosen to remove one or more plugins.
Newlines are necessary.
+ echo -e \
+ "SET sql_log_bin=0;
+ CREATE TABLE IF NOT EXISTS mysql.plugin (name CHAR(64) COLLATE
utf8_bin NOT NULL DEFAULT '',
+ dl CHAR(128) COLLATE utf8_bin NOT NULL DEFAULT '',
+ PRIMARY KEY (name)) ENGINE=MyISAM DEFAULT CHARSET=utf8
COLLATE=utf8_bin COMMENT='MySQL plugins';
+ INSERT INTO mysql.plugin (name, dl) VALUES ('unix_socket',
'auth_socket');" | $MYSQL_BOOTSTRAP 2>&1 | $ERR_LOGGER
set -e
if ! set_mysql_rootpw; then
+ # The reset of the root@localhost user password and the
activation of
+ # the auth_socket plugin for it has failed
password_error="yes"
- # restore old config file if exists
- [ -e $oldconf ] && mv $oldconf $dc
+ # The debian.cnf file is restored if pre-existing
+ [ -e "$oldconf" ] && mv "$oldconf" "$dc"
else
- [ -e $oldconf ] && rm -f $oldconf
- # purge debian-sys-maint user
+ [ -e "$oldconf" ] && rm -f "$oldconf"
+ # Purge of the debian-sys-maint@localhost user that is
replaced with
+ # auth_socket on root@localhost
+
+ # WARNING: This line might yield "The MariaDB server is
running with
+ # the --skip-grant-tables option so it cannot execute this
statement"
set +e
- echo "$replace_query" | $MYSQL_BOOTSTRAP 2>&1 | $ERR_LOGGER
+ echo -e \
+ "SET sql_mode='', sql_log_bin=0;
+ DROP USER 'debian-sys-maint'@'localhost';" |
$MYSQL_BOOTSTRAP 2>&1 | $ERR_LOGGER
set -e
fi
;;
@@ -210,7 +232,7 @@
;;
esac
-# here we check to see if we can connect as root without a password
+# Here we check to see if we can connect as root without a password
# this should catch upgrades from previous versions where the root
# password wasn't set. if there is a password, or if the connection
# fails for any other reason, nothing happens.
Follow ups
References
