codekingpro/portable-devtools

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"""Implements Backup Utility"""10 11import json12import copy13import functools14import operator15 16from flask import render_template, request, current_app, \17    url_for, Response18from flask_babel import gettext19from flask_security import current_user20from pgadmin.user_login_check import pga_login_required21from pgadmin.misc.bgprocess.processes import BatchProcess, IProcessDesc22from pgadmin.utils import PgAdminModule, get_storage_directory, html, \23    fs_short_path, document_dir, does_utility_exist, get_server, \24    filename_with_file_manager_path25from pgadmin.utils.ajax import make_json_response, bad_request, unauthorized26 27from config import PG_DEFAULT_DRIVER28# This unused import is required as API test cases will fail if we remove it,29# Have to identify the cause and then remove it.30from pgadmin.model import Server, SharedServer31from pgadmin.misc.bgprocess import escape_dquotes_process_arg32from pgadmin.utils.constants import MIMETYPE_APP_JS33from pgadmin.tools.grant_wizard import _get_rows_for_type, \34    get_node_sql_with_type, properties, get_data35 36# set template path for sql scripts37MODULE_NAME = 'backup'38server_info = {}39 40 41class BackupModule(PgAdminModule):42    """43    class BackupModule():44 45        It is a utility which inherits PgAdminModule46        class and define methods to load its own47        javascript file.48    """49 50    LABEL = gettext('Backup')51 52    def show_system_objects(self):53        """54        return system preference objects55        """56        return self.pref_show_system_objects57 58    def get_exposed_url_endpoints(self):59        """60        Returns:61            list: URL endpoints for backup module62        """63        return ['backup.create_server_job', 'backup.create_object_job',64                'backup.utility_exists', 'backup.objects',65                'backup.schema_objects']66 67 68# Create blueprint for BackupModule class69blueprint = BackupModule(70    MODULE_NAME, __name__, static_url_path=''71)72 73 74class BACKUP():75    """76    Constants defined for Backup utilities77    """78    GLOBALS = 179    SERVER = 280    OBJECT = 381 82 83class BackupMessage(IProcessDesc):84    """85    BackupMessage(IProcessDesc)86 87    Defines the message shown for the backup operation.88    """89 90    def __init__(self, _type, _sid, _bfile, *_args, **_kwargs):91        self.backup_type = _type92        self.sid = _sid93        self.bfile = _bfile94        self.database = _kwargs['database'] if 'database' in _kwargs else None95        self.cmd = ''96        self.args_str = "{0} ({1}:{2})"97 98        def cmd_arg(x):99            if x:100                x = x.replace('\\', '\\\\')101                x = x.replace('"', '\\"')102                x = x.replace('""', '\\"')103                return ' "' + x + '"'104            return ''105 106        for arg in _args:107            if arg and len(arg) >= 2 and arg[:2] == '--':108                self.cmd += ' ' + arg109            else:110                self.cmd += cmd_arg(arg)111 112    def get_server_name(self):113        s = get_server(self.sid)114 115        if s is None:116            return gettext("Not available")117 118        from pgadmin.utils.driver import get_driver119        driver = get_driver(PG_DEFAULT_DRIVER)120        manager = driver.connection_manager(self.sid)121 122        host = manager.local_bind_host if manager.use_ssh_tunnel else s.host123        port = manager.local_bind_port if manager.use_ssh_tunnel else s.port124 125        return "{0} ({1}:{2})".format(s.name, host, port)126 127    @property128    def type_desc(self):129        if self.backup_type == BACKUP.OBJECT:130            return gettext("Backing up an object on the server")131        if self.backup_type == BACKUP.GLOBALS:132            return gettext("Backing up the global objects")133        elif self.backup_type == BACKUP.SERVER:134            return gettext("Backing up the server")135        else:136            # It should never reach here.137            return gettext("Unknown Backup")138 139    @property140    def message(self):141        server_name = self.get_server_name()142 143        if self.backup_type == BACKUP.OBJECT:144            return gettext(145                "Backing up an object on the server '{0}' "146                "from database '{1}'"147            ).format(server_name, self.database)148        if self.backup_type == BACKUP.GLOBALS:149            return gettext("Backing up the global objects on "150                           "the server '{0}'").format(151                server_name152            )153        elif self.backup_type == BACKUP.SERVER:154            return gettext("Backing up the server '{0}'").format(155                server_name156            )157        else:158            # It should never reach here.159            return "Unknown Backup"160 161    def details(self, cmd, args):162        server_name = self.get_server_name()163        backup_type = gettext("Backup")164        if self.backup_type == BACKUP.OBJECT:165            backup_type = gettext("Backup Object")166        elif self.backup_type == BACKUP.GLOBALS:167            backup_type = gettext("Backup Globals")168        elif self.backup_type == BACKUP.SERVER:169            backup_type = gettext("Backup Server")170 171        return {172            "message": self.message,173            "cmd": cmd + self.cmd,174            "server": server_name,175            "object": self.database,176            "type": backup_type,177        }178 179 180@blueprint.route("/")181@pga_login_required182def index():183    return bad_request(errormsg=gettext("This URL cannot be called directly."))184 185 186@blueprint.route("/backup.js")187@pga_login_required188def script():189    """render own javascript"""190    return Response(191        response=render_template(192            "backup/js/backup.js", _=_193        ),194        status=200,195        mimetype=MIMETYPE_APP_JS196    )197 198 199def _get_args_params_values(data, conn, backup_obj_type, backup_file, server,200                            manager):201    """202    Used internally by create_backup_objects_job. This function will create203    the required args and params for the job.204    :param data: input data205    :param conn: connection obj206    :param backup_obj_type: object type207    :param backup_file: file name208    :param server: server obj209    :param manager: connection manager210    :return: args array211    """212    from pgadmin.utils.driver import get_driver213    driver = get_driver(PG_DEFAULT_DRIVER)214 215    host, port = (manager.local_bind_host, str(manager.local_bind_port)) \216        if manager.use_ssh_tunnel else (server.host, str(server.port))217    args = [218        '--file',219        backup_file,220        '--host',221        host,222        '--port',223        port,224        '--username',225        manager.user,226        '--no-password'227    ]228 229    def set_param(key, param, assertion=True):230        if not assertion:231            return232        if data.get(key, None):233            args.append(param)234 235    def set_value(key, param, default_value=None, assertion=True):236        if not assertion:237            return238        val = data.get(key, default_value)239        if val:240            if isinstance(val, list):241                for c_val in val:242                    args.append(param)243                    args.append(c_val)244                return245            args.append(param)246            args.append(val)247 248    if backup_obj_type != 'objects':249        args.append('--database')250        args.append(server.maintenance_db)251 252    if backup_obj_type == 'globals':253        args.append('--globals-only')254 255    set_value('role', '--role')256 257    if backup_obj_type == 'objects' and data.get('format', None):258        args.extend(['--format={0}'.format({259            'custom': 'c',260            'tar': 't',261            'plain': 'p',262            'directory': 'd'263        }[data['format']])])264 265        # --blobs is deprecated from v16266        if manager.version >= 160000:267            set_param('blobs', '--large-objects',268                      data['format'] in ['custom', 'tar'])269        else:270            set_param('blobs', '--blobs', data['format'] in ['custom', 'tar'])271        set_value('ratio', '--compress')272 273    set_value('encoding', '--encoding')274    set_value('no_of_jobs', '--jobs')275 276    # Data options277    set_param('only_data', '--data-only',278              data.get('only_data', None))279    set_param('only_schema', '--schema-only',280              data.get('only_schema', None) and281              not data.get('only_data', None))282    set_param('only_tablespaces', '--tablespaces-only',283              data.get('only_tablespaces', None))284    set_param('only_roles', '--roles-only',285              data.get('only_roles', None))286 287    # Sections288    set_param('pre_data', '--section=pre-data')289    set_param('data', '--section=data')290    set_param('post_data', '--section=post-data')291 292    # Do not Save293    set_param('dns_owner', '--no-owner')294    set_param('dns_privilege', '--no-privileges')295    set_param('dns_tablespace', '--no-tablespaces')296    set_param('dns_unlogged_tbl_data', '--no-unlogged-table-data')297    set_param('dns_comments', '--no-comments', manager.version >= 110000)298    set_param('dns_publications', '--no-publications',299              manager.version >= 110000)300    set_param('dns_subscriptions', '--no-subscriptions',301              manager.version >= 110000)302    set_param('dns_security_labels', '--no-security-labels',303              manager.version >= 110000)304    set_param('dns_toast_compression', '--no-toast-compression',305              manager.version >= 140000)306    set_param('dns_table_access_method', '--no-table-access-method',307              manager.version >= 150000)308    set_param('dns_no_role_passwords', '--no-role-passwords')309 310    # Query Options311    set_param('use_insert_commands', '--inserts')312    set_value('max_rows_per_insert', '--rows-per-insert', None,313              manager.version >= 120000)314    set_param('on_conflict_do_nothing', '--on-conflict-do-nothing',315              manager.version >= 120000)316    set_param('include_create_database', '--create')317    set_param('include_drop_database', '--clean')318    set_param('if_exists', '--if-exists')319 320    # Table options321    set_param('use_column_inserts', '--column-inserts')322    set_param('load_via_partition_root', '--load-via-partition-root',323              manager.version >= 110000)324    set_param('enable_row_security', '--enable-row-security')325    set_value('exclude_table_data', '--exclude-table-data')326    set_value('table_and_children', '--table-and-children', None,327              manager.version >= 160000)328    set_value('exclude_table_and_children', '--exclude-table-and-children',329              None, manager.version >= 160000)330    set_value('exclude_table_data_and_children',331              '--exclude-table-data-and-children', None,332              manager.version >= 160000)333    set_value('exclude_table', '--exclude-table')334 335    # Disable options336    set_param('disable_trigger', '--disable-triggers',337              data.get('only_data', None) and338              data.get('format', '') == 'plain')339    set_param('disable_quoting', '--disable-dollar-quoting')340 341    # Misc Options342    set_param('verbose', '--verbose')343    set_param('dqoute', '--quote-all-identifiers')344    set_param('use_set_session_auth', '--use-set-session-authorization')345    set_value('exclude_schema', '--exclude-schema')346    set_value('extra_float_digits', '--extra-float-digits', None,347              manager.version >= 120000)348    set_value('lock_wait_timeout', '--lock-wait-timeout')349    set_value('exclude_database', '--exclude-database', None,350              manager.version >= 160000)351 352    args.extend(353        functools.reduce(operator.iconcat, map(354            lambda s: ['--schema', r'{0}'.format(driver.qtIdent(conn, s).355                                                 replace('"', '\"'))],356            data.get('schemas', [])), []357        )358    )359 360    args.extend(361        functools.reduce(operator.iconcat, map(362            lambda t: ['--table',363                       r'{0}'.format(driver.qtIdent(conn, t[0], t[1])364                                     .replace('"', '\"'))],365            data.get('tables', [])), []366        )367    )368 369    if 'objects' in data:370        selected_objects = data.get('objects', {})371        for _key in selected_objects:372            param = 'schema' if _key == 'schema' else 'table'373            args.extend(374                functools.reduce(operator.iconcat, map(375                    lambda s: [f'--{param}',376                               r'{0}.{1}'.format(377                                   driver.qtIdent(conn, s['schema']).replace(378                                       '"', '\"'),379                                   driver.qtIdent(conn, s['name']).replace(380                                       '"', '\"')) if type(381                                   s) is dict else driver.qtIdent(382                                   conn, s).replace('"', '\"')],383                    selected_objects[_key] or []), [])384            )385 386    return args387 388 389@blueprint.route(390    '/job/<int:sid>', methods=['POST'], endpoint='create_server_job'391)392@blueprint.route(393    '/job/<int:sid>/object', methods=['POST'], endpoint='create_object_job'394)395@pga_login_required396def create_backup_objects_job(sid):397    """398    Args:399        sid: Server ID400 401        Creates a new job for backup task402        (Backup Database(s)/Schema(s)/Table(s))403 404    Returns:405        None406    """407 408    data = json.loads(request.data)409    backup_obj_type = data.get('type', 'objects')410 411    try:412        backup_file = filename_with_file_manager_path(413            data['file'], (data.get('format', '') != 'directory'))414    except PermissionError as e:415        return unauthorized(errormsg=str(e))416    except Exception as e:417        return bad_request(errormsg=str(e))418 419    # Fetch the server details like hostname, port, roles etc420    server = get_server(sid)421 422    if server is None:423        return make_json_response(424            success=0,425            errormsg=gettext("Could not find the specified server.")426        )427 428    # To fetch MetaData for the server429    from pgadmin.utils.driver import get_driver430    driver = get_driver(PG_DEFAULT_DRIVER)431    manager = driver.connection_manager(server.id)432    conn = manager.connection()433    connected = conn.connected()434 435    if not connected:436        return make_json_response(437            success=0,438            errormsg=gettext("Please connect to the server first.")439        )440 441    utility = manager.utility('backup') if backup_obj_type == 'objects' \442        else manager.utility('backup_server')443 444    ret_val = does_utility_exist(utility)445    if ret_val:446        return make_json_response(447            success=0,448            errormsg=ret_val449        )450 451    args = _get_args_params_values(452        data, conn, backup_obj_type, backup_file, server, manager)453 454    escaped_args = [455        escape_dquotes_process_arg(arg) for arg in args456    ]457    try:458        bfile = data['file'].encode('utf-8') \459            if hasattr(data['file'], 'encode') else data['file']460        if backup_obj_type == 'objects':461            args.append(data['database'])462            escaped_args.append(data['database'])463            p = BatchProcess(464                desc=BackupMessage(465                    BACKUP.OBJECT, server.id, bfile,466                    *args,467                    database=data['database']468                ),469                cmd=utility, args=escaped_args, manager_obj=manager470            )471        else:472            p = BatchProcess(473                desc=BackupMessage(474                    BACKUP.SERVER if backup_obj_type != 'globals'475                    else BACKUP.GLOBALS,476                    server.id, bfile,477                    *args478                ),479                cmd=utility, args=escaped_args, manager_obj=manager480            )481 482        p.set_env_variables(server)483        p.start()484        jid = p.id485    except Exception as e:486        current_app.logger.exception(e)487        return make_json_response(488            status=410,489            success=0,490            errormsg=str(e)491        )492 493    # Return response494    return make_json_response(495        data={'job_id': jid, 'desc': p.desc.message, 'Success': 1}496    )497 498 499@blueprint.route(500    '/utility_exists/<int:sid>/<backup_obj_type>', endpoint='utility_exists'501)502@pga_login_required503def check_utility_exists(sid, backup_obj_type):504    """505    This function checks the utility file exist on the given path.506 507    Args:508        sid: Server ID509        backup_obj_type: Type of the object510    Returns:511        None512    """513    server = get_server(sid)514 515    if server is None:516        return make_json_response(517            success=0,518            errormsg=gettext("Could not find the specified server.")519        )520 521    from pgadmin.utils.driver import get_driver522    driver = get_driver(PG_DEFAULT_DRIVER)523    manager = driver.connection_manager(server.id)524 525    utility = manager.utility('backup') if backup_obj_type == 'objects' \526        else manager.utility('backup_server')527 528    ret_val = does_utility_exist(utility)529    if ret_val:530        return make_json_response(531            success=0,532            errormsg=ret_val533        )534 535    return make_json_response(success=1)536 537 538@blueprint.route(539    '/objects/<int:sid>/<int:did>', endpoint='objects'540)541@blueprint.route(542    '/objects/<int:sid>/<int:did>/<int:scid>', endpoint='schema_objects'543)544@pga_login_required545def objects(sid, did, scid=None):546    """547    This function returns backup objects548 549    Args:550        sid: Server ID551        did: database ID552        scid: schema ID553    Returns:554        list of objects555    """556    server = get_server(sid)557 558    if server is None:559        return make_json_response(560            success=0,561            errormsg=gettext("Could not find the specified server.")562        )563 564    from pgadmin.utils.driver import get_driver565    from pgadmin.utils.ajax import precondition_required566 567    server_info = {}568    server_info['manager'] = get_driver(PG_DEFAULT_DRIVER) \569        .connection_manager(sid)570    server_info['conn'] = server_info['manager'].connection(571        did=did)572    # If DB not connected then return error to browser573    if not server_info['conn'].connected():574        return precondition_required(575            gettext("Connection to the server has been lost.")576        )577 578    # Set template path for sql scripts579    server_info['server_type'] = server_info['manager'].server_type580    server_info['version'] = server_info['manager'].version581    if server_info['server_type'] == 'pg':582        server_info['template_path'] = 'grant_wizard/pg/#{0}#'.format(583            server_info['version'])584    elif server_info['server_type'] == 'ppas':585        server_info['template_path'] = 'grant_wizard/ppas/#{0}#'.format(586            server_info['version'])587 588    res, _, empty_schema_list = get_data(sid, did, scid,589                                         'schema' if scid else 'database',590                                         server_info, True)591 592    tree_data = {593        'table': [],594        'view': [],595        'materialized view': [],596        'foreign table': [],597        'sequence': []598    }599 600    schema_group = {}601 602    for data in res:603        obj_type = data['object_type'].lower()604        if obj_type in ['table', 'view', 'materialized view', 'foreign table',605                        'sequence']:606 607            if data['nspname'] not in schema_group:608                schema_group[data['nspname']] = {609                    'id': data['nspname'],610                    'name': data['nspname'],611                    'icon': 'icon-schema',612                    'children': copy.deepcopy(tree_data),613                    'is_schema': True,614                }615            icon_data = {616                'materialized view': 'icon-mview',617                'foreign table': 'icon-foreign_table'618            }619            icon = icon_data[obj_type] if obj_type in icon_data \620                else data['icon']621            schema_group[data['nspname']]['children'][obj_type].append({622                'id': f'{data["nspname"]}_{data["name"]}',623                'name': data['name'],624                'icon': icon,625                'schema': data['nspname'],626                'type': obj_type,627                '_name': '{0}.{1}'.format(data['nspname'], data['name'])628            })629 630    schema_group = [dt for k, dt in schema_group.items()]631    for ch in schema_group:632        children = []633        for obj_type, data in ch['children'].items():634            if data:635                icon_data = {636                    'materialized view': 'icon-coll-mview',637                    'foreign table': 'icon-coll-foreign_table'638                }639                icon = icon_data[obj_type] if obj_type in icon_data \640                    else f'icon-coll-{obj_type.lower()}',641                children.append({642                    'id': f'{ch["id"]}_{obj_type}',643                    'name': f'{obj_type.title()}s',644                    'icon': icon,645                    'children': data,646                    'type': obj_type,647                    'is_collection': True,648                })649 650        ch['children'] = children651 652    for empty_schema in empty_schema_list:653        schema_group.append({654            'id': empty_schema,655            'name': empty_schema,656            'icon': 'icon-schema',657            'children': [],658            'is_schema': True,659        })660    return make_json_response(661        data=schema_group,662        success=200663    )664