def reencrpyt_server_passwords(user_id, old_key, new_key):
"""
This function will decrypt the saved passwords in SQLite with old key
and then encrypt with new key
"""
from pgadmin.utils.driver import get_driver
driver = get_driver(config.PG_DEFAULT_DRIVER)
for server in Server.query.filter_by(user_id=user_id).all():
manager = driver.connection_manager(server.id)
_password_check(server, manager, old_key, new_key)
if server.tunnel_password is not None:
tunnel_password = decrypt(server.tunnel_password, old_key)
if isinstance(tunnel_password, bytes):
tunnel_password = tunnel_password.decode()
tunnel_password = encrypt(tunnel_password, new_key)
setattr(server, 'tunnel_password', tunnel_password)
manager.tunnel_password = tunnel_password
elif manager.tunnel_password is not None:
tunnel_password = decrypt(manager.tunnel_password, old_key)
if isinstance(tunnel_password, bytes):
tunnel_password = tunnel_password.decode()
tunnel_password = encrypt(tunnel_password, new_key)
manager.tunnel_password = tunnel_password
db.session.commit()
manager.update_session()
def get(self):
host = request.args.get('hostname')
cred_name = request.args.get('cred_name')
import util
cred_info = util.get_credentials_by_name(cred_name)
enc_secret = util.get_value("GLOBAL", "SECRET", "")
enc_key = "{0}{1}".format(enc_secret, cred_info.get("cred_uuid"))
username = cred_info.get("ssh_user")
password = ""
if cred_info.get("ssh_passwd"):
password = decrypt(cred_info.get("ssh_passwd"), enc_key)
ssh_key = ""
if cred_info.get("ssh_key"):
ssh_key = decrypt(cred_info.get("ssh_key"), enc_key)
sudo_pwd = ""
if cred_info.get("ssh_sudo_pwd"):
sudo_pwd = decrypt(cred_info.get("ssh_sudo_pwd"), enc_key)
# username = request.args.get('username')
# password = request.args.get('password')
# ssh_key = request.args.get('ssh_key')
# sudo_pwd = request.args.get('sudo_pwd', None)
from PgcRemote import PgcRemote
json_dict = {}
try:
remote = PgcRemote(host,
username,
password=password,
ssh_key=ssh_key,
sudo_pwd=sudo_pwd)
if not sudo_pwd:
remote.connect()
json_dict['state'] = "success"
try:
remote_pgc_path = remote.get_exixting_pgc_path()
for key in remote_pgc_path.keys():
json_dict[key] = remote_pgc_path[key]
except Exception as e:
print(str(e))
pass
data = json.dumps([json_dict])
remote.disconnect()
except Exception as e:
errmsg = "ERROR: Cannot connect to " + username + "@" + host + " - " + str(
e)
json_dict['state'] = "error"
json_dict['msg'] = errmsg
data = json.dumps([json_dict])
return data
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 export_password_env(self, env):
if self.password:
crypt_key_present, crypt_key = get_crypt_key()
if not crypt_key_present:
return False, crypt_key
password = decrypt(self.password, crypt_key).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 _password_check(server, manager, old_key, new_key):
# Check if old password was stored in pgadmin4 sqlite database.
# If yes then update that password.
if server.password is not None:
password = decrypt(server.password, old_key)
if isinstance(password, bytes):
password = password.decode()
password = encrypt(password, new_key)
setattr(server, 'password', password)
manager.password = password
def get(self, host, comp, pgpasswd, username=None, password=None):
from PgcRemote import PgcRemote
json_dict = {}
if password == None or username == None:
import util
pgc_host_info = util.get_pgc_host(host)
ssh_host = pgc_host_info.get('host')
ssh_host_name = pgc_host_info.get('host_name')
ssh_cred_id = pgc_host_info.get('ssh_cred_id')
cred_info = util.get_credentials_by_uuid(ssh_cred_id)
enc_secret = util.get_value("GLOBAL", "SECRET", "")
enc_key = "{0}{1}".format(enc_secret, cred_info.get("cred_uuid"))
ssh_username = cred_info.get("ssh_user")
password = ""
if cred_info.get("ssh_passwd"):
ssh_password = decrypt(cred_info.get("ssh_passwd"), enc_key)
ssh_key = ""
if cred_info.get("ssh_key"):
ssh_key = decrypt(cred_info.get("ssh_key"), enc_key)
sudo_pwd = ""
if cred_info.get("ssh_sudo_pwd"):
sudo_pwd = decrypt(cred_info.get("ssh_sudo_pwd"), enc_key)
is_sudo = pgc_host_info.get('is_sudo')
util.update_cred_used(cred_info.get("cred_uuid"))
try:
remote = PgcRemote(ssh_host,
ssh_username,
password=ssh_password,
ssh_key=ssh_key)
remote.connect()
is_file_added = remote.add_file('/tmp/.pgpass', pgpasswd)
remote.disconnect()
data = pgc.get_data("init", comp, ssh_host_name, '/tmp/.pgpass')
except Exception as e:
errmsg = "ERROR: Cannot connect to " + ssh_username + "@" + ssh_host + " - " + str(
e.args[0])
json_dict['state'] = "error"
json_dict['msg'] = errmsg
data = json.dumps([json_dict])
return data
def get(self):
host = request.args.get('hostname')
check_sudo_password = request.args.get('pwd')
pgc_host_info = util.get_pgc_host(host)
pgc_host = pgc_host_info.get('host')
ssh_cred_id = pgc_host_info.get('ssh_cred_id')
cred_info = util.get_credentials_by_uuid(ssh_cred_id)
enc_secret = util.get_value("GLOBAL", "SECRET", "")
enc_key = "{0}{1}".format(enc_secret, cred_info.get("cred_uuid"))
pgc_user = cred_info.get("ssh_user")
pgc_passwd = ""
if cred_info.get("ssh_passwd"):
pgc_passwd = decrypt(cred_info.get("ssh_passwd"), enc_key)
pgc_ssh_key = ""
if cred_info.get("ssh_key"):
pgc_ssh_key = decrypt(cred_info.get("ssh_key"), enc_key)
util.update_cred_used(cred_info.get("cred_uuid"))
from PgcRemote import PgcRemote
json_dict = {}
try:
remote = PgcRemote(pgc_host,
pgc_user,
password=pgc_passwd,
ssh_key=pgc_ssh_key,
sudo_pwd=check_sudo_password)
remote.connect()
is_sudo = remote.has_root_access()
json_dict['state'] = "success"
json_dict['isSudo'] = is_sudo
data = json.dumps([json_dict])
remote.disconnect()
except Exception as e:
errmsg = "ERROR: Cannot connect to " + username + "@" + host + " - " + str(
e)
json_dict['state'] = "error"
json_dict['msg'] = errmsg
data = json.dumps([json_dict])
return data
def connect(self, **kwargs):
if self.conn:
if self.conn.closed:
self.conn = None
else:
return True, None
pg_conn = None
password = None
mgr = self.manager
if 'password' in kwargs:
encpass = kwargs['password']
else:
encpass = getattr(mgr, 'password', None)
if encpass:
# Fetch Logged in User Details.
user = User.query.filter_by(id=current_user.id).first()
if user is None:
return False, gettext("Unauthorized Request.")
try:
password = decrypt(encpass, user.password)
except Exception as e:
current_app.logger.exception(e)
return False, \
_("Failed to decrypt the saved password!\nError: {0}").format(
str(e)
)
# password is in bytes, for python3 we need it in string
if isinstance(password, bytes):
password = password.decode()
try:
import os
os.environ['PGAPPNAME'] = '{0} - {1}'.format(
config.APP_NAME, self.conn_id)
pg_conn = psycopg2.connect(host=mgr.host,
port=mgr.port,
database=self.db,
user=mgr.user,
password=password,
async=self. async)
def validate_master_password(password):
"""
Validate the password/key against the stored encrypted text
:param password: password/key
:return: Valid or not
"""
# master pass is incorrect if decryption fails
try:
decrypted_text = decrypt(current_user.masterpass_check, password)
if isinstance(decrypted_text, bytes):
decrypted_text = decrypted_text.decode()
if MASTERPASS_CHECK_TEXT != decrypted_text:
return False
else:
return True
except Exception as _:
False
def reset(self):
if self.conn:
if self.conn.closed:
self.conn = None
pg_conn = None
mgr = self.manager
password = getattr(mgr, 'password', None)
if password:
# Fetch Logged in User Details.
user = User.query.filter_by(id=current_user.id).first()
if user is None:
return False, gettext("Unauthorized Request.")
password = decrypt(password, user.password).decode()
try:
pg_conn = psycopg2.connect(host=mgr.host,
port=mgr.port,
database=self.db,
user=mgr.user,
password=password)
except psycopg2.Error as e:
msg = e.pgerror if e.pgerror else e.message \
if e.message else e.diag.message_detail \
if e.diag.message_detail else str(e)
current_app.logger.error(
gettext("""
Failed to reset the connection of the server due to following error:
{0}""").Format(msg))
return False, msg
self.conn = pg_conn
self.__backend_pid = pg_conn.get_backend_pid()
return True, None
def change_password(self, gid, sid):
"""
This function is used to change the password of the
Database Server.
Args:
gid: Group id
sid: Server id
"""
try:
data = json.loads(request.form['data'])
if data and ('password' not in data or
data['password'] == '' or
'newPassword' not in data or
data['newPassword'] == '' or
'confirmPassword' not in data or
data['confirmPassword'] == ''):
return make_json_response(
status=400,
success=0,
errormsg=gettext(
"Couldn't find the required parameter(s)."
)
)
if data['newPassword'] != data['confirmPassword']:
return make_json_response(
status=200,
success=0,
errormsg=gettext(
"Passwords do not match."
)
)
# Fetch Server Details
server = Server.query.filter_by(id=sid).first()
if server is None:
return bad_request(gettext("Server not found."))
# Fetch User Details.
user = User.query.filter_by(id=current_user.id).first()
if user is None:
return unauthorized(gettext("Unauthorized request."))
from pgadmin.utils.driver import get_driver
manager = get_driver(PG_DEFAULT_DRIVER).connection_manager(sid)
conn = manager.connection()
decrypted_password = decrypt(manager.password, user.password)
if isinstance(decrypted_password, bytes):
decrypted_password = decrypted_password.decode()
password = data['password']
# Validate old password before setting new.
if password != decrypted_password:
return unauthorized(gettext("Incorrect password."))
# Hash new password before saving it.
password = pqencryptpassword(data['newPassword'], manager.user)
SQL = render_template("/".join([
'servers/sql',
'9.2_plus' if manager.version >= 90200 else '9.1_plus',
'change_password.sql'
]),
conn=conn, _=gettext,
user=manager.user, encrypted_password=password)
status, res = conn.execute_scalar(SQL)
if not status:
return internal_server_error(errormsg=res)
password = encrypt(data['newPassword'], user.password)
# Check if old password was stored in pgadmin4 sqlite database.
# If yes then update that password.
if server.password is not None:
setattr(server, 'password', password)
db.session.commit()
# Also update password in connection manager.
manager.password = password
manager.update_session()
return make_json_response(
status=200,
success=1,
info=gettext(
"Password changed successfully."
)
)
except Exception as e:
return internal_server_error(errormsg=str(e))
def change_password(self, gid, sid):
"""
This function is used to change the password of the
Database Server.
Args:
gid: Group id
sid: Server id
"""
try:
data = json.loads(request.form['data'], encoding='utf-8')
if data and ('password' not in data or data['password'] == '' or
'newPassword' not in data or data['newPassword'] == ''
or 'confirmPassword' not in data
or data['confirmPassword'] == ''):
return make_json_response(
status=400,
success=0,
errormsg=gettext(
"Could not find the required parameter(s)."))
if data['newPassword'] != data['confirmPassword']:
return make_json_response(
status=200,
success=0,
errormsg=gettext("Passwords do not match."))
# Fetch Server Details
server = Server.query.filter_by(id=sid).first()
if server is None:
return bad_request(gettext("Server not found."))
# Fetch User Details.
user = User.query.filter_by(id=current_user.id).first()
if user is None:
return unauthorized(gettext("Unauthorized request."))
manager = get_driver(PG_DEFAULT_DRIVER).connection_manager(sid)
conn = manager.connection()
decrypted_password = decrypt(manager.password, user.password)
if isinstance(decrypted_password, bytes):
decrypted_password = decrypted_password.decode()
password = data['password']
# Validate old password before setting new.
if password != decrypted_password:
return unauthorized(gettext("Incorrect password."))
# Hash new password before saving it.
password = pqencryptpassword(data['newPassword'], manager.user)
SQL = render_template(
"/servers/sql/#{0}#/change_password.sql".format(
manager.version),
conn=conn,
_=gettext,
user=manager.user,
encrypted_password=password)
status, res = conn.execute_scalar(SQL)
if not status:
return internal_server_error(errormsg=res)
password = encrypt(data['newPassword'], user.password)
# Check if old password was stored in pgadmin4 sqlite database.
# If yes then update that password.
if server.password is not None and config.ALLOW_SAVE_PASSWORD:
setattr(server, 'password', password)
db.session.commit()
# Also update password in connection manager.
manager.password = password
manager.update_session()
return make_json_response(
status=200,
success=1,
info=gettext("Password changed successfully."))
except Exception as e:
return internal_server_error(errormsg=str(e))
def export_password_env(self, env):
if self.password:
password = decrypt(
self.password, current_user.password
).decode()
os.environ[str(env)] = password
def export_password_env(self, env):
if self.password:
password = decrypt(
self.password, current_user.password
).decode()
os.environ[str(env)] = password
def cancel_transaction(self, conn_id, did=None):
"""
This function is used to cancel the running transaction
of the given connection id and database id using
PostgreSQL's pg_cancel_backend.
Args:
conn_id: Connection id
did: Database id (optional)
"""
cancel_conn = self.manager.connection(did=did, conn_id=conn_id)
query = """SELECT pg_cancel_backend({0});""".format(
cancel_conn.__backend_pid)
status = True
msg = ''
# if backend pid is same then create a new connection
# to cancel the query and release it.
if cancel_conn.__backend_pid == self.__backend_pid:
password = getattr(self.manager, 'password', None)
if password:
# Fetch Logged in User Details.
user = User.query.filter_by(id=current_user.id).first()
if user is None:
return False, gettext("Unauthorized Request.")
password = decrypt(password, user.password).decode()
try:
pg_conn = psycopg2.connect(host=self.manager.host,
port=self.manager.port,
database=self.db,
user=self.manager.user,
password=password)
# Get the cursor and run the query
cur = pg_conn.cursor()
cur.execute(query)
# Close the connection
pg_conn.close()
pg_conn = None
except psycopg2.Error as e:
status = False
if e.pgerror:
msg = e.pgerror
elif e.diag.message_detail:
msg = e.diag.message_detail
else:
msg = str(e)
return status, msg
else:
if self.connected():
status, msg = self.execute_void(query)
if status:
cancel_conn.execution_aborted = True
else:
status = False
msg = gettext("Not connected to the database server.")
return status, msg
