How to connect to a Postgres DB using SSH tunnels

db_conn_via_tunnel.py

# Run this in your instance that doesn't have a direction connection to the DB

import sqlalchemy
from sshtunnel import SSHTunnelForwarder
import pandas as pd

bastion_url = "ec2-xxxxx.us-west-1.compute.amazonaws.com" # The EC2 instance from which you can connect to RDS

host_prod = "my-db-instance.us-west-1.rds.amazonaws.com" # RDS instance URL
username = "postgres" # DB username
password = "postgres" # DB password
database = "postgres" # DB name

with SSHTunnelForwarder(
	(bastion_url, 22),
	ssh_username = "ec2-user", # The EC2 instance's username
	ssh_private_key= "key.pem", # The private key used to SSH into the EC2 instance
	remote_bind_address=(host_prod, 5432)
) as tunnel:

	link = f"postgresql+psycopg2://{username}:{password}@127.0.0.1:{tunnel.local_bind_port}/{database}"
	eng = sqlalchemy.create_engine(link)
	conn = eng.connect()

	sql_query = "SELECT * FROM users WHERE age = 24" # Example query
	df = pd.read_sql_query(sql_query, conn)

	conn.close() # Excplicitly close the DB connection
	eng.dispose()