Skip to content

Instantly share code, notes, and snippets.

@deehzee
Last active September 13, 2023 08:09
Show Gist options
  • Star 29 You must be signed in to star a gist
  • Fork 3 You must be signed in to fork a gist
  • Save deehzee/53c8708417312e5deb32c58b73dca7a5 to your computer and use it in GitHub Desktop.
Save deehzee/53c8708417312e5deb32c58b73dca7a5 to your computer and use it in GitHub Desktop.
How to Connect To PostgreSQL Using SSHTunnelForwarder and Psycopg2
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()
...
@tomdecort
Copy link

tomdecort commented Dec 20, 2019

I had to add following line, because tunnel was not closing properly (and thus script not stopping)
tunnel._server_list[0].block_on_close = False

Just hope this saves some time to others facing the same issue

@mbant
Copy link

mbant commented Feb 18, 2020

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

@arthurgailes
Copy link

arthurgailes commented Mar 4, 2020

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.

@lchapo
Copy link

lchapo commented Sep 22, 2020

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)

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment