Connecting to remote PostgreSQL database over SSH tunnel using Python

Question

I have a problem with connecting to a remote database using SSH tunnel (now I'm trying with Paramiko). Here is my code:

#!/usr/bin/env python3
import psycopg2
import paramiko
import time

#ssh = paramiko.SSHClient()
#ssh.load_system_host_keys()
#ssh.set_missing_host_key_policy(paramiko.AutoAddPolicy())
#ssh.connect('pluton.kt.agh.edu.pl', 22, username='aburban', password='pass')

t = paramiko.Transport(('pluton.kt.agh.edu.pl', 22))
t.connect(username="aburban", password='pass') 
c = paramiko.Channel(t)
conn = psycopg2.connect(database="dotest")
curs = conn.cursor()
sql = "select * from tabelka"
curs.execute(sql)
rows = curs.fetchall()
print(rows)

A problem is that the program always tries to connect to the local database. I tried with other SSH tunnels and there was the same situation. Database on remote server exists and works fine using "classical" SSH connection via terminal.

Answer 1

You can try using sshtunnel module that uses Paramiko and it's Python 3 compatible.

Hopefully it helps you... I scratched myself the head for a while too to do it within Python code and avoid SSH external tunnels, we need to thank developers that wrap complex libraries into simple code!

Will be simple, generate a tunnel to port 5432 in localhost of remote server from a local port then you use it to connect via localhost to the remote DB.

This will be a sample working code for your needs:

#!/usr/bin/env python3
import psycopg2
from sshtunnel import SSHTunnelForwarder
import time

with SSHTunnelForwarder(
         ('pluton.kt.agh.edu.pl', 22),
         ssh_password="password",
         ssh_username="aburban",
         remote_bind_address=('127.0.0.1', 5432)) as server:

    conn = psycopg2.connect(database="dotest",port=server.local_bind_port)
    curs = conn.cursor()
    sql = "select * from tabelka"
    curs.execute(sql)
    rows = curs.fetchall()
    print(rows)