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

Issue

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

ssh [email protected] -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 [email protected] -L 5432:localhost:5432 -p 222")

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

Solution

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 [email protected] -L %d:localhost:%d -p 222 -fN" % (someport, someport)

with SSHTunnel(command):
    test()

Please let me know if anybody has a better idea

Answered By - Luca Fiaschi

Answer Checked By - Terry (WPSolving Volunteer)