Connect and query Mongo database over SSH with private key in Python

Question

i have a code file mongo.py like below to connect n query mongo with username host password and its work, but how i can connect to ssh with ssh address username and auth method private_key ? private_key in the other file

here is my sample config with ssh:

sql_local = {'mongomdm':{'host':'xx.xxx.xxx.xx', 'user':'aaa', 'pkey':'/Users/satu/dua/tiga/config/settings/googlecloud_dev_rsa'}}

i have ready some references and should import library like this ?

from paramiko import SSHClient, AutoAddPolicy, RSAKey
from paramiko.auth_handler import AuthenticationException
from scp import SCPClient, SCPException
from io import StringIO

here is my code file mongo.py

import csv                                                                              
import pandas as pd
from pymongo import MongoClient
from pymongo.errors import ConnectionFailure
import config.config as cfg
import config.modules.common as comm
from bson.objectid import ObjectId

class mongoFunc:
def __init__(self):
    self.types = 'mongodb'
    self.host = cfg.sql_local[self.types]['host']
    self.user = cfg.sql_local[self.types]['user']
    self.password = cfg.sql_local[self.types]['password']
    self.uri = 'mongodb://{user}:{password}@{host}'.format(user = self.user, password = self.password, host = self.host)

def connection(self):
    try:
        client= MongoClient(self.uri)
        client.server_info()
        print('Connection Established')
    except ConnectionFailure as err:
        raise(err)
    return client

def get_collection(self, client, database, collection):
    self.client= client
    self.database= database
    self.collection= collection

    db= self.client[self.database]
    return db[self.collection]

def get_query(self,id_data= None,start_date=None,end_date=None,query=None):
    self.id = ObjectId(id_data)
    self.start_date = start_date
    self.end_date = end_date
    self.query = query
    
    if self.end_date:
        script= {'date':{'$gt':self.start_date,'$lte':self.end_date}}
    if self.end_date is None:
        script= {'date':{'$gt':self.start_date}}
    if self.id:
        script = {'_id':{'$gt':self.id}}
    if self.query:
        script.update(self.query)
    return script

def get_data_to_pandas(self, database, collection, query, skip, limit=None):
    self.database = database
    self.collection = collection
    self.query = query
    self.limit = limit
    self.skip = skip

    self.client = self.connection()
    self.collection = self.get_collection(self.client,self.database,self.collection)

    if limit:   
        cursor = self.collection.find(self.query).skip(self.skip).limit(self.limit)
    if not limit :
        cursor = collection.find(self.query).skip(self.skip)
    df = pd.DataFrame(list(cursor))
    return df

Edit:

def connection(self):
    try:
        print('Establishing SSH Connection')
        self.client = paramiko.SSHClient()
        self.client.set_missing_host_key_policy(paramiko.AutoAddPolicy())
        if (self.password == ''):
            private_key = paramiko.RSAKey.from_private_key_file(self.pkey)
            self.client.connect(hostname=self.host, port=self.port, username=self.user, pkey=private_key, allow_agent=False,look_for_keys=False)
            print("Connected to the server"),self.host
        else:
            self.client.connect(hostname=self.host, port=self.port, username=self.user, password=self.password, allow_agent=False,look_for_keys=False)    
            print("Connected to the server"),self.host
    except paramiko.AuthenticationException:
        print("Authentication failed, please verify your credentials")
        result_flag = False
    except paramiko.SSHException as sshException:
        print("Could not establish SSH connection: %s") % sshException
        result_flag = False
    else:
        result_flag = True
    return result_flag

def get_collection(self, client, database, collection):
    self.client= client
    self.database= database
    self.collection= collection

    db= self.client[self.database]
    return db[self.collection]`

I got an error:

line 64, in get_collection
    db= self.client[self.database]
TypeError: 'bool' object is not subscriptable

What should I do?

Martin Prikryl · Answer 1 · 2020-10-13T08:11:15.990

Unless MongoClient module supports SSH tunneling on its own (I do not know), you have to implement port forwarding explicitly.

Modifying the code from Nested SSH using Python Paramiko for database tunneling, you get a code like this:

ssh_client = paramiko.SSHClient()
# ...

ssh_client.connect(hostname=ssh_hostname, username=ssh_username, key_filename=ssh_key)

transport = ssh_client.get_transport()
dest_addr = (mongo_host, mongo_port)
local_unique_port = 4000 # any unused local port
local_host = 'localhost'
local_addr = (local_host, local_unique_port)
vmchannel = vmtransport.open_channel("direct-tcpip", dest_addr, local_addr)

self.uri = 'mongodb://{user}:{password}@{local_host}:{port}'.
    format(user=mongo_user, password=mongo_password, local_host=local_host,
           port=local_unique_port)

If the Mongo database runs on the SSH server itself, then it will typically listen on the loopback interface only. In that case mongo_host should be set to localhost.

Same question about PostgreSQL: Setup SSH tunnel with Paramiko to access PostgreSQL.

alkanschtein · Answer 2 · 2020-07-25T13:25:48.803

This worked for me. I checked the paramiko example above but did not understand and found this. I took the code from here and modified just a little bit for my needs.

def data_call(collection_name, query={}):
    from sshtunnel import SSHTunnelForwarder
    import pymongo

    MONGO_HOST = 'HOST' 
    SERVER_USER   = 'server_user'
    PRIVATE_KEY ='pem.txt'

    MONGO_USER='user_mongo'
    MONGO_PASS ='pass_mongo'

    MONGO_DB = "db"

    # define ssh tunnel
    server = SSHTunnelForwarder(
        MONGO_HOST,
        ssh_username=SERVER_USER,
        ssh_pkey=PRIVATE_KEY,
        remote_bind_address=('127.0.0.1', 27017)
    )

    # start ssh tunnel
    server.start()

    connection = pymongo.MongoClient('127.0.0.1', server.local_bind_port)
    db = connection[MONGO_DB]

    data = db[collection_name].find(query)
    return data

Mindey I. · Answer 3 · 2020-11-11T06:45:14.620

In your connection info:

sql_local = {
    'mongomdm': {
        'host':'xx.xxx.xxx.xx',
        'user':'aaa', 
        'pkey':'/Users/satu/dua/tiga/config/settings/googlecloud_dev_rsa'}}

You can pip install ssh-pymongo, and then:

from ssh_pymongo import MongoSession

session = MongoSession('xx.xxx.xxx.xx', 
    user='aaa',
    key='/Users/satu/dua/tiga/config/settings/googlecloud_dev_rsa',
    uri='mongodb://{mongo-user}:{mongo-password}@{host}:{local-port}'
)

db = session.connection['db-name']

More information on another post: How to connect remote mongodb with pymongo .

score 0 · Answer 4 · answered Nov 05 '20 at 23:13

just improving some other answer that actually helped me here

added the line db.authenticate(MONGO_USER, MONGO_PASS)

rememberings :"MAKE AUTH ON MONGO!!!!"

and converted for a pandas dataframe if its useful for anyone (for me it was)

from sshtunnel import SSHTunnelForwarder
import pymongo
import pandas as pd


def data_call(collection_name, query={}):
    MONGO_HOST = 'ip.add.res'
    SERVER_USER = 'user'
    PRIVATE_KEY = 'PATH TO/id_rsa'

    MONGO_USER = 'mongouser'
    MONGO_PASS = 'mongopass'
    MONGO_DB = "dbname"
    # define ssh tunnel
    server = SSHTunnelForwarder(
        MONGO_HOST,
        ssh_username=SERVER_USER,
        ssh_pkey=PRIVATE_KEY,
        remote_bind_address=('127.0.0.1', dbport)
    )
    # start ssh tunnel
    server.start()

    
    connection = pymongo.MongoClient('localhost', server.local_bind_port)
    db = connection[MONGO_DB]
    db.authenticate(MONGO_USER, MONGO_PASS)


    data = pd.DataFrame(list(db[collection_name].find(query))).drop(columns=['_id'])

    return data

Connect and query Mongo database over SSH with private key in Python

4 Answers4

Linked