Connection remote Postgres server with ssh tunneling using pgAdmin4

Question

I am using pgAdmin4 version 3.1 on macOS. Today I tried to connect my remote Postgres server with ssh tunneling. When my remote server ssh port is the default (22) then there is no any problem. pgAdmin can connect to the server by creating a ssh tunnel. But if you assign different port like me pgAdmin could not create a ssh tunnel. I don't know this problem exist on Windows or not. But on macOS pgAdmin4 could not create ssh tunnel if the remote server port is not 22.

Answer 1

You can use SSH tunneling option with pgAdmin4 v3.1 if your remote server ssh port is 22. If your remote server ssh port is not 22 then you could not do this with pgAdmin.

One option creates ssh tunnel with using a terminal, then connect to the database with pgAdmin4 using the local port which you used when creating ssh tunneling.

But if you want to do this with pgAdmin4 then you should change the default server_manager.py script.

If you check the default server_manager.py code you see the below code snippet to creating ssh tunnel

def create_ssh_tunnel(self, tunnel_password):
    """
    This method is used to create ssh tunnel and update the IP Address and
    IP Address and port to localhost and the local bind port return by the
    SSHTunnelForwarder class.
    :return: True if tunnel is successfully created else error message.
    """
    # Fetch Logged in User Details.
    user = User.query.filter_by(id=current_user.id).first()
    if user is None:
        return False, gettext("Unauthorized request.")

    try:
        tunnel_password = decrypt(tunnel_password, user.password)
        # Handling of non ascii password (Python2)
        if hasattr(str, 'decode'):
            tunnel_password = \
                tunnel_password.decode('utf-8').encode('utf-8')
        # password is in bytes, for python3 we need it in string
        elif isinstance(tunnel_password, bytes):
            tunnel_password = tunnel_password.decode()

    except Exception as e:
        current_app.logger.exception(e)
        return False, "Failed to decrypt the SSH tunnel " \
                      "password.\nError: {0}".format(str(e))

    try:
        # If authentication method is 1 then it uses identity file
        # and password
        if self.tunnel_authentication == 1:
            self.tunnel_object = SSHTunnelForwarder(
                self.tunnel_host,
                ssh_username=self.tunnel_username,
             ssh_pkey=get_complete_file_path(self.tunnel_identity_file),
                ssh_private_key_password=tunnel_password,
                remote_bind_address=(self.host, self.port)
            )
        else:
            self.tunnel_object = SSHTunnelForwarder(
                self.tunnel_host,
                ssh_username=self.tunnel_username,
                ssh_password=tunnel_password,
                remote_bind_address=(self.host, self.port)
            )

        self.tunnel_object.start()
    except BaseSSHTunnelForwarderError as e:
        current_app.logger.exception(e)
        return False, "Failed to create the SSH tunnel." \
                      "\nError: {0}".format(str(e))

    # Update the port to communicate locally
    self.local_bind_port = self.tunnel_object.local_bind_port

    return True, None

As you see this method use only tunnel host address from server configuration so that whatever you set the tunnel_port property from pgAdmin4 does not matter this method always use the default ssh port 22 for creating ssh tunnel. So to solve this problem just change the method and pass the tunnel_port property to SSHTunnelForwarder obect as param. So the final code likes below code snippet

if self.tunnel_authentication == 1:
   self.tunnel_object = SSHTunnelForwarder(
     (self.tunnel_host, int(self.tunnel_port)),
      ssh_username=self.tunnel_username,
      ssh_pkey=get_complete_file_path(self.tunnel_identity_file),
      ssh_private_key_password=tunnel_password,
      remote_bind_address=(self.host, self.port)
     )
else:
   self.tunnel_object = SSHTunnelForwarder(
     (self.tunnel_host, int(self.tunnel_port)),
      ssh_username=self.tunnel_username,
      ssh_password=tunnel_password,
      remote_bind_address=(self.host, self.port)
     )

tunnel_port property is a string so that we need to cast this property to int type.

for more information about SSHTunnelForwarder

Answer 2

Try downloading pgAdmin4 v3.2, I have seen some fixes around SSH tunneling.

Ref: https://www.pgadmin.org/docs/pgadmin4/dev/release_notes_3_2.html