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)
