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 [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.

Answer 1

You could also use sshtunnel, short and sweet:

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 2

With sshtunnel package

I was not familiar with SSH tunnels, so I had some difficulties to use mrts's answer. Maybe thoses precisions could help someone.

In psycopg2.connect(), host and port are the one you just created by connecting remote host by ssh tunnel.

Here is my code :

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)

I hope this example make it clearer.