Search code examples
pythonpostgresqlsshparamikossh-tunnel

Setup SSH tunnel with Paramiko to access PostgreSQL

I currently use Paramiko to access an SFTP server and connect to the PostgreSQL on the same server. I found many examples using sshtunnel to log on PostgreSQL. But I don't know how to do it with pure Paramiko.

Currently my code looks something like:

# establish SSH tunnel
self.ssh = paramiko.SSHClient()
self.ssh.set_missing_host_key_policy(paramiko.AutoAddPolicy())
self.ssh.connect(hostname=host, username=user, password=password)

# setup SFTP server
self.sftp = self.ssh.open_sftp()

# connect to datebase
self.engine = create_engine('postgres+psycopg2://{}:{}@{}:{}/{}'.format(user, password, host, port, db))

Thanks for any suggestions!

Solution

  • Note that sshtunnel is just a wrapper around Paramiko. So in general, you can use it to simplify the code, unless you have some restrictions preventing you from installing additional packages.

    For example: Connecting to PostgreSQL database through SSH tunneling in Python

    If you cannot install it, you basically can reuse its code at least.

    Obligatory warning: Do not use AutoAddPolicy - You are losing a protection against MITM attacks by doing so. For a correct solution, see Paramiko "Unknown Server".