[SOLVED] Python: Connect to an Azure PostgreSQL instance through SSH Tunnel

Issue

I am trying to use Python to connect to a PostgreSQL instance, which is located on Azure through an SSH tunnel. I can connect to the database with DBeaver with no Problem. Here is the code that I am using.

from sshtunnel import SSHTunnelForwarder

server = SSHTunnelForwarder(
    ('160.**.**.**', 22),
    ssh_username="*******",
    ssh_password="*******",
    remote_bind_address=('localhost', 5432))

server.start()
print("server connected")

params = {
    'database': '*******',
    'user': '*****postgresadmin@*****dev-postgres',
    'password': '************',
    'host': '**********-postgres.postgres.database.azure.com',
    'port': server.local_bind_port
}

conn = psycopg2.connect(**params)
cur = conn.cursor()

text = "select * from table"
cur.execute(text)

However I get the following error:

conn = _connect(dsn, connection_factory=connection_factory, **kwasync) psycopg2.OperationalError: could not translate host name "**********-postgres.postgres.database.azure.com" to address: Unknown host

I also tried sqlalchemy using this with the same result. Any idea on what I am doing wrong? Do I maybe need the IP-address of the host instead of the domain?

Solution

The SSHTunnelForwarder is used if you want to do some stuff on the remote server.

Another code block is needed if you need to use remote server as a bridge to connect to another server:

import sshtunnel

with sshtunnel.open_tunnel(
    (REMOTE_SERVER_IP, 443),
    ssh_username="",
    ssh_password="*******",
    remote_bind_address=(AZURE_SERVER_HOST, 22),
    local_bind_address=('0.0.0.0', 10022)
) as tunnel:
    params = {
        'database': '*******',
        'user': '*****postgresadmin@*****dev-postgres',
        'password': '************',
        'host': '127.0.0.1',
        'port': 10022
    }

    conn = psycopg2.connect(**params)
    cur = conn.cursor()

    text = "select * from table"
    cur.execute(text)

Answered By - ti.voodoo