SSH authentication error from msqldump when pushing database (after successful file pushes)

[ThinkByDesign]

So, to be clear, I'm new at wordmove. This is my second site with it, and the first one isn't quite working yet, but for different reasons.

Describe the bug

When wordmove gets to the remote dump stage, it generates the error noted below (SSH authentication). What's confusing is that it's just successfully conducted SSH connections to move all the files successfully. I'm also able to ssh directly from the command line, as well as through FileZilla.

When I login to the web server and look at the wp-content directory, I don't see a dump file there. HOWEVER, if I login to the web server (using the credential in the config file) and just run the mysqldump (substituting real values for the secrets omitted in the error), it works, and generates the dump.sql file.

I've also looked at the auth.log for possible clues. When I do a push with --all, I see several chunks like this:
Oct 6 02:22:16 web02 sshd[8898]: Accepted publickey for [correct user] from 99.164.33.88 port 57945 ssh2: [key stuff]
Oct 6 02:22:16 web02 sshd[8898]: pam_unix(sshd:session): session opened for user [correct user] by ([correct uid])
Oct 6 02:22:16 web02 systemd-logind[1422]: New session 82907 of user [correct user].
Oct 6 02:22:17 web02 sshd[8974]: Received disconnect from 99.164.33.88 port 57945:11: disconnected by user
Oct 6 02:22:17 web02 sshd[8974]: Disconnected from user [correct user] 99.164.33.88 port 57945
Oct 6 02:22:17 web02 sshd[8898]: pam_unix(sshd:session): session closed for user [correct user]
Oct 6 02:22:17 web02 systemd-logind[1422]: Removed session 82907.

but then when it tries to do the database thing, it just gets this:
Oct 6 02:22:22 web02 sshd[9364]: Connection closed by authenticating user [correct user - same as above] 99.164.33.88 port 57981 [preauth]

It's almost like it's using different credentials for that step? I do have my ssh config setup to filter by user and apply different keys (I have two logins to the same server with their own keys - both work correctly from the command line, but maybe this code is doing something different?)

Any help would be appreciated!!

Wordmove command
wordmove push -e staging -all

Expected behavior
Pushing all the files up to the remote staging server (which succeeded).
Pushing database up to the remote server (failed).

movefile.yml

global:
  sql_adapter: wpcli
  #sql_adapter: default #possible alternative if I run into issues
local:
  vhost: http://local.greenmeadowstx.com
  wordpress_path: /Users/kep/Sites/GreenMeadowsTX.com/wordpress # use an absolute path here
  database:
    name: wp_GreenMeadowsTXcom
    user: GreenMeadows
    password: "*************" # could be blank, so always use quotes around
    host: localhost
    mysqldump_options: '--socket /Applications/MAMP/tmp/mysql/mysql.sock'
    mysql_options: '--socket /Applications/MAMP/tmp/mysql/mysql.sock'

staging:
  vhost: https://greenmeadowstx.thinkbydesign.com
  wordpress_path: [absolute path to where wordpress is installed] # use an absolute path here
  
  database:
    name: GreenMeadowsTX_Wordpress
    user: [RemoteUserName]
    password: "[RemotePassword]"
    host: database.visible.ninja
 
  ssh:
    host: web02.visible.ninja
    # User, HostName, Port, and IdentityFile are all set in ~/.ssh/config
    user: [user with full access to the wordpress directory - key files are specified in ssh config and independently tested/working]
    #port: [port number] # Port is optional
    rsync_options: '--verbose --itemize-changes' # Additional rsync options, optional

  exclude:
    - '.git/'
    - '.gitignore'
    - '.gitmodules'
    - '.env'
    - 'node_modules/'
    - 'bin/'
    - 'tmp/*'
    - 'Gemfile*'
    - 'Movefile'
    - 'movefile'
    - 'movefile.yml'
    - 'movefile.yaml'
    - 'wp-config.php'
    - 'wp-content/*.sql.gz'
    - '*.orig'

Exception/trace

   remote | mysqldump --host=[secret] --user=wordpressuser --password=[secret] --result-file="[secret]/wp-content/dump.sql" GreenMeadowsTX_Wordpress
/Users/kep/.rvm/gems/ruby-2.3.0/gems/net-ssh-6.1.0/lib/net/ssh.rb:268:in `start': Authentication failed for user [REDACTED]@web02.visible.ninja (Net::SSH::AuthenticationFailed)
	from /Users/kep/.rvm/gems/ruby-2.3.0/gems/photocopier-1.4.0/lib/photocopier/ssh.rb:71:in `session'
	from /Users/kep/.rvm/gems/ruby-2.3.0/gems/photocopier-1.4.0/lib/photocopier/ssh.rb:44:in `exec!'
	from /Users/kep/.rvm/gems/ruby-2.3.0/gems/wordmove-5.2.2/lib/wordmove/deployer/ssh.rb:66:in `remote_run'
	from /Users/kep/.rvm/gems/ruby-2.3.0/gems/wordmove-5.2.2/lib/wordmove/deployer/ssh.rb:79:in `download_remote_db'
	from /Users/kep/.rvm/gems/ruby-2.3.0/gems/wordmove-5.2.2/lib/wordmove/deployer/ssh/wpcli_sql_adapter.rb:6:in `backup_remote_db!'
	from /Users/kep/.rvm/gems/ruby-2.3.0/gems/wordmove-5.2.2/lib/wordmove/deployer/ssh.rb:37:in `push_db'
	from /Users/kep/.rvm/gems/ruby-2.3.0/gems/wordmove-5.2.2/lib/wordmove/cli.rb:116:in `block in push'
	from /Users/kep/.rvm/gems/ruby-2.3.0/gems/wordmove-5.2.2/lib/wordmove/cli.rb:40:in `block in handle_options'
	from /Users/kep/.rvm/gems/ruby-2.3.0/gems/wordmove-5.2.2/lib/wordmove/cli.rb:39:in `each'
	from /Users/kep/.rvm/gems/ruby-2.3.0/gems/wordmove-5.2.2/lib/wordmove/cli.rb:39:in `handle_options'
	from /Users/kep/.rvm/gems/ruby-2.3.0/gems/wordmove-5.2.2/lib/wordmove/cli.rb:115:in `push'
	from /Users/kep/.rvm/gems/ruby-2.3.0/gems/thor-0.20.3/lib/thor/command.rb:27:in `run'
	from /Users/kep/.rvm/gems/ruby-2.3.0/gems/thor-0.20.3/lib/thor/invocation.rb:126:in `invoke_command'
	from /Users/kep/.rvm/gems/ruby-2.3.0/gems/thor-0.20.3/lib/thor.rb:387:in `dispatch'
	from /Users/kep/.rvm/gems/ruby-2.3.0/gems/thor-0.20.3/lib/thor/base.rb:466:in `start'
	from /Users/kep/.rvm/gems/ruby-2.3.0/gems/wordmove-5.2.2/exe/wordmove:6:in `<top (required)>'
	from /Users/kep/.rvm/gems/ruby-2.3.0/bin/wordmove:23:in `load'
	from /Users/kep/.rvm/gems/ruby-2.3.0/bin/wordmove:23:in `<main>'

Environment (please complete the following information):

• OS: MacOS 11.6
• Ruby: 2.3.0
• Wordmove: 5.2.2

Doctor
Reports all green except for
❌ error | [/database/mysql_options] key 'mysql_options:' is undefined.

[alessandro-fazzi]

Hi @ThinkByDesign ,

thanks for the inquiry. As a first try I'd suggest to try to add a config to your ~/.ssh/config (if I understood you already have one so you know what I'm talking about) under the interested host:

Host example.com
  ControlMaster auto
  ControlPath /tmp/master-%r@%h:%p
  Compression yes

I know that with some hosting there are problem with SSH auth during db transfer operations. This is due to wordmove making a lot of different close and subsequent connections. Some hosting providers have something like anti-burst security config conflicting with this behaviour.

Having that configurations you'd end having something like a socket open for all the connections.

I'm not sure it will work because its also possible that every connection will open the "socket", do its business then release the "socket" and pass on to the next operation. But it worth a try.

In the past I've tried to solve this problem by code here #528 but never received enough feedback and never had the opportunity to test it myself; so I don't know if my "blind coding" made something interesting there.

[ThinkByDesign]

Well, it was a good, solid suggestion, but no luck on the ssh config. I'll check out the code idea and see what's going on there.

[ThinkByDesign]

After reviewing the other case, I don't believe that they are related. In your case, it was at least connecting in order to run the code. I wish mine were getting that far. :-) Mine isn't even connecting, but only on the database connection. Which does give me an idea.

Is it possible that wordmove is trying to connect directly to the database server with an ssh connection? I'll need to check the logs and see if perhaps that's happening. In that case, I would just need to give the same user the same info there? I'd originally assumed that the dump was running on the web server and just making a remote sql call.

[ThinkByDesign]

OK. So, every single time I run the database pull, I see an entry in the database server's sshd log for an attempted connection coming from another Digital Ocean IP address (where my servers are). I'm asking them to identify the IP address because it's not one that I have associated with either droplet.

Regardless, it appears that wordmove (or one of the associated programs) is trying to connect to the database server over ssh as root.

Does wordmove have such a requirement? That the web server be able to make an ssh connection to the database server (assuming they are separated) as root?

[alessandro-fazzi]

Oh wow...it's getting hot out there.

Let me try to reply somewhat; what Wordmove does is something like

• connect via SSH to the remote machine
• this remote machine is assumed to be the webserver where WP lies
• from inside the remote machine it runs mysql/mysqldump using the connection params from movefile.yml

This is how is explained in the docs

@note: you have always to configure the database credential based on how you configured wp-config.php; when working on a remote, Wordmove will firstly connect to it and within there it will connect to the database. So do not imagine to connect from your laptop to the database, but think from the WP install perspective. We think it's even easier to simply copy credential from the wp-config.php, isn't it?

The assumption guiding Wordmove is right there: if WP can connect to the DB, then I (Wormdove) should be able do it too.

That said: your remote configuration in movefile.yml has a single ssh key and those ssh configurations will be used for both file sync and db sync operations. Wordmove can't be able to connect to different things through SSH during any of the operations it will run.

ASIDE: this could lead to some confusion if you're using gateway config, but I bet you'd have cited the thing already if you were.

And...well I think that's all ATM.

I hope I dropped something useful for you.

[alessandro-fazzi]

Oh I forgot! One way to simulate Wordmove's work is to SSH on your webserver, then

mysql -uDBUSER -h DBHOST -p

copying params from wp-config.php from WP installation on that server. If you could connect that way then Wordmove should be able too.

Best

[ThinkByDesign]

(Update: just saw your reply while typing this. I'll try that and see what happens.)

OK. That actually matches my original assumption - that from my local machine, there's an ssh connection to the web server, and then the web server connects to the database server just like wordpress does. That also makes sense based on how the config file is setup, etc.

The only clue not matching that scenario is that the database pull experiences an ssh authentication failure, which APPEARS to be a connection to the database server as root. (It's possible that I'm seeing coincidental hacking attempts as well, though, and jumping to an inaccurate conclusion.)

[ThinkByDesign]

mysql connects just fine from the command line of the web server account that wordmove is configured to connect to over ssh. (So, the mysql -u *** -h *** -p)

I wonder if there's anything different about how the rsync operations connect vs how the database operations connect? I mean, there would obviously be some difference because one is using rsync (I assume) where the other is actually opening a shell, but I wonder if the database ssh code is adding or omitting something? I've never written ruby code, but I'm sure I can figure out how to print the actual ssh command being attempted - that might let me see what's going on.

[alessandro-fazzi]

I can't get how that logs could be related to Wordmove.

Looking at your web server sshd logs you should see failures from your development IP address, tho. I can tell it from this piece of the trace:

Authentication failed for user [REDACTED]@web02.visible.ninja 

I've redacted the user there, since you had redacted it from the pasted movefile.yml

One thing I'm looking at doubtful is that you said ssh user is present in your ~/.ssh/config but still you've set it up in the movefile...are you sure they are the same? Usually when you want to handle ssh configs from the config file you'd like to remove the ssh.user key in movefile...

[ThinkByDesign]

I have two user accounts that I can use to ssh into the web server. One is mine, and one is the web server (so it would be able to write/update files directly, which my own account can only do with sudo). From the command line, I can get to either just fine (I setup remote access for the web server for this project), and my local ssh config determines which keys to use using the "match user [user]" directive. I suppose I could try commenting out my own account info and see if maybe the match user bit is confusing things for the database-related ssh connection?

Or perhaps if the rsync operations are using the standard .ssh configurations, where maybe the Ruby ssh code is ignoring them, which would mean that it doesn't know to use the correct ID files? Just thinking out loud.

[alessandro-fazzi]

The fact is that if you have ssh.user it will superseed the one in ~/.ssh/config. Now it's impossible for me to read deeply how the SSH ruby's implementation will behave in such a scenario, but if you have some directive like "match user xxx" then you're risking to not match it because you're passing another one within movefile.yml.

Think of a situation where you have

Host foo_host
  User foo_user
  Hostname 1.1.1.1

Match User foo_user
  Do something with your keys

and you're calling SSH like

ssh -l bar_user foo_host

this could be something similar to what's happening when Wordmove calls the SSH routines

then I fear this would blow up your Match stanza. Not sure this is your case, but I'd check into it in your place.

[ThinkByDesign]

Hmm. So, the users were the same between the movefile.yml and the match user statements in ssh config (if it didn't specify which one to use, ssh wouldn't know which identity file to use).

However...

Once I commented out the match user lines and left only the web server's identityfile, it worked!!

Now, I'm just getting errors about the "you need at least one of the PROCESS privileges" stuff, which I thought I'd already set but perhaps not. Totally different thing, though.

So, the takeaway here seems to be that if you have multiple users that you connect with over ssh and you use the match user directives to choose different IdentityFile directives, wordmove gets confused (or just isn't using/relying on that in order to do things).

Does that make sense?

[ThinkByDesign]

And, we have liftoff!!

So, the key to making ssh work for the database part was not using "match user" in the ssh config. Fortunately, I can work around that (I don't NEED to login as both in general, and if I did need to I could switch things temporarily). However, it would certainly be nice if it did work, or at least some notes letting people know about the situation (which this will do, in part).

But, it is working. :-)

[alessandro-fazzi]

I've read the part of Net::SSH (ruby's core lib handling SSH connections) which handles SSH's config files: Net::SSH::Config /~https://github.com/net-ssh/net-ssh/blob/master/lib/net/ssh/config.rb

NOTE: If you're not a rubist do not waste time on code implementation: it looks hard for me, whom write quite a bit of ruby, and it's a mess of regular expressions. But comments are interesting for everyone.

From what I understand the only supported Matches statements are all and host. So I'd confirm Match user is not parsed and no warning is thrown to STDOUT.

So yes, your assumption was just on point: Wordmove doesn't (cannot) support Match user neither. This could go into some part of documentation.

A polite way to handle such situation would be to use two different Host instead of a Match on the user, e.g.:

Host foouser_websrv
  User foouser
  Hostname 1.1.1.1
  IdentityFile /some/key

Host baruser_websrv
  User baruser
  Hostname 1.1.1.1
  IdentityFile /some/other/key

I've retagged and assigned the ticket to me in order to take the time to think where to put a note about this fact in the docs.

Thank you very much for your detailed troubleshooting and for the time invested.

[alessandro-fazzi]

Going to close as there are not more actions to go for

[humblecoder]

Apologies for resurrecting a "dead" thread, but Net::SSH::AuthenticationFailed is still happening for me.

Currently running: MacOS 12.4 on M1 Max

I've tried every single version of ruby from 2.6.0 to 3.1.1, along with each relevant Wordmove gem. None work, and the majority fail with Net::SSH::AuthenticationFailed. I did this on both the host machine as well as a Vagrant Linux VM. It would be nice, at the very least, to get a bit more verbose output regarding what failed.

I am able to manually SSH to the server via ssh keys from both the host and guest machines. Wordmove (or more specifically net-ssh gem doesn't seem to care).

Any additional thoughts? 🤔

UPDATE
Turns out Issue 586 was the key (pun intended) to my issue. 😖

[alessandro-fazzi]

(pun intended)

😄

Thanks for updating