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 10"""Implements Grant Wizard"""11 12import json13from flask import Response, url_for14from flask import render_template, request, current_app15from flask_babel import gettext16from pgadmin.user_login_check import pga_login_required17from urllib.parse import unquote18 19from pgadmin.browser.server_groups.servers.utils import parse_priv_to_db20from pgadmin.utils import PgAdminModule21from pgadmin.utils.ajax import make_response as ajax_response, \22    make_json_response, internal_server_error, bad_request23from pgadmin.utils.driver import get_driver24 25from config import PG_DEFAULT_DRIVER26from pgadmin.utils.ajax import precondition_required27from functools import wraps28from pgadmin.utils.preferences import Preferences29from pgadmin.utils.constants import MIMETYPE_APP_JS30 31# set template path for sql scripts32MODULE_NAME = 'grant_wizard'33server_info = {}34 35 36class GrantWizardModule(PgAdminModule):37    """38    class GrantWizardModule():39 40        It is a wizard which inherits PgAdminModule41        class and define methods to load its own42        javascript file.43    """44 45    def show_system_objects(self):46        """47        return system preference objects48        """49        return self.pref_show_system_objects50 51    def register_preferences(self):52        """53        Get show_system_objects preference54        """55        self.browser_preference = Preferences.module('browser')56        self.pref_show_system_objects = self.browser_preference.preference(57            'show_system_objects'58        )59 60    def get_exposed_url_endpoints(self):61        """62        Returns:63            list: URL endpoints for grant-wizard module64        """65        return [66            'grant_wizard.acl', 'grant_wizard.objects', 'grant_wizard.apply',67            'grant_wizard.modified_sql'68        ]69 70 71# Create blueprint for GrantWizardModule class72blueprint = GrantWizardModule(73    MODULE_NAME, __name__, static_url_path='')74 75 76def check_precondition(f):77    """78    This function will behave as a decorator which will checks79    database connection before running view, it will also attaches80    manager,conn & template_path properties to instance of the method.81 82    Assumptions:83        This function will always be used as decorator of a class method.84    """85 86    @wraps(f)87    def wrap(*args, **kwargs):88        # Here args[0] will hold self & kwargs will hold sid,did89 90        server_info.clear()91        server_info['manager'] = get_driver(PG_DEFAULT_DRIVER)\92            .connection_manager(kwargs['sid'])93        server_info['conn'] = server_info['manager'].connection(94            did=kwargs['did']95        )96        # If DB not connected then return error to browser97        if not server_info['conn'].connected():98            return precondition_required(99                gettext("Connection to the server has been lost.")100            )101 102        # Set template path for sql scripts103        server_info['server_type'] = server_info['manager'].server_type104        server_info['version'] = server_info['manager'].version105        if server_info['server_type'] == 'pg':106            server_info['template_path'] = 'grant_wizard/pg/#{0}#'.format(107                server_info['version'])108        elif server_info['server_type'] == 'ppas':109            server_info['template_path'] = 'grant_wizard/ppas/#{0}#'.format(110                server_info['version'])111 112        return f(*args, **kwargs)113 114    return wrap115 116 117@blueprint.route("/")118@pga_login_required119def index():120    return bad_request(121        errormsg=gettext("This URL cannot be called directly.")122    )123 124 125@blueprint.route("/grant_wizard.js")126@pga_login_required127def script():128    """render own javascript"""129    return Response(response=render_template(130        "grant_wizard/js/grant_wizard.js", _=gettext),131        status=200,132        mimetype=MIMETYPE_APP_JS)133 134 135@blueprint.route(136    '/acl/<int:sid>/<int:did>/', methods=['GET'], endpoint='acl'137)138@pga_login_required139@check_precondition140def acl_list(sid, did):141    """render list of acls"""142    server_prop = server_info143    return Response(response=render_template(144        server_prop['template_path'] + "/acl.json", _=gettext),145        status=200,146        mimetype="application/json")147 148 149def _get_rows_for_type(conn, ntype, server_prop, node_id):150    """151    Used internally by properties to get rows for an object type152    :param conn: connection object153    :param ntype: object type154    :param server_prop: server properties155    :param node_id: oid156    :return: status, execute response157    """158    function_sql_url = '/sql/function.sql'159    status, res = True, []160 161    if ntype in ['function']:162        sql = render_template("/".join(163            [server_prop['template_path'], function_sql_url]),164            node_id=node_id, type='function')165 166        status, res = conn.execute_dict(sql)167    # Fetch procedures only if server type is EPAS or PG >= 11168    elif len(server_prop) > 0 and (169        server_prop['server_type'] == 'ppas' or (170            server_prop['server_type'] == 'pg' and171            server_prop['version'] >= 11000172        )173    ) and ntype in ['procedure']:174        sql = render_template("/".join(175            [server_prop['template_path'], function_sql_url]),176            node_id=node_id, type='procedure')177 178        status, res = conn.execute_dict(sql)179 180    # Fetch trigger functions181    elif ntype in ['trigger_function']:182        sql = render_template("/".join(183            [server_prop['template_path'], function_sql_url]),184            node_id=node_id, type='trigger_function')185        status, res = conn.execute_dict(sql)186 187    # Fetch Sequences against schema188    elif ntype in ['sequence']:189        sql = render_template("/".join(190            [server_prop['template_path'], '/sql/sequence.sql']),191            node_id=node_id)192 193        status, res = conn.execute_dict(sql)194 195    # Fetch Tables against schema196    elif ntype in ['table']:197        sql = render_template("/".join(198            [server_prop['template_path'], '/sql/table.sql']),199            node_id=node_id)200 201        status, res = conn.execute_dict(sql)202 203    # Fetch Views against schema204    elif ntype in ['view']:205        sql = render_template("/".join(206            [server_prop['template_path'], '/sql/view.sql']),207            node_id=node_id, node_type='v')208 209        status, res = conn.execute_dict(sql)210 211    # Fetch Materialzed Views against schema212    elif ntype in ['mview']:213        sql = render_template("/".join(214            [server_prop['template_path'], '/sql/view.sql']),215            node_id=node_id, node_type='m')216 217        status, res = conn.execute_dict(sql)218    # Fetch Foreign tables.219    elif ntype in ['foreign_table']:220        sql = render_template("/".join(221            [server_prop['template_path'], '/sql/foreign_table.sql']),222            node_id=node_id, node_type='m')223 224        status, res = conn.execute_dict(sql)225 226    # Logic for generating privileges sql only for ppas227    # Fetch Packages.228    if server_prop['server_type'] == 'ppas' and ntype in ['package']:229        sql = render_template("/".join(230            [server_prop['template_path'], '/sql/package.sql']),231            node_id=node_id)232 233        status, res = conn.execute_dict(sql)234 235    return status, res236 237 238def get_node_sql_with_type(node_id, node_type, server_prop,239                           get_schema_sql_url, show_sysobj):240    if node_type == 'database':241        sql = render_template("/".join(242            [server_prop['template_path'], get_schema_sql_url]),243            show_sysobj=show_sysobj)244        ntype = 'schema'245    else:246        sql = render_template("/".join(247            [server_prop['template_path'], get_schema_sql_url]),248            show_sysobj=show_sysobj, nspid=node_id)249        ntype = node_type250 251    return sql, ntype252 253 254@blueprint.route(255    '/<int:sid>/<int:did>/<int:node_id>/<node_type>/',256    methods=['GET'], endpoint='objects'257)258@pga_login_required259@check_precondition260def properties(sid, did, node_id, node_type):261    """It fetches the properties of object types262       and render into selection page of wizard263    """264 265    res_data, msg = get_data(sid, did, node_id, node_type, server_info)266 267    if res_data is None and isinstance(msg, Response):268        return msg269 270    return make_json_response(271        result=res_data,272        info=msg,273        status=200274    )275 276 277def get_data(sid, did, node_id, node_type, server_data,278             return_emtpy_schema=False):279    get_schema_sql_url = '/sql/get_schemas.sql'280 281    # unquote encoded url parameter282    node_type = unquote(node_type)283 284    server_prop = server_data285 286    res_data = []287    failed_objects = []288    manager = get_driver(PG_DEFAULT_DRIVER).connection_manager(sid)289    conn = manager.connection(did=did)290 291    show_sysobj = blueprint.show_system_objects().get()292 293    sql, ntype = get_node_sql_with_type(node_id, node_type, server_prop,294                                        get_schema_sql_url, show_sysobj)295 296    status, res = conn.execute_dict(sql)297 298    if not status:299        return None, internal_server_error(errormsg=res)300    node_types = res['rows']301 302    def _append_rows(status, res, disp_type):303        if not status:304            current_app.logger.error(res)305            failed_objects.append(disp_type)306        else:307            if len(res) > 0:308                res_data.extend(res['rows'])309 310    empty_schema_list = []311    for row in node_types:312        is_empty_schema = True313        if 'oid' in row:314            node_id = row['oid']315 316        if ntype == 'schema':317            status, res = _get_rows_for_type(318                conn, 'function', server_prop, node_id)319 320            _append_rows(status, res, 'function')321 322            status, res = _get_rows_for_type(323                conn, 'procedure', server_prop, node_id)324 325            _append_rows(status, res, 'procedure')326 327            status, res = _get_rows_for_type(328                conn, 'trigger_function', server_prop, node_id)329 330            _append_rows(status, res, 'trigger function')331 332            status, res = _get_rows_for_type(333                conn, 'sequence', server_prop, node_id)334 335            if len(res['rows']):336                is_empty_schema = False337 338            _append_rows(status, res, 'sequence')339 340            status, res = _get_rows_for_type(341                conn, 'table', server_prop, node_id)342 343            if len(res['rows']):344                is_empty_schema = False345 346            _append_rows(status, res, 'table')347 348            status, res = _get_rows_for_type(349                conn, 'view', server_prop, node_id)350 351            if len(res['rows']):352                is_empty_schema = False353 354            _append_rows(status, res, 'view')355 356            status, res = _get_rows_for_type(357                conn, 'mview', server_prop, node_id)358 359            if len(res['rows']):360                is_empty_schema = False361 362            _append_rows(status, res, 'materialized view')363 364            status, res = _get_rows_for_type(365                conn, 'foreign_table', server_prop, node_id)366 367            if len(res['rows']):368                is_empty_schema = False369 370            _append_rows(status, res, 'foreign table')371 372            status, res = _get_rows_for_type(373                conn, 'package', server_prop, node_id)374 375            if (type(res) is list and len(res) > 0) or (376                    'rows' in res and len(res['rows']) > 0):377                is_empty_schema = False378 379            _append_rows(status, res, 'package')380 381            if is_empty_schema and row['name'] not in empty_schema_list:382                empty_schema_list.append(row['name'])383 384        else:385            status, res = _get_rows_for_type(conn, ntype, server_prop, node_id)386            _append_rows(status, res, 'function')387 388    msg = None389    if len(failed_objects) > 0:390        msg = gettext('Unable to fetch the {} objects'.format(391            ", ".join(failed_objects))392        )393    if return_emtpy_schema:394        return res_data, msg, empty_schema_list395 396    return res_data, msg397 398 399def get_req_data():400    return request.form if request.form else json.loads(request.data.decode())401 402 403def set_priv_for_package(server_prop, data, acls):404    if server_prop['server_type'] == 'ppas':405        data['priv']['package'] = parse_priv_to_db(406            data['acl'],407            acls['package']['acl'])408 409 410@blueprint.route(411    '/sql/<int:sid>/<int:did>/',412    methods=['POST'], endpoint='modified_sql'413)414@pga_login_required415@check_precondition416def msql(sid, did):417    """418    This function will return modified SQL419    """420    server_prop = server_info421    data = get_req_data()422    # Form db connection423    manager = get_driver(PG_DEFAULT_DRIVER).connection_manager(sid)424    conn = manager.connection(did=did)425 426    acls = []427    try:428        acls = render_template(429            "/".join([server_prop['template_path'], '/acl.json'])430        )431        acls = json.loads(acls)432    except Exception as e:433        current_app.logger.exception(e)434 435    try:436        # Parse privileges437        data['priv'] = {}438        if 'acl' in data:439            # Get function acls440            data['priv']['function'] = parse_priv_to_db(441                data['acl'],442                acls['function']['acl'])443 444            data['priv']['sequence'] = parse_priv_to_db(445                data['acl'],446                acls['sequence']['acl'])447 448            data['priv']['table'] = parse_priv_to_db(449                data['acl'],450                acls['table']['acl'])451 452            data['priv']['foreign_table'] = parse_priv_to_db(453                data['acl'],454                acls['foreign_table']['acl'])455 456            # Logic for setting privileges only for ppas457            set_priv_for_package(server_prop, data, acls)458 459        # Pass database objects and get SQL for privileges460        sql_data = ''461        data_func = {'objects': data['objects'],462                     'priv': data['priv']['function']}463        sql = render_template(464            "/".join([server_prop['template_path'],465                      '/sql/grant_function.sql']),466            data=data_func, conn=conn)467        if sql and sql.strip('\n') != '':468            sql_data += sql469 470        data_seq = {'objects': data['objects'],471                    'priv': data['priv']['sequence']}472        sql = render_template(473            "/".join([server_prop['template_path'],474                      '/sql/grant_sequence.sql']),475            data=data_seq, conn=conn)476        if sql and sql.strip('\n') != '':477            sql_data += sql478 479        data_table = {'objects': data['objects'],480                      'priv': data['priv']['table']}481        sql = render_template(482            "/".join([server_prop['template_path'], '/sql/grant_table.sql']),483            data=data_table, conn=conn)484        if sql and sql.strip('\n') != '':485            sql_data += sql486 487        data_table = {'objects': data['objects'],488                      'priv': data['priv']['foreign_table']}489        sql = render_template(490            "/".join([server_prop['template_path'],491                      '/sql/grant_foreign_table.sql']),492            data=data_table, conn=conn)493        if sql and sql.strip('\n') != '':494            sql_data += sql495 496        # Logic for generating privileges sql only for ppas497        if server_prop['server_type'] == 'ppas':498            data_package = {'objects': data['objects'],499                            'priv': data['priv']['package']}500            sql = render_template(501                "/".join([server_prop['template_path'],502                          '/sql/grant_package.sql']),503                data=data_package, conn=conn)504            if sql and sql.strip('\n') != '':505                sql_data += sql506 507        res = {'data': sql_data}508 509        return ajax_response(510            response=res,511            status=200512        )513 514    except Exception as e:515        return make_json_response(516            status=410,517            success=0,518            errormsg=e.message519        )520 521 522def parse_priv(data, acls, server_prop):523    if 'acl' in data:524        # Get function acls525        data['priv']['function'] = parse_priv_to_db(526            data['acl'],527            acls['function']['acl'])528 529        data['priv']['sequence'] = parse_priv_to_db(530            data['acl'],531            acls['sequence']['acl'])532 533        data['priv']['table'] = parse_priv_to_db(534            data['acl'],535            acls['table']['acl'])536 537        data['priv']['foreign_table'] = parse_priv_to_db(538            data['acl'],539            acls['foreign_table']['acl'])540 541        # Logic for setting privileges only for ppas542        set_priv_for_package(server_prop, data, acls)543 544 545@blueprint.route(546    '/<int:sid>/<int:did>/', methods=['POST'], endpoint='apply'547)548@pga_login_required549@check_precondition550def save(sid, did):551    """552    This function will apply the privileges to the selected553    Database Objects554    """555    server_prop = server_info556    data = get_req_data()557 558    # Form db connection and we use conn to execute sql559    manager = get_driver(PG_DEFAULT_DRIVER).connection_manager(sid)560    conn = manager.connection(did=did)561 562    acls = []563    try:564        acls = render_template(565            "/".join([server_prop['template_path'], 'acl.json']),566        )567        acls = json.loads(acls)568    except Exception as e:569        current_app.logger.exception(e)570 571    try:572 573        # Parse privileges574        data['priv'] = {}575        parse_priv(data, acls, server_prop)576        # Pass database objects and get SQL for privileges577        # Pass database objects and get SQL for privileges578        sql_data = ''579        data_func = {'objects': data['objects'],580                     'priv': data['priv']['function']}581        sql = render_template(582            "/".join([server_prop['template_path'],583                      '/sql/grant_function.sql']),584            data=data_func, conn=conn)585        if sql and sql.strip('\n') != '':586            sql_data += sql587 588        data_seq = {'objects': data['objects'],589                    'priv': data['priv']['sequence']}590        sql = render_template(591            "/".join([server_prop['template_path'],592                      '/sql/grant_sequence.sql']),593            data=data_seq, conn=conn)594        if sql and sql.strip('\n') != '':595            sql_data += sql596 597        data_table = {'objects': data['objects'],598                      'priv': data['priv']['table']}599        sql = render_template(600            "/".join([server_prop['template_path'], '/sql/grant_table.sql']),601            data=data_table, conn=conn)602        if sql and sql.strip('\n') != '':603            sql_data += sql604 605        data_table = {'objects': data['objects'],606                      'priv': data['priv']['foreign_table']}607        sql = render_template(608            "/".join([server_prop['template_path'],609                      '/sql/grant_foreign_table.sql']),610            data=data_table, conn=conn)611        if sql and sql.strip('\n') != '':612            sql_data += sql613 614        # Logic for generating privileges sql only for ppas615        if server_prop['server_type'] == 'ppas':616            data_package = {'objects': data['objects'],617                            'priv': data['priv']['package']}618            sql = render_template(619                "/".join([server_prop['template_path'],620                          '/sql/grant_package.sql']),621                data=data_package, conn=conn)622            if sql and sql.strip('\n') != '':623                sql_data += sql624 625        status, res = conn.execute_dict(sql_data)626        if not status:627            return internal_server_error(errormsg=res)628 629        return make_json_response(630            success=1,631            info="Privileges applied"632        )633 634    except Exception as e:635        return internal_server_error(errormsg=e.message)636