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.

Secreta: a little suite for managing configuration secrets

After learning AWS and Lambda, in the past few weeks I wrote Secreta, which is a set of three tools to manage configuration secrets in AWS Lambda functions.

secreta-generate-aws is a command line utility that creates a pair of keys for asymmetric key encryption, using RSA. It does so by

  1. running the forge module directly in an AWS Lambda function
  2. saving the private key directly in an AWS Parameter, encrypted and protected by an access tag
  3. saving the public key to a local file

secreta-encrypt is a command line utility that encrypts to a local .secreta file the secrets referenced in your configuration files (using a public key).

secreta-decrypt-aws is a NodeJS module that you can install into your AWS Lambda function to decrypt .secreta files in memory (using the corresponding private key, retrieved from the AWS Parameter) and merge them into the rest of the configuration, as if they had never been encrypted.

You can share the public key in your project repository. This will allow any other trusted developer (like yourself) to always have a current public key to keep encrypting configuration secrets. These could be obtained by exchanging GPG email messages, for example.

You can share the .secreta files in your project repository. This will allow any other developer to always have current configuration secrets to keep deploying your AWS Lambda function.

Documentation here.

AWS Lambda Invoke Errors

Lately, I’ve been developing a Lambda function to create a pair of keys, store one in a parameter and return the other to the user. Today I got my first clean run, and I’m writing this to celebrate.

As part of learning lots of things along the way, because I hadn’t developed anything on AWS before, I found out that AWS reports errors in many different ways. Here are those that occurred to me in the last few hours.

See: AWS Lambda Invoke Errors (documentation)

Error — InvalidZipFileException

{ InvalidZipFileException: Lambda was not able to unzip the file
       <stack trace...>
     message: 'Lambda was not able to unzip the file',
     code: 'InvalidZipFileException',
     time: 2017-09-16T16:04:04.558Z,
     requestId: '<request id...>',
     statusCode: 502,
     retryable: true } }
  • This error occurred because the zip file I had uploaded was wrong: the files it contained were stored into a directory.
  • This is a Lambda creation error, and it’s quite complete.
  • Unhandled / Handled classification is limited to Lambda execution.

Error — Process exited

{ errorMessage: 'RequestId: <request id...> Process exited before completing request' }
  • This error occurred because I threw a validation error.
  • I could catch this error, but don’t, so it’s classified as Unhandled.
  • The logs show my error and its stack trace.
17:12:39 START RequestId: <request id...> Version: $LATEST
17:12:39 2017-09-16T17:12:39.442Z   <request id...>    Error: Expected a pair ID for your keys. at setPairId (/var/task/createPairOfKeys.js:9832:15) at handler (/var/task/createPairOfKeys.js:9805:9)
17:12:39 END RequestId: <request id...>
17:12:39 REPORT RequestId: <request id...> Duration: 203.72 ms Billed Duration: 300 ms Memory Size: 128 MB Max Memory Used: 78 MB
17:12:39 RequestId: <request id...> Process exited before completing request

Error — Timed out

{ errorMessage: '2017-09-16T17:32:08.746Z <request id...> Task timed out after 10.00 seconds' }
  • This error occurred because generating a pair of keys is not fast (bigint maths involved) and, in my few tests, it sometimes needed more than 40 seconds.
  • Surprisingly, my MacBook is faster than AWS Lambda. A few times it took less than 1 second, most of the times less than 6 seconds, and only once it timed out at 10 seconds.
  • I can’t catch this error, so it’s classified as Unhandled.

Error — AccessDeniedException

{ errorMessage: 'User: <user arn...> is not authorized to perform: ssm:PutParameter on resource: <resource arn...>',
        errorType: 'AccessDeniedException',
        stackTrace: [Object] } } }
  • This error occurred because the role I had assigned to my Lambda function didn’t have the right to write SSM parameters.
  • I catch this error and swallow it, so it’s classified as Handled.

Error — ParameterAlreadyExists

{ errorMessage: 'The parameter already exists. To overwrite this value, set the overwrite option in the request to true.',
        errorType: 'ParameterAlreadyExists',
        stackTrace: [Object] } } }
  • This error occurred because I explicitly create my SSM parameter with overwrite set to false. Not gonna change it, this is by design.
  • Notice how this is an exception whose name doesn’t end with Exception.
  • I catch this error and swallow it, so it’s classified as Handled.