Psycopg2 无需手动打开 SSH 隧道访问远程 PostgreSQL 数据库

Question

我通常访问远程服务器上的PostgreSQL数据库的步骤是，首先创建一个ssh隧道，命令是：

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

然后我会在另一个命令行窗口中用Python运行我的查询，命令是：

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

cur = conn.cursor()

cur.execute(query)

这段Python代码在隧道建立后运行得很好。不过，我希望psycopg2能够直接打开SSH隧道，或者以某种方式连接到远程数据库，而不需要在我的本地机器上进行重定向。

这样做在psycopg2中可行吗？

如果不行，我能否在Python代码中打开ssh隧道？

如果我使用：

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

那么命令行会被重定向到远程主机，这样就会阻塞主线程的执行。

Answer 1

使用sshtunnel包

我对SSH隧道不太熟悉，所以在使用mrts的回答时遇到了一些困难。也许这些补充说明能帮助到某些人。

在psycopg2.connect()中，host和port是你通过SSH隧道连接远程主机时创建的。

这是我的代码：

from sshtunnel import SSHTunnelForwarder

server = SSHTunnelForwarder((REMOTE_HOST, REMOTE_SSH_PORT),
         ssh_username=REMOTE_USERNAME,
         ssh_password=REMOTE_PASSWORD,
         remote_bind_address=('localhost', PORT),
         local_bind_address=('localhost', PORT))
server.start()

import psycopg2
conn = psycopg2.connect(
    database=DATABASE,
    user=USER,
    host=server.local_bind_host,
    port=server.local_bind_port,
    password=PWD)
cur = conn.cursor()
cur.execute("select * from yourtable limit 1;")
data = cur.fetchall()
print(data)

希望这个例子能让事情变得更清楚。

Answer 2

你也可以使用 sshtunnel，这个工具简单又好用：

from 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(...)

Answer 3

目前我正在使用一个基于这个链接的解决方案：

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

如果有人有更好的主意，请告诉我。