def on_Btn_cups_clicked(self, widget, ip, usuario, password, puerto, notebook, spinner, estado):
ssh_path = os.environ['HOME'] + '/.ssh/id_rsa'
spinner.start()
estado.set_text("Creando tunel...")
puerto = int(puerto)
try:
#Borra en el archivo cupsd.conf la autentificacion
with settings(host_string=ip, port=puerto, password=password, user=usuario):
sudo('sed -i."old" "/Require user @SYSTEM/d" /etc/cups/cupsd.conf;sed -i."old2" "/AuthType Default/d" /etc/cups/cupsd.conf;/etc/init.d/cups reload')
server = SSHTunnelForwarder((ip, puerto), ssh_username=usuario, ssh_private_key=ssh_path, remote_bind_address=('127.0.0.1', 631))
server.start()
puerto_local = str(server.local_bind_port)
scrolledwindow = Gtk.ScrolledWindow()
scrolledwindow.set_hexpand(True)
scrolledwindow.set_vexpand(True)
page = WebKit.WebView()
page.set_border_width(10)
page.open("http://127.0.0.1:" + puerto_local)
scrolledwindow.add(page)
tab_label = tablabel.TabLabel("CUPS " + ip, Gtk.Image.new_from_file("/usr/share/grx/icons/cups32.png"))
tab_label.connect("close-clicked", tablabel.on_close_clicked, notebook, page)
notebook.append_page(scrolledwindow, tab_label)
self.show_all()
except:
self.mensaje("No se ha podido ejecutar cups", "Atencion", atencion)
spinner.stop()
estado.set_text("")
class SSHTunnel(object):
class TunnelException(Exception):
pass
def __init__(self, host, username, key_file, remote_port, host_port=nat_ssh_port_forwarding):
"""
Returns tuple consisting of local port and sshtunnel SSHTunnelForwarder object.
Caller must call stop() on object when finished
"""
logger = logging.getLogger('sshtunnel')
logger.setLevel(logging.ERROR)
try:
self._server = SSHTunnelForwarder((host, host_port),
ssh_username=username, ssh_private_key=key_file,
remote_bind_address=('127.0.0.1', remote_port), logger=logger)
except sshtunnel.BaseSSHTunnelForwarderError as e:
raise self.TunnelException(e)
def connect(self):
self._server.start()
self.local_port = self._server.local_bind_port
def close(self):
self._server.stop()
def __enter__(self):
self.connect()
return self
def __exit__(self, type, value, traceback):
self.close()
def create_ssh_tunnel(self, tunnel_password):
"""
This method is used to create ssh tunnel and update the IP Address and
IP Address and port to localhost and the local bind port return by the
SSHTunnelForwarder class.
:return: True if tunnel is successfully created else error message.
"""
# Fetch Logged in User Details.
user = User.query.filter_by(id=current_user.id).first()
if user is None:
return False, gettext("Unauthorized request.")
if tunnel_password is not None and tunnel_password != '':
try:
tunnel_password = decrypt(tunnel_password, user.password)
# Handling of non ascii password (Python2)
if hasattr(str, 'decode'):
tunnel_password = \
tunnel_password.decode('utf-8').encode('utf-8')
# password is in bytes, for python3 we need it in string
elif isinstance(tunnel_password, bytes):
tunnel_password = tunnel_password.decode()
except Exception as e:
current_app.logger.exception(e)
return False, "Failed to decrypt the SSH tunnel " \
"password.\nError: {0}".format(str(e))
try:
# If authentication method is 1 then it uses identity file
# and password
if self.tunnel_authentication == 1:
self.tunnel_object = SSHTunnelForwarder(
(self.tunnel_host, int(self.tunnel_port)),
ssh_username=self.tunnel_username,
ssh_pkey=get_complete_file_path(self.tunnel_identity_file),
ssh_private_key_password=tunnel_password,
remote_bind_address=(self.host, self.port)
)
else:
self.tunnel_object = SSHTunnelForwarder(
(self.tunnel_host, int(self.tunnel_port)),
ssh_username=self.tunnel_username,
ssh_password=tunnel_password,
remote_bind_address=(self.host, self.port)
)
self.tunnel_object.start()
self.tunnel_created = True
except BaseSSHTunnelForwarderError as e:
current_app.logger.exception(e)
return False, "Failed to create the SSH tunnel." \
"\nError: {0}".format(str(e))
# Update the port to communicate locally
self.local_bind_port = self.tunnel_object.local_bind_port
return True, None
def sshtunnel():
server = SSHTunnelForwarder(
(current_app.config['SSH_HOST'],current_app.config['SSH_PORT']),
ssh_username=current_app.config['SSH_USER'],
ssh_password=current_app.config['SSH_PASSWORD'],
remote_bind_address=(current_app.config['SSH_REMOTE_HOST'], current_app.config['SSH_REMOTE_PORT'])
)
server.start()
return server.local_bind_port
class TunelSSH():
def __init__(self, ssh_address, ssh_port, ssh_username, ssh_password, remote_bind_address, remote_bind_port):
self.server = SSHTunnelForwarder(ssh_address=(ssh_address, ssh_port), ssh_username=ssh_username,
ssh_password=ssh_password, remote_bind_address=(remote_bind_address, remote_bind_port))
def Iniciar(self):
self.server.start()
return self.server.local_bind_port
def Cerrar(self):
self.server.stop()
def on_Btn_vncviewer_clicked(self, widget, ip, usuario, puerto, password, clave_remoto, spinner, estado):
spinner.start()
estado.set_text("Conectando con el equipo")
ssh_path=os.environ['HOME'] + '/.ssh/id_rsa'
try:
puerto = int(puerto)
server = SSHTunnelForwarder((ip, puerto), ssh_username=usuario,ssh_private_key=ssh_path,remote_bind_address=(ip, 5900))
server.start()
puerto_local = str(server.local_bind_port)
msg = local('echo "' + clave_remoto + '" | vncviewer -autopass -compresslevel 9 -bgr233 127.0.0.1:' + puerto_local + ' &')
#msg=local('echo "'+clave_remoto+'" | vinagre -autopass -compresslevel 9 -bgr233 127.0.0.1:'+puerto_local+' &')
except:
self.mensaje("No se ha podido ejecutar 'vncviewer' en el equipo remoto", "Atencion", atencion)
spinner.stop()
estado.set_text("")
def dal_connect():
server = SSHTunnelForwarder(
(settings.ssh_host, settings.ssh_port),
ssh_password=settings.ssh_password,
ssh_username=settings.ssh_username,
remote_bind_address=('127.0.0.1', 3306))
server.start()
uri = 'mysql://{username}:{password}@127.0.0.1:{port}/{db}'.format(
username = settings.mysql_username,
password = settings.mysql_password,
db = settings.mysql_dbname,
port = server.local_bind_port
)
db = DAL(uri, migrate = False)
return db
def create_tunnel_to_cdh_manager(self, local_bind_address='localhost', local_bind_port=7180, remote_bind_port=7180):
self._local_bind_address = local_bind_address
self._local_bind_port = local_bind_port
self.cdh_manager_tunnel = SSHTunnelForwarder(
(self._hostname, self._hostport),
ssh_username=self._username,
local_bind_address=(local_bind_address, local_bind_port),
remote_bind_address=(self.extract_cdh_manager_host(), remote_bind_port),
ssh_private_key_password=self._key_password,
ssh_private_key=self._key
)
def port_forward(self, port=PORT):
key = paramiko.RSAKey.from_private_key_file(
config.get('EC2', 'PrivateKeyFile')
)
self._forward = SSHTunnelForwarder(
ssh_address=(self.ip, 22),
ssh_username=config.get('EC2', 'User'),
ssh_private_key=key,
remote_bind_address=('127.0.0.1', PORT),
local_bind_address=('127.0.0.1', PORT)
)
self._forward.start()
def _portforward_frida_start(self):
"""Setup local port forward to enable communication with the Frida server running on the device."""
self.printer.debug('{} Setting up port forwarding on port {}'.format("[FRIDA]", Constants.FRIDA_PORT))
localhost = '127.0.0.1'
self._frida_server = SSHTunnelForwarder(
(self._ip, int(self._port)),
ssh_username=self._username,
ssh_password=self._password,
local_bind_address=(localhost, Constants.FRIDA_PORT),
remote_bind_address=(localhost, Constants.FRIDA_PORT),
)
self._frida_server.start()
def _portforward_agent_start(self):
"""Setup local port forward to enable communication with the Needle server running on the device."""
self.printer.debug('{} Setting up port forwarding on port {}'.format(Constants.AGENT_TAG, self._agent_port))
localhost = '127.0.0.1'
self._port_forward_agent = SSHTunnelForwarder(
(self._ip, int(self._port)),
ssh_username=self._username,
ssh_password=self._password,
local_bind_address=(localhost, self._agent_port),
remote_bind_address=(localhost, self._agent_port),
)
self._port_forward_agent.start()
def get_connection_params(self):
kwargs = super(DatabaseWrapper, self).get_connection_params()
host = kwargs['host']
port = kwargs['port']
config = self.settings_dict["TUNNEL_CONFIG"]
config['remote_bind_address'] = (host, port)
self.tunnel = SSHTunnelForwarder(**config)
self.tunnel.daemon_forward_servers = True
self.tunnel.daemon_transport = True
self.tunnel.start()
kwargs["host"] = '127.0.0.1'
kwargs['port'] = self.tunnel.local_bind_port
return kwargs
def __init__(self, host, username, key_file, remote_port, host_port=nat_ssh_port_forwarding):
"""
Returns tuple consisting of local port and sshtunnel SSHTunnelForwarder object.
Caller must call stop() on object when finished
"""
logger = logging.getLogger('sshtunnel')
logger.setLevel(logging.ERROR)
try:
self._server = SSHTunnelForwarder((host, host_port),
ssh_username=username, ssh_private_key=key_file,
remote_bind_address=('127.0.0.1', remote_port), logger=logger)
except sshtunnel.BaseSSHTunnelForwarderError as e:
raise self.TunnelException(e)
class DatabaseWrapper(MysqlDatabaseWrapper):
def __init__(self, *args, **kwargs):
super(DatabaseWrapper, self).__init__(*args, **kwargs)
self.tunnel = None
def get_connection_params(self):
kwargs = super(DatabaseWrapper, self).get_connection_params()
host = kwargs['host']
port = kwargs['port']
config = self.settings_dict["TUNNEL_CONFIG"]
config['remote_bind_address'] = (host, port)
self.tunnel = SSHTunnelForwarder(**config)
self.tunnel.daemon_forward_servers = True
self.tunnel.daemon_transport = True
self.tunnel.start()
kwargs["host"] = '127.0.0.1'
kwargs['port'] = self.tunnel.local_bind_port
return kwargs
def _close(self):
super(DatabaseWrapper, self)._close()
if self.tunnel is not None:
self.tunnel.stop()
def __init__(self):
ssh_tunnel = Credentials.ssh_tunnel
db_config = Credentials.vicnode_db
self._server = SSHTunnelForwarder(
((ssh_tunnel['host']), (int(ssh_tunnel['port']))),
ssh_password=ssh_tunnel['ssh_password'],
ssh_username=(ssh_tunnel['username']),
ssh_pkey=(ssh_tunnel['private_key_file']),
remote_bind_address=(db_config['host'], 5432),
allow_agent=False
)
self._server.start()
# we are about to bind to a 'local' server by means of an ssh tunnel
# ssh tunnel: which will be seen as a local server...
# so replace the loaded config host
db_config['host'] = 'localhost'
db_config['port'] = self._server.local_bind_port
self._db_connection = psycopg2.connect(**db_config)
self._db_cur = self._db_connection.cursor(
cursor_factory=psycopg2.extras.RealDictCursor)
self.test_connection()
def db_show():
with SSHTunnelForwarder(
(dbConfigs["HOST_IP"], dbConfigs["HOST_PORT"]),
ssh_host_key=None,
ssh_pkey=None,
ssh_username=dbConfigs["HOST_USER"],
ssh_password=dbConfigs["HOST_PASS"],
remote_bind_address=("localhost", dbConfigs["DB_PORT"]),
allow_agent=False) as ssh:
ssh.start()
conn = pymysql.connect(host='localhost',
port=ssh.local_bind_port,
user=dbConfigs["DB_USER"],
password=dbConfigs["DB_PASS"],
db=dbConfigs["DB_NAME"],
charset='utf8',
cursorclass=pymysql.cursors.DictCursor)
cursor = conn.cursor()
sql = ""
cursor.execute(sql)
results = cursor.fetchall()
conn.close()
return "param1:{}".format(results)
def flushTables():
with SSHTunnelForwarder(
(hostname, 22),
ssh_username=username,
ssh_password=password,
remote_bind_address=(sql_hostname, sql_port)) as tunnel:
conn = pymysql.connect(host='127.0.0.1',
user=sql_username,
passwd=sql_password,
db=sql_main_database,
port=tunnel.local_bind_port)
query = [
'''SHOW TABLES;''', '''select * from customer;''',
'''select * from payment;'''
]
for command in query:
print("=" * 50, command, "=" * 50)
data = pd.read_sql_query(command, conn)
print(data)
input("Press Enter to continue...")
conn.close()
def loadDocData(authorList, doc_id, chunk_size = 1000):
texts = [] # list of text samples
labels = [] # list of label ids
import DatabaseQuery
from sshtunnel import SSHTunnelForwarder
PORT=5432
with SSHTunnelForwarder((databaseConnectionServer, 22),
ssh_username='******',
ssh_password='******',
remote_bind_address=('localhost', 5432),
local_bind_address=('localhost', 5400)):
textToUse = DatabaseQuery.getWordDocData(5400, doc_id, documentTable = documentTable,
chunk_size = chunk_size)
labels = []
texts = []
for index, row in textToUse.iterrows():
labels.append(authorList.index(row.author_id))
texts.append(row.doc_content)
del textToUse
print('Found %s texts.' % len(texts))
return (texts, labels)
def wrapper(conn_info, *args):
if conn_info.use_ssh is True:
if conn_info.ssh_local_bind_port > 0:
conn_info.db_host = conn_info.original_db_host
conn_info.db_port = conn_info.original_db_port
else:
conn_info.original_db_host = conn_info.db_host
conn_info.original_db_port = conn_info.db_port
with SSHTunnelForwarder(
ssh_address_or_host=(conn_info.ssh_host,
conn_info.ssh_port),
ssh_username=conn_info.ssh_user,
ssh_pkey=conn_info.ssh_key,
remote_bind_address=(conn_info.db_host,
int(conn_info.db_port))) as tunnel:
conn_info.db_host = '127.0.0.1'
conn_info.db_port = tunnel.local_bind_port
conn_info.ssh_local_bind_port = tunnel.local_bind_port
return_obj = func(conn_info, *args)
return return_obj
else:
return func(conn_info, *args)
def create_tunnel(self, remote_host, remote_port, local_port=0):
"""
Creates a tunnel to the remote host
:param remote_host: Remote host to tunnel to
:type remote_host: String
:param remote_port: Remote port to tunnel to
:type remote_port: Number
:param local_port: Local port. If set to 0, random local port is selected
:type local_port: Number
"""
if local_port is 0:
local_port = self.get_available_local_port()
with SSHTunnelForwarder((self.host, self.port),
ssh_username=self.username,
remote_bind_address=(remote_host, remote_port),
local_bind_address=('0.0.0.0', local_port)):
try:
while True:
sleep(1)
except KeyboardInterrupt:
pass
def query_db(self, query, params=None):
"""
query the database
:param dict params: the params to be add to the query in the form of {param_name: value}
:param str query: The sql query in a string format
:return: Data-frame with the result of the query
"""
SSHTunnelForwarder.daemon_forward_servers = True # fix problems with python >= 3.7
with SSHTunnelForwarder(
(self._server_host, self._ssh_port),
ssh_username=self._ssh_username,
ssh_password=self._ssh_password,
remote_bind_address=(self._sqlhost, self._sqlport)
) as server:
server.start()
_con = psycopg2.connect(dbname=self._db_name,
user=self._db_username,
password=self._db_password,
host=self._sqlhost,
port=server.local_bind_port)
tr = pd.read_sql(query, _con, params=params)
return tr
def dbSetting(hostAddr, sqlStr):
#print('server adder=%s'%hostAddr)
ssh_host = hostAddr #'35.201.246.119' #SSH服务器地址
ssh_port = 22 #SSH端口
keyfile = '../assistence/lisakey' #SSH密钥
ssh_user = '******' #SSH用户名
db_host = '127.0.0.1' #数据库地址
db_name = 'live_casting' #数据库名
db_port = 3306 #数据库端口
db_user = '******' #数据库用户名
db_passwd = 'mysql' #数据库密码
with SSHTunnelForwarder(
(ssh_host, ssh_port),
ssh_pkey=keyfile,
ssh_username=ssh_user,
remote_bind_address=(db_host, db_port)
) as server:
db = pymysql.connect(
host=db_host,
port=server.local_bind_port,
user=db_user,
passwd=db_passwd,
db=db_name,
charset="utf8")
cursor = db.cursor()
try:
for i in sqlStr:
#print(i)
cursor.execute(i)
db.commit()
except Exception as err:
print("Error %s from exceute sql: %s" % (err, i))
db.rollback()
finally:
cursor.close()
db.close()
return
def query(q, fl=1):
with SSHTunnelForwarder(
(ssh_host, ssh_port),
ssh_username=ssh_user,
ssh_pkey=mypkey,
remote_bind_address=(sql_hostname, sql_port)) as tunnel:
conn = pymysql.connect(host='127.0.0.1',
user=sql_username,
passwd=sql_password,
db=sql_main_database,
port=tunnel.local_bind_port)
cur = conn.cursor()
cur.execute(q)
if (fl == 1):
arr = list(cur.fetchall())
l = []
for i in arr:
l.append(list(i))
conn.close()
return l
elif (fl == 2):
arr = list(cur.fetchall())
dic = {}
val = 1
for i in arr:
li = list(i[1:7])
a = str(val) + ".jpeg"
li.insert(0, a)
dic[i[0]] = li
val += 1
return dic
else:
arr = list(cur.fetchall())
l = ""
for i in arr:
l = i[0]
return l
def test_database(self):
"""Test data parse from remoted database."""
try:
# create ssh-tunnel for connectd
with SSHTunnelForwarder(
(SSH['hostname'], SSH['port']),
ssh_password=SSH['password'],
ssh_username=SSH['username'],
remote_bind_address=(DATABASE['host'], DATABASE['port']),
local_bind_address=('localhost', 6543)) as tunnel:
self.assertEqual(tunnel.is_active, True)
# create database connection and fetch data
with psycopg2.connect(database=DATABASE['db'],
user=DATABASE['user'],
password=DATABASE['password'],
host=tunnel.local_bind_host,
port=tunnel.local_bind_port) as conn:
curs = conn.cursor()
curs.execute('''SELECT username, email
FROM mantis_user_table
WHERE enabled IS TRUE;''')
rows = curs.fetchall()
self.assertIsNotNone(rows)
# store answer for separated test
self.__class__.data_database = sorted(rows,
key=lambda x: x[0])
except (BaseSSHTunnelForwarderError, ):
assert False, 'Invalid ssh credentials'
except (psycopg2.OperationalError):
assert False, 'Invalid database credentials'
def dbQuery(hostAddr, sqlStr):
sshtunnel.SSH_TIMEOUT = 15
ssh_host = hostAddr #'35.201.246.119' #SSH服务器地址
ssh_port = 22 #SSH端口
keyfile = './lisakey' #SSH密钥
ssh_user = '******' #SSH用户名
db_host = '127.0.0.1' #数据库地址
db_name = 'live_casting' #数据库名
db_port = 3306 #数据库端口
db_user = '******' #数据库用户名
db_passwd = 'mysql' #数据库密码
with SSHTunnelForwarder((ssh_host, ssh_port),
ssh_pkey=keyfile,
ssh_username=ssh_user,
remote_bind_address=(db_host, db_port)) as server:
db = pymysql.connect(host=db_host,
port=server.local_bind_port,
user=db_user,
passwd=db_passwd,
db=db_name,
charset="utf8")
cursor = db.cursor()
collect = []
try:
#print(sqlStr)
cursor.execute(sqlStr)
data = cursor.fetchall()
for result in data:
collect.append(result)
#print(collect)
except Exception as err:
print("Error %s from exceute sql: %s" % (err, sqlStr))
finally:
cursor.close()
db.close()
return collect
def start_tunnels(self, ssh_port: int = 22):
"""Starts all tunnels
Arguments:
ssh_port {int} -- SSH Port (default: 22)
Returns:
success {bool} -- Returns true if all tunnels started successfully
"""
self.tunnels = []
for idx, tunnel_info in enumerate(self.tunnel_info):
# If we're not the first element, set the bastion to the local port of the previous tunnel
if idx > 0:
tunnel_info['ssh_address_or_host'] = (
'localhost', self.tunnels[-1].local_bind_port)
# If we are the last element, the target is the real target
if idx == len(self.tunnel_info) - 1:
target = (self.target_ip, self.target_port)
# Otherwise the target is the next bastion
else:
target = (self.tunnel_info[idx + 1]['ssh_address_or_host'],
ssh_port)
logger.debug(
"Attempting to start tunnel to target '%s' with info '%s'",
target, tunnel_info)
try:
self.tunnels.append(
SSHTunnelForwarder(remote_bind_address=target,
**tunnel_info))
self.tunnels[idx].start()
except Exception as ex:
logger.error("Unable to start tunnel to '%s', exception: %s",
target, ex)
return False
return True
def remote_query(sql_query, typeOp="select"):
rows = ""
config_dict = _read_config()
if typeOp == "select":
with SSHTunnelForwarder(
(config_dict['ssh_host'], int(config_dict['ssh_port'])),
ssh_username=config_dict['ssh_user'],
ssh_password=config_dict['mypkey'],
remote_bind_address=(config_dict['sql_hostname'],
int(config_dict['sql_port']))) as tunnel:
connection_object = pymysql.connect(
host='127.0.0.1',
user=config_dict['sql_username'],
passwd=config_dict['sql_password'],
db=config_dict['sql_main_database'],
port=tunnel.local_bind_port,
cursorclass=pymysql.cursors.DictCursor)
try:
cursor_object = connection_object.cursor()
cursor_object.execute(sql_query)
# connection_object.commit()
rows = cursor_object.fetchall()
except Exception as e:
print(f"Exception occured: {e}")
finally:
cursor_object.close()
connection_object.close()
return rows
else:
return rows
def return_tweets(tar=False, low=False):
tweetvec = []
tweets = []
targets = []
rec = []
nonrec = []
with SSHTunnelForwarder('ucla.seanbeaton.com',
ssh_port=7822,
ssh_username="******",
local_bind_address=settings.local_address,
remote_bind_address=('127.0.0.1', 3306)) as server:
cnx = pymysql.connect(cursorclass=pymysql.cursors.DictCursor,
**settings.db_config)
with cnx.cursor() as cur:
sql = "SELECT * FROM tweets ORDER BY RAND()"
cur.execute(sql)
tweets = cur.fetchall()
tweetids = []
for tweet in tweets:
tweetids.append(int(tweet['tweetid']))
tweetvec.append(clean(tweet['tweettext'], low))
if tweet['category'] == 'r' or tweet['category'] == 's':
targets.append(1)
rec.append(clean(tweet['tweettext'], low))
else:
targets.append(0)
nonrec.append(clean(tweet['tweettext'], low))
cnx.close()
if tar:
return (tweetvec, targets)
else:
return (rec, nonrec)
def login_server(self, address, address_port, username, remote_address, remote_address_port, **kw):
"""
:param address: 跳扳机的IP地址
:param address_port: 跳扳机的端口号
:param username: 跳板机的SSH登录账号
:param remote_address: 远程服务器的IP地址
:param remote_address_port: 远程服务器的端口号
:param kw: 参数ssh_pkey(客户端私钥路径)参数key_password(客户端开机密码) 参数ssh_password(客户端密码)
:return: 返回服务器操作指针
"""
try:
# 通过密钥调用connect函数建立Linux连接
if "ssh_pkey" in kw.keys():
server = SSHTunnelForwarder(
# 跳板机ip与ssh登录端口号
ssh_address_or_host=(address, address_port),
# 跳板机登录账号
ssh_username=username,
# PC(客户端)的私钥路径
ssh_pkey=paramiko.RSAKey.from_private_key_file(kw["ssh_pkey"]),
# PC(客户端)的密码
ssh_private_key_password=kw["key_password"],
# 远程MYSQL服务器的绑定的IP和端口号
remote_bind_address=(remote_address, remote_address_port)
)
# 通过密码调用connect函数建立Linux连接
elif "password" in kw.keys():
server = SSHTunnelForwarder(
ssh_address_or_host=(address, address_port),
ssh_username=username,
ssh_password=kw["password"],
remote_bind_address=(remote_address, remote_address_port),
# 绑定本地地址(默认127.0.0.1和端口号)及与跳板机相通的端口
local_bind_address=('127.0.0.1', 22),
)
else:
print("登录信息与方法错误，抛出异常 - ValueError")
raise ValueError
server.start()
print("账号【%s】登录【%s】服务器启动服务器【%s】的MySQL服务" % (username, address, remote_address))
return server
except Exception as e:
print("发生未知错误: %s" % e)
raise
def connection_from_settings(
ssh_username,
ssh_pkey,
local_port,
remote_host,
private_dburl,
private_port=5432,
remote_port=22):
pk = paramiko.RSAKey.from_private_key(
file_obj=io.StringIO(six.text_type(ssh_pkey))
)
private_host = host_from_dburl(private_dburl)
connection_url = local_dburl(private_dburl, local_port)
with SSHTunnelForwarder(
(remote_host, remote_port),
ssh_username=ssh_username,
ssh_pkey=pk,
remote_bind_address=(private_host, private_port),
local_bind_address=('0.0.0.0', local_port)
):
yield connection_url
def ssh_Mysql(FSRAR_ID):
sql_hostname = 'cashsrv'
sql_username = '******'
sql_password = '******'
sql_main_database = 'documentsAll'
sql_port = 3306
sql_ip = 'cashsrv'
sql_host='127.0.0.1'
ssh_host = 'cashsrv'
ssh_user = '******'
ssh_port = 22
ssh_password1='Ma4u-Pik4u'
with SSHTunnelForwarder(
(ssh_host, ssh_port),
ssh_username = sql_username,
ssh_password = ssh_password1,
remote_bind_address = (sql_host, sql_port)) as tunnel:
conn = pymysql.connect(host=sql_host, user=sql_username,
passwd=sql_password, db=sql_main_database,
port=tunnel.local_bind_port)
## query1 = '''SET @row_number = 0;SELECT (@row_number:=@row_number + 1) AS num, cashcode, excisemark, alcocode from goodsitem WHERE cashcode LIKE "''' + FSRAR_ID + '''1''' + '''" INTO OUTFILE "/var/lib/mysql-files/''' + FSRAR_ID + '''.csv" FIELDS ENCLOSED BY '"' TERMINATED BY ';' ESCAPED BY '"' LINES TERMINATED BY "\\r\\n";'''
try:
query1 = '''SELECT @i:=@i+1 num, cashcode, excisemark, alcocode from goodsitem, (SELECT @i:=0) X WHERE cashcode LIKE "''' + FSRAR_ID + '''1''' + '''" INTO OUTFILE "/var/lib/mysql-files/''' + FSRAR_ID + '''.csv" FIELDS ENCLOSED BY '"' TERMINATED BY ';' ESCAPED BY '"' LINES TERMINATED BY "\\r\\n";'''
print ("Формирую список марок")
data1 = pd.read_sql_query(query1, conn)
except:
print ("Список сформирован!")
conn.close()
def connect_and_excute(host, port, ssh_user, ssh_password, db_user,
db_password, query):
data = []
with SSHTunnelForwarder(ssh_address_or_host=(host, 22),
ssh_username=ssh_user,
ssh_password=ssh_password,
remote_bind_address=('127.0.0.1',
int(port))) as server:
db = 'bookroll'
myConfig = pymysql.connect(user=db_user,
passwd=db_password,
host="127.0.0.1",
db=db,
port=server.local_bind_port)
cursor = myConfig.cursor()
# for q in query:
# cursor.execute(q)
cursor.execute(query)
data = list(cursor.fetchall())
cursor.close()
# return all fetched data
return data
def syncProduct(self, mage2Setting, attributeSet, products):
with SSHTunnelForwarder(
(mage2Setting['SSHSERVER'], mage2Setting['SSHSERVERPORT']),
ssh_username=mage2Setting['SSHUSERNAME'],
ssh_pkey=mage2Setting.get('SSHPKEY'),
ssh_password=mage2Setting.get('SSHPASSWORD'),
remote_bind_address=(mage2Setting['REMOTEBINDSERVER'],
mage2Setting['REMOTEBINDSERVERPORT']),
local_bind_address=(
mage2Setting['LOCALBINDSERVER'],
mage2Setting['LOCALBINDSERVERPORT'])) as server:
sleep(2)
for product in products:
sku = product["sku"]
typeId = product["data"].pop("type_id", "simple")
storeId = product["data"].pop("store_id", "0")
product["data"] = dict((k, v) for k, v in dict(
(k, product["data"].get(v.get("key")) if product["data"].
get(v.get("key")) is not None else v.get("default"))
for k, v in txmap.items()).items() if v is not None)
try:
product['product_id'] = self.mage2Connector.syncProduct(
sku, attributeSet, product["data"], typeId, storeId)
product['sync_status'] = 'S'
except Exception:
product['sync_status'] = 'F'
product['log'] = traceback.format_exc()
logger.info(
json.dumps(product,
indent=4,
cls=JSONEncoder,
ensure_ascii=False))
del self.mage2Connector
server.stop()
server.close()
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)
def download_table_as_excel(table: str, filter_time: str = None):
"""
Downloads the entire data from the database as an excel file
[Note]: If timestamp is not included it fetches all the records from the table
"""
if filter_time == None:
q = "select * from " + table + " "
else:
q = "select * from " + table + " where Timestamp = '" + filter_time + "'"
with SSHTunnelForwarder((host, 24226),
ssh_username=ssh_username,
ssh_pkey=ssh_private_key,
remote_bind_address=(localhost, 1521),
local_bind_address=(localhost, 1563)) as server:
dsn_tns = cx_Oracle.makedsn(localhost, 1563, service_name=database)
conn = cx_Oracle.connect(user=user, password=password, dsn=dsn_tns)
c = conn.cursor()
print(q)
try:
# c.execute(q)
# res = c.fetchall()
# c.close()
data = pd.read_sql(q, conn)
print(data.head())
data.to_excel(table + '.xlsx')
conn.close()
except:
data = pd.read_sql(q, conn)
print(data.head())
data.to_excel(table + '.xlsx')
conn.close()
#return data.to_excel(table + '.xlsx')
return FileResponse(EXCEL_PATH + table + '.xlsx',
media_type='application/octet-stream')
def get_data_db():
"""
连接数据库，获取初始数据集
:return: data_list[]
"""
global results
with SSHTunnelForwarder(
('54.223.128.196', 11690), # 指定ssh登录的跳转机的address
ssh_username="******", # 跳转机的用户
ssh_pkey="/Users/letote/.ssh/id_rsa",
ssh_private_key_password="******",
remote_bind_address=(
'staging-mysql.czqjl6kiyvxa.rds.cn-north-1.amazonaws.com.cn', 3306)) as server: # A机器的配置
db = pymysql.connect(host='127.0.0.1', # 此处必须是是127.0.0.1
port=server.local_bind_port,
user='******',
passwd='Lt123456',
db='letote_staging')
data_list = []
with db:
cursor = db.cursor()
for sql_name, sql in config.sql_dict.items():
# print(sql)
try:
# 执行 SQL 查询
cursor.execute(sql)
# 获取所有记录列
results = cursor.fetchall()
data_df = pd.DataFrame(list(results),
columns=['id', 'iid', 'image', 'description', 'title', 'FFCGO', 'name',
'url'])
except Exception as e:
print(e)
return data_df
def create_aurora_db_connection(dsn):
#pkeyfilepath = '/tmp/glue_key.pem'
#mypkey = paramiko.RSAKey.from_private_key_file(pkeyfilepath)
#Private key pulled from odin
material = ""
pkey = helpers.get_odin_material(material, 'PrivateKey')
pkey = base64.encodestring(pkey).strip()
pkey_tmpl = '-----BEGIN PRIVATE KEY-----\n%s\n-----END PRIVATE KEY-----'
pkey = pkey_tmpl % (pkey, )
mypkey = paramiko.RSAKey.from_private_key(mypkey)
# db properties
dbname = dsn['url']['name']
db_host = dsn['url']['host']
db_port = dsn['url']['port']
# ssh properties
ssh_user = '******'
ssh_port = 22
ssh_host = ''
#db user name and password
db_user, db_password = get_dsn_credential(dsn)
with SSHTunnelForwarder((ssh_host, ssh_port),
ssh_username=ssh_user,
ssh_pkey=mypkey,
remote_bind_address=(db_host, db_port)) as tunnel:
conn = pymysql.connect(host='127.0.0.1',
user=db_user,
passwd=db_password,
db=dbname,
port=tunnel.local_bind_port)
return conn
def insert_to_tables(self, table_name: str, data: dict):
# TODO:亮点是对需要单个写入的数据，进行批量写入
u'''插入数据，在对拿到数据做处理后
:param table_name: 数据库中表名
:param data: 字典型数据
:return: 无返回，需要try/except/finally关闭数据库否则会一直运行
'''
with SSHTunnelForwarder(
("192.168.29.37", 22),
ssh_username='******',
ssh_password='******',
remote_bind_address=('0.0.0.0', 3306)) as server:
try:
connect = mc.connect(host="127.0.0.1",
port=server.local_bind_port,
user=self.user,
passwd=self.passwd,
database=self.database)
cursor = connect.cursor()
table_header = ",".join(data['header'])
for contents in data['data']:
for content in contents:
sql = "INSERT INTO " + table_name + "(" + table_header + ") VALUES (" + str(
"%s," * len(data['header'])).strip(",") + ")"
cursor.execute(
sql,
tuple([
content[i]
for i in range(0, len(data['header']))
]))
connect.commit()
print("The database was commited.")
except:
connect.rollback()
finally:
connect.close()
def mysql(direction):
import pymysql
import pandas as pd
from sshtunnel import SSHTunnelForwarder
from os.path import expanduser
sql_hostname = 'localhost'
sql_username = '******'
sql_password = ''
sql_main_database = 'perl'
sql_port = 3306
ssh_host = '10.36.65.1'
ssh_user = '******'
ssh_password = '******'
ssh_port = 22
sql_ip = '127.0.0.1'
with SSHTunnelForwarder(
(ssh_host, ssh_port),
ssh_username=ssh_user,
ssh_password=ssh_password,
remote_bind_address=(sql_hostname, sql_port)) as tunnel:
conn = pymysql.connect(host='127.0.0.1',
user=sql_username,
passwd=sql_password,
db=sql_main_database,
port=tunnel.local_bind_port)
query = '''SELECT VERSION();'''
cursor = conn.cursor()
cursor.execute("SELECT * FROM traffic WHERE " + direction +
"_port = '443';")
arr = np.array(cursor.fetchall())
cursor.close()
data = pd.read_sql_query(query, conn)
conn.close()
return arr
def dbconn_database(sql_code, **kw):
'''
连接MySQL数据库，并且带ssh的，另外可以传入相应的参数
'''
try:
with SSHTunnelForwarder(
('ssh_host', 22312), # B机器的配置
ssh_password='******',
ssh_username='******',
remote_bind_address=('remote_bind_address',
3306)) as server: # A机器的配置
database = pymysql.connect(
host='127.0.0.1', # 此处必须是是127.0.0.1
port=server.local_bind_port,
user='******',
passwd='passwd',
db='db',
charset='utf8')
cursor = database.cursor()
cursor.execute(sql_code.format(**kw))
data = cursor.fetchall()
return data
except Exception as e:
print(e)
def query(use_ssh,
q,
db_host,
db_user,
db_password,
db_port,
db,
ssh_username,
ssh_password,
charset='utf8mb4'):
if use_ssh:
with SSHTunnelForwarder(ssh_address_or_host=(db_host, 22),
ssh_password=ssh_password,
ssh_username=ssh_username,
remote_bind_address=('127.0.0.1',
db_port)) as server:
conn = sql.connect(host='127.0.0.1',
port=server.local_bind_port,
user=db_user,
passwd=db_password,
db=db,
charset=charset)
response = pd.read_sql_query(q, conn)
conn.close()
return response
else:
conn = sql.connect(host=db_host,
port=db_port,
user=db_user,
passwd=db_password,
db=db,
charset=charset)
response = pd.read_sql_query(q, conn)
conn.close()
return response
def mongoConnection():
MONGO_HOST = "13.58.236.43"
MONGO_DB = "Quant_database"
MONGO_USER = "******"
MONGO_PASS = "******"
server = SSHTunnelForwarder(
MONGO_HOST,
ssh_username='******',
ssh_password='******',
remote_bind_address=('127.0.0.1', 27017)
)
server.start()
client = pymongo.MongoClient('127.0.0.1', server.local_bind_port) # server.local_bind_port is assigned local port
db = client[MONGO_DB]
pprint.pprint(db.collection_names())
server.stop()
def get_unidade(pk):
server = None
if dev:
server = SSHTunnelForwarder('18.229.203.58',
ssh_username='******',
ssh_pkey='./cnjinova.pem',
remote_bind_address=('10.0.22.39', 27017),
local_bind_address=('0.0.0.0', 27018))
server.start()
uri = "mongodb://%s:%s@%s" % (quote_plus(user), quote_plus(password), host)
client = MongoClient(uri)
db = client.cnjinova
unidade = db.unidades.find_one({'id': str(pk)})
client.close()
if dev:
server.stop()
return unidade
def remote_kernel():
# push code to colab and run the colab start and stop
try:
url = input("Enter the url generated in colab: ")
hostname, port = url.split(":")
port = int(port)
key = input("Enter secret key: ")
passwd = hashlib.sha1(key.encode("utf-8")).hexdigest()[:10]
except Exception as e:
print("Error: " + str(e))
exit(1)
try:
spinner = Halo(text="Connecting", spinner="dots")
spinner.start()
with Connection(host=hostname,
port=port,
user="******",
connect_kwargs={"password": passwd}) as c:
server = SSHTunnelForwarder((hostname, port),
ssh_username="******",
ssh_password=passwd,
remote_bind_address=('0.0.0.0', 9000),
local_bind_address=('0.0.0.0', 8888))
server.start()
spinner.succeed("Connected")
sudopass = Responder(
pattern=r"\[sudo\] password for root:",
response=f"{passwd}\n",
)
print(
f"Open {bcolors.WARNING}http://localhost:8888/?token={passwd}{bcolors.ENDC} to connect to Jupyter. Press CTRL+C twice to kill."
)
c.run(
f"LC_ALL=en_US.utf8 python3 -m jupyter notebook --NotebookApp.token='{passwd}' --ip=0.0.0.0 --port=8888 --no-browser",
pty=True,
watchers=[sudopass],
)
except Exception as e:
spinner.fail("Something went wrong when running.")
print(str(e))
exit(1)
server.stop()
def __enter__(self):
try:
sqlserver = SSHTunnelForwarder(ssh_address_or_host=(self.ip,
self.port),
ssh_username="******",
ssh_pkey=mypkey,
remote_bind_address=('127.0.0.1',
3306))
sqlserver.start()
self.con = pymysql.connect(host="127.0.0.1",
user=self.user,
passwd=self.password,
db=self.db,
charset='utf8mb4',
port=sqlserver.local_bind_port)
'''
把通过ssh连接数据库的端口给inception,连接本地数据库密码写死的
ALTER TABLE `core_databaselist` ADD COLUMN `sshport` int(11) NULL AFTER `after`;
'''
insert_sshport = pymysql.connect(host='127.0.0.1',
user='******',
passwd='bbotte',
db='yearning',
charset='utf8mb4',
port=3306,
connect_timeout=1)
sshportcursor = insert_sshport.cursor()
sshportsql = "update yearning.core_databaselist set sshport={} WHERE ip='{}'".format(
sqlserver.local_bind_port, self.ip)
sshportcursor.execute(sshportsql)
insert_sshport.commit()
insert_sshport.close()
except:
sqlserver.stop()
self.con = pymysql.connect(host=self.ip,
user=self.user,
passwd=self.password,
db=self.db,
charset='utf8mb4',
port=self.port)
finally:
return self
GPIO.setup(S1_ABE,GPIO.IN,pull_up_down=GPIO.PUD_DOWN) #S_ABERTO
GPIO.setup(S1_INF,GPIO.IN,pull_up_down=GPIO.PUD_DOWN) #S_INFRA
GPIO.setup(S1_LAC1,GPIO.IN,pull_up_down=GPIO.PUD_DOWN) #S_LACO1
GPIO.setup(S1_LAC2,GPIO.IN,pull_up_down=GPIO.PUD_DOWN) #S_LACO2
GPIO.setup(S2_FEC,GPIO.IN, pull_up_down=GPIO.PUD_DOWN) #S_FECHADO
GPIO.setup(S2_ABE,GPIO.IN,pull_up_down=GPIO.PUD_DOWN) #S_ABERTO
GPIO.setup(S2_INF,GPIO.IN,pull_up_down=GPIO.PUD_DOWN) #S_INFRA
GPIO.setup(S2_LAC1,GPIO.IN,pull_up_down=GPIO.PUD_DOWN) #S_LACO1
GPIO.setup(2,GPIO.IN) #RESERVA
#GPIO.setup(11,GPIO.IN) #RESERVA
#GPIO.setup(7,GPIO.IN) #RESERVA
#**********************************
server = SSHTunnelForwarder(
("192.168.15.6",22),
ssh_username="******",
ssh_password="******",
remote_bind_address=("localhost",3306))
server.start()
print server.local_bind_port
#server.stop()
db=MySQLdb.connect(host="192.168.15.7",
port=server.local_bind_port,
user="******",
passwd="toor",
db="wordpress")
cur=db.cursor()
#testar conexao com banco de dados
def dba():
try:
cur.execute("SELECT VERSION()")
def getRemoteDB(self):
# TODO: This needs to add new rows since the last update, rather than replace everything.
'''
# Get the local database last record timestamp
conn = pymysql.connect(host='127.0.0.1', port=3306, user=self.config['localusername'], passwd=self.config['localpassword'], db=self.config['localdbname'])
cur = conn.cursor()
cur.execute('SELECT Time_uploaded_to_server FROM remotedata ORDER BY Time_uploaded_to_server ASC;')
lts = cur.fetchone()
lastTimestamp = lts[0]
cur.close()
conn.close()
'''
# The database query
sql = """select enc.dateTime, enc.pushedToServerDateTime, enc.howFeeling, enc.takenMedsToday+0,
MAX(IF(Observation.question_id = "20140544-1bee-4d02-b764-d80102437adc", Observation.valueNumeric, NULL)) AS Nose,
MAX(IF(Observation.question_id = "22d7940f-eb30-42cd-b511-d0d65b89eec6", Observation.valueNumeric, NULL)) AS Eyes,
MAX(IF(Observation.question_id = "2cd9490f-d8ca-4f14-948a-1adcdf105fd0", Observation.valueNumeric, NULL)) AS Breathing,
demo.birthYear, demo.gender, demo.allergiesDivulged+0, demo.hayFever+0, demo.asthma+0, demo.otherAllergy+0,
demo.unknownAllergy+0, loc.latitude, loc.longitude, loc.accuracy, loc.whenObtained
from Encounter as enc inner join Observation on Observation.encounter_id = enc.id
inner join Question on Observation.question_id = Question.id join Demographics as demo on demo.id = enc.demographics_id
join LocationInfo as loc on loc.id = enc.locationInfo_id
group by enc.id;"""
# Open SSH tunnel
server = SSHTunnelForwarder(
(self.config['remotehostname'], 1522),
ssh_username=self.config['remoteusername'],
ssh_password=self.config['remotepassword'],
remote_bind_address=('127.0.0.1', 3306)
)
# Select data from remote database
server.start()
#print(server.local_bind_port)
#print(server.tunnel_is_up)
#print(server.local_is_up(('127.0.0.1', 3306)))
if server.is_alive:
print('Connection up...executing sql...')
#print(os.system('mysql -h 127.0.0.1 --port='+str(server.local_bind_port)+' -u'+self.config['remoteusername']+' -p'))
try:
conn = pymysql.connect(host='127.0.0.1', port=server.local_bind_port, user=self.config['remoteusername'], passwd=self.config['remotedbpassword'], db=self.config['remotedbname'])
cur = conn.cursor()
cur.execute(sql)
cur.close()
conn.close()
except pymysql.err.OperationalError:
print('MySQL failed...exiting.')
server.stop()
sys.exit(0)
# Close the ssh tunnel
server.stop()
# Update local database
lconn = pymysql.connect(host='127.0.0.1', port=3306, user=self.config['localusername'], passwd=self.config['localpassword'], db=self.config['localdbname'], autocommit=True)
lcur = lconn.cursor()
lcur.execute('TRUNCATE TABLE remotedata;')
rowcount = 0
for row in cur:
#print(row[0])
sql = """INSERT INTO remotedata (id, Time_answered_on_phone, Time_uploaded_to_server, How_feeling, Taken_meds_today, Nose, Eyes, Breathing, Year_of_Birth, Gender, Optional_data_shared, hay_fever, asthma, other_allergy, unknown, latitude, longitude, accuracy, time_of_location_fix) VALUES ('', '{}', '{}', '{}', '{}', '{}', '{}', '{}', '{}', '{}', '{}', '{}', '{}', '{}', '{}', '{}', '{}', '{}', '{}');
""".format(row[0], row[1], row[2], row[3], row[4], row[5], row[6], row[7], row[8], row[9], row[10], row[11], row[12], row[13], row[14], row[15], row[16], row[17])
# Update the remotedata table
lcur.execute(sql)
# Update the row count
rowcount = rowcount+1
print('Rows processed: ', rowcount)
# Done
lcur.close()
lconn.close()
# DB sync complete, update the postcodes
self.addPostcodesToDB()
def DatabaseReachPasswordTimeout(self,p_database_index):
if not self.v_databases[p_database_index]['prompt_password']:
return { 'timeout': False, 'message': ''}
else:
#Create tunnel if enabled
if self.v_databases[p_database_index]['tunnel']['enabled']:
v_create_tunnel = False
if self.v_databases[p_database_index]['tunnel_object'] != None:
try:
result = 0
v_tunnel_object = tunnels[self.v_databases[p_database_index]['database'].v_conn_id]
if not v_tunnel_object.is_active:
v_tunnel_object.stop()
v_create_tunnel = True
except Exception as exc:
v_create_tunnel = True
None
if self.v_databases[p_database_index]['tunnel_object'] == None or v_create_tunnel:
try:
if self.v_databases[p_database_index]['tunnel']['key'].strip() != '':
v_file_name = '{0}'.format(str(time.time())).replace('.','_')
v_full_file_name = os.path.join(settings.TEMP_DIR, v_file_name)
with open(v_full_file_name,'w') as f:
f.write(self.v_databases[p_database_index]['tunnel']['key'])
server = SSHTunnelForwarder(
(self.v_databases[p_database_index]['tunnel']['server'], int(self.v_databases[p_database_index]['tunnel']['port'])),
ssh_username=self.v_databases[p_database_index]['tunnel']['user'],
ssh_private_key_password=self.v_databases[p_database_index]['tunnel']['password'],
ssh_pkey = v_full_file_name,
remote_bind_address=(self.v_databases[p_database_index]['database'].v_active_server, int(self.v_databases[p_database_index]['database'].v_active_port)),
logger=logger
)
else:
server = SSHTunnelForwarder(
(self.v_databases[p_database_index]['tunnel']['server'], int(self.v_databases[p_database_index]['tunnel']['port'])),
ssh_username=self.v_databases[p_database_index]['tunnel']['user'],
ssh_password=self.v_databases[p_database_index]['tunnel']['password'],
remote_bind_address=(self.v_databases[p_database_index]['database'].v_active_server, int(self.v_databases[p_database_index]['database'].v_active_port)),
logger=logger
)
server.set_keepalive = 120
server.start()
s = SessionStore(session_key=self.v_user_key)
tunnels[self.v_databases[p_database_index]['database'].v_conn_id] = server
self.v_databases[p_database_index]['tunnel_object'] = str(server.local_bind_port)
self.v_databases[p_database_index]['database'].v_connection.v_host = '127.0.0.1'
self.v_databases[p_database_index]['database'].v_connection.v_port = server.local_bind_port
#GO OVER ALL TABS CONNECTION OBJECTS AND UPDATE HOST AND PORT FOR THIS CONN_ID
try:
for k in list(self.v_tab_connections.keys()):
if self.v_tab_connections[k].v_conn_id == p_database_index:
self.v_tab_connections[k].v_connection.v_host = '127.0.0.1'
self.v_tab_connections[k].v_connection.v_port = server.local_bind_port
except Exception:
None
s['omnidb_session'] = self
s.save()
except Exception as exc:
return { 'timeout': True, 'message': str(exc)}
#Reached timeout, must request password
if not self.v_databases[p_database_index]['prompt_timeout'] or datetime.now() > self.v_databases[p_database_index]['prompt_timeout'] + timedelta(0,custom_settings.PWD_TIMEOUT_TOTAL):
#Try passwordless connection
self.v_databases[p_database_index]['database'].v_connection.v_password = ''
v_test = self.v_databases[p_database_index]['database'].TestConnection()
if v_test=='Connection successful.':
s = SessionStore(session_key=self.v_user_key)
s['omnidb_session'].v_databases[p_database_index]['prompt_timeout'] = datetime.now()
s['omnidb_session'].v_databases[p_database_index]['database'].v_connection.v_password = ''
s.save()
return { 'timeout': False, 'message': ''}
else:
return { 'timeout': True, 'message': v_test}
#Reached half way to timeout, update prompt_timeout
if datetime.now() > self.v_databases[p_database_index]['prompt_timeout'] + timedelta(0,settings.PWD_TIMEOUT_REFRESH):
s = SessionStore(session_key=self.v_user_key)
s['omnidb_session'].v_databases[p_database_index]['prompt_timeout'] = datetime.now()
s.save()
return { 'timeout': False, 'message': ''}
class Instance(object):
""" Encapsulates the AWS EC2 instance to add additional functionality
for running the MuMax3 simulations.
"""
def __init__(self, aws_instance):
self._instance = aws_instance
self._forward = None
def start(self):
aws.start_instances(instance_ids=self.id)
self.add_ready_tags()
def add_ready_tags(self):
self._instance.add_tag('mucloud', __version__)
def stop(self):
aws.stop_instances(instance_ids=[self.id])
def terminate(self):
# Toggle on delete on termination
devices = ["%s=1" % dev for dev, bd in
self._instance.block_device_mapping.items()]
self._instance.modify_attribute('BlockDeviceMapping', devices)
aws.terminate_instances(instance_ids=[self.id])
def is_up(self):
return self._instance.state == u'running'
def is_ready(self):
return self.state == u'ready'
def is_simulating(self):
return self.state == u'simulating'
def wait_for_boot(self, delay=10):
""" Waits for an instance to boot up """
log.info("Waiting for instance to boot...")
while not self.is_up():
sleep(delay)
self._instance.update()
sleep(delay)
@property
def directory(self):
return "/home/%s" % config.get('EC2', 'User')
def paths(self, local_input_file):
basename = os.path.basename(local_input_file)
directory = "/home/%s" % config.get('EC2', 'User')
return {
'local_input_file': local_input_file,
'local_output_dir': local_input_file.replace(".mx3", ".out"),
'input_file': "%s/simulations/%s" % (directory, basename),
'output_dir': "%s/simulations/%s" % (
directory,
basename.replace(".mx3", ".out")
),
'basename': basename,
'log': "%s/log.txt" % directory,
'finished': "%s/finished" % directory,
}
def connect(self):
""" Connects to the instance through SSH and SFTP
"""
log.info("Making secure connection to instance %s..." % self.id)
ssh = paramiko.SSHClient()
ssh.set_missing_host_key_policy(paramiko.AutoAddPolicy())
ssh.connect(
self.ip,
username=config.get('EC2', 'User'),
key_filename=config.get('EC2', 'PrivateKeyFile')
)
sftp = ssh.open_sftp()
return ssh, sftp
def run(self, local_input_file, port=PORT, detach=False):
""" Run the mumax input file on a ready instance """
if not self.is_ready():
raise Exception("The instance %s is not ready to be run" % repr(
self))
try:
ssh, sftp = self.connect()
except:
log.error("Could not connect to remote server")
return
try:
# Determine file paths
paths = self.paths(local_input_file)
self._instance.add_tags({
'local_input_file': local_input_file,
'port': port,
})
log.info("Transferring input file to instance:"
" %s" % paths['basename'])
sftp.put(local_input_file, paths['input_file'])
log.info("Starting port forwarding: http://127.0.0.1:%d" % port)
self.port_forward(port)
# Starting screen
ssh.exec_command("screen -dmS %s" % SCREEN)
sleep(0.5)
cmd = "source ./run_mumax3 %s %s" % (port, paths['input_file'])
log.info("Running %s on MuMax3" % paths['basename'])
ssh.exec_command("screen -S %s -X stuff $'%s'\r" % (SCREEN, cmd))
except KeyboardInterrupt:
log.info("\n\nCanceling simulation on keyboard interrupt")
self.clean(ssh, sftp)
return
if detach:
log.info("Stopping port forwarding")
self.stop_port_forward()
log.info("Detaching from instance with simulation running")
log.info("Reattach with: python mucloud.py "
"reattach %s" % self.id)
return
detach = self.wait_for_simulation(ssh, sftp)
log.info(MUMAX_OUTPUT)
log.info("Stopping port forwarding")
self.stop_port_forward()
if detach:
return
# Exit screen
ssh.exec_command("screen -S %s -X stuff $'exit\r'" % SCREEN)
self.clean(ssh, sftp)
self.stop_or_terminate()
def wait_for_simulation(self, ssh, sftp):
local_input_file = self.tags['local_input_file']
paths = self.paths(local_input_file)
try:
log.info(MUMAX_OUTPUT)
while not rexists(sftp, paths['log']):
sleep(0.1) # Wait for log
f = sftp.open(paths['log'], 'r')
while not rexists(sftp, paths['finished']):
data = f.read()
if data != "":
# TODO: Incorporate with logging module
print(data.decode('utf8'), end='') # end argument to prevent newline
print(f.read().decode('utf8'), end='')
except KeyboardInterrupt:
log.info("\n\nCaught keyboard interrupt during simulation")
answer = input("Detach, abort, or continue the "
"simulation? [Dac]: ")
if len(answer) == 0 or answer.startswith(("D", "d")):
log.info("Detaching from instance with simulation running")
log.info("Reattach with: python mucloud.py"
" reattach %s" % self.id)
return True
elif answer.startswith(("A", "a")):
self.halt(ssh, sftp)
return False
else:
log.info("Continuing the simulation")
return self.wait_for_simulation(ssh, sftp)
def halt(self, ssh, sftp):
log.info("Aborting the simulation")
# Keyboard interrupt the screen
ssh.exec_command("screen -S %s -X stuff $'\\003\r'" % SCREEN)
def clean(self, ssh, sftp):
""" Clean the instance when the simulation has been stopped
"""
local_input_file = self.tags['local_input_file']
paths = self.paths(local_input_file)
if rexists(sftp, paths['output_dir']):
log.info("Receiving output files from instance")
if not os.path.isdir(paths['local_output_dir']):
os.mkdir(paths['local_output_dir'])
os.chdir(paths['local_output_dir'])
sftp.chdir(paths['output_dir'])
files = sftp.listdir()
for f in tqdm(files):
sftp.get(f, f)
log.info("Removing simulation output from instance")
ssh.exec_command("rm -r %s" % paths['output_dir'])
if rexists(sftp, paths['input_file']):
log.info("Removing input file from instance")
sftp.remove(paths['input_file'])
if rexists(sftp, paths['log']):
log.info("Removing logs from instance")
sftp.remove(paths['log'])
if rexists(sftp, paths['finished']):
sftp.remove(paths['finished'])
ssh.close()
# Remove tags
self._instance.remove_tags({
'local_input_file': None,
'port': None,
})
def stop_or_terminate(self):
answer = input("Terminate the instance? [Yn]: ")
if len(answer) == 0 or answer.startswith(("Y", "y")):
log.info("Terminating instance")
self.terminate()
else:
answer = input("Stop the instance? [Yn]: ")
if len(answer) == 0 or answer.startswith(("Y", "y")):
log.info("Stopping instance")
self.stop()
else:
log.info("The instance has been left running")
def reattach(self):
if 'local_input_file' in self.tags:
local_input_file = self.tags['local_input_file']
port = int(self.tags['port'])
paths = self.paths(local_input_file)
log.info("Reconnecting to running instance")
try:
ssh, sftp = self.connect()
except:
log.error("Could not connect to remote server")
return
if not rexists(sftp, paths['input_file']):
log.info("The input file has not been uploaded correctly")
return
log.info("Starting port forwarding: http://127.0.0.1:%d" % port)
self.port_forward(port)
disconnect = self.wait_for_simulation(ssh, sftp)
log.info(MUMAX_OUTPUT)
log.info("Stopping port forwarding")
self.stop_port_forward()
if disconnect:
return
# Exit screen
ssh.exec_command("screen -S %s -X stuff $'exit\r'" % SCREEN)
self.clean(ssh, sftp)
self.stop_or_terminate()
else:
log.info("Instance %s is not running a simulation" % self.id)
def port_forward(self, port=PORT):
key = paramiko.RSAKey.from_private_key_file(
config.get('EC2', 'PrivateKeyFile')
)
self._forward = SSHTunnelForwarder(
ssh_address=(self.ip, 22),
ssh_username=config.get('EC2', 'User'),
ssh_private_key=key,
remote_bind_address=('127.0.0.1', PORT),
local_bind_address=('127.0.0.1', PORT)
)
self._forward.start()
def stop_port_forward(self):
if self._forward is not None:
self._forward.stop()
self._forward = None
@property
def ip(self):
return self._instance.ip_address
@property
def id(self):
return self._instance.id
@property
def tags(self):
return self._instance.tags
@property
def state(self):
if self._instance.state == u'running':
# Determine if its ready or simulating
if 'local_input_file' in self.tags:
return u'simulating'
else:
return u'ready'
else:
return self._instance.state
@staticmethod
def has_mumax(aws_instance):
return (
'mucloud' in aws_instance.tags and
aws_instance.tags['mucloud'] == str(__version__) and
aws_instance.state != u'terminated'
)
@staticmethod
def launch():
""" Launch a new AWS instance """
reservation = aws.run_instances(
config.get('EC2', 'Image'),
key_name=config.get('EC2', 'PrivateKeyName'),
instance_type=config.get('EC2', 'InstanceType'),
security_groups=config.get('EC2', 'SecurityGroups').split(',')
)
instance = Instance(reservation.instances[0])
log.info("Creating a new instance %s from image %s" % (
instance.id, config.get('EC2', 'Image')))
sleep(1)
instance.add_ready_tags()
return instance
def __repr__(self):
return "<MuCloud Instance(id='%s')>" % self.id
class DB(object):
"""
Read: https://colinnewell.wordpress.com/2016/01/21/hand-coding-sql-with-psycopg2-for-odoo/
"""
_db_connection = None
_db_cur = None
_server = None
def __init__(self):
db_config = Configuration.get_vicnode_db()
ssh_intermediate = Configuration.get_ssh_tunnel_info()
self._server = SSHTunnelForwarder(
((ssh_intermediate['host']), (int(ssh_intermediate['port']))),
ssh_password=ssh_intermediate['ssh_password'],
ssh_username=(ssh_intermediate['username']),
ssh_pkey=(ssh_intermediate['private_key_file']),
remote_bind_address=(db_config['host'], 5432),
allow_agent=False
)
self._server.start()
# we are about to bind to a 'local' server by means of an ssh tunnel
# ssh tunnel: which will be seen as a local server...
# so replace the loaded config host
db_config['host'] = 'localhost'
db_config['port'] = self._server.local_bind_port
self._db_connection = psycopg2.connect(**db_config)
self._db_cur = self._db_connection.cursor(
cursor_factory=psycopg2.extras.RealDictCursor)
self.test_connection()
def __del__(self):
self.close_connection()
def close_connection(self):
if self._server:
logging.info("Closing the VicNode DB connection")
# if the connection was not established, there will be no close()
# attribute...
self._db_connection.close()
logging.info("Stopping the ssh tunnel")
self._server.stop()
logging.info("The VicNode DB connection is closed")
self._server = None
@staticmethod
def get_product_code(product):
products = settings.STORAGE_PRODUCT_CODES
if product == COMPUTATIONAL:
products = settings.COMPUTE_PRODUCT_CODES
elif product == MARKET:
products = settings.MARKET_PRODUCT_CODES
elif product == VAULT:
products = settings.VAULT_MARKET_CODES
return products
@connection_required
def test_connection(self):
self._db_cur.execute("SELECT * FROM applications_suborganization;")
rows = self._db_cur.fetchall()
# print(rows)
@connection_required
def get_allocated(self, day_date):
"""
:param self:
:param day_date:
:return:
"""
q_allocated = """
SELECT
sum(size),
CASE
WHEN storage_product_id IN %(compute)s
THEN 'computational'
WHEN storage_product_id IN %(market)s
THEN 'market'
ELSE 'vault' END AS product
FROM applications_allocation
WHERE storage_product_id IN %(all_types)s
AND COALESCE(applications_allocation.creation_date, '2014-11-14' :: DATE) <
(%(day_date)s :: DATE + '1 day' :: INTERVAL)
GROUP BY storage_product_id;
"""
self._db_cur.execute(q_allocated, {
'compute': settings.COMPUTE_PRODUCT_CODES,
'market': settings.MARKET_PRODUCT_CODES,
'all_types': settings.STORAGE_PRODUCT_CODES,
'day_date': day_date
})
return self._db_cur.fetchall()
@connection_required
def get_allocated_by_faculty(self, day_date, product='all'):
"""
:param product:
:param self:
:param day_date:
:return:
"""
products = self.get_product_code(product)
q_allocated = """
SELECT
sum(size) AS used,
CASE
WHEN institution_id != 2
THEN 'external'
WHEN applications_suborganization.id = 1
THEN 'ABP'
WHEN applications_suborganization.id = 2
THEN 'FBE'
WHEN applications_suborganization.id = 3
THEN 'FoA'
WHEN applications_suborganization.id = 4
THEN 'MGSE'
WHEN applications_suborganization.id = 5
THEN 'MSE'
WHEN applications_suborganization.id = 6
THEN 'MLS'
WHEN applications_suborganization.id = 7
THEN 'MDHS'
WHEN applications_suborganization.id = 8
THEN 'FoS'
WHEN applications_suborganization.id = 9
THEN 'VAS'
WHEN applications_suborganization.id = 10
THEN 'VCAMCM'
WHEN applications_suborganization.id = 11
THEN 'services'
ELSE 'unknown' END AS faculty
FROM applications_allocation
LEFT JOIN applications_request
ON applications_allocation.application_id =
applications_request.id
LEFT JOIN applications_suborganization
ON applications_request.institution_faculty_id =
applications_suborganization.id
WHERE storage_product_id IN %(products)s
AND COALESCE(applications_allocation.creation_date, '2014-11-14' :: DATE) <
(%(day_date)s :: DATE + '1 day' :: INTERVAL)
GROUP BY faculty;
"""
# print(self._db_cur.mogrify(q_allocated,
# {'products': products, 'day_date': day_date}))
self._db_cur.execute(q_allocated, {
'products': products,
'day_date': day_date
})
return self._db_cur.fetchall()
@connection_required
def get_storage_used(self, day_date):
q_used = """
SELECT
sum(used_capacity),
CASE
WHEN storage_product_id IN %(compute)s
THEN 'computational'
WHEN storage_product_id IN %(market)s
THEN 'market'
ELSE 'vault' END AS product
FROM applications_ingest t1
WHERE storage_product_id IN %(all_types)s
-- and this is the last record
AND extraction_date =
(SELECT MAX(extraction_date)
FROM applications_ingest t2
WHERE t2.collection_id = t1.collection_id
AND t2.storage_product_id = t1.storage_product_id
AND t2.extraction_date <
(%(day_date)s :: DATE + '1 day' :: INTERVAL)
)
GROUP BY product;
"""
self._db_cur.execute(q_used, {
'compute': settings.COMPUTE_PRODUCT_CODES,
'market': settings.MARKET_PRODUCT_CODES,
'all_types': settings.STORAGE_PRODUCT_CODES,
'day_date': day_date
})
return self._db_cur.fetchall()
@connection_required
def get_used_by_faculty(self, day_date, product='all'):
products = self.get_product_code(product)
q_used = """
SELECT
sum(used_capacity),
CASE
WHEN suborg_id IS NULL
THEN 'external'
WHEN suborg_id = 1
THEN 'ABP'
WHEN suborg_id = 2
THEN 'FBE'
WHEN suborg_id = 3
THEN 'FoA'
WHEN suborg_id = 4
THEN 'MGSE'
WHEN suborg_id = 5
THEN 'MSE'
WHEN suborg_id = 6
THEN 'MLS'
WHEN suborg_id = 7
THEN 'MDHS'
WHEN suborg_id = 8
THEN 'FoS'
WHEN suborg_id = 9
THEN 'VAS'
WHEN suborg_id = 10
THEN 'VCAMCM'
WHEN suborg_id = 11
THEN 'services'
ELSE 'unknown' END AS faculty
FROM applications_ingest ingest
LEFT JOIN (
SELECT
request.id,
coalesce(suborganization.id, -1) AS suborg_id
FROM applications_request request
LEFT JOIN applications_suborganization suborganization
ON institution_faculty_id = suborganization.id
WHERE
request.institution_id = '2'
ORDER BY id
) AS names ON names.id = ingest.collection_id
WHERE storage_product_id IN %(products)s
-- and this is the last record
AND extraction_date =
(SELECT MAX(extraction_date)
FROM applications_ingest t2
WHERE t2.collection_id = ingest.collection_id
AND t2.storage_product_id = ingest.storage_product_id
AND
t2.extraction_date <
(%(day_date)s :: DATE + '1 day' :: INTERVAL)
)
GROUP BY faculty;
"""
self._db_cur.execute(q_used, {
'products': products,
'day_date': day_date
})
return self._db_cur.fetchall()
@connection_required
def get_headroom_unused(self, day_date):
q_used = """
SELECT
sum(allocated_capacity - used_capacity) AS headroom,
CASE
WHEN storage_product_id IN %(compute)s
THEN 'computational'
WHEN storage_product_id IN %(market)s
THEN 'market'
ELSE 'vault' END AS product
FROM applications_ingest AS t1
WHERE storage_product_id IN %(all_types)s
-- and this is the last record
AND extraction_date =
(SELECT MAX(extraction_date)
FROM applications_ingest t2
WHERE t2.collection_id = t1.collection_id
AND t2.storage_product_id = t1.storage_product_id
AND t2.extraction_date <
(%(day_date)s :: DATE + '1 day' :: INTERVAL)
)
GROUP BY storage_product_id;
"""
self._db_cur.execute(q_used, {
'compute': settings.COMPUTE_PRODUCT_CODES,
'market': settings.MARKET_PRODUCT_CODES,
'all_types': settings.STORAGE_PRODUCT_CODES,
'day_date': day_date
})
return self._db_cur.fetchall()
@connection_required
def get_headroom_unused_by_faculty(self, day_date, product='all'):
products = self.get_product_code(product)
q_used = """
SELECT sum(allocated_capacity - used_capacity) AS headroom,
CASE
WHEN suborg_id IS NULL
THEN 'external'
WHEN suborg_id = 1
THEN 'ABP'
WHEN suborg_id = 2
THEN 'FBE'
WHEN suborg_id = 3
THEN 'FoA'
WHEN suborg_id = 4
THEN 'MGSE'
WHEN suborg_id = 5
THEN 'MSE'
WHEN suborg_id = 6
THEN 'MLS'
WHEN suborg_id = 7
THEN 'MDHS'
WHEN suborg_id = 8
THEN 'FoS'
WHEN suborg_id = 9
THEN 'VAS'
WHEN suborg_id = 10
THEN 'VCAMCM'
WHEN suborg_id = 11
THEN 'services'
ELSE 'unknown' END AS faculty
FROM applications_ingest ingest
LEFT JOIN (
SELECT
request.id,
coalesce(suborganization.id, -1) AS suborg_id
FROM applications_request request
LEFT JOIN applications_suborganization suborganization
ON institution_faculty_id = suborganization.id
WHERE
request.institution_id = '2'
ORDER BY id
) AS names ON names.id = ingest.collection_id
WHERE storage_product_id IN %(products)s
-- and this is the last record
AND extraction_date =
(SELECT MAX(extraction_date)
FROM applications_ingest t2
WHERE t2.collection_id = ingest.collection_id
AND t2.storage_product_id = ingest.storage_product_id
AND t2.extraction_date <
(%(day_date)s :: DATE + '1 day' :: INTERVAL)
)
GROUP BY faculty;
"""
self._db_cur.execute(q_used, {
'products': products,
'day_date': day_date
})
return self._db_cur.fetchall()
@connection_required
def get_storage_capacity(self):
query = """
SELECT
capacity * 1000 AS capacity,
CASE
WHEN id IN %(compute)s
THEN 'computational'
WHEN id IN %(market)s
THEN 'market'
ELSE 'vault' END AS product
FROM applications_storageproduct
WHERE id IN %(all_types)s;
"""
self._db_cur.execute(query, {
'compute': settings.COMPUTE_PRODUCT_CODES,
'market': settings.MARKET_PRODUCT_CODES,
'all_types': settings.STORAGE_PRODUCT_CODES
})
return self._db_cur.fetchall()
@connection_required
def get_storage_types(self):
"""
:return:
The set of storage types in the vicnode database
"""
query = """
SELECT
value
FROM applications_storageproduct
LEFT JOIN labels_label
ON labels_label.id =
applications_storageproduct.product_name_id;
"""
result = set()
self._db_cur.execute(query)
result_set = self._db_cur.fetchall()
for row in result_set:
result.add(row['value'])
return result
class CdhConfExtractor(object):
def __init__(self, config):
self._logger = logging.getLogger(__name__)
self._hostname = config['machines']['cdh-launcher']['hostname']
self._hostport = config['machines']['cdh-launcher']['hostport']
self._username = config['machines']['cdh-launcher']['username']
key_path = config['machines']['cdh-launcher']['key_filename']
self._key = os.path.expanduser(key_path)
self._key_password = config['machines']['cdh-launcher']['key_password']
self._is_openstack = config['openstack_env']
self._is_kerberos = config['kerberos_used']
self._cdh_manager_ip = config['machines']['cdh-manager']['ip']
self._cdh_manager_user = config['machines']['cdh-manager']['user']
self._cdh_manager_sshtunnel_required = config['machines']['cdh-manager']['sshtunnel_required']
self._cdh_manager_password = config['machines']['cdh-manager']['password']
def __enter__(self):
extractor = self
try:
if self._cdh_manager_sshtunnel_required:
self._logger.info('Creating tunnel to CDH-Manager.')
extractor.create_tunnel_to_cdh_manager()
extractor.start_cdh_manager_tunneling()
self._logger.info('Tunnel to CDH-Manager has been created.')
else:
self._logger.info('Connection to CDH-Manager host without ssh tunnel.')
self._local_bind_address = self.extract_cdh_manager_host()
self._local_bind_port = 7180
return extractor
except Exception as exc:
self._logger.error('Cannot creating tunnel to CDH-Manager machine.')
raise exc
def __exit__(self, exc_type, exc_val, exc_tb):
try:
if self._cdh_manager_sshtunnel_required:
self.stop_cdh_manager_tunneling()
self._logger.info('Tunelling to CDH-Manager stopped.')
except Exception as exc:
self._logger.error('Cannot close tunnel to CDH-Manager machine.')
raise exc
# Cdh launcher methods
def create_ssh_connection(self, hostname, username, key_filename, key_password):
try:
self._logger.info('Creating connection to remote host {0}.'.format(hostname))
self.ssh_connection = paramiko.SSHClient()
self.ssh_connection.set_missing_host_key_policy(paramiko.AutoAddPolicy())
self.ssh_connection.connect(hostname, username=username, key_filename=key_filename, password=key_password)
self._logger.info('Connection to host {0} established.'.format(hostname))
except Exception as exc:
self._logger.error('Cannot creating connection to host {0} machine. Check your settings '
'in fetcher_config.yml file.'.format(hostname))
raise exc
def close_ssh_connection(self):
try:
self.ssh_connection.close()
self._logger.info('Connection to remote host closed.')
except Exception as exc:
self._logger.error('Cannot close connection to the remote host.')
raise exc
def ssh_call_command(self, command, subcommands=None):
self._logger.info('Calling remote command: "{0}" with subcommands "{1}"'.format(command, subcommands))
ssh_in, ssh_out, ssh_err = self.ssh_connection.exec_command(command, get_pty=True)
if subcommands != None:
for subcommand in subcommands:
ssh_in.write(subcommand + '\n')
ssh_in.flush()
return ssh_out.read() if ssh_out is not None else ssh_err.read()
def extract_cdh_manager_host(self):
self._logger.info('Extracting CDH-Manager address.')
if self._cdh_manager_ip is None:
self.create_ssh_connection(self._hostname, self._username, self._key, self._key_password)
if self._is_openstack:
ansible_ini = self.ssh_call_command('cat ansible-cdh/platform-ansible/inventory/cdh')
else:
ansible_ini = self.ssh_call_command('cat ansible-cdh/inventory/cdh')
self._cdh_manager_ip = self._get_host_ip('cdh-manager', ansible_ini)
self.close_ssh_connection()
self._logger.info('CDH-Manager adress extracted: {}'.format(self._cdh_manager_ip))
return self._cdh_manager_ip
# Cdh manager methods
def create_tunnel_to_cdh_manager(self, local_bind_address='localhost', local_bind_port=7180, remote_bind_port=7180):
self._local_bind_address = local_bind_address
self._local_bind_port = local_bind_port
self.cdh_manager_tunnel = SSHTunnelForwarder(
(self._hostname, self._hostport),
ssh_username=self._username,
local_bind_address=(local_bind_address, local_bind_port),
remote_bind_address=(self.extract_cdh_manager_host(), remote_bind_port),
ssh_private_key_password=self._key_password,
ssh_private_key=self._key
)
def start_cdh_manager_tunneling(self):
try:
self.cdh_manager_tunnel.start()
except Exception as e:
self._logger.error('Cannot start tunnel: ' + e.message)
def stop_cdh_manager_tunneling(self):
try:
self.cdh_manager_tunnel.stop()
except Exception as e:
self._logger.error('Cannot stop tunnel: ' + e.message)
def extract_cdh_manager_details(self, settings):
for host in settings['hosts']:
if 'cdh-manager' in host['hostname']:
return host
def extract_nodes_info(self, name, settings):
nodes = []
for host in settings['hosts']:
if name in host['hostname']:
nodes.append(host)
return nodes
def extract_service_namenode(self, service_name, role_name, settings):
hdfs_service = self._find_item_by_attr_value(service_name, 'name', settings['clusters'][0]['services'])
hdfs_namenode = self._find_item_by_attr_value(role_name, 'name', hdfs_service['roles'])
host_id = hdfs_namenode['hostRef']['hostId']
return self._find_item_by_attr_value(host_id, 'hostId', settings['hosts'])['hostname']
def get_client_config_for_service(self, service_name):
result = requests.get('http://{0}:{1}/api/v10/clusters/CDH-cluster/services/{2}/clientConfig'.format(self._local_bind_address, self._local_bind_port, service_name))
return base64.standard_b64encode(result.content)
def generate_keytab(self, principal_name):
def transfer_keytab_script(target):
sftp = self.ssh_connection.open_sftp()
with tempfile.NamedTemporaryFile('w') as f:
f.file.write(GENERATE_KEYTAB_SCRIPT)
f.file.close()
sftp.put(f.name, target)
self._logger.info('Generating keytab for {} principal.'.format(principal_name))
self.create_ssh_connection(self._hostname, self._username, self._key, self._key_password)
transfer_keytab_script('/tmp/generate_keytab_script.sh')
self.ssh_call_command('scp -o UserKnownHostsFile=/dev/null -o StrictHostKeyChecking=no /tmp/generate_keytab_script.sh {0}:/tmp/'.format(self._cdh_manager_ip))
self.ssh_call_command('ssh -t {0} -o UserKnownHostsFile=/dev/null -o StrictHostKeyChecking=no "chmod 700 /tmp/generate_keytab_script.sh"'.format(self._cdh_manager_ip))
keytab_hash = self.ssh_call_command('ssh -t {0} -o UserKnownHostsFile=/dev/null -o StrictHostKeyChecking=no "/tmp/generate_keytab_script.sh {1}"'
.format(self._cdh_manager_ip, principal_name))
self.close_ssh_connection()
lines = keytab_hash.splitlines()
self._logger.info('Keytab for {} principal has been generated.'.format(principal_name))
return '{}'.format(''.join(lines[2:-2]))
def generate_base64_for_file(self, file_path, hostname):
self._logger.info('Generating base64 for {} file.'.format(file_path))
self.create_ssh_connection(self._hostname, self._username, self._key, self._key_password)
base64_file_hash = self.ssh_call_command('ssh -t {0} -o UserKnownHostsFile=/dev/null -o StrictHostKeyChecking=no "base64 {1}"'.format(hostname, file_path))
self.close_ssh_connection()
lines = base64_file_hash.splitlines()
self._logger.info('Base64 hash for {0} file on {1} machine has been generated.'.format(file_path, hostname))
return '{}'.format(''.join(lines[2:-2]))
def get_all_deployments_conf(self):
result = {}
deployments_settings = json.loads(requests.get('http://' + self._local_bind_address + ':'
+ str(self._local_bind_port) + '/api/v10/cm/deployment',
auth=(self._cdh_manager_user, self._cdh_manager_password)).content)
result['cloudera_manager_internal_host'] = self.extract_cdh_manager_details(deployments_settings)['hostname']
helper = CdhApiHelper(ApiResource(self._local_bind_address, username=self._cdh_manager_user, password=self._cdh_manager_password, version=9))
if self._is_kerberos:
result['kerberos_host'] = result['cloudera_manager_internal_host']
result['hdfs_keytab_value'] = self.generate_keytab('hdfs')
result['auth_gateway_keytab_value'] = self.generate_keytab('authgateway/sys')
result['hgm_keytab_value'] = self.generate_keytab('hgm/sys')
result['vcap_keytab_value'] = self.generate_keytab('vcap')
result['krb5_base64'] = self.generate_base64_for_file('/etc/krb5.conf', self._cdh_manager_ip)
result['kerberos_cacert'] = self.generate_base64_for_file('/var/krb5kdc/cacert.pem', self._cdh_manager_ip)
sentry_service = helper.get_service_from_cdh('SENTRY')
result['sentry_port'] = helper.get_entry(sentry_service, 'sentry_service_server_rpc_port')
result['sentry_address'] = helper.get_host(sentry_service)
result['sentry_keytab_value'] = self.generate_keytab('hive/sys')
result['auth_gateway_profile'] = 'cloud,sentry-auth-gateway,zookeeper-auth-gateway,hdfs-auth-gateway,kerberos-hgm-auth-gateway,yarn-auth-gateway,hbase-auth-gateway'
hgm_service = helper.get_service_from_cdh('HADOOPGROUPSMAPPING')
result['hgm_adress'] = 'http://' + helper.get_host(hgm_service, 'HADOOPGROUPSMAPPING-HADOOPGROUPSMAPPING_RESTSERVER') + ':' \
+ helper.get_entry_from_group(hgm_service, 'rest_port', 'HADOOPGROUPSMAPPING-HADOOPGROUPSMAPPING_RESTSERVER-BASE')
result['hgm_password'] = helper.get_entry_from_group(hgm_service, 'basic_auth_pass', 'HADOOPGROUPSMAPPING-HADOOPGROUPSMAPPING_RESTSERVER-BASE')
result['hgm_username'] = helper.get_entry_from_group(hgm_service, 'basic_auth_user', 'HADOOPGROUPSMAPPING-HADOOPGROUPSMAPPING_RESTSERVER-BASE')
else:
result['sentry_port'] = ''
result['sentry_address'] = ''
result['sentry_keytab_value'] = ''
result['hdfs_keytab_value'] = ''
result['auth_gateway_keytab_value'] = ''
result['vcap_keytab_value'] = ''
result['hgm_keytab_value'] = ''
result['krb5_base64'] = ''
result['kerberos_cacert'] = ''
result['auth_gateway_profile'] = 'cloud,zookeeper-auth-gateway,hdfs-auth-gateway,https-hgm-auth-gateway,yarn-auth-gateway,hbase-auth-gateway'
hgm_service = helper.get_service_from_cdh('HADOOPGROUPSMAPPING')
result['hgm_adress'] = 'https://' + helper.get_host(hgm_service, 'HADOOPGROUPSMAPPING-HADOOPGROUPSMAPPING_RESTSERVER') + ':'\
+ helper.get_entry_from_group(hgm_service, 'rest_port', 'HADOOPGROUPSMAPPING-HADOOPGROUPSMAPPING_RESTSERVER-BASE')
result['hgm_password'] = helper.get_entry_from_group(hgm_service, 'basic_auth_pass', 'HADOOPGROUPSMAPPING-HADOOPGROUPSMAPPING_RESTSERVER-BASE')
result['hgm_username'] = helper.get_entry_from_group(hgm_service, 'basic_auth_user', 'HADOOPGROUPSMAPPING-HADOOPGROUPSMAPPING_RESTSERVER-BASE')
result['cloudera_address'] = result['cloudera_manager_internal_host']
result['cloudera_port'] = 7180
result['cloudera_user'] = self._cdh_manager_user
result['cloudera_password'] = self._cdh_manager_password
oozie_server = helper.get_service_from_cdh('OOZIE')
result['oozie_server'] = 'http://' + helper.get_host(oozie_server) + ':' + helper.get_entry(oozie_server, 'oozie_http_port')
yarn = helper.get_service_from_cdh('YARN')
result['job_tracker'] = helper.get_host(yarn) + ':' + helper.get_entry(yarn, 'yarn_resourcemanager_address')
sqoop_client = helper.get_service_from_cdh('SQOOP_CLIENT')
result['metastore'] = self._get_property_value(helper.get_entry(sqoop_client, 'sqoop-conf/sqoop-site.xml_client_config_safety_valve'), 'sqoop.metastore.client.autoconnect.url')
master_nodes = self.extract_nodes_info('cdh-master', deployments_settings)
for i, node in enumerate(master_nodes):
result['master_node_host_' + str(i+1)] = node['hostname']
result['namenode_internal_host'] = self.extract_service_namenode('HDFS', 'HDFS-NAMENODE', deployments_settings)
result['hue_node'] = self.extract_service_namenode('HUE', 'HUE-HUE_SERVER', deployments_settings)
result['h2o_node'] = self.extract_nodes_info('cdh-worker-0', deployments_settings)[0]['hostname']
result['arcadia_node'] = self.extract_nodes_info('cdh-worker-0', deployments_settings)[0]['hostname']
result['import_hadoop_conf_hdfs'] = self.get_client_config_for_service('HDFS')
result['import_hadoop_conf_hbase'] = self.get_client_config_for_service('HBASE')
result['import_hadoop_conf_yarn'] = self.get_client_config_for_service('YARN')
result['import_hadoop_conf_hive'] = self.get_client_config_for_service('HIVE')
return result
# helpful methods
def _get_property_value(self, config, key):
properties = ET.fromstring('<properties>' + config + '</properties>')
for property in properties:
if property.find('name').text == key:
return property.find('value').text
def _find_item_by_attr_value(self, attr_value, attr_name, array_with_dicts):
return next(item for item in array_with_dicts if item[attr_name] == attr_value)
def _get_host_ip(self, host, ansible_ini):
host_info = []
for line in ansible_ini.split('\n'):
if host in line:
host_info.append(line.strip())
return host_info[host_info.index('[' + host + ']') + 1].split(' ')[1].split('=')[1]
def _load_config_yaml(self, filename):
with open(filename, 'r') as stream:
return yaml.load(stream)
class Device(object):
# ==================================================================================================================
# FRAMEWORK ATTRIBUTES
# ==================================================================================================================
# Connection Parameters
_ip, _port, _agent_port, _username, _password = '', '', '', '', ''
_tools_local = None
# Port Forwarding
_frida_server = None
_port_forward_ssh, _port_forward_agent = None, None
# App specific
_applist, _ios_version = None, None
# Reference to External Objects
ssh, agent = None, None
app, installer = None, None
local_op, remote_op = None, None
printer = None
# On-Device Paths
TEMP_FOLDER = Constants.DEVICE_PATH_TEMP_FOLDER
DEVICE_TOOLS = Constants.DEVICE_TOOLS
# ==================================================================================================================
# INIT
# ==================================================================================================================
def __init__(self, ip, port, agent_port, username, password, pub_key_auth, tools):
# Setup params
self._ip = ip
self._port = port
self._agent_port = agent_port
self._username = username
self._password = password
self._pub_key_auth = bool(pub_key_auth)
self._tools_local = tools
# Init related objects
self.app = App(self)
self.local_op = LocalOperations()
self.remote_op = RemoteOperations(self)
self.printer = Printer()
self.agent = NeedleAgent(self)
# ==================================================================================================================
# UTILS - USB
# ==================================================================================================================
def _portforward_usb_start(self):
"""Setup USB port forwarding with TCPRelay."""
# Check if the user chose a valid port
if str(self._port) == '22':
raise Exception('Chosen port must be different from 22 in order to use USB over SSH')
# Setup the forwarding
self.printer.debug('Setting up USB port forwarding on port %s' % self._port)
cmd = '{app} -t 22:{port}'.format(app=self._tools_local['TCPRELAY'], port=self._port)
self._port_forward_ssh = self.local_op.command_subproc_start(cmd)
def _portforward_usb_stop(self):
"""Stop USB port forwarding."""
self.printer.debug('Stopping USB port forwarding')
self.local_op.command_subproc_stop(self._port_forward_ssh)
# ==================================================================================================================
# UTILS - SSH
# ==================================================================================================================
def _connect_ssh(self):
"""Open a new SSH connection using Paramiko."""
try:
self.printer.verbose("[SSH] Connecting ({}:{})...".format(self._ip, self._port))
ssh = paramiko.SSHClient()
ssh.set_missing_host_key_policy(paramiko.AutoAddPolicy())
ssh.connect(self._ip, port=self._port, username=self._username, password=self._password,
allow_agent=self._pub_key_auth, look_for_keys=self._pub_key_auth)
self.printer.notify("[SSH] Connected ({}:{})".format(self._ip, self._port))
return ssh
except paramiko.AuthenticationException as e:
raise Exception('Authentication failed when connecting to %s. %s: %s' % (self._ip, type(e).__name__, e.message))
except paramiko.SSHException as e:
raise Exception('Connection dropped. Please check your connection with the device, '
'and reload the module. %s: %s' % (type(e).__name__, e.message))
except Exception as e:
raise Exception('Could not open a connection to %s. %s - %s' % (self._ip, type(e).__name__, e.message))
def _disconnect_ssh(self):
"""Close the SSH connection, if available."""
self.printer.verbose("[SSH] Disconnecting...")
if self.ssh:
self.ssh.close()
@Retry()
def _exec_command_ssh(self, cmd, internal):
"""Execute a shell command on the device, then parse/print output."""
def hotfix_67():
# TODO: replace with a more long-term fix
import time
timeout = 30
endtime = time.time() + timeout
while not stdout.channel.eof_received:
time.sleep(1)
if time.time() > endtime:
stdout.channel.close()
break
# Paramiko Exec Command
stdin, stdout, stderr = self.ssh.exec_command(cmd)
hotfix_67()
# Parse STDOUT/ERR
out = stdout.readlines()
err = stderr.readlines()
if internal:
# For processing, don't display output
if err:
# Show error and abort run
err_str = ''.join(err)
raise Exception(err_str)
else:
# Display output
if out: map(lambda x: print('\t%s' % x, end=''), out)
if err: map(lambda x: print('\t%s%s%s' % (Colors.R, x, Colors.N), end=''), err)
return out, err
# ==================================================================================================================
# UTILS - AGENT
# ==================================================================================================================
def _portforward_agent_start(self):
"""Setup local port forward to enable communication with the Needle server running on the device."""
self.printer.debug('{} Setting up port forwarding on port {}'.format(Constants.AGENT_TAG, self._agent_port))
localhost = '127.0.0.1'
self._port_forward_agent = SSHTunnelForwarder(
(self._ip, int(self._port)),
ssh_username=self._username,
ssh_password=self._password,
local_bind_address=(localhost, self._agent_port),
remote_bind_address=(localhost, self._agent_port),
)
self._port_forward_agent.start()
def _portforward_agent_stop(self):
"""Stop local port forwarding for Needle server."""
self.printer.debug('{} Stopping port forwarding'.format(Constants.AGENT_TAG))
if self._port_forward_agent:
self._port_forward_agent.stop()
def _connect_agent(self):
self.agent.connect()
# Ensure the tunnel has been established (especially after auto-reconnecting)
self.agent.exec_command_agent(Constants.AGENT_CMD_OS_VERSION)
def _disconnect_agent(self):
self.agent.disconnect()
# ==================================================================================================================
# FRIDA PORT FORWARDING
# ==================================================================================================================
def _portforward_frida_start(self):
"""Setup local port forward to enable communication with the Frida server running on the device."""
self.printer.debug('{} Setting up port forwarding on port {}'.format("[FRIDA]", Constants.FRIDA_PORT))
localhost = '127.0.0.1'
self._frida_server = SSHTunnelForwarder(
(self._ip, int(self._port)),
ssh_username=self._username,
ssh_password=self._password,
local_bind_address=(localhost, Constants.FRIDA_PORT),
remote_bind_address=(localhost, Constants.FRIDA_PORT),
)
self._frida_server.start()
def _portforward_frida_stop(self):
"""Stop local port forwarding for Frida server."""
self.printer.debug('{} Stopping port forwarding'.format("FRIDA"))
if self._frida_server:
self._frida_server.stop()
# ==================================================================================================================
# UTILS - OS
# ==================================================================================================================
def _list_apps(self, hide_system_apps=False):
"""Retrieve all the 3rd party apps installed on the device."""
agent_list = self.agent.exec_command_agent(Constants.AGENT_CMD_LIST_APPS)
self._applist = Utils.string_to_json(agent_list)
if hide_system_apps:
self._applist = {k: v for k, v in self._applist.iteritems() if v["BundleType"] == "User"}
def select_target_app(self):
"""List all apps installed and let the user choose which one to target."""
# Show menu to user
self.printer.notify('Apps found:')
app_name = choose_from_list(self._applist.keys())
return app_name
# ==================================================================================================================
# EXPOSED COMMANDS
# ==================================================================================================================
def is_usb(self):
"""Returns true if using SSH over USB."""
return self._ip == '127.0.0.1' or self._ip == 'localhost'
def connect(self):
"""Connect to the device (both SSH and AGENT)."""
# Using USB, setup port forwarding first
if self.is_usb():
self._portforward_usb_start()
self._portforward_agent_start()
# Setup channels
self._connect_agent()
self.ssh = self._connect_ssh()
def disconnect(self):
"""Disconnect from the device (both SSH and AGENT)."""
# Close channels
self._disconnect_ssh()
self._disconnect_agent()
# Using USB, stop port forwarding first
if self._port_forward_ssh:
self._portforward_usb_stop()
self._portforward_agent_stop()
def setup(self):
"""Create temp folder, and check if all tools are available"""
# Setup temp folder
self.printer.debug("Creating temp folder: %s" % self.TEMP_FOLDER)
self.remote_op.dir_create(self.TEMP_FOLDER)
# Detect OS version
if not self._ios_version:
self._ios_version = self.agent.exec_command_agent(Constants.AGENT_CMD_OS_VERSION).strip()
def cleanup(self):
"""Remove temp folder from device."""
self.printer.debug("Cleaning up remote temp folder: %s" % self.TEMP_FOLDER)
self.remote_op.dir_delete(self.TEMP_FOLDER)
def shell(self):
"""Spawn a system shell on the device."""
cmd = 'sshpass -p "{password}" ssh {hostverification} -p {port} {username}@{ip}'.format(password=self._password,
hostverification=Constants.DISABLE_HOST_VERIFICATION,
port=self._port,
username=self._username,
ip=self._ip)
self.local_op.command_interactive(cmd)
def pull(self, src, dst):
"""Pull a file from the device."""
self.printer.info("Pulling: %s -> %s" % (src, dst))
self.remote_op.download(src, dst)
def push(self, src, dst):
"""Push a file on the device."""
self.printer.info("Pushing: %s -> %s" % (src, dst))
self.remote_op.upload(src, dst)
class DB:
_db_connection = None
_db_cur = None
def __init__(self):
ssh_tunnel = Credentials.ssh_tunnel
db_config = Credentials.vicnode_db
self._server = SSHTunnelForwarder(
((ssh_tunnel['host']), (int(ssh_tunnel['port']))),
ssh_password=ssh_tunnel['ssh_password'],
ssh_username=(ssh_tunnel['username']),
ssh_pkey=(ssh_tunnel['private_key_file']),
remote_bind_address=(db_config['host'], 5432),
allow_agent=False
)
self._server.start()
# we are about to bind to a 'local' server by means of an ssh tunnel
# ssh tunnel: which will be seen as a local server...
# so replace the loaded config host
db_config['host'] = 'localhost'
db_config['port'] = self._server.local_bind_port
self._db_connection = psycopg2.connect(**db_config)
self._db_cur = self._db_connection.cursor(
cursor_factory=psycopg2.extras.RealDictCursor)
self.test_connection()
def __del__(self):
self.close_connection()
def close_connection(self):
if self._server:
self._db_connection.close()
self._server.stop()
self._server = None
def test_connection(self):
self._db_cur.execute("SELECT * FROM applications_suborganization;")
rows = self._db_cur.fetchall()
def get_contacts(self):
"""
Returns: every project name and it's chief investigator (can be more
than one?).
"""
query = """
SELECT
collection.id,
contacts_contact.email_address,
contacts_contact.business_email_address
FROM applications_project AS collection
LEFT JOIN applications_custodian
ON collection_id = collection.id
LEFT JOIN contacts_contact
ON applications_custodian.person_id = contacts_contact.id
WHERE applications_custodian.role_id = 293
ORDER BY id;
"""
self._db_cur.execute(query)
return self._db_cur.fetchall()
def get_sub_organizations(self):
"""
Returns: all the projects
"""
query = """
SELECT
applications_request.project_id,
contacts_organisation.short_name,
CASE
WHEN applications_suborganization.id = 1
THEN 'ABP'
WHEN applications_suborganization.id = 2
THEN 'FBE'
WHEN applications_suborganization.id = 3
THEN 'FoA'
WHEN applications_suborganization.id = 4
THEN 'MGSE'
WHEN applications_suborganization.id = 5
THEN 'MSE'
WHEN applications_suborganization.id = 6
THEN 'MLS'
WHEN applications_suborganization.id = 7
THEN 'MDHS'
WHEN applications_suborganization.id = 8
THEN 'FoS'
WHEN applications_suborganization.id = 9
THEN 'VAS'
WHEN applications_suborganization.id = 10
THEN 'VCAMCM'
WHEN applications_suborganization.id = 11
THEN 'External'
ELSE 'Unknown' END AS faculty
FROM applications_request
LEFT JOIN applications_suborganization
ON applications_request.institution_faculty_id =
applications_suborganization.id
LEFT JOIN contacts_organisation
ON applications_request.institution_id = contacts_organisation.id
WHERE -- institution_id = 2
-- don't worry about this, as only UoM projects are assigned
project_id NOTNULL
AND name NOTNULL
GROUP BY short_name, project_id, faculty
ORDER BY project_id;
"""
self._db_cur.execute(query)
return self._db_cur.fetchall()
def get_used(self, end_date):
query = """
SELECT
collection_id,
sum
FROM (
SELECT
collection_id,
SUM(used_capacity) AS sum
FROM applications_ingest
WHERE storage_product_id IN (1, 4, 10, 23, 24)
-- and this is the last record
AND extraction_date =
(SELECT MAX(extraction_date)
FROM applications_ingest t2
WHERE t2.collection_id = applications_ingest.collection_id
AND t2.storage_product_id =
applications_ingest.storage_product_id
AND t2.extraction_date < (DATE %(end)s))
GROUP BY collection_id
ORDER BY collection_id) totals
WHERE sum NOTNULL AND sum > 0;
"""
self._db_cur.execute(query, {
'end': end_date.strftime("%Y-%m-%d")
})
return self._db_cur.fetchall()
class ServerManager(object):
"""
class ServerManager
This class contains the information about the given server.
And, acts as connection manager for that particular session.
"""
def __init__(self, server):
self.connections = dict()
self.local_bind_host = '127.0.0.1'
self.local_bind_port = None
self.tunnel_object = None
self.tunnel_created = False
self.update(server)
def update(self, server):
assert (server is not None)
assert (isinstance(server, Server))
self.ver = None
self.sversion = None
self.server_type = None
self.server_cls = None
self.password = None
self.tunnel_password = None
self.sid = server.id
self.host = server.host
self.hostaddr = server.hostaddr
self.port = server.port
self.db = server.maintenance_db
self.did = None
self.user = server.username
self.password = server.password
self.role = server.role
self.ssl_mode = server.ssl_mode
self.pinged = datetime.datetime.now()
self.db_info = dict()
self.server_types = None
self.db_res = server.db_res
self.passfile = server.passfile
self.sslcert = server.sslcert
self.sslkey = server.sslkey
self.sslrootcert = server.sslrootcert
self.sslcrl = server.sslcrl
self.sslcompression = True if server.sslcompression else False
self.service = server.service
self.connect_timeout = \
server.connect_timeout if server.connect_timeout else 0
if config.SUPPORT_SSH_TUNNEL:
self.use_ssh_tunnel = server.use_ssh_tunnel
self.tunnel_host = server.tunnel_host
self.tunnel_port = server.tunnel_port
self.tunnel_username = server.tunnel_username
self.tunnel_authentication = server.tunnel_authentication
self.tunnel_identity_file = server.tunnel_identity_file
self.tunnel_password = server.tunnel_password
else:
self.use_ssh_tunnel = 0
self.tunnel_host = None
self.tunnel_port = 22
self.tunnel_username = None
self.tunnel_authentication = None
self.tunnel_identity_file = None
self.tunnel_password = None
for con in self.connections:
self.connections[con]._release()
self.update_session()
self.connections = dict()
def as_dict(self):
"""
Returns a dictionary object representing the server manager.
"""
if self.ver is None or len(self.connections) == 0:
return None
res = dict()
res['sid'] = self.sid
res['ver'] = self.ver
res['sversion'] = self.sversion
if hasattr(self, 'password') and self.password:
# If running under PY2
if hasattr(self.password, 'decode'):
res['password'] = self.password.decode('utf-8')
else:
res['password'] = str(self.password)
else:
res['password'] = self.password
if self.use_ssh_tunnel:
if hasattr(self, 'tunnel_password') and self.tunnel_password:
# If running under PY2
if hasattr(self.tunnel_password, 'decode'):
res['tunnel_password'] = \
self.tunnel_password.decode('utf-8')
else:
res['tunnel_password'] = str(self.tunnel_password)
else:
res['tunnel_password'] = self.tunnel_password
connections = res['connections'] = dict()
for conn_id in self.connections:
conn = self.connections[conn_id].as_dict()
if conn is not None:
connections[conn_id] = conn
return res
def ServerVersion(self):
return self.ver
@property
def version(self):
return self.sversion
def MajorVersion(self):
if self.sversion is not None:
return int(self.sversion / 10000)
raise Exception("Information is not available.")
def MinorVersion(self):
if self.sversion:
return int(int(self.sversion / 100) % 100)
raise Exception("Information is not available.")
def PatchVersion(self):
if self.sversion:
return int(int(self.sversion / 100) / 100)
raise Exception("Information is not available.")
def connection(
self, database=None, conn_id=None, auto_reconnect=True, did=None,
async_=None, use_binary_placeholder=False, array_to_string=False
):
if database is not None:
if hasattr(str, 'decode') and \
not isinstance(database, unicode):
database = database.decode('utf-8')
if did is not None:
if did in self.db_info:
self.db_info[did]['datname'] = database
else:
if did is None:
database = self.db
elif did in self.db_info:
database = self.db_info[did]['datname']
else:
maintenance_db_id = u'DB:{0}'.format(self.db)
if maintenance_db_id in self.connections:
conn = self.connections[maintenance_db_id]
if conn.connected():
status, res = conn.execute_dict(u"""
SELECT
db.oid as did, db.datname, db.datallowconn,
pg_encoding_to_char(db.encoding) AS serverencoding,
has_database_privilege(db.oid, 'CREATE') as cancreate, datlastsysoid
FROM
pg_database db
WHERE db.oid = {0}""".format(did))
if status and len(res['rows']) > 0:
for row in res['rows']:
self.db_info[did] = row
database = self.db_info[did]['datname']
if did not in self.db_info:
raise Exception(gettext(
"Could not find the specified database."
))
if database is None:
# Check SSH Tunnel is alive or not.
if self.use_ssh_tunnel == 1:
self.check_ssh_tunnel_alive()
else:
raise ConnectionLost(self.sid, None, None)
my_id = (u'CONN:{0}'.format(conn_id)) if conn_id is not None else \
(u'DB:{0}'.format(database))
self.pinged = datetime.datetime.now()
if my_id in self.connections:
return self.connections[my_id]
else:
if async_ is None:
async_ = 1 if conn_id is not None else 0
else:
async_ = 1 if async_ is True else 0
self.connections[my_id] = Connection(
self, my_id, database, auto_reconnect, async_,
use_binary_placeholder=use_binary_placeholder,
array_to_string=array_to_string
)
return self.connections[my_id]
def _restore(self, data):
"""
Helps restoring to reconnect the auto-connect connections smoothly on
reload/restart of the app server..
"""
# restore server version from flask session if flask server was
# restarted. As we need server version to resolve sql template paths.
from pgadmin.browser.server_groups.servers.types import ServerType
self.ver = data.get('ver', None)
self.sversion = data.get('sversion', None)
if self.ver and not self.server_type:
for st in ServerType.types():
if st.instanceOf(self.ver):
self.server_type = st.stype
self.server_cls = st
break
# Hmm.. we will not honour this request, when I already have
# connections
if len(self.connections) != 0:
return
# We need to know about the existing server variant supports during
# first connection for identifications.
self.pinged = datetime.datetime.now()
try:
if 'password' in data and data['password']:
data['password'] = data['password'].encode('utf-8')
if 'tunnel_password' in data and data['tunnel_password']:
data['tunnel_password'] = \
data['tunnel_password'].encode('utf-8')
except Exception as e:
current_app.logger.exception(e)
connections = data['connections']
for conn_id in connections:
conn_info = connections[conn_id]
conn = self.connections[conn_info['conn_id']] = Connection(
self, conn_info['conn_id'], conn_info['database'],
conn_info['auto_reconnect'], conn_info['async_'],
use_binary_placeholder=conn_info['use_binary_placeholder'],
array_to_string=conn_info['array_to_string']
)
# only try to reconnect if connection was connected previously and
# auto_reconnect is true.
if conn_info['wasConnected'] and conn_info['auto_reconnect']:
try:
# Check SSH Tunnel needs to be created
if self.use_ssh_tunnel == 1 and not self.tunnel_created:
status, error = self.create_ssh_tunnel(
data['tunnel_password'])
# Check SSH Tunnel is alive or not.
self.check_ssh_tunnel_alive()
conn.connect(
password=data['password'],
server_types=ServerType.types()
)
# This will also update wasConnected flag in connection so
# no need to update the flag manually.
except Exception as e:
current_app.logger.exception(e)
self.connections.pop(conn_info['conn_id'])
def release(self, database=None, conn_id=None, did=None):
# Stop the SSH tunnel if release() function calls without
# any parameter.
if database is None and conn_id is None and did is None:
self.stop_ssh_tunnel()
if did is not None:
if did in self.db_info and 'datname' in self.db_info[did]:
database = self.db_info[did]['datname']
if hasattr(str, 'decode') and \
not isinstance(database, unicode):
database = database.decode('utf-8')
if database is None:
return False
else:
return False
my_id = (u'CONN:{0}'.format(conn_id)) if conn_id is not None else \
(u'DB:{0}'.format(database)) if database is not None else None
if my_id is not None:
if my_id in self.connections:
self.connections[my_id]._release()
del self.connections[my_id]
if did is not None:
del self.db_info[did]
if len(self.connections) == 0:
self.ver = None
self.sversion = None
self.server_type = None
self.server_cls = None
self.password = None
self.update_session()
return True
else:
return False
for con in self.connections:
self.connections[con]._release()
self.connections = dict()
self.ver = None
self.sversion = None
self.server_type = None
self.server_cls = None
self.password = None
self.update_session()
return True
def _update_password(self, passwd):
self.password = passwd
for conn_id in self.connections:
conn = self.connections[conn_id]
if conn.conn is not None or conn.wasConnected is True:
conn.password = passwd
def update_session(self):
managers = session['__pgsql_server_managers'] \
if '__pgsql_server_managers' in session else dict()
updated_mgr = self.as_dict()
if not updated_mgr:
if self.sid in managers:
managers.pop(self.sid)
else:
managers[self.sid] = updated_mgr
session['__pgsql_server_managers'] = managers
session.force_write = True
def utility(self, operation):
"""
utility(operation)
Returns: name of the utility which used for the operation
"""
if self.server_cls is not None:
return self.server_cls.utility(operation, self.sversion)
return None
def export_password_env(self, env):
if self.password:
password = decrypt(
self.password, current_user.password
).decode()
os.environ[str(env)] = password
def create_ssh_tunnel(self, tunnel_password):
"""
This method is used to create ssh tunnel and update the IP Address and
IP Address and port to localhost and the local bind port return by the
SSHTunnelForwarder class.
:return: True if tunnel is successfully created else error message.
"""
# Fetch Logged in User Details.
user = User.query.filter_by(id=current_user.id).first()
if user is None:
return False, gettext("Unauthorized request.")
if tunnel_password is not None and tunnel_password != '':
try:
tunnel_password = decrypt(tunnel_password, user.password)
# Handling of non ascii password (Python2)
if hasattr(str, 'decode'):
tunnel_password = \
tunnel_password.decode('utf-8').encode('utf-8')
# password is in bytes, for python3 we need it in string
elif isinstance(tunnel_password, bytes):
tunnel_password = tunnel_password.decode()
except Exception as e:
current_app.logger.exception(e)
return False, "Failed to decrypt the SSH tunnel " \
"password.\nError: {0}".format(str(e))
try:
# If authentication method is 1 then it uses identity file
# and password
if self.tunnel_authentication == 1:
self.tunnel_object = SSHTunnelForwarder(
(self.tunnel_host, int(self.tunnel_port)),
ssh_username=self.tunnel_username,
ssh_pkey=get_complete_file_path(self.tunnel_identity_file),
ssh_private_key_password=tunnel_password,
remote_bind_address=(self.host, self.port)
)
else:
self.tunnel_object = SSHTunnelForwarder(
(self.tunnel_host, int(self.tunnel_port)),
ssh_username=self.tunnel_username,
ssh_password=tunnel_password,
remote_bind_address=(self.host, self.port)
)
self.tunnel_object.start()
self.tunnel_created = True
except BaseSSHTunnelForwarderError as e:
current_app.logger.exception(e)
return False, "Failed to create the SSH tunnel." \
"\nError: {0}".format(str(e))
# Update the port to communicate locally
self.local_bind_port = self.tunnel_object.local_bind_port
return True, None
def check_ssh_tunnel_alive(self):
# Check SSH Tunnel is alive or not. if it is not then
# raise the ConnectionLost exception.
if self.tunnel_object is None or not self.tunnel_object.is_active:
self.tunnel_created = False
raise SSHTunnelConnectionLost(self.tunnel_host)
def stop_ssh_tunnel(self):
# Stop the SSH tunnel if created.
if self.tunnel_object and self.tunnel_object.is_active:
self.tunnel_object.stop()
self.local_bind_port = None
self.tunnel_object = None
self.tunnel_created = False
def __init__(self, ssh_address, ssh_port, ssh_username, ssh_password, remote_bind_address, remote_bind_port):
self.server = SSHTunnelForwarder(ssh_address=(ssh_address, ssh_port), ssh_username=ssh_username,
ssh_password=ssh_password, remote_bind_address=(remote_bind_address, remote_bind_port))
class StorageDB:
_db_connection = None
_db_cur = None
def __init__(self):
ssh_tunnel = Credentials.ssh_tunnel
db_config = Credentials.vicnode_db
self._server = SSHTunnelForwarder(
((ssh_tunnel['host']), (int(ssh_tunnel['port']))),
ssh_password=ssh_tunnel['ssh_password'],
ssh_username=(ssh_tunnel['username']),
ssh_pkey=(ssh_tunnel['private_key_file']),
remote_bind_address=(db_config['host'], 5432),
allow_agent=False
)
self._server.start()
# we are about to bind to a 'local' server by means of an ssh tunnel
# ssh tunnel: which will be seen as a local server...
# so replace the loaded config host
db_config['host'] = 'localhost'
db_config['port'] = self._server.local_bind_port
self._db_connection = psycopg2.connect(**db_config)
self._db_cur = self._db_connection.cursor(
cursor_factory=psycopg2.extras.RealDictCursor)
self.test_connection()
def __enter__(self):
return self
def __exit__(self, type, value, traceback):
self.close_connection()
def __del__(self):
self.close_connection()
def close_connection(self):
if self._server:
self._db_connection.close()
self._server.stop()
self._server = None
def test_connection(self):
self._db_cur.execute("SELECT * FROM applications_suborganization;")
rows = self._db_cur.fetchall()
def get_contacts(self):
"""
Returns: every project name and it's chief investigator (can be more
than one).
"""
query = """
SELECT
collection.id,
contacts_contact.first_name,
contacts_contact.last_name,
contacts_contact.email_address,
contacts_contact.business_email_address
FROM applications_project AS collection
LEFT JOIN applications_custodian
ON collection_id = collection.id
LEFT JOIN contacts_contact
ON applications_custodian.person_id = contacts_contact.id
WHERE applications_custodian.role_id = 293
ORDER BY id;
"""
self._db_cur.execute(query)
return self._db_cur.fetchall()
def get_sub_organizations(self):
"""
Returns: all the projects that have a suborganization
"""
query = """
SELECT
applications_allocation.collection_id AS project_id,
CASE
WHEN applications_suborganization.id = 1
THEN 'ABP'
WHEN applications_suborganization.id = 2
THEN 'FBE'
WHEN applications_suborganization.id = 3
THEN 'FoA'
WHEN applications_suborganization.id = 4
THEN 'MGSE'
WHEN applications_suborganization.id = 5
THEN 'MSE'
WHEN applications_suborganization.id = 6
THEN 'MLS'
WHEN applications_suborganization.id = 7
THEN 'MDHS'
WHEN applications_suborganization.id = 8
THEN 'FoS'
WHEN applications_suborganization.id = 9
THEN 'VAS'
WHEN applications_suborganization.id = 10
THEN 'VCAMCM'
WHEN applications_suborganization.id = 11
THEN 'Services'
ELSE 'Unknown' END AS faculty
FROM applications_allocation
LEFT JOIN applications_request
ON applications_allocation.application_id = applications_request.id
LEFT JOIN applications_suborganization
ON applications_request.institution_faculty_id =
applications_suborganization.id
WHERE institution_id = 2
GROUP BY collection_id, faculty
ORDER BY collection_id;
"""
self._db_cur.execute(query)
return self._db_cur.fetchall()
def get_allocated(self, year_wanted):
"""
Returns:
"""
query = """
SELECT
collection_id,
name,
sum(size) AS allocated
FROM applications_allocation
LEFT JOIN applications_request
ON applications_allocation.application_id = applications_request.id
LEFT JOIN applications_project
ON applications_allocation.collection_id = applications_project.id
WHERE storage_product_id IN (1, 4, 10, 23, 24)
AND COALESCE(applications_allocation.creation_date, '2014-11-14' :: DATE) < %(day_date)s :: DATE
AND applications_request.institution_id = 2
GROUP BY collection_id, name
ORDER BY collection_id;
"""
self._db_cur.execute(query, {
'day_date': '%s-12-31' % year_wanted
})
return self._db_cur.fetchall()
def get_used(self, year_wanted, collection_id):
query = """
SELECT
-- lt.max_date,
-- lt.storage_product_id,
SUM(rt.used_capacity) AS used
FROM
(
-- get the max date for each product type
SELECT
MAX(extraction_date) AS max_date,
storage_product_id
FROM applications_ingest
WHERE collection_id =%(collection_id)s
AND extraction_date < %(day_date)s :: DATE
GROUP BY storage_product_id
) lt
INNER JOIN
(
-- get the max date for each change in the used capacity of each
-- product type
SELECT
MAX(extraction_date) AS max_date,
used_capacity
FROM applications_ingest
WHERE collection_id = %(collection_id)s
AND extraction_date < %(day_date)s :: DATE
GROUP BY used_capacity
) rt
ON lt.max_date = rt.max_date;
"""
self._db_cur.execute(query, {
'day_date': '%s-12-31' % year_wanted,
'collection_id': collection_id
})
return self._db_cur.fetchone()
from math import log
from sshtunnel import SSHTunnelForwarder # for SSH connection
import pymysql.cursors # MySQL handling API
import sys
sys.path.append("./configs/")
import server_config # (1) info2_server (2) exploration_db
if __name__ == "__main__":
# READ DATA FROM SERVER
#read_Data_from_Server()
# Server connection
server = SSHTunnelForwarder(
(server_config.info2_server['host'], 22),
ssh_username=server_config.info2_server['user'],
ssh_password=server_config.info2_server['password'],
remote_bind_address=('127.0.0.1', 3306))
server.start()
connection = pymysql.connect(host='127.0.0.1',
port=server.local_bind_port,
user=server_config.exploration_db['user'],
password=server_config.exploration_db['password'],
db=server_config.exploration_db['database'])
connection.autocommit(True)
cursor = connection.cursor()
print("MySQL connection established.")
# Get the participants list from the table of 'final_participants'
config.set('SSH', 'Address', '127.0.0.1')
config.set('SSH', 'Enable', 'true')
with open('config.cfg', 'w') as configfile:
config.write(configfile)
print('Please fill out config.cfg, then restart the program.')
exit(1)
ser = serial.Serial(config.get('Serial', 'Port'),
config.getint('Serial', 'Baud'))
forwarder = SSHTunnelForwarder(
config.get('SSH', 'Address'),
ssh_username=config.get('SSH', 'Username'),
ssh_password=config.get('SSH', 'Password'),
remote_bind_address=('127.0.0.1', config.getint('SSH', 'ServerPort')),
local_bind_address=('127.0.0.1', config.getint('SSH', 'ClientPort')),
set_keepalive=5
)
if config.getboolean('SSH', 'Enable'):
forwarder.start()
print('[SSH] Opened SSH tunnel with %s:%d' % (forwarder.ssh_host, forwarder.ssh_port))
engine = create_engine(
config.get('Database', 'ConnectionString'),
pool_recycle=3600,
pool_size=5
)
Base = declarative_base()
