Psycopg2 accès PostgreSQL databese sur l'hôte distant sans l'ouvrir manuellement le tunnel ssh

Vous pouvez également utiliser sshtunnel, court et doux:

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

OriginalL'auteur mrts

Clodoaldo Neto code a fonctionné parfaitement pour moi mais attention il ne nettoie pas le processus par la suite.

La méthode indiquée par Luca Fiaschi fonctionne aussi pour moi. J'ai mis à jour un peu pour python3 et la mise à jour psutil module. Les changements ne s'agit que de processus.nom d'utilisateur et le processus.cmdline sont désormais les fonctions et que l'itérateur est process_iter() au lieu de get_process_list().

Voici un exemple d'une version légèrement modifiée du code de Luca Fiaschi posté qui fonctionne avec python3 (nécessite psutil module). J'espère que c'est au moins la plupart du temps 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)

OriginalL'auteur mathewguest

from time import sleep
os.system("ssh [email protected] -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)

OriginalL'auteur Clodoaldo Neto