Geek asks and answers

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

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

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

and then run my request in python from another shell like:

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 great after creating the tunnel. However, I would like psycopg2 to already open the SSH tunnel or somehow reach the remote database without having to redirect it to my local host.

Is it possible to do this with psycopg2?

Otherwise, can I open an 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 the main thread.

+6
source share
5

bsed 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()

, , - .

-1

sshtunnel, :

from sshtunnel.sshtunnel import SSHTunnelForwarder
PORT=5432
with SSHTunnelForwarder((REMOTE_HOST, REMOTE_SSH_PORT),
         ssh_username=REMOTE_USERNAME,
         ssh_password=REMOTE_PASSWORD,
         remote_bind_address=('localhost', PORT),
         local_bind_address=('localhost', PORT)):
    conn = psycopg2.connect(...)
+9

ssh ​​ os.system /. -N ssh, .

+1

Clodoaldo Neto , , .

, Luca Fiaschi, . python3 psutil. , process.username process.cmdline process_iter() get_process_list().

Here is an example of a very slightly modified version of Luca Fiaschi code that works with python3 (requires psutil module). Hope this is at least basically correct!

#!/usr/bin/env python3

import psutil
import psycopg2
import subprocess
import time
import os

# Tunnel Config
SSH_HOST = "111.222.333.444"
SSH_USER = "user"
SSH_KEYFILE = "key.pem"
SSH_FOREIGN_PORT = 5432   # Port that postgres is running on the foreign server
SSH_INTERNAL_PORT = 5432  # Port we open locally that is forwarded to
                          # FOREIGN_PORT on the server.

# Postgres Config
DB_HOST = "127.0.0.1"
DB_PORT = SSH_INTERNAL_PORT
DB_PASSWORD = "password"
DB_DATABASE = "postgres"
DB_USER = "user"

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
        self.ssh_tunnel = None

    def create_tunnel(self):
        tunnel_cmd = self._tunnel_command
        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.process_iter() 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
        """
        if self.ssh_tunnel:
            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()

command = "ssh -i %s %s@%s -fNL %d:localhost:%d"\
    % (SSH_KEYFILE, SSH_USER, SSH_HOST, SSH_INTERNAL_PORT, SSH_FOREIGN_PORT)

with SSHTunnel(command):
    conn = psycopg2.connect(host = DB_HOST, password = DB_PASSWORD,
                     database = DB_DATABASE, user = DB_USER, port = DB_PORT)
    curs = conn.cursor()
    sql = "select * from table"
    curs.execute(sql)
    rows = curs.fetchall()
    print(rows)
0
source 
from time import sleep

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

while True:
    try:
        conn = psycopg2.connect(
            "host=localhost dbname={0} user={1} password={2}".format(
                conf.dbname, conf.user, conf.password
            )
        )
        break
    except psycopg2.OperationalError:
        sleep(3)
-2
source

More articles:


All Articles