def _get_connection(self, sql, params, method_instance): """ Creates a connection to the database Args: sql: The original SQL query. params: The original params. method_instance: An instance of the method requesting the database connection. Returns: The results of the original method instance. """ if self.use_ssh: with open_tunnel( (self.ssh_host, self.ssh_port), ssh_username=self.ssh_user, ssh_pkey=self.ssh_key_path, remote_bind_address=(self._db_host, self.db_port), local_bind_address=(self.ssh_local_bind_address, self.ssh_local_bind_port)) as tunnel: if self._config.ssh_logging_level: tunnel.logger = create_logger( loglevel=self._config.ssh_logging_level) host = tunnel.local_bind_host port = tunnel.local_bind_port return self._database_connection_sub_method( host, port, method_instance, sql, params) else: host = self._db_host port = self._db_port return self._database_connection_sub_method( host, port, method_instance, sql, params)
def connectDB(dotenv_dict): import psycopg2 as pg # connect to db try: with SSHTunnelForwarder( (dotenv_dict["HOST"], 22), ssh_username=dotenv_dict["SSH_USER_NAME"], ssh_password=dotenv_dict["SSH_PASSWORD"], remote_bind_address=(dotenv_dict["DB_HOST"], 5432), logger=create_logger(loglevel=1), ) as server: server.start() conn = pg.connect( host=dotenv_dict["DB_HOST"], dbname=dotenv_dict["DB_NAME"], user=dotenv_dict["DB_USER_NAME"], password=dotenv_dict["DB_PASSWORD"], port=server.local_bind_port, ) c = conn.cursor() c.execute("select * from lobby_refactored.information_schema.tables") rows = c.fetchall() for r in rows: print(r) except ConnectionError: print("failed") return conn
def setUpClass(cls): super(SSHClientTest, cls).setUpClass() socket.setdefaulttimeout(sshtunnel.SSH_TIMEOUT) cls.log = logging.getLogger(sshtunnel.__name__) cls.log = sshtunnel.create_logger(logger=cls.log, loglevel='DEBUG') cls._sshtunnel_log_handler = MockLoggingHandler(level='DEBUG') cls.log.addHandler(cls._sshtunnel_log_handler) cls.sshtunnel_log_messages = cls._sshtunnel_log_handler.messages
def connect(proxyInfo): server = SSHTunnelForwarder( ssh_address_or_host=(sshIp, sshPort), ssh_username=sshUser, ssh_password=sshPassword, local_bind_address=('127.0.0.1', proxyInfo.localPort), #绑定的端口 remote_bind_address=(proxyInfo.host, proxyInfo.port), #代理远程的端口 logger=create_logger(loglevel=1) #sshtunnel打开日志输出 ) server.start()
def __init__(self, host=default_host, port=default_ssh_port, username=default_ssh_username, sshkey=default_ssh_pkey, db_ip=default_remote_db_ip, db_port=default_remote_db_port, debug_logger=True): super(DbConnect, self).__init__() self.host = host self.port = port self.username = username self.sshkey = sshkey self.db_ip = db_ip self.db_port = db_port if debug_logger: self.ssh_logger = create_logger(loglevel=1) # debug else: self.ssh_logger = create_logger( loglevel='ERROR') # only report error or fatal
def setUpClass(cls): super(SSHClientTest, cls).setUpClass() socket.setdefaulttimeout(sshtunnel.SSH_TIMEOUT) cls.log = logging.getLogger(sshtunnel.__name__) cls.log = sshtunnel.create_logger(logger=cls.log, loglevel='DEBUG') cls._sshtunnel_log_handler = MockLoggingHandler(level='DEBUG') cls.log.addHandler(cls._sshtunnel_log_handler) cls.sshtunnel_log_messages = cls._sshtunnel_log_handler.messages # set verbose format for logging _fmt = '%(asctime)s| %(levelname)-4.3s|%(threadName)10.9s/' \ '%(lineno)04d@%(module)-10.9s| %(message)s' for handler in cls.log.handlers: handler.setFormatter(logging.Formatter(_fmt))
async def request(self, transaction): nodes = cycle([self.current_node] + self.another_nodes) while True: node = nodes.__next__() ssh_tunnel = None try: logger = logging.Logger("dist-systems-logger", level=logging.CRITICAL) sshtunnel.create_logger(logger=logger, loglevel='CRITICAL') ssh_tunnel = SSHTunnelForwarder( node['host'], ssh_username=node['username'], ssh_password=node['password'], remote_bind_address=("127.0.0.1", node['port']), mute_exceptions=True, logger=logger) ssh_tunnel.start() url = f"ws://127.0.0.1:{ssh_tunnel.local_bind_port}" async with websockets.connect(url) as ws: await ws.send(json.dumps(transaction)) response = json.loads(await ws.recv()) print("|| Received:") for el in response: if el.get('action') == 'read': print("DONE", el.get('action'), el.get('var'), ": ", el.get('result')) elif el.get('action') == 'update': print("DONE", el.get('action'), el.get('var')) break except Exception: continue finally: if ssh_tunnel is not None: ssh_tunnel.close()
def _create_ssh_tunnel_to_DbLog(self): try: print("Inside tunnel creation") self.server = sshtunnel.SSHTunnelForwarder( (self.jumpBox_ip, 22), ssh_username=self.jumpBox_User, ssh_pkey=self.jumpBox_pkeyFile, remote_bind_address=(self.databases[1], int(self.databases[4])), logger=sshtunnel.create_logger(loglevel=1)) self.server.start() print("Tunnel creation success at port " + str(self.server.local_bind_port)) except Exception as e: print("Vinay : Tunnel creation failed with reason : " + str(e))
def forward_necessary_ports(cluster_id=None, private_key_path=None, profile='default'): master_private_address = str(get_master_address(cluster_id, profile)) forwarder_logger = create_logger(loglevel=40) ports = { 'drill_ui_port': 8047, 'resmgr_port': 9026, 'namenode_port': 9101, 'zookeeper_port': 2181, 'drill_user_port': 31010, 'hive_port': 10000, 'pump_port': 8080 } port_forwarders = [] for port in ports.keys(): port_number = ports[port] port_forwarders.append( SSHTunnelForwarder( master_private_address, 22, ssh_username='******', ssh_private_key=private_key_path, local_bind_address=('localhost', port_number), remote_bind_address=(master_private_address, port_number), logger=forwarder_logger ) ) try: for forwarder in port_forwarders: try: forwarder.start() except: pass print("\n\nForwarding necessary ports. Ctrl-C will terminate port forwarding.\n") print("Ports forwarded:\n==============================\n") for port in ports.keys(): print(" * "+(port.split('_port')[0]+":").ljust(20)+" 127.0.0.1:{0}".format(ports[port])) print("\n") while True: sleep(1) except KeyboardInterrupt: print("Stopping port forwarding.") for forwarder in port_forwarders: forwarder.stop()
def get_api_fame(): api_file = open("/tmp/.donotopen", "w+") with SSHTunnelForwarder( MONGO_HOST, ssh_username="******", # auto script to have fame + fame ssh_password="******", remote_bind_address=('127.0.0.1', 3306), logger=create_logger(loglevel=1)) as server: server.start() client = MongoClient( 'fame_ip', 27017) # server.local_bind_port is assigned local port db = client[MONGO_DB] col = db.users.find() for doc in col: q.update(doc) api_file.write(q[u'api_key']) server.stop() api_file.close()
def start(self): """ Connect to server (ssh as well as database). """ self.server = SSHTunnelForwarder(ssh_address_or_host=self.ssh_host, ssh_username=self.dbname, ssh_pkey='~/.ssh/id_rsa', remote_bind_address=('127.0.0.1', 3306), logger=create_logger(loglevel=0)) self.server.start() local_port = str(self.server.local_bind_port) self.engine = create_engine( '{}://{}:{}@127.0.0.1:{}/{}?charset=utf8mb4'.format( self.db_engine, self.db_user, self.db_pass, local_port, self.db_name)) self.conn = self.engine.connect()
def __init__(self): Thread.__init__(self) # call super constructor self.running = True start_point = time() self.tunnel = SSHTunnelForwarder( ssh_address_or_host=(PC.getSSHRemotename(), 22), # connect to Remote on ssh default port # TODO: integare it into the condif ssh_username=PC.getSSHUsername(), #ssh_password='', ssh_pkey=PC.getSSHkeyfilepath(), remote_bind_address=(PC.getSSHTunnelname(), 3306), # connect to mysql default port set_keepalive= 15, # send keep alive messages to held the connection open local_bind_address=('0.0.0.0', 3307), # bind ssh port on all local interfaces logger=create_logger(loglevel=logging.DEBUG)) self.tunnel.start() end_point = time() print(end_point - start_point) self.start()
def __init__(self, ssh_host: str, ssh_key_path: str, remote_host: str, remote_port: int, sender_class: callable, ssh_port: int = 22, username: str = None, debug: bool = False): self.ssh_address = (ssh_host, ssh_port) self.username = username if username is not None else getpass.getuser() self.ssh_key_path = ssh_key_path self.remote_host = remote_host self.remote_port = remote_port self.local_bind_address = '127.0.0.1' self.local_port = find_free_port() self.server = None self.sender = sender_class(server=self.local_bind_address, port=self.local_port) self.logger = None if debug: self.logger = sshtunnel.create_logger(loglevel=10) self._init_server()
def run_query(query, db_engine=PRODUCTION_ENGINE, **kwargs): remote_bind_address = tuple() local_bind_port = None if 'mysql' in db_engine: remote_bind_address = ('mysql-db-endpoint', 3306) local_bind_port = MYSQL_BIND_PORT elif 'postgresql' in db_engine: remote_bind_address = ('redshift-db-endpoint', 5439) local_bind_port = REDSHIFT_BIND_PORT # Create SSH tunnel server = None server_kwargs = { 'ssh_address_or_host': SSH_HOSTNAME, 'ssh_username': '******', 'ssh_pkey': SSH_CERTIFICATE, # path 'remote_bind_address': remote_bind_address, 'local_bind_address': ('127.0.0.1', local_bind_port), 'logger': create_logger(loglevel=logging.CRITICAL) } try: server = SSHTunnelForwarder(**server_kwargs) server.start() except HandlerSSHTunnelForwarderError: # SSH tunnel already opened in a separate process server.stop() df = pd.read_sql_query(query, db_engine, **kwargs) # Close SSH tunnel if server: server.stop() return df
def connect_db(): with SSHTunnelForwarder( ('mm-ts-next.chinaeast.cloudapp.chinacloudapi.cn', 1021), # 远端ssh server ip 和 ssh端口 logger=create_logger(loglevel=1), # sshtunnel debug log 打印输出 ssh_username="******", ssh_pkey="loadtesters_rsa-2.pem", remote_bind_address=('127.0.0.1', 3306), # 远端db server ip 和 db 端口 # local_bind_address=('127.0.0.1', 3306) # 本地连接db的端口 ) as server: print(server.local_bind_address) print(server.local_bind_port) db = pymysql.connect( host='127.0.0.1', # 此处必须是是127.0.0.1 port=server.local_bind_port, user='******', password='******', database='mm') cursor = db.cursor() cursor.execute("show tables") sql = 'select * from `User` limit 101' cursor.execute(sql) print(cursor.rowcount)
# https://sshtunnel.readthedocs.io/en/latest/ import configparser, getpass, logging, pathlib, sys import sshtunnel import toolbox as tb ini_file = 'tunnel.ini' defaults = { 'proxy_port': '22', 'proxy_user': getpass.getuser(), 'remote_host': 'localhost' } config = configparser.ConfigParser(defaults=defaults) logger = sshtunnel.create_logger(loglevel='INFO') logger.handlers[0].setFormatter(logging.Formatter('! %(message)s')) class MockTunnel: def __init__(self, local_bind_host, local_bind_port): self.local_bind_host = local_bind_host self.local_bind_port = local_bind_port def __enter__(self): # for `with tunnel:` return self def __exit__(self, *args): # for `with tunnel:` pass def tunnel(remote_host, remote_port, local_port=0): # `0` means random port if tb.is_pyinstaller():
import argparse import getpass import logging import logging.config import os import sys from pathlib import Path from time import sleep import sshtunnel LOG_DIR = Path( os.environ.get('HOME', '/Users/kincerb')).joinpath('Documents', 'log') logger = logging.getLogger(__name__) logger = sshtunnel.create_logger(logger) def main(): args = get_args() configure_logging(args.verbosity) create_tunnel(args.gateway, args.destination_address, args.destination_port, args.private_key, gateway_ssh_port=args.gateway_ssh_port, username=args.user, local_address=args.local_address, local_port=args.local_port) def create_tunnel(gateway_host, destination_address, destination_port, private_key, gateway_ssh_port=22, username=getpass.getuser(), local_address='127.0.0.1', local_port=8888): """Establish tunnel for remote port forwading Arguments:
from sshtunnel import SSHTunnelForwarder, create_logger import psycopg2 import logging logger = logging.getLogger('sshtunnel') logger.setLevel(logging.DEBUG) sh = SSHTunnelForwarder( ('10.82.152.96,eu-west-2'), ssh_username="******", ssh_private_key="~/.ssh/id_rsa", logger=create_logger(loglevel=10), local_bind_address=('', 5432), remote_bind_address=( 'int-ugc-postgres.c65kz9sr8urn.eu-west-2.rds.amazonaws.com', 5432)) sh.start() print("****SSH Tunnel Established****:" + str(tunnel)) connection = psycopg2.connect(user="******", password="******", host="127.0.0.1", port="5432", database="ugc") try: campaignId = 'u22249586' cursor = connection.cursor() cursor.execute( "select id, submission_id from ugc_schema.file where submission_id in (select id from ugc_schema.submission where campaign = %s);", (campaignId, ))
def __init__(self, *args, **kwargs): # Allow tests to enable/disable specific key types self.__allowed_keys = kwargs.pop('allowed_keys', []) self.log = kwargs.pop('log', sshtunnel.create_logger(loglevel='DEBUG')) super(NullServer, self).__init__(*args, **kwargs)