How to Connect To PostgreSQL Using SSHTunnelForwarder and Psycopg2

psycopg2_sshtunnel.py

import psycopg2
from sshtunnel import SSHTunnelForwarder

# For interactive work (on ipython) it's easier to work with explicit objects
# instead of contexts.

# Create an SSH tunnel
tunnel = SSHTunnelForwarder(
    ('128.199.169.188', 22),
    ssh_username='<username>',
    ssh_private_key='</path/to/private/key>',
    remote_bind_address=('localhost', 5432),
    local_bind_address=('localhost',6543), # could be any available port
)
# Start the tunnel
tunnel.start()

# Create a database connection
conn = psycopg2.connect(
    database='<database>',
    user='<db_user>',
    host=tunnel.local_bind_host,
    port=tunnel.local_bind_port,
)

# Get a database cursor
cur = conn.cursor()

# Execute SQL
cur.execute("""
SQL-Statements;
""")

# Get the result
result = cur.fetchall()
print(result)

# Close connections
conn.close()

# Stop the tunnel
tunnel.stop()

# Alternatively use contexts...
with SSHTunnelForwarder(...) as tunnel:
    with psycopg2.connect(...) as connect:
        cur = conn.cursor()
        ...
        

A similar fix as above works by setting tunnel.daemon_forward_servers = True

Thanks so much. I'd been searching for a solution to this problem for hours. A tip for people like me: make sure the port in local_bind_address is different from the one in remote_bind_address. Having them both the same (i.e. 5432) caused me problems.

Another tip is the remote_bind_address may not be localhost if you need to forward the request to a different host, e.g. in my case to an RDS address. In that case you'd want remote_bind_address=(db_host_address, 5432)