Can't establish Postgres pgAdmin SSH tunnel on Ubuntu

Question

I have a droplet on DigitalCloud with Ubuntu 14.04 and PostgreSQL 9.3. On local machine i have the same configuration.

My ssh connection is working so there is no problem with it. It must be somewhere in my Postgres connection or environment settings.

So what i have already done on the server is:

• changed the listening port in /etc/ssh/sshd_config

  Port 4321

• enabled remote connections in /etc/postgresql/9.3/main/pg_hba.conf

  host all all 0.0.0.0/0 md5

• added listening addresses in /etc/postgresql/9.3/main/postgresql.conf

  listen_addresses = '*'

And here is my local connection settings:

Here is my Properties tab screen

Here is my SSH Tunnel tab screen

I use just the same (and only) id_rsa.pub file which i used to establish my ssh connection before. Which is working. And this is an error that i get upon trying to connect:

SSH error: Authentication by identify file failed with error code -16 [Unable to extract public key from private key file: Wrong passphrase or invalid/unrecognized private key file format]

My passphrase was set to empty.

If i try to connect without SSH tunnel there is another error:

Error connecting to the server: SSL error: unknown protocol expected authentication request from server, but received S

I'm a complete newbie to it and I may have missed something important. So tell me if you want me to provide any other info on this matter.

EDIT 1:

If i use correct id_rsa file (without pub) then i get this same error:

Error connecting to the server: SSL error: unknown protocol expected authentication request from server, but received S

Answer 1

I use just the same (and only) id_rsa.pub file

The first issue is that this is the wrong file. See if you have a file in the same place named "id_rsa" without the ".pub" extension. That is the file you should use as the identity file.

RSA ssh keys come in two files: "id_rsa" contains the private key, while "id_rsa.pub" contains the public key. id_rsa.pub is installed onto the server that you are connecting to, while id_rsa is used by the client that is making the connection to the server.

(Key files can be named something other than "id_rsa", of course. The point is that the private key is in foobar, while the public key is in foobar.pub.)

If you don't have an id_rsa file, then you should generate a new key and keep both files this time.

The second issue is that you have the wrong port on the Properties tab. The port number on the properties tab is the port that the PG server is running on. You should set this to 5432 or whatever port your server is actually listening on. It seems you should also set the "host" on the properties tab to "localhost", but I don't know if this is required.

Error connecting to the server: SSL error: unknown protocol expected authentication request from server, but received S

What is happening now is that your tunneled PG connection is going to port 4321 on the remote host, which is the SSH server. It happens that the first thing an SSH server sends to a new client is a version string, which looks like "SSH-2.0-OpenSSH_6.9" I don't know the PG protocol, but apparently your client reads the "S" in the SSH string and immediately knows it's not connected to a PG server.