Python: Connect to an Azure PostgreSQL instance through SSH Tunnel

Question

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?

ti.voodoo · Accepted Answer · 2021-05-31 08:26:52Z

1

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 May 31, 2021 at 8:26

ti.voodoo

901 silver badge8 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Python: Connect to an Azure PostgreSQL instance through SSH Tunnel

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related