Is there a python native way to connect django to a database through an ssh tunnel? I have seen people using ssh port forwarding in the host machine but I would prefer a solution that can be easily containerized.
Add a comment
|
2 Answers
It is pretty seamless.
Requirements:
The sshtunnel package https://github.com/pahaz/sshtunnel
- In the django
settings.pycreate an ssh tunnel before the django DB settings block:
from sshtunnel import SSHTunnelForwarder
# Connect to a server using the ssh keys. See the sshtunnel documentation for using password authentication
ssh_tunnel = SSHTunnelForwarder(
SERVER_IP,
ssh_private_key=PATH_TO_SSH_PRIVATE_KEY,
ssh_private_key_password=SSH_PRIVATE_KEY_PASSWORD,
ssh_username=SSH_USERNAME,
remote_bind_address=('localhost', LOCAL_DB_PORT_ON_THE_SERVER),
)
ssh_tunnel.start()
- Then add the DB info block in the
settings.py. Here I am adding a default local DB and the remote DB that we connect to using the ssh tunnel
DATABASES = {
'default': {
'ENGINE': 'django.contrib.gis.db.backends.postgis',
'HOST': NORMAL_DB_HOST,
'PORT': NORMAL_DB_PORT,
'NAME': NORMAL_DB_NAME,
'USER': NORMAL_DB_USER,
'PASSWORD': NORMAL_DB_PASSWORD,
},
'shhtunnel_db': {
'ENGINE': 'django.contrib.gis.db.backends.postgis',
'HOST': 'localhost',
'PORT': ssh_tunnel.local_bind_port,
'NAME': REMOTE_DB_DB_NAME,
'USER': REMOTE_DB_USERNAME,
'PASSWORD': REMOTE_DB_PASSWORD,
},
}
That is it. Now one can make migratations to the remote db using commands like $ python manage.py migrate --database=shhtunnel_db or make calls to the db from within the python code using lines like Models.objects.all().using('shhtunnel_db')
Extra: In my case the remote db was created by someone else and I only wanted to read it. In order to avoid writing the models and deactivating the model manager I used the following django command to get the models from the database [src]:
python manage.py inspectdb
7 Comments
souravjamwal77
Could please post an example for the
default DB connection? I want to connect Django's default DB to a remote DB using SSH tunnel.
Charalamm
I guess using the ssh tunnel db configurations under the
default key would work as expectedsouravjamwal77
If you could please post an example for the same. I have the pem file, but no password is required for SSH tunnel. If I'm passing a blank password. Then, it's failing to authenticate. Our SSH tunnel doesn't require password to connect just a pem file. I was able to connect on command line using the pem file and SSH command.
David Henson
I am getting error 'django.utils.connection.ConnectionDoesNotExist: The connection 'sshtunnel_db' doesn't exist.' any ideas?
Charalamm
@DavidHenson Are you able to connect to the db with the same inputs from lets say pg4admin or a similar software?
|
Yes, it is possible using library such as sshtunnel to establish an SSH tunnel subsequently modify Django's settings.py file to make use of this connection prior to defining the DATABASES. You need to achieve this by changing the values for host and port to those assigned by the SSH tunnel binding.
Here's a script you can use to test your database connection through an SSH tunnel:
import os
import atexit
import pyodbc
from sshtunnel import SSHTunnelForwarder
ssh_tunnel = None
DB_DRIVER = ""
SSH_HOST = ""
SSH_PORT = ""
SSH_USER = ""
SSH_PASSWORD = ""
SSH_DB_NAME = ""
SSH_DB_USERNAME = ""
SSH_DB_PASSWORD = ""
SSH_DB_HOST = ""
SSH_DB_PORT = ""
try:
ssh_tunnel = SSHTunnelForwarder(
(SSH_HOST, int(SSH_PORT)),
ssh_username=SSH_USER,
ssh_password=SSH_PASSWORD,
remote_bind_address=(SSH_DB_HOST, int(SSH_DB_PORT)),
local_bind_address=("127.0.0.1", 0),
)
ssh_tunnel.start()
if ssh_tunnel.is_active:
print(f"SSH tunnel established: Local bind at {ssh_tunnel.local_bind_host}:{ssh_tunnel.local_bind_port}")
conn_str = (
f"DRIVER={DB_DRIVER};"
f"SERVER={ssh_tunnel.local_bind_host},{ssh_tunnel.local_bind_port};"
f"DATABASE={SSH_DB_NAME};"
f"UID={SSH_DB_USERNAME};"
f"PWD={SSH_DB_PASSWORD};"
)
try:
conn = pyodbc.connect(conn_str, timeout=30)
print("Connection successful!")
conn.close()
except pyodbc.Error as e:
print(f"Connection error: {e}")
else:
print("SSH tunnel could not be established.")
except Exception as e:
print(f"Error attempting to establish SSH tunnel: {e}")
def close_sshtunnel():
global ssh_tunnel
if ssh_tunnel and ssh_tunnel.is_active:
ssh_tunnel.stop()
print("SSH tunnel closed.")
atexit.register(close_sshtunnel)
