Advice for `trellis db open` with vagrant dev box?

[techieshark]

Hi, I had trouble running the following command:

trellis db open --app=sequel-ace development

Curious if this works for others, or if I’m running it in some unexpected way.

trellis db open --app=sequel-ace development
Running command => ansible-playbook dump_db_credentials.yml -e env=development -e site=some-site.com -e dest=/var/folders/7f/djdftbb16rs70g91q_dh1p440000gr/T/247274988.json

PLAY [Trellis CLI: Dump database credentials] **********************************

TASK [Dump database credentials] ***********************************************
changed: [192.168.56.5] => (item=some-site.com)
[WARNING]: Platform darwin on host 192.168.56.5 is using the discovered Python
interpreter at /usr/bin/python, but future installation of another Python
interpreter could change the meaning of that path. See https://docs.ansible.com
/ansible/2.10/reference_appendices/interpreter_discovery.html for more
information.

PLAY RECAP *********************************************************************
192.168.56.5               : ok=1    changed=1    unreachable=0    failed=0    skipped=0    rescued=0    ignored=0

Running command => open /var/folders/7f/djdftbb16rs70g91q_dh1p440000gr/T/315929179.spf
✓ Open some-site.com (development) database with sequel-ace

That successfully opens the Sequel Ace GUI app, and it populates the SSH connection settings tab, but upon connection attempt, a pop up with an error appears:

SSH connection failed!

Used command:  /usr/bin/ssh -v -N -S none -o ControlMaster=no -o ExitOnForwardFailure=yes -o ConnectTimeout=10 -o NumberOfPasswordPrompts=3 -o UserKnownHostsFile=/Users/some-user/Library/Containers/com.sequel-ace.sequel-ace/Data/.keys/ssh_known_hosts_strict -F /Applications/Sequel Ace.app/Contents/Resources/ssh_config -o TCPKeepAlive=no -o ServerAliveInterval=60 -o ServerAliveCountMax=1 -p 22 vagrant@192.168.56.5 -L 57258:localhost:3306

OpenSSH_8.6p1, LibreSSL 2.8.3
debug1: Reading configuration data /Applications/Sequel Ace.app/Contents/Resources/ssh_config
debug1: /Applications/Sequel Ace.app/Contents/Resources/ssh_config line 1: Applying options for *
debug1: Authenticator provider $SSH_SK_PROVIDER did not resolve; disabling
debug1: Connecting to 192.168.56.5 [192.168.56.5] port 22.
debug1: fd 3 clearing O_NONBLOCK
debug1: Connection established.
debug1: identity file /Users/some-user/.keys/id_rsa type -1
debug1: identity file /Users/some-user/.keys/id_rsa-cert type -1
debug1: Local version string SSH-2.0-OpenSSH_8.6
debug1: Remote protocol version 2.0, remote software version OpenSSH_8.2p1
debug1: compat_banner: match: OpenSSH_8.2p1 pat OpenSSH* compat 0x04000000
debug1: Authenticating to 192.168.56.5:22 as 'vagrant'
debug1: load_hostkeys: fopen /etc/ssh/ssh_known_hosts: No such file or directory
debug1: load_hostkeys: fopen /etc/ssh/ssh_known_hosts2: No such file or directory
debug1: SSH2_MSG_KEXINIT sent
debug1: SSH2_MSG_KEXINIT received
debug1: kex: algorithm: curve25519-sha256@libssh.org
debug1: kex: host key algorithm: ssh-ed25519
debug1: kex: server->client cipher: chacha20-poly1305@openssh.com MAC: <implicit> compression: none
debug1: kex: client->server cipher: chacha20-poly1305@openssh.com MAC: <implicit> compression: none
debug1: expecting SSH2_MSG_KEX_ECDH_REPLY
debug1: SSH2_MSG_KEX_ECDH_REPLY received
debug1: Server host key: ssh-ed25519 SHA256:pXmm...
debug1: load_hostkeys: fopen /etc/ssh/ssh_known_hosts: No such file or directory
debug1: load_hostkeys: fopen /etc/ssh/ssh_known_hosts2: No such file or directory
debug1: Host '192.168.56.5' is known and matches the ED25519 host key.
debug1: Found key in /Users/some-user/Library/Containers/com.sequel-ace.sequel-ace/Data/.keys/ssh_known_hosts_strict:1
debug1: rekey out after 134217728 blocks
debug1: SSH2_MSG_NEWKEYS sent
debug1: expecting SSH2_MSG_NEWKEYS
debug1: SSH2_MSG_NEWKEYS received
debug1: rekey in after 134217728 blocks
debug1: pubkey_prepare: ssh_get_authentication_socket: Operation not permitted
debug1: Will attempt key: /Users/some-user/.keys/id_rsa  explicit
debug1: SSH2_MSG_EXT_INFO received
debug1: kex_input_ext_info: server-sig-algs=<ssh-ed25519,sk-ssh-ed25519@openssh.com,ssh-rsa,rsa-sha2-256,rsa-sha2-512,ssh-dss,ecdsa-sha2-nistp256,ecdsa-sha2-nistp384,ecdsa-sha2-nistp521,sk-ecdsa-sha2-nistp256@openssh.com>
debug1: SSH2_MSG_SERVICE_ACCEPT received
debug1: Authentications that can continue: publickey
debug1: Next authentication method: publickey
debug1: Trying private key: /Users/some-user/.keys/id_rsa
no such identity: /Users/some-user/.keys/id_rsa: No such file or directory
debug1: No more authentication methods to try.

Getting this working…

In the end, I ran trellis ssh development (or vagrant ssh) and observed the SSH command being run. I then used the port forwarding CLI options from above and tacked them onto the vagrant ssh command, and arrived at:

/usr/local/bin/ssh -v -N -o LogLevel=FATAL -o Compression=yes -o DSAAuthentication=yes -o IdentitiesOnly=yes -o StrictHostKeyChecking=no -o UserKnownHostsFile=/dev/null -i /Users/some-user/some-site/trellis/.vagrant/machines/default/virtualbox/private_key -o ForwardAgent=yes -p 2222 vagrant@127.0.0.1  -L 57191:127.0.0.1:3306

Running that gets us a tunnel from port 57191 to MySQL at 3306 on the Vagrant box.

Then in Sequel Ace I switched the to TCP connection tab (instead of SSH), and switched the port from 3306 to 57191, and the connection then worked.

But just wondering… surely there’s an easier way, something I missed?

[swalkinshaw]

Looks like the main difference (and part missing) is specifying the SSH private key. You can see in the initial generated command there’s no -i <key> option.

Your manual version has -i /Users/some-user/some-site/trellis/.vagrant/machines/default/virtualbox/private_key

I didn’t add the db open command so I’m not 100% familiar with it, but I’m assuming it’s main use case was non-vagrant servers so it would “just work” on those, and this specific use case wasn’t considered.

We can’t really hardcode that private key path in trellis-cli since it varies based on the Vagrant provider. However… Trellis already uses a Vagrant hook to update your local ~/.ssh/config file and I’m assuming there’s a way for Sequel Ace to use that? Maybe the config we generate would overwrite that though

[swalkinshaw]

I got it working There’s a few preferences you need to set in Sequel Ace:

1. make sure it’s using your main SSH config and known hosts files:
   

   

2. you also need to give the app access to those files and the Vagrant private key
   

   

Adding the private key was the main one tripping me up at first. After that’s done, Sequel Ace will use your local ssh config (assuming it’s setup correctly).

I can just do ssh vagrant@example.test locally for example.

fake edit: actually… there’s one more issue. Trellis’ Vagrantfile only sets the ssh config with the canonical host (like example.test), but trellis-cli generates the db open config with the Ansible host (the IP like 192.168.56.5).

So if I manually edit my ssh config and add the IP after the host like this:

Host example.test 192.168.56.5
  # config etc

Then it all works