codekingpro/portable-devtools
114k
1##########################################################################2#3# pgAdmin 4 - PostgreSQL Tools4#5# Copyright (C) 2013 - 2024, The pgAdmin Development Team6# This software is released under the PostgreSQL Licence7#8##########################################################################9 10"""11Implementation of ServerManager12"""13import os14import datetime15import config16import logging17from flask import current_app, session18from flask_security import current_user19from flask_babel import gettext20from werkzeug.exceptions import InternalServerError21 22from pgadmin.utils import get_complete_file_path23from pgadmin.utils.crypto import decrypt24from pgadmin.utils.master_password import process_masterpass_disabled25from .connection import Connection26from pgadmin.model import Server, User27from pgadmin.utils.exception import ConnectionLost, SSHTunnelConnectionLost,\28 CryptKeyMissing29from pgadmin.utils.master_password import get_crypt_key30from pgadmin.utils.exception import ObjectGone31from pgadmin.utils.passexec import PasswordExec32from psycopg.conninfo import make_conninfo33 34if config.SUPPORT_SSH_TUNNEL:35 from sshtunnel import SSHTunnelForwarder, BaseSSHTunnelForwarderError36 37CONN_STRING = 'CONN:{0}'38DB_STRING = 'DB:{0}'39 40 41class ServerManager(object):42 """43 class ServerManager44 45 This class contains the information about the given server.46 And, acts as connection manager for that particular session.47 """48 _INFORMATION_MSG = gettext("Information is not available.")49 50 def __init__(self, server):51 self.connections = dict()52 self.local_bind_host = '127.0.0.1'53 self.local_bind_port = None54 self.tunnel_object = None55 self.tunnel_created = False56 self.display_connection_string = ''57 58 self.update(server)59 60 def update(self, server):61 assert (server is not None)62 assert (isinstance(server, Server))63 64 self.ver = None65 self.sversion = None66 self.server_type = None67 self.server_cls = None68 self.password = None69 self.tunnel_password = None70 71 self.sid = server.id72 self.host = server.host73 self.port = server.port74 self.db = server.maintenance_db75 self.shared = server.shared76 self.did = None77 self.user = server.username78 self.password = server.password79 self.role = server.role80 self.pinged = datetime.datetime.now()81 self.db_info = dict()82 self.server_types = None83 self.db_res = server.db_res84 self.name = server.name85 self.passexec = \86 PasswordExec(server.passexec_cmd, server.passexec_expiration) \87 if server.passexec_cmd else None88 self.service = server.service89 90 if config.SUPPORT_SSH_TUNNEL:91 self.use_ssh_tunnel = server.use_ssh_tunnel92 self.tunnel_host = server.tunnel_host93 self.tunnel_port = \94 22 if server.tunnel_port is None else server.tunnel_port95 self.tunnel_username = server.tunnel_username96 self.tunnel_authentication = 0 \97 if server.tunnel_authentication is None \98 else server.tunnel_authentication99 self.tunnel_identity_file = server.tunnel_identity_file100 self.tunnel_password = server.tunnel_password101 self.tunnel_keep_alive = server.tunnel_keep_alive102 else:103 self.use_ssh_tunnel = 0104 self.tunnel_host = None105 self.tunnel_port = 22106 self.tunnel_username = None107 self.tunnel_authentication = None108 self.tunnel_identity_file = None109 self.tunnel_password = None110 self.tunnel_keep_alive = 0111 112 self.kerberos_conn = server.kerberos_conn113 self.gss_authenticated = False114 self.gss_encrypted = False115 self.connection_params = server.connection_params116 self.create_connection_string(self.db, self.user)117 self.prepare_threshold = server.prepare_threshold118 119 for con in self.connections:120 self.connections[con]._release()121 122 self.update_session()123 124 self.connections = dict()125 126 def _set_password(self, res):127 """128 Set password for server manager object.129 :param res: response dict.130 :return:131 """132 if hasattr(self, 'password') and self.password:133 if hasattr(self.password, 'decode'):134 res['password'] = self.password.decode('utf-8')135 else:136 res['password'] = str(self.password)137 else:138 res['password'] = self.password139 140 def as_dict(self):141 """142 Returns a dictionary object representing the server manager.143 """144 if self.ver is None or len(self.connections) == 0:145 return None146 147 res = dict()148 res['sid'] = self.sid149 res['ver'] = self.ver150 res['sversion'] = self.sversion151 152 self._set_password(res)153 154 if self.use_ssh_tunnel:155 if hasattr(self, 'tunnel_password') and self.tunnel_password:156 if hasattr(self.tunnel_password, 'decode'):157 res['tunnel_password'] = \158 self.tunnel_password.decode('utf-8')159 else:160 res['tunnel_password'] = str(self.tunnel_password)161 else:162 res['tunnel_password'] = self.tunnel_password163 164 connections = res['connections'] = dict()165 166 for conn_id in self.connections:167 conn = self.connections[conn_id].as_dict()168 169 if conn is not None:170 connections[conn_id] = conn171 172 return res173 174 def server_version(self):175 return self.ver176 177 @property178 def version(self):179 return self.sversion180 181 def major_version(self):182 if self.sversion is not None:183 return int(self.sversion / 10000)184 raise InternalServerError(self._INFORMATION_MSG)185 186 def minor_version(self):187 if self.sversion:188 return int(int(self.sversion / 100) % 100)189 raise InternalServerError(self._INFORMATION_MSG)190 191 def patch_version(self):192 if self.sversion:193 return int(int(self.sversion / 100) / 100)194 raise InternalServerError(self._INFORMATION_MSG)195 196 def connection(self, **kwargs):197 database = kwargs.get('database', None)198 conn_id = kwargs.get('conn_id', None)199 auto_reconnect = kwargs.get('auto_reconnect', True)200 did = kwargs.get('did', None)201 async_ = kwargs.get('async_', None)202 use_binary_placeholder = kwargs.get('use_binary_placeholder', False)203 array_to_string = kwargs.get('array_to_string', False)204 205 if database is not None:206 if did is not None and did in self.db_info:207 self.db_info[did]['datname'] = database208 else:209 conn_str = CONN_STRING.format(conn_id)210 if did is None:211 database = self.db212 elif did in self.db_info:213 database = self.db_info[did]['datname']214 elif conn_id and conn_str in self.connections:215 database = self.connections[conn_str].db216 else:217 maintenance_db_id = DB_STRING.format(self.db)218 if maintenance_db_id in self.connections:219 conn = self.connections[maintenance_db_id]220 # try to connect maintenance db if not connected221 if not conn.connected():222 conn.connect()223 224 if conn.connected():225 status, res = conn.execute_dict("""226SELECT227 db.oid as did, db.datname, db.datallowconn,228 pg_catalog.pg_encoding_to_char(db.encoding) AS serverencoding,229 pg_catalog.has_database_privilege(db.oid, 'CREATE') as cancreate,230 datistemplate231FROM232 pg_catalog.pg_database db233WHERE db.oid = {0}""".format(did))234 235 if status and len(res['rows']) > 0:236 for row in res['rows']:237 self.db_info[did] = row238 database = self.db_info[did]['datname']239 240 if did not in self.db_info:241 raise ObjectGone(gettext(242 "Could not find the specified database."243 ))244 245 if not get_crypt_key()[0] and (246 config.SERVER_MODE or config.DISABLED_LOCAL_PASSWORD_STORAGE):247 # the reason its not connected might be missing key248 raise CryptKeyMissing()249 250 if database is None:251 # Check SSH Tunnel is alive or not.252 if self.use_ssh_tunnel == 1:253 self.check_ssh_tunnel_alive()254 else:255 raise ConnectionLost(self.sid, None, None)256 257 my_id = (CONN_STRING.format(conn_id)) if conn_id is not None else \258 (DB_STRING.format(database))259 260 self.pinged = datetime.datetime.now()261 262 if my_id in self.connections:263 return self.connections[my_id]264 else:265 if async_ is None:266 async_ = 1 if conn_id is not None else 0267 else:268 async_ = 1 if async_ is True else 0269 self.connections[my_id] = Connection(270 self, my_id, database, auto_reconnect=auto_reconnect,271 async_=async_,272 use_binary_placeholder=use_binary_placeholder,273 array_to_string=array_to_string274 )275 276 return self.connections[my_id]277 278 @staticmethod279 def _get_password_to_conn(data, masterpass_processed):280 """281 Get password for connect to server with simple and ssh connection.282 :param data: Data.283 :param masterpass_processed:284 :return:285 """286 # The data variable is a copy so is not automatically synced287 # update here288 if masterpass_processed and 'password' in data:289 data['password'] = None290 if masterpass_processed and 'tunnel_password' in data:291 data['tunnel_password'] = None292 293 def _get_server_type(self):294 """295 Get server type and server cls.296 :return:297 """298 from pgadmin.browser.server_groups.servers.types import ServerType299 300 if self.ver and not self.server_type:301 for st in ServerType.types():302 if st.instance_of(self.ver):303 self.server_type = st.stype304 self.server_cls = st305 break306 307 def _check_and_reconnect_server(self, conn, conn_info, data):308 """309 Check and try to reconnect the server if server previously connected310 and auto_reconnect is true.311 :param conn:312 :type conn:313 :param conn_info:314 :type conn_info:315 :param data:316 :type data:317 :return:318 :rtype:319 """320 from pgadmin.browser.server_groups.servers.types import ServerType321 if conn_info['wasConnected'] and conn_info['auto_reconnect']:322 try:323 # Check SSH Tunnel needs to be created324 if self.use_ssh_tunnel == 1 and \325 not self.tunnel_created:326 self.create_ssh_tunnel(data['tunnel_password'])327 328 # Check SSH Tunnel is alive or not.329 self.check_ssh_tunnel_alive()330 331 conn.connect(332 password=data['password'],333 server_types=ServerType.types()334 )335 # This will also update wasConnected flag in336 # connection so no need to update the flag manually.337 except CryptKeyMissing:338 # maintain the status as this will help to restore once339 # the key is available340 conn.wasConnected = conn_info['wasConnected']341 conn.auto_reconnect = conn_info['auto_reconnect']342 except Exception as e:343 current_app.logger.exception(e)344 self.connections.pop(conn_info['conn_id'])345 raise346 347 def _restore(self, data):348 """349 Helps restoring to reconnect the auto-connect connections smoothly on350 reload/restart of the app server..351 """352 # restore server version from flask session if flask server was353 # restarted. As we need server version to resolve sql template paths.354 masterpass_processed = process_masterpass_disabled()355 356 ServerManager._get_password_to_conn(data, masterpass_processed)357 # Get server type.358 self._get_server_type()359 360 # We need to know about the existing server variant supports during361 # first connection for identifications.362 self.pinged = datetime.datetime.now()363 try:364 if 'password' in data and data['password'] and \365 hasattr(data['password'], 'encode'):366 data['password'] = data['password'].encode('utf-8')367 if 'tunnel_password' in data and data['tunnel_password']:368 data['tunnel_password'] = \369 data['tunnel_password'].encode('utf-8')370 except Exception as e:371 current_app.logger.exception(e)372 373 connections = data['connections']374 375 for conn_id in connections:376 conn_info = connections[conn_id]377 if conn_info['conn_id'] in self.connections:378 conn = self.connections[conn_info['conn_id']]379 else:380 conn = self.connections[conn_info['conn_id']] = Connection(381 self, conn_info['conn_id'], conn_info['database'],382 auto_reconnect=conn_info['auto_reconnect'],383 async_=conn_info['async_'],384 use_binary_placeholder=conn_info[385 'use_binary_placeholder'],386 array_to_string=conn_info['array_to_string']387 )388 389 # only try to reconnect390 self._check_and_reconnect_server(conn, conn_info, data)391 392 def _restore_connections(self):393 for conn_id in self.connections:394 conn = self.connections[conn_id]395 # only try to reconnect if connection was connected previously396 # and auto_reconnect is true.397 was_connected = conn.wasConnected398 auto_reconnect = conn.auto_reconnect399 if conn.wasConnected and conn.auto_reconnect:400 try:401 # Check SSH Tunnel needs to be created402 if self.use_ssh_tunnel == 1 and \403 not self.tunnel_created:404 self.create_ssh_tunnel(self.tunnel_password)405 406 # Check SSH Tunnel is alive or not.407 self.check_ssh_tunnel_alive()408 409 conn.connect()410 # This will also update wasConnected flag in411 # connection so no need to update the flag manually.412 except CryptKeyMissing:413 # maintain the status as this will help to restore once414 # the key is available415 conn.wasConnected = was_connected416 conn.auto_reconnect = auto_reconnect417 except Exception as e:418 self.connections.pop(conn_id)419 current_app.logger.exception(e)420 raise421 422 def _stop_ssh_tunnel(self, did, database, conn_id):423 """424 Stop ssh tunnel connection if function call without any parameter.425 :param did: Database Id.426 :param database: Database.427 :param conn_id: COnnection Id.428 :return:429 """430 if database is None and conn_id is None and did is None:431 self.stop_ssh_tunnel()432 433 def _check_db_info(self, did, conn_id, database):434 """435 Check did is not none and it is resent in db_info.436 :param did: Database Id.437 :param conn_id: Connection Id.438 :return:439 """440 if database is None and conn_id is None and did is None:441 self.stop_ssh_tunnel()442 443 my_id = None444 if did is not None:445 if did in self.db_info and 'datname' in self.db_info[did]:446 database = self.db_info[did]['datname']447 if database is None:448 return True, False, my_id449 else:450 return True, False, my_id451 452 if conn_id is not None:453 my_id = CONN_STRING.format(conn_id)454 elif database is not None:455 my_id = DB_STRING.format(database)456 457 return False, True, my_id458 459 def release(self, database=None, conn_id=None, did=None):460 # Stop the SSH tunnel if release() function calls without461 # any parameter.462 is_return, return_value, my_id = self._check_db_info(did, conn_id,463 database)464 if is_return:465 return return_value466 467 if my_id is not None:468 if my_id in self.connections:469 self.connections[my_id]._release()470 del self.connections[my_id]471 if did is not None:472 del self.db_info[did]473 474 if len(self.connections) == 0:475 self.ver = None476 self.sversion = None477 self.server_type = None478 self.server_cls = None479 self.password = None480 481 self.update_session()482 483 return True484 else:485 return False486 487 for con_key in list(self.connections.keys()):488 conn = self.connections[con_key]489 # Cancel the ongoing transaction before closing the connection490 # as it may hang forever491 if conn.connected() and conn.conn_id is not None and \492 conn.conn_id.startswith('CONN:'):493 conn.cancel_transaction(conn.conn_id[5:])494 conn._release()495 496 self.connections = dict()497 self.ver = None498 self.sversion = None499 self.server_type = None500 self.server_cls = None501 self.password = None502 503 self.update_session()504 505 return True506 507 def _update_password(self, passwd):508 self.password = passwd509 for conn_id in self.connections:510 conn = self.connections[conn_id]511 if conn.conn is not None or conn.wasConnected is True:512 conn.password = passwd513 514 def update_session(self):515 managers = session['__pgsql_server_managers'] \516 if '__pgsql_server_managers' in session else dict()517 updated_mgr = self.as_dict()518 519 if not updated_mgr:520 if self.sid in managers:521 managers.pop(self.sid)522 else:523 managers[self.sid] = updated_mgr524 session['__pgsql_server_managers'] = managers525 session.force_write = True526 527 def utility(self, operation):528 """529 utility(operation)530 531 Returns: name of the utility which used for the operation532 """533 if self.server_cls is not None:534 return self.server_cls.utility(operation, self.sversion)535 536 return None537 538 def export_password_env(self, env):539 if self.password:540 if config.DISABLED_LOCAL_PASSWORD_STORAGE:541 crypt_key_present, crypt_key = get_crypt_key()542 if not crypt_key_present:543 return False, crypt_key544 password = decrypt(self.password, crypt_key).decode()545 elif hasattr(self.password, 'decode'):546 password = self.password.decode('utf-8')547 else:548 password = self.password549 550 os.environ[str(env)] = password551 elif self.passexec:552 password = self.passexec.get()553 os.environ[str(env)] = password554 555 def create_ssh_tunnel(self, tunnel_password):556 """557 This method is used to create ssh tunnel and update the IP Address and558 IP Address and port to localhost and the local bind port return by the559 SSHTunnelForwarder class.560 :return: True if tunnel is successfully created else error message.561 """562 # Fetch Logged in User Details.563 user = User.query.filter_by(id=current_user.id).first()564 if user is None:565 return False, gettext("Unauthorized request.")566 567 if tunnel_password is not None and tunnel_password != '':568 if config.DISABLED_LOCAL_PASSWORD_STORAGE:569 crypt_key_present, crypt_key = get_crypt_key()570 if not crypt_key_present:571 raise CryptKeyMissing()572 573 try:574 if config.DISABLED_LOCAL_PASSWORD_STORAGE:575 tunnel_password = decrypt(tunnel_password, crypt_key)576 # password is in bytes, for python3 we need it in string577 if isinstance(tunnel_password, bytes):578 tunnel_password = tunnel_password.decode()579 580 except Exception as e:581 current_app.logger.exception(e)582 return False, gettext("Failed to decrypt the SSH tunnel "583 "password.\nError: {0}").format(str(e))584 585 try:586 # If authentication method is 1 then it uses identity file587 # and password588 ssh_logger = None589 if current_app.debug:590 ssh_logger = logging.getLogger('sshtunnel')591 ssh_logger.setLevel(logging.DEBUG)592 for h in current_app.logger.handlers:593 ssh_logger.addHandler(h)594 if self.tunnel_authentication == 1:595 self.tunnel_object = SSHTunnelForwarder(596 (self.tunnel_host, int(self.tunnel_port)),597 ssh_username=self.tunnel_username,598 ssh_pkey=get_complete_file_path(self.tunnel_identity_file),599 ssh_private_key_password=tunnel_password,600 remote_bind_address=(self.host, self.port),601 logger=ssh_logger,602 set_keepalive=int(self.tunnel_keep_alive)603 )604 else:605 self.tunnel_object = SSHTunnelForwarder(606 (self.tunnel_host, int(self.tunnel_port)),607 ssh_username=self.tunnel_username,608 ssh_password=tunnel_password,609 remote_bind_address=(self.host, self.port),610 logger=ssh_logger,611 set_keepalive=int(self.tunnel_keep_alive)612 )613 # flag tunnel threads in daemon mode to fix hang issue.614 self.tunnel_object.daemon_forward_servers = True615 self.tunnel_object.start()616 self.tunnel_created = True617 except BaseSSHTunnelForwarderError as e:618 current_app.logger.exception(e)619 return False, gettext("Failed to create the SSH tunnel.\n"620 "Error: {0}").format(str(e))621 622 # Update the port to communicate locally623 self.local_bind_port = self.tunnel_object.local_bind_port624 625 return True, None626 627 def check_ssh_tunnel_alive(self):628 # Check SSH Tunnel is alive or not. if it is not then629 # raise the ConnectionLost exception.630 if self.tunnel_object is None or not self.tunnel_object.is_active:631 self.tunnel_created = False632 raise SSHTunnelConnectionLost(self.tunnel_host)633 634 def stop_ssh_tunnel(self):635 # Stop the SSH tunnel if created.636 if self.tunnel_object and self.tunnel_object.is_active:637 self.tunnel_object.stop()638 self.local_bind_port = None639 self.tunnel_object = None640 self.tunnel_created = False641 642 def get_connection_param_value(self, param_name):643 """644 This function return the value of param_name if found in the645 connection parameter.646 """647 value = None648 if self.connection_params and param_name in self.connection_params:649 value = self.connection_params[param_name]650 651 return value652 653 def create_connection_string(self, database, user, password=None):654 """655 This function is used to create connection string based on the656 parameters.657 """658 dsn_args = dict()659 dsn_args['host'] = self.host660 dsn_args['port'] = \661 self.local_bind_port if self.use_ssh_tunnel else self.port662 dsn_args['dbname'] = database663 dsn_args['user'] = user664 if self.service is not None:665 dsn_args['service'] = self.service666 if self.use_ssh_tunnel:667 dsn_args['hostaddr'] = self.local_bind_host668 669 # Make a copy to display the connection string on GUI.670 display_dsn_args = dsn_args.copy()671 # Password should not be visible into the connection string, so672 # setting the value with password to 'xxxxxxx'.673 if password:674 display_dsn_args['password'] = 'xxxxxxx'675 dsn_args['password'] = password676 677 # Loop through all the connection parameters set in the server dialog.678 if self.connection_params and isinstance(self.connection_params, dict):679 for key, value in self.connection_params.items():680 with_complete_path = False681 orig_value = value682 # Getting complete file path if the key is one of the below.683 if key in ['passfile', 'sslcert', 'sslkey', 'sslrootcert',684 'sslcrl', 'sslcrldir']:685 with_complete_path = True686 value = get_complete_file_path(value)687 688 # If key is hostaddr and ssh tunnel is in use don't overwrite.689 if key == 'hostaddr' and self.use_ssh_tunnel:690 continue691 692 dsn_args[key] = value693 display_dsn_args[key] = orig_value if with_complete_path else \694 value695 696 self.display_connection_string = make_conninfo(**display_dsn_args)697 698 return make_conninfo(**dsn_args)699