Introduction

The Secure Shell Protocol (SSH) provides IT professionals with a secure method to remotely connect to client environments. SSH typically uses a password or public-key method to encrypt connections and authenticate network devices. This tutorial will focus on establishing an SSH connection for data transfer between the postgres users on two Ubuntu 22.04 LTS instances.

Step 1: Install OpenSSH

Before we can communicate between servers, we need to install OpenSSH, a suite of tools designed to use SSH to provide an encrypted, secure connection between servers:

sudo apt -y install openssh-server

Step 2: Generate SSH Keys

With OpenSSH installed, we now have tools in place that will allow an SSH connection, but we still need to give each server permission to connect with the other. To establish our connection, we generate a pair of ssh keys on each server. The pair of keys will consist of one private key and one public key. The private key contents should always be kept private (hence the name), while the public key can be shared in instances such as this to establish a connection. After creation, we will then transfer the public key contents of one server to an authorized_keys file on the other.

Since we are connecting between the postgres user on each server, we will need to set the postgres user’s password on each server to connect. We can do so with:

sudo passwd postgres

You will be prompted to enter and repeat a new password. Now, let’s become the postgres user on both servers to bypass some extra steps:

sudo -iu postgres

The ssh-keygen command will create our pair of ssh keys. As the postgres user, let’s begin by creating keys on each server:

ssh-keygen -t rsa -b 4096 -N "" -f /var/lib/postgresql/.ssh/id_rsa

Let’s discuss the options we have included in our command:

• -t indicates the type of key we will be generating (rsa).
• -b specifies the key size in bits (4096).
• -N specifies the passphrase, which we are excluding. If you wish to include a passphrase (recommended for security purposes), you may do so by excluding this option in the command and entering one when prompted after executing the command. This prevents your password from being recorded in any terminal logs.
• -f indicates the output key file, which creates the .ssh directory with a private key labeled id_rsa and a public key named id_rsa.pub. This is the default path for the key placement, but we have included it explicitly for clarity of operations, or so you know how to change the path if desired.

This will produce output similar to:

Generating public/private rsa key pair.
Created directory '/var/lib/postgresql/.ssh'.
Your identification has been saved in /var/lib/postgresql/.ssh/id_rsa
Your public key has been saved in /var/lib/postgresql/.ssh/id_rsa.pub
The key fingerprint is:
SHA256:pzXyCMcv4LOXdjtlgAsnkggYiG4OLfDywbd9M4pNhD0 postgres@bi-primary
The key's randomart image is:
+---[RSA 4096]----+
|=. |
|* |
|o= . + . |
|+o* = E.o . |
|++ o =o=S.+. |
| .. ..o+=O .o |
| +oo++oo |
| . oo+.o |
| .o ..o |
+----[SHA256]-----+

Step 3: Transfer Public Keys

Now, we transfer the public key from one server to the other. There are a few methods to do this, but the simplest is to copy/paste them from one to the other.

Let’s begin by creating the authorized_keys file where we placed the public key from the other server. We then change permissions on the file to keep it secure. The authorized_keys file should be readable and writable only to our postgres user, which is what the 600 setting means below:

touch ~/.ssh/authorized_keys
chmod 600 ~/.ssh/authorized_keys

The contents of the public key file will be a single, long line with no line breaks. However, less will wrap the display across multiple lines of the screen. We copy the contents of the primary server public key (id_rsa.pub) into the authorized_keys file of the secondary server, and vice versa. View the contents of the file on the primary server with:

less ~/.ssh/id_rsa.pub

Now, copy the string in the file, which should begin with ssh-rsa and end with postgres@primary_name. Next, open the authorized_keys file on the secondary and paste the contents from the primary server’s id_rsa.pub:

vi ~/.ssh/authorized_keys

Repeat this procedure using the secondary server public key and the primary server authorized_keys file.

Step 4: Test Connection

Next, let’s test the connection. During this process, you will be prompted to confirm the fingerprint from the server you are trying to connect to. The fingerprint is a shortened version of the host’s public key, which is easier to visually confirm than the entire key. This fingerprint is stored on the client server, usually in the ~/.ssh/known_hosts file, to validate the connection from the client to the host and prevent man-in-the-middle attacks. The fingerprints on a server are usually stored in the /etc/ssh/ directory, so we can list the fingerprints on the primary server with this command:

for k in $(ls -1 /etc/ssh/ssh_host_*_key.pub) ; do ssh-keygen -l -f "$k" ; done

It should return something like this:

256 SHA256:orldbEz5z8lTOkjARQACvv3NUlUbTatvi11Wz5UuypE root@primary (ECDSA)
256 SHA256:46ndduXK7ftOBFjL1ER6dvDzvHjKDCNYd0Tar3x/8FE root@primary (ED25519)
3072 SHA256:qLUOsQ3o63tZ+eM/9ZumZsEgWzJCfidK4St+ZyFX7eE root@primary (RSA)

This list includes the fingerprints for the ECDSA, ED25519, and RSA encryptions, but the server you are on may have more. Now that we know what fingerprints the primary server has, we can confirm them when we try to connect. To connect from the secondary, we enter:

ssh postgres@192.168.0.1

You should now be asked to confirm the fingerprint on the host server with something like:

The authenticity of host '192.168.0.1 (192.168.0.1)' can't be established.
ED25519 key fingerprint is SHA256:46ndduXK7ftOBFjL1ER6dvDzvHjKDCNYd0Tar3x/8FE.
This key is not known by any other names
Are you sure you want to continue connecting (yes/no/[fingerprint])?

This output is telling us that the host 192.168.0.1 is not in the known_hosts file, which actually does not exist yet since we have not yet connected to any hosts. We are asked to confirm the fingerprint from the host, and we can visually compare this to the ED25519 fingerprint from the list we just printed. Since they match, we can safely type yes to continue connecting.

You will receive an output that looks something like this:

Warning: Permanently added '192.168.0.1' (ED25519) to the list of known hosts.
ssh_dispatch_run_fatal: Connection to 192.168.0.1 port 22: Broken pipe

The known_hosts file has been created, and the fingerprint has been added. Let’s attempt to connect again:

ssh postgres@192.168.0.1

You should now be prompted to enter the postgres password for the primary server:

postgres@192.168.0.1's password:

Enter the password you set earlier, and you should receive some welcoming information indicating you are on the primary server. We can confirm this by issuing the hostname command:

hostname

And it should display the primary server’s name:

primary

Assuming there are no hangups, we are ready to transfer data!

Summary

We have covered how to establish an SSH connection between two servers. We installed OpenSSH, generated key-pairs, transferred public key information, and then tested our connection. This will allow us to securely transfer data across systems with our postgres user. Thanks for tuning in!