wsrep_sst_auth don't work in Percona-XtraDB-Cluster-56-5.6.25-25.12.1.el7

[Zheng, Ping]

I wrote a dockerfile for Percona-XtraDB-Cluster-56, and build a cluster on it. Today I upgrade my cluster from 5.6.24 to 5.6.25, then I notice that sst failed. I have to downgrade my percona cluster, change lines from

> RUN yum -y install Percona-XtraDB-Cluster-56

to

> RUN yum -y install Percona-XtraDB-Cluster-client-56-5.6.24 \
> Percona-XtraDB-Cluster-server-56-5.6.24 \
> Percona-XtraDB-Cluster-shared-56-5.6.24 \
> Percona-XtraDB-Cluster-galera-3-3.9-1 \
> percona-xtrabackup

then everything went back to normal.

innobackup.backup.log

InnoDB Backup Utility v1.5.1-xtrabackup; Copyright 2003, 2009 Innobase Oy
and Percona LLC and/or its affiliates 2009-2013. All Rights Reserved.

This software is published under
the GNU GENERAL PUBLIC LICENSE Version 2, June 1991.

Get the latest version of Percona XtraBackup, documentation, and help resources:
http://www.percona.com/xb/p

151012 17:27:17 innobackupex: Connecting to MySQL server with DSN 'dbi:mysql:;mysql_read_default_file=/etc/my.cnf;mysql_read_def
ault_group=xtrabackup;mysql_socket=/var/lib/mysql/mysql.sock' as 'sst' (using password: NO).
innobackupex: got a fatal error with the following stacktrace: at /usr//bin/innobackupex line 3011.
        main::mysql_connect('abort_on_error', 1) called at /usr//bin/innobackupex line 1570
innobackupex: Error: Failed to connect to MySQL server: DBI connect(';mysql_read_default_file=/etc/my.cnf;mysql_read_default_grou
p=xtrabackup;mysql_socket=/var/lib/mysql/mysql.sock','sst',...) failed: Access denied for user 'sst'@'localhost' (using password:
 YES) at /usr//bin/innobackupex line 2995.

for me it seems like innobackupex script didn't get the password from /etc/my.cnf.

[Ramesh Sivaraman (rameshvs02)]

Able to reproduce the issue with PXC-5.6.25. Backup script is not picking wsrep_sst_auth value as expected.

** Error info

151020 06:32:07 innobackupex: Connecting to MySQL server with DSN 'dbi:mysql:;mysql_read_default_file=/etc/my.cnf;mysql_read_default_group=xtrabackup;mysql_socket=/var/lib/mysql/mysql.sock' as 'sstuser' (using password: NO).
innobackupex: got a fatal error with the following stacktrace: at /usr//bin/innobackupex line 3011.
 main::mysql_connect('abort_on_error', 1) called at /usr//bin/innobackupex line 1570
innobackupex: Error: Failed to connect to MySQL server: DBI connect(';mysql_read_default_file=/etc/my.cnf;mysql_read_default_group=xtrabackup;mysql_socket=/var/lib/mysql/mysql.sock','sstuser',...) failed: Access denied for user 'sstuser'@'localhost' (using password: NO) at /usr//bin/innobackupex line 2995.
[root@Dock1 mysql]#

** my.cnf

wsrep_sst_auth="sstuser:s3cret"

[Ramesh Sivaraman (rameshvs02)]

This issue is happening when we have "passward" variable in my.cnf file. Backup script is reading "password" variable first instead of "wsrep_sst_auth". If "password" variable is not present in my.cnf backup script will use "wsrep_sst_auth" value to start PXC.

[Raghavendra D Prabhu (raghavendra-prabhu)]

This happens only when [client] has a user/password combination (or just password) and wsrep_sst_auth has a different user/password combination.

It happens because in 5.6.24, password is passed to innobackupex through MYSQL_PWD if possible to avoid password appearing in ps output,

 The issue here is, innobackupex (and libmysqlclient) in general takes password for a different user (or any password in my.cnf for [client]) even if password is provided for the correct user (supplied through --user to innobackupex) through MYSQL_PWD.

This needs to be workaround in SST script, ie. to not use MYSQL_PWD when a password is present in [client] in cnf file.

[Raghavendra D Prabhu (raghavendra-prabhu)]

Note that even though https://dev.mysql.com/doc/refman/5.6/en/password-security-user.html states MYSQL_PWD as being insecure when ps can query it, at least on Linux that is not true. Non-root users cannot query /proc/$pid/environ (hence ps e) of processes which they don't own but they still can see the command-line of those processes.

[Krunal Bauskar (krunal-bauskar)]

Preference used by mysql client is following

1. Command line --password
2. cnf file
3. MYSQL_PWD

When client is invoked through PXC (indirectly from innobackupex - Percona Xtrabackup) password is in form of MYSQL_PWD that will have lower priority than password set explicitly in cnf file.

[client]
password=xyz

Even though this password is not something that user want to use for sst operation.

For sst user has specified wsrep_sst_auth and passwd from this field is set in MYSQL_PWD which is kind of ignored due to preference order.

------------

To keep the semantics clear:

1. When client connection is open for SST it has to use user-name passwd provided in wsrep_sst_auth
2. To make use of it and over-write any other preference this user-name and password should be passed through command line.
3. client may specify different user/paswd in client section of cnf file for normal/other operations.

[Krunal Bauskar (krunal-bauskar)]

commit c0300e2564e8d03fee5c6e554918d8b98f67c16c
Merge: 4055140 b966fc0
Author: Krunal Bauskar <email address hidden>
Date: Thu Oct 22 15:05:42 2015 +0530

    Merge pull request #48 from kbauskar/5.6-pxc-460

    - PXC#460: wsrep_sst_auth don't work in Percona-XtraDB-Cluster-56-5.6…

commit b966fc0e200b0bd7ed32027c5ade7749f70dca51
Author: Krunal Bauskar <email address hidden>
Date: Thu Oct 22 14:56:29 2015 +0530

    - PXC#460: wsrep_sst_auth don't work in Percona-XtraDB-Cluster-56-5.6.25-25.12.1.el7

      Semantics:
      ---------

      * Generally end-user will create a separate user with needed privileges for
        performing DONOR action.
      * This user credentials are specified using wsrep_sst_auth.

      * Along with this user there could be other user(s) created on the server
        that sysadmin may use for normal or other operations
      * Credentials for these user(s) can be specified in same cluster/server
        cnf file as part of [client] section

      When cluster act as DONOR and if wsrep_sst_auth is provided then it should
      strictly use it for performing SST based action.

      What if end-user has same credentials for performing both SST action and
      normal admin work ?
      * Then end-user can simply specify these credentials as part of [client]
        section in cnf file and skip providing wsrep_sst_auth.

      Issue:
      -----

      MySQL client user/password parsing preference order is as follows:
      * command line (through --user/--password)
      * cnf file
      * MYSQL_PWD enviornment variable.

      Recent change tried passing sst user password through MYSQL_PWD
      (and user though --user command line param as before).

      On the system where-in admin had another user for performing non-SST actions,
      credentials for such user were present in cnf file under [client] section.

      Due to mysql client preference order, SST user name was used (as it was
      passed through command line) but password of other user (meant for non-SST)
      action was being used as it was passed through cnf file.

      Password passed through MYSQL_PWD was completely ignored causing
      user-name/password mismatch.

      Solution:
      ---------

      * If user has specified credentials for SST then pass them through command
        line so that they are used in priority.

      (There could be security concern on passing things through command line but
       when I tried passing user-name and password through command line to mysql
       client and then did ps I saw this
       ./bin/mysql --user=sstuser --password=x xxxxxxxx -S /tmp/n1.sock
      so seems like password is not shown)

[Shahriyar Rzayev (rzayev-sehriyar)]

Percona now uses JIRA for bug reports so this bug report is migrated to: https://jira.percona.com/browse/PXC-1127