def panel(trans_id):
"""
Return panel template for PSQL tools.
:param trans_id:
"""
params = {'trans_id': trans_id, 'title': request.form['title']}
if 'sid_soid_mapping' not in app.config:
app.config['sid_soid_mapping'] = dict()
if request.args:
params.update({k: v for k, v in request.args.items()})
# Set TERM env for xterm.
os.environ['TERM'] = 'xterm'
# If psql is enabled in server mode, set psqlrc and hist paths
# to individual user storage.
if config.ENABLE_PSQL and config.SERVER_MODE:
os.environ['PSQLRC'] = get_complete_file_path('.psqlrc', False)
os.environ['PSQL_HISTORY'] = \
get_complete_file_path('.psql_history', False)
o_db_name = _get_database(params['sid'], params['did'])
return render_template(
'editor_template.html',
sid=params['sid'],
db=underscore_unescape(o_db_name) if o_db_name else 'postgres',
server_type=params['server_type'],
is_enable=config.ENABLE_PSQL,
title=underscore_unescape(params['title']),
theme=params['theme'],
o_db_name=o_db_name,
platform=_platform)
def get_conn_str_win(manager, db):
"""
Get connection attributes for psql connection.
:param manager:
:param db:
:return:
"""
manager.export_password_env('PGPASSWORD')
db = db.replace('"', '\\"')
db = db.replace("'", "\\'")
conn_attr =\
'host=\'{0}\' port=\'{1}\' dbname=\'{2}\' user=\'{3}\' ' \
'sslmode=\'{4}\' sslcompression=\'{5}\' ' \
''.format(
manager.local_bind_host if manager.use_ssh_tunnel else
manager.host,
manager.local_bind_port if manager.use_ssh_tunnel else
manager.port,
db if db != '' else 'postgres',
underscore_unescape(manager.user) if manager.user else 'postgres',
manager.ssl_mode,
True if manager.sslcompression else False,
)
if manager.hostaddr:
conn_attr = " {0} hostaddr='{1}'".format(conn_attr, manager.hostaddr)
if manager.passfile:
conn_attr = " {0} passfile='{1}'".format(
conn_attr, get_complete_file_path(manager.passfile))
if get_complete_file_path(manager.sslcert):
conn_attr = " {0} sslcert='{1}'".format(
conn_attr, get_complete_file_path(manager.sslcert))
if get_complete_file_path(manager.sslkey):
conn_attr = " {0} sslkey='{1}'".format(
conn_attr, get_complete_file_path(manager.sslkey))
if get_complete_file_path(manager.sslrootcert):
conn_attr = " {0} sslrootcert='{1}'".format(
conn_attr, get_complete_file_path(manager.sslrootcert))
if get_complete_file_path(manager.sslcrl):
conn_attr = " {0} sslcrl='{1}'".format(
conn_attr, get_complete_file_path(manager.sslcrl))
if manager.service:
conn_attr = " {0} service='{1}'".format(
conn_attr, get_complete_file_path(manager.service))
return conn_attr
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 != '':
crypt_key_present, crypt_key = get_crypt_key()
if not crypt_key_present:
raise CryptKeyMissing()
try:
tunnel_password = decrypt(tunnel_password, crypt_key)
# 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 current_storage_dir(self):
if config.SERVER_MODE:
file = self.bfile
try:
# check if file name is encoded with UTF-8
file = self.bfile.decode('utf-8')
except Exception:
# do nothing if bfile is not encoded.
pass
path = get_complete_file_path(file)
path = file if path is None else path
if IS_WIN:
path = os.path.realpath(path)
storage_directory = os.path.basename(get_storage_directory())
if storage_directory in path:
start = path.index(storage_directory)
end = start + (len(storage_directory))
last_dir = os.path.dirname(path[end:])
else:
last_dir = file
last_dir = replace_path_for_win(last_dir)
return None if hasattr(self, 'is_import') and self.is_import \
else last_dir
return None
def get_connection(self, connection_details):
"""
This function is used to connect to DB and returns the publications
:param connection_details:
:return: publication list
"""
passfile = connection_details['passfile'] if \
'passfile' in connection_details and \
connection_details['passfile'] != '' else None
conn = psycopg2.connect(
host=connection_details['host'],
database=connection_details['db'],
user=connection_details['username'],
password=connection_details['password']
if connection_details['password'] else None,
port=connection_details['port']
if connection_details['port'] else None,
passfile=get_complete_file_path(passfile),
connect_timeout=connection_details['connect_timeout']
if 'connect_timeout' in connection_details
and connection_details['connect_timeout'] else 0)
# create a cursor
cur = conn.cursor()
cur.execute('SELECT pubname from pg_publication')
publications = cur.fetchall()
# Close the connection
conn.close()
return publications
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 set_env_variables(self, server, **kwargs):
"""Set environment variables"""
if server:
# Set SSL related ENV variables
if server.sslcert and server.sslkey and server.sslrootcert:
# SSL environment variables
self.env['PGSSLMODE'] = server.ssl_mode
self.env['PGSSLCERT'] = get_complete_file_path(server.sslcert)
self.env['PGSSLKEY'] = get_complete_file_path(server.sslkey)
self.env['PGSSLROOTCERT'] = get_complete_file_path(
server.sslrootcert)
# Set service name related ENV variable
if server.service:
self.env['PGSERVICE'] = server.service
if 'env' in kwargs:
self.env.update(kwargs['env'])
def set_env_variables(self, server, **kwargs):
"""Set environment variables"""
if server:
# Set SSL related ENV variables
if server.sslcert and server.sslkey and server.sslrootcert:
# SSL environment variables
self.env['PGSSLMODE'] = server.ssl_mode
self.env['PGSSLCERT'] = get_complete_file_path(server.sslcert)
self.env['PGSSLKEY'] = get_complete_file_path(server.sslkey)
self.env['PGSSLROOTCERT'] = get_complete_file_path(
server.sslrootcert
)
# Set service name related ENV variable
if server.service:
self.env['PGSERVICE'] = server.service
if 'env' in kwargs:
self.env.update(kwargs['env'])
def get_connection(self, connection_details):
"""
This function is used to connect to DB and returns the publications
:param connection_details:
:return: publication list
"""
passfile = connection_details['passfile'] if \
'passfile' in connection_details and \
connection_details['passfile'] != '' else None
try:
conn = psycopg2.connect(
host=connection_details['host'],
database=connection_details['db'],
user=connection_details['username'],
password=connection_details['password']
if 'password' in connection_details else None,
port=connection_details['port']
if connection_details['port'] else None,
passfile=get_complete_file_path(passfile),
connect_timeout=connection_details['connect_timeout']
if 'connect_timeout' in connection_details
and connection_details['connect_timeout'] else 0,
sslmode=connection_details['sslmode'],
sslcert=get_complete_file_path(connection_details['sslcert']),
sslkey=get_complete_file_path(connection_details['sslkey']),
sslrootcert=get_complete_file_path(
connection_details['sslrootcert']),
sslcompression=True
if connection_details['sslcompression'] else False,
)
# create a cursor
cur = conn.cursor()
cur.execute('SELECT pubname from pg_catalog.pg_publication')
publications = cur.fetchall()
# Close the connection
conn.close()
return publications, True
except Exception as error:
return error, False