CODEWITHSUNDEEP
pythondjangossh-tunnel
Charalamm
Charalamm

Reputation: 1947

django db with ssh tunnel

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.

Upvotes: 4

Views: 9154

Answers (2)

Alberto Licea
Alberto Licea

Reputation: 5

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)

Upvotes: 0

Charalamm
Charalamm

Reputation: 1947

It is pretty seamless.

Requirements: The sshtunnel package https://github.com/pahaz/sshtunnel

  1. In the django settings.py create 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()
  1. 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

Upvotes: 13

Related Questions