Psycopg2 access PostgreSQL database on remote host without manually opening ssh tunnel

Question

My standard procedure for accessing a PostgreSQL database on a remote server is to first create an ssh tunnel as:

ssh username1@remote.somewhere.com -L 5432:localhost:5432 -p 222

and then run my query in python from another shell as:

conn = psycopg2.connect("host=localhost" + " dbname=" +
                         conf.dbname + " user=" + conf.user + 
                         " password=" + conf.password)

cur = conn.cursor()

cur.execute(query)

This piece of python code works nicely once the tunnel is created. However, I would like psycopg2 to already open the SSH tunnel or reach "somehow" the remote database without need to redirect it on my localhost.

Is it possible to do this with psycopg2?

Is otherwise possible open the ssh tunnel in my python code?

if I use:

os.system("ssh username1@remote.somewhere.com -L 5432:localhost:5432 -p 222")

The shell will be redirected to the remote host blocking the execution of main thread.

Answer 1

For the moment I am using a solution bsed on this gist:

class SSHTunnel(object):
    """
    A context manager implementation of an ssh tunnel opened from python

    """


    def __init__(self, tunnel_command):

        assert "-fN" in tunnel_command, "need to open the tunnel with -fN"
        self._tunnel_command = tunnel_command
        self._delay = 0.1

    def create_tunnel(self):

        tunnel_cmd = self._tunnel_command
        import time, psutil, subprocess
        ssh_process = subprocess.Popen(tunnel_cmd,  universal_newlines=True,
                                                    shell=True,
                                                    stdout=subprocess.PIPE,
                                                    stderr=subprocess.STDOUT,
                                                    stdin=subprocess.PIPE)

        # Assuming that the tunnel command has "-f" and "ExitOnForwardFailure=yes", then the
        # command will return immediately so we can check the return status with a poll().

        while True:
            p = ssh_process.poll()
            if p is not None: break
            time.sleep(self._delay)


        if p == 0:
            # Unfortunately there is no direct way to get the pid of the spawned ssh process, so we'll find it
            # by finding a matching process using psutil.

            current_username = psutil.Process(os.getpid()).username
            ssh_processes = [proc for proc in psutil.get_process_list() if proc.cmdline == tunnel_cmd.split() and proc.username == current_username]

            if len(ssh_processes) == 1:
                self.ssh_tunnel = ssh_processes[0]
                return ssh_processes[0]
            else:
                raise RuntimeError, 'multiple (or zero?) tunnel ssh processes found: ' + str(ssh_processes)
        else:
            raise RuntimeError, 'Error creating tunnel: ' + str(p) + ' :: ' + str(ssh_process.stdout.readlines())


    def release(self):
        """ Get rid of the tunnel by killin the pid
        """
        self.ssh_tunnel.terminate()


    def __enter__(self):
        self.create_tunnel()
        return self


    def __exit__(self, type, value, traceback):

        self.release()


    def __del__(self):
        self.release()


def test():
    #do things that will fail if the tunnel is not opened

    print "done =========="


command = "ssh username@someserver.com -L %d:localhost:%d -p 222 -fN" % (someport, someport)

with SSHTunnel(command):
    test()

Please let me know if anybody has a better idea