How to connect from SequelPro to an Ubuntu server using Public Key authentication

Connecting from SequelPro to an Ubuntu server using Public Key authentication looks like a very simple setup, and in fact it entails just a few steps, but I had to learn again each of them the hard way. After many months without using SequelPro to access my WordPress database on DigitalOcean, my Ubuntu server was already the second new instance since the last time I had configured SequelPro and I hadn’t the slightest idea of which was the last working configuration and how it was set up.

Machines

• Remote: the machine you want to connect to with SSH
• Local: the machine you want to connect from with SSH

Setup

1. Remote: Create a group of users with permission to login with SSH
   • Open a terminal window on Local and SSH into Remote using the root user
   • Run # addgroup sshlogin
   • Run # adduser root sshlogin
   • Edit the /etc/ssh/sshd_config file and append a line with AllowGroups sshlogin.
   • Run # systemctl restart ssh
   • Before closing this terminal window, open a new one and try to login with SSH using the root user. If you are denied access, go back to the previous terminal window and try to figure out how to fix root access while you still have root access.
2. Remote: Create a SequelPro user and add it to the sshlogin group
   • Run # adduser sequel_pro
   • Run # adduser sequel_pro sshlogin

   The SequelPro user is a common user, with its own home directory.

   Set a long password, only used to prevent unauthorised impersonation (without an authorised key).

3. Local: Generate a key pair

   • Run $ ssh-keygen -b 4096

   I used an empty passphrase to protect the private key.

4. Remote: Authorise the key for the SequelPro user

   • Edit the /home/sequel_pro/.ssh/authorized_keys file and append a line with the pubic key (one long line).
   • Run # chown -R sequel_pro:sequel_pro /home/sequel_pro/.ssh
   • Run # chmod 0700 /home/sequel_pro/.ssh
   • Run # chmod 0600 /home/sequel_pro/.ssh/authorized_keys

Test

1. (add the SequelPro user to the sshlogin group and) confirm that you can login

   andrea at Lock-and-Stock in ~
   $ ssh sequel_pro@159.89.188.53
   sequel_pro@159.89.188.53: Permission denied (publickey).
   
   andrea at Lock-and-Stock in ~
   $ ssh -i ./.ssh/sequel_pro-id_rsa sequel_pro@159.89.188.53
   Welcome to Ubuntu 16.04.3 LTS (GNU/Linux 4.4.0-127-generic x86_64)
   ...
   sequel_pro@wordpress-1vcpu-2gb-nyc3-01:~$
   

2. remove the SequelPro user from the sshlogin group and confirm that you cannot login

   root@wordpress-1vcpu-2gb-nyc3-01:/etc/ssh# deluser sequel_pro sshlogin
   Removing user `sequel_pro' from group `sshlogin' ...
   Done.
   

   andrea at Lock-and-Stock in ~
   $ ssh -i .ssh/sequel_pro-id_rsa sequel_pro@159.89.188.53
   sequel_pro@159.89.188.53: Permission denied (publickey).
   

Troubleshooting

• On remote
  1. Check owner and permissions of the .ssh directory and its contents.

  2. Make sure that AllowGroups sshlogin is working nicely with Match rules.

     In my case, the former was not working for sequel_pro (i.e. sequel_pro could login both when it belonged to sshlogin and when it did not) because the former appeared just before.