Sequel pro + ssh to vagrant

[cimocimocimo]

Why was this disabled? I use Trellis to run multiple WP sites on one host and find using the root account to manage dumps and other maintenance tasks much easier than managing multiple user accounts. This is especially true during development. I can understand disabling root access on a production server but would like the option to override that behaviour.

Could I do this by editing the MariaDB role?

[cimocimocimo]

Ok, sorry for the above comment. I read through everything and found out that this is default MariaDB behaviour and nothing to do with Trellis.

As others have mentioned in other posts this is due to the root login using the auth plugin unix_socket. You can restore the old root password login behaviour following this post:

Cannot connect to MySQL

Hi guys, I have this issue also and I’m trying to track down where it has come from. It appears to be present since the mariaDB source was changed in ansible here: https://github.com/roots/trellis/commit/f7873ec29f7544b20255e05aaadd18403b98307b It appears there is an issue with the plugin unix_socket and the vagrant user not being allowed to login as root because unix_socket requires sudo. That explains why vagrant access for the WP user still functions. But connections to the mysql root user …

I don’t know Ansible well enough to setup to run a script during provisioning but I imagine that it’s possible.

What I ended up doing to get my backup scripts running again quickly was to just add another mysql user with full privileges. I just added this:

- name: Add admin user
  mysql_user:
    name: admin
    host: "{{ item }}"
    password: "{{ mysql_root_password }}"
    check_implicit_admin: yes
    priv: '*.*:ALL,GRANT'
    state: present
  with_items:
    - 127.0.0.1
    - ::1
    - localhost

to “roles/mariadb/tasks/main.yml”.

Probably not the best idea from a security perspective I but I don’t see it as any worse than before when mysql root password logins were allowed. And since I only allowed local access to the db.

[ericrowan]

For anyone else unable to get the above suggestions working, this article by the Roots team may help: Simplifying Trellis - blog - Roots Discourse

The gist:

Another example is db_name and db_user. We always documented and recommended the use of the site name for these values. So if your site is named roots.io then it makes sense to have a database called roots_io_production and a user called roots_io (MySQL doesn’t like dots).

[broskees]

Trying to connect to my local server via Sequel Pro. Followed the doc’s and got this:

Used command:  /usr/bin/ssh -v -N -S none -o ControlMaster=no -o ExitOnForwardFailure=yes -o ConnectTimeout=10 -o NumberOfPasswordPrompts=3 -o TCPKeepAlive=no -o ServerAliveInterval=60 -o ServerAliveCountMax=1 vagrant@syracusesedationandfamilydentistry.test -L 62303:127.0.0.1:3306

OpenSSH_7.6p1, LibreSSL 2.6.2
debug1: Reading configuration data /Users/josephroberts/.ssh/config
debug1: /Users/josephroberts/.ssh/config line 2: Applying options for *
debug1: Reading configuration data /etc/ssh/ssh_config
debug1: /etc/ssh/ssh_config line 48: Applying options for *
debug1: Control socket " none" does not exist
debug1: Connecting to syracusesedationandfamilydentistry.test [192.168.50.5] port 22.
debug1: fd 6 clearing O_NONBLOCK
debug1: Connection established.
debug1: identity file /Users/josephroberts/.ssh/id_rsa type 0
debug1: key_load_public: No such file or directory
debug1: identity file /Users/josephroberts/.ssh/id_rsa-cert type -1
debug1: Local version string SSH-2.0-OpenSSH_7.6
debug1: Remote protocol version 2.0, remote software version OpenSSH_7.2p2
debug1: match: OpenSSH_7.2p2 pat OpenSSH* compat 0x04000000
debug1: Authenticating to syracusesedationandfamilydentistry.test:22 as 'vagrant'
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: Server host key: ssh-ed25519 SHA256:YD4Fbjv6igUaXf2u7OfZtgYndkdf31qK0IejP3tyuGk
debug1: read_passphrase: can't open /dev/tty: Device not configured
debug1: permanently_drop_suid: 501
Host key verification failed.

Thoughts?