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##########################################################################9import re10from functools import wraps11 12import pgadmin.browser.server_groups as sg13import json14from flask import render_template, request, jsonify, current_app15from flask_babel import gettext as _16import dateutil.parser as dateutil_parser17from pgadmin.browser.collection import CollectionNodeModule18from pgadmin.browser.utils import PGChildNodeView19from pgadmin.utils.ajax import make_json_response, \20 make_response as ajax_response, precondition_required, \21 internal_server_error, forbidden, success_return, gone22from pgadmin.utils.driver import get_driver23from pgadmin.utils.constants import ERROR_FETCHING_ROLE_INFORMATION24from pgadmin.utils.exception import ExecuteError25 26from config import PG_DEFAULT_DRIVER27from flask_babel import gettext28 29_REASSIGN_OWN_SQL = 'reassign_own.sql'30 31 32class RoleModule(CollectionNodeModule):33 _NODE_TYPE = 'role'34 _COLLECTION_LABEL = _("Login/Group Roles")35 36 def __init__(self, *args, **kwargs):37 self.min_ver = None38 self.max_ver = None39 40 super().__init__(*args, **kwargs)41 42 def get_nodes(self, gid, sid):43 """44 Generate the collection node45 """46 if self.show_node:47 yield self.generate_browser_collection_node(sid)48 49 @property50 def node_inode(self):51 """52 Override this property to make the node as leaf node.53 """54 return False55 56 @property57 def script_load(self):58 """59 Load the module script for server, when any of the server-group node is60 initialized.61 """62 return sg.ServerGroupModule.node_type63 64 @property65 def csssnippets(self):66 """67 Returns a snippet of css to include in the page68 """69 snippets = [70 render_template(71 self._COLLECTION_CSS,72 node_type=self.node_type73 ),74 render_template("roles/css/role.css")]75 76 for submodule in self.submodules:77 snippets.extend(submodule.csssnippets)78 79 return snippets80 81 @property82 def module_use_template_javascript(self):83 """84 Returns whether Jinja2 template is used for generating the javascript85 module.86 """87 return False88 89 90blueprint = RoleModule(__name__)91 92 93class RoleView(PGChildNodeView):94 node_type = 'role'95 96 parent_ids = [97 {'type': 'int', 'id': 'gid'},98 {'type': 'int', 'id': 'sid'}99 ]100 ids = [101 {'type': 'int', 'id': 'rid'}102 ]103 104 operations = dict({105 'obj': [106 {'get': 'properties', 'delete': 'drop', 'put': 'update'},107 {'get': 'list', 'post': 'create', 'delete': 'drop'},108 ],109 'nodes': [{'get': 'node'}, {'get': 'nodes'}],110 'sql': [{'get': 'sql'}],111 'msql': [{'get': 'msql'}, {'get': 'msql'}],112 'dependency': [{'get': 'dependencies'}],113 'dependent': [{'get': 'dependents'}],114 'children': [{'get': 'children'}],115 'vopts': [{}, {'get': 'voptions'}],116 'variables': [{'get': 'variables'}],117 'reassign': [{'get': 'get_reassign_own_sql',118 'post': 'role_reassign_own'}]119 })120 121 def _validate_input_dict_for_new(self, data, req_keys):122 """123 This functions validates the input dict and check for required124 keys in the dict when creating a new object125 :param data: input dict126 :param req_keys: required keys127 :return: Valid or Invalid128 """129 if not isinstance(data, list):130 return False131 132 for item in data:133 if not isinstance(item, dict):134 return False135 136 for a_key in req_keys:137 if a_key not in item:138 return False139 140 return True141 142 def _validate_input_dict_for_update(143 self, data, req_add_keys, req_delete_keys):144 """145 This functions validates the input dict and check for required146 keys in the dict when updating an existing object147 :param data: input dict148 :param req_add_keys: required keys when adding, updating149 :param req_delete_keys: required keys when deleting150 :return: Valid or Invalid151 """152 if not isinstance(data, dict):153 return False154 155 for op in ['added', 'deleted', 'changed']:156 op_data = data.get(op, [])157 check_keys = req_add_keys \158 if op in ['added', 'changed'] else req_delete_keys159 if not self._validate_input_dict_for_new(op_data, check_keys):160 return False161 162 return True163 164 def _validate_rolvaliduntil(self, data):165 """166 Validate the rolvaliduntil in input data dict167 :param data: role data168 :return: valid or invalid message169 """170 if 'rolvaliduntil' in data:171 # Make date explicit so that it works with every172 # postgres database datestyle format173 try:174 if data['rolvaliduntil'] is not None and \175 data['rolvaliduntil'] != '' and \176 len(data['rolvaliduntil']) > 0:177 data['rolvaliduntil'] = dateutil_parser.parse(178 data['rolvaliduntil']179 ).isoformat()180 except Exception:181 return _("Date format is invalid.")182 183 return None184 185 def _validate_rolconnlimit(self, data):186 """187 Validate the rolconnlimit data dict188 :param data: role data189 :return: valid or invalid message190 """191 if 'rolconnlimit' in data:192 # If roleconnlimit is empty string then set it to -1193 if data['rolconnlimit'] == '':194 data['rolconnlimit'] = -1195 196 if data['rolconnlimit'] is not None:197 data['rolconnlimit'] = int(data['rolconnlimit'])198 if not isinstance(data['rolconnlimit'], int) or \199 data['rolconnlimit'] < -1:200 return _("Connection limit must be an integer value "201 "or equal to -1.")202 return None203 204 def _process_rolemembership(self, id, data):205 """206 Process the input rolemembership list to appropriate keys207 :param id: id of role208 :param data: input role data209 """210 def _part_dict_list(dict_list, condition, list_key=None):211 ret_val = []212 for d in dict_list:213 if condition(d):214 ret_val.append(d[list_key])215 216 return ret_val217 218 if id == -1:219 data['members'] = []220 data['admins'] = []221 222 data['admins'] = _part_dict_list(223 data['rolmembership'], lambda d: d['admin'], 'role')224 data['members'] = _part_dict_list(225 data['rolmembership'], lambda d: not d['admin'], 'role')226 else:227 data['admins'] = _part_dict_list(228 data['rolmembership'].get('added', []),229 lambda d: d['admin'], 'role')230 data['members'] = _part_dict_list(231 data['rolmembership'].get('added', []),232 lambda d: not d['admin'], 'role')233 234 data['admins'].extend(_part_dict_list(235 data['rolmembership'].get('changed', []),236 lambda d: d['admin'], 'role'))237 data['revoked_admins'] = _part_dict_list(238 data['rolmembership'].get('changed', []),239 lambda d: not d['admin'], 'role')240 241 data['revoked'] = _part_dict_list(242 data['rolmembership'].get('deleted', []),243 lambda _: True, 'role')244 245 def _process_rolmembers(self, id, data):246 """247 Parser role members.248 :param id:249 :param data:250 """251 def _part_dict_list(dict_list, condition, list_key=None):252 ret_val = []253 for d in dict_list:254 if condition(d):255 ret_val.append(d[list_key])256 257 return ret_val258 if id == -1:259 data['rol_members'] = []260 data['rol_admins'] = []261 262 data['rol_admins'] = _part_dict_list(263 data['rolmembers'], lambda d: d['admin'], 'role')264 data['rol_members'] = _part_dict_list(265 data['rolmembers'], lambda d: not d['admin'], 'role')266 else:267 data['rol_admins'] = _part_dict_list(268 data['rolmembers'].get('added', []),269 lambda d: d['admin'], 'role')270 data['rol_members'] = _part_dict_list(271 data['rolmembers'].get('added', []),272 lambda d: not d['admin'], 'role')273 274 data['rol_admins'].extend(_part_dict_list(275 data['rolmembers'].get('changed', []),276 lambda d: d['admin'], 'role'))277 data['rol_revoked_admins'] = _part_dict_list(278 data['rolmembers'].get('changed', []),279 lambda d: not d['admin'], 'role')280 281 data['rol_revoked'] = _part_dict_list(282 data['rolmembers'].get('deleted', []),283 lambda _: True, 'role')284 285 def _validate_rolemembers(self, id, data):286 """287 Validate the rolmembers data dict288 :param data: role data289 :return: valid or invalid message290 """291 if 'rolmembers' not in data:292 return None293 294 if id == -1:295 msg = _("""296Role members information must be passed as an array of JSON objects in the297following format:298 299rolmembers:[{300 role: [rolename],301 admin: True/False302 },303 ...304]""")305 306 if not self._validate_input_dict_for_new(data['rolmembers'],307 ['role', 'admin']):308 return msg309 310 self._process_rolmembers(id, data)311 return None312 313 msg = _("""314Role membership information must be passed as a string representing an array of315JSON objects in the following format:316rolmembers:{317 'added': [{318 role: [rolename],319 admin: True/False320 },321 ...322 ],323 'deleted': [{324 role: [rolename],325 admin: True/False326 },327 ...328 ],329 'updated': [{330 role: [rolename],331 admin: True/False332 },333 ...334 ]335""")336 if not self._validate_input_dict_for_update(data['rolmembers'],337 ['role', 'admin'],338 ['role']):339 return msg340 341 self._process_rolmembers(id, data)342 return None343 344 def _validate_rolemembership(self, id, data):345 """346 Validate the rolmembership data dict347 :param data: role data348 :return: valid or invalid message349 """350 if 'rolmembership' not in data:351 return None352 353 if id == -1:354 msg = _("""355Role membership information must be passed as an array of JSON objects in the356following format:357 358rolmembership:[{359 role: [rolename],360 admin: True/False361 },362 ...363]""")364 365 if not self._validate_input_dict_for_new(366 data['rolmembership'], ['role', 'admin']):367 return msg368 369 self._process_rolemembership(id, data)370 return None371 372 msg = _("""373Role membership information must be passed as a string representing an array of374JSON objects in the following format:375rolmembership:{376 'added': [{377 role: [rolename],378 admin: True/False379 },380 ...381 ],382 'deleted': [{383 role: [rolename],384 admin: True/False385 },386 ...387 ],388 'updated': [{389 role: [rolename],390 admin: True/False391 },392 ...393 ]394""")395 if not self._validate_input_dict_for_update(396 data['rolmembership'], ['role', 'admin'], ['role']):397 return msg398 399 self._process_rolemembership(id, data)400 return None401 402 def _validate_seclabels(self, id, data):403 """404 Validate the seclabels data dict405 :param data: role data406 :return: valid or invalid message407 """408 if 'seclabels' not in data or self.manager.version < 90200:409 return None410 411 if id == -1:412 msg = _("""413Security Label must be passed as an array of JSON objects in the following414format:415seclabels:[{416 provider: <provider>,417 label: <label>418 },419 ...420]""")421 if not self._validate_input_dict_for_new(422 data['seclabels'], ['provider', 'label']):423 return msg424 425 return None426 427 msg = _("""428Security Label must be passed as an array of JSON objects in the following429format:430seclabels:{431 'added': [{432 provider: <provider>,433 label: <label>434 },435 ...436 ],437 'deleted': [{438 provider: <provider>,439 label: <label>440 },441 ...442 ],443 'updated': [{444 provider: <provider>,445 label: <label>446 },447 ...448 ]449""")450 if not self._validate_input_dict_for_update(451 data['seclabels'], ['provider', 'label'], ['provider']):452 return msg453 454 return None455 456 def _validate_variables(self, id, data):457 """458 Validate the variables data dict459 :param data: role data460 :return: valid or invalid message461 """462 if 'variables' not in data:463 return None464 465 if id == -1:466 msg = _("""467Configuration parameters/variables must be passed as an array of JSON objects468in the following format in create mode:469variables:[{470database: <database> or null,471name: <configuration>,472value: <value>473},474...475]""")476 if not self._validate_input_dict_for_new(477 data['variables'], ['name', 'value']):478 return msg479 480 return None481 482 msg = _("""483Configuration parameters/variables must be passed as an array of JSON objects484in the following format in update mode:485rolmembership:{486'added': [{487 database: <database> or null,488 name: <configuration>,489 value: <value>490 },491 ...492 ],493'deleted': [{494 database: <database> or null,495 name: <configuration>,496 value: <value>497 },498 ...499 ],500'updated': [{501 database: <database> or null,502 name: <configuration>,503 value: <value>504 },505 ...506 ]507""")508 if not self._validate_input_dict_for_update(509 data['variables'], ['name', 'value'], ['name']):510 return msg511 return None512 513 def _validate_rolname(self, id, data):514 """515 Validate the rolname data dict516 :param data: role data517 :return: valid or invalid message518 """519 if (id == -1) and 'rolname' not in data:520 return precondition_required(521 _("Name must be specified.")522 )523 return None524 525 def validate_request(f):526 @wraps(f)527 def wrap(self, **kwargs):528 if request.data:529 data = json.loads(request.data)530 else:531 data = dict()532 for k, v in request.args.items():533 try:534 # comments should be taken as is because if user enters535 # a json comment it is parsed by loads which should not536 # happen537 if k in ('comment',):538 data[k] = v539 else:540 data[k] = json.loads(v)541 except ValueError:542 data[k] = v543 544 invalid_msg_arr = [545 self._validate_rolname(kwargs.get('rid', -1), data),546 self._validate_rolvaliduntil(data),547 self._validate_rolconnlimit(data),548 self._validate_rolemembership(kwargs.get('rid', -1), data),549 self._validate_seclabels(kwargs.get('rid', -1), data),550 self._validate_variables(kwargs.get('rid', -1), data),551 self._validate_rolemembers(kwargs.get('rid', -1), data)552 ]553 554 for invalid_msg in invalid_msg_arr:555 if invalid_msg is not None:556 return precondition_required(invalid_msg)557 558 self.request = data559 560 return f(self, **kwargs)561 562 return wrap563 564 @staticmethod565 def _check_action(action, kwargs):566 check_permission = False567 fetch_name = False568 forbidden_msg = None569 if action in ['drop', 'update']:570 if 'rid' in kwargs:571 fetch_name = True572 check_permission = True573 574 if action == 'drop':575 forbidden_msg = _(576 "The current user does not have permission to drop"577 " the role."578 )579 else:580 forbidden_msg = _(581 "The current user does not have permission to "582 "update the role."583 )584 elif action == 'create':585 check_permission = True586 forbidden_msg = _(587 "The current user does not have permission to create "588 "the role."589 )590 elif action == 'msql' and 'rid' in kwargs:591 fetch_name = True592 593 return fetch_name, check_permission, forbidden_msg594 595 def _check_permission(self, check_permission, action, kwargs):596 if check_permission:597 user = self.manager.user_info598 599 if not user['is_superuser'] and \600 not user['can_create_role'] and \601 (action != 'update' or 'rid' in kwargs) and \602 kwargs['rid'] != -1 and \603 user['id'] != kwargs['rid']:604 return True605 return False606 607 def _check_and_fetch_name(self, fetch_name, kwargs):608 if fetch_name:609 status, res = self.conn.execute_dict(610 render_template(611 self.sql_path + 'permission.sql',612 rid=kwargs['rid'],613 conn=self.conn614 )615 )616 617 if not status:618 return True, internal_server_error(619 _(620 "Error retrieving the role information.\n{0}"621 ).format(res)622 )623 624 if len(res['rows']) == 0:625 return True, gone(626 _("Could not find the role on the database "627 "server.")628 )629 630 row = res['rows'][0]631 632 self.role = row['rolname']633 self.rolCanLogin = row['rolcanlogin']634 self.rolSuper = row['rolsuper']635 636 return False, ''637 638 def check_precondition(action=None):639 """640 This function will behave as a decorator which will checks the status641 of the database connection for the maintainance database of the server,642 beforeexecuting rest of the operation for the wrapped function. It will643 also attach manager, conn (maintenance connection for the server) as644 properties of the instance.645 """646 647 def wrap(f):648 @wraps(f)649 def wrapped(self, **kwargs):650 self.manager = get_driver(651 PG_DEFAULT_DRIVER652 ).connection_manager(653 kwargs['sid']654 )655 self.conn = self.manager.connection()656 657 driver = get_driver(PG_DEFAULT_DRIVER)658 self.qtIdent = driver.qtIdent659 660 if not self.conn.connected():661 return precondition_required(662 _("Connection to the server has been lost.")663 )664 665 self.datistemplate = False666 if (667 self.manager.db_info is not None and668 self.manager.did in self.manager.db_info and669 'datistemplate' in self.manager.db_info[self.manager.did]670 ):671 self.datistemplate = self.manager.db_info[672 self.manager.did]['datistemplate']673 674 self.sql_path = 'roles/sql/#{0}#'.format(self.manager.version)675 676 self.alterKeys = [677 'rolcanlogin', 'rolsuper', 'rolcreatedb',678 'rolcreaterole', 'rolinherit', 'rolreplication',679 'rolconnlimit', 'rolvaliduntil', 'rolpassword',680 'rolbypassrls'681 ] if self.manager.version >= 90200 else [682 'rolcanlogin', 'rolsuper', 'rolcreatedb',683 'rolcreaterole', 'rolinherit', 'rolconnlimit',684 'rolvaliduntil', 'rolpassword'685 ]686 687 fetch_name, check_permission, \688 forbidden_msg = RoleView._check_action(action, kwargs)689 690 is_permission_error = self._check_permission(check_permission,691 action, kwargs)692 if is_permission_error:693 return forbidden(forbidden_msg)694 695 is_error, errmsg = self._check_and_fetch_name(fetch_name,696 kwargs)697 if is_error:698 return errmsg699 700 return f(self, **kwargs)701 702 return wrapped703 704 return wrap705 706 @check_precondition(action='list')707 def list(self, gid, sid):708 status, res = self.conn.execute_dict(709 render_template(710 self.sql_path + self._PROPERTIES_SQL711 )712 )713 714 if not status:715 return internal_server_error(716 _(ERROR_FETCHING_ROLE_INFORMATION + "\n{0}").format(res))717 718 self.transform(res)719 720 return ajax_response(721 response=res['rows'],722 status=200723 )724 725 @check_precondition(action='nodes')726 def nodes(self, gid, sid):727 728 status, rset = self.conn.execute_2darray(729 render_template(self.sql_path + self._NODES_SQL)730 )731 732 if not status:733 return internal_server_error(734 _(ERROR_FETCHING_ROLE_INFORMATION + "\n{0}").format(rset))735 736 res = []737 for row in rset['rows']:738 res.append(739 self.blueprint.generate_browser_node(740 row['oid'], sid,741 row['rolname'],742 'icon-role' if row['rolcanlogin'] else 'icon-group',743 can_login=row['rolcanlogin'],744 is_superuser=row['rolsuper'],745 description=row['description']746 )747 )748 749 return make_json_response(750 data=res,751 status=200752 )753 754 @check_precondition(action='node')755 def node(self, gid, sid, rid):756 757 status, rset = self.conn.execute_2darray(758 render_template(759 self.sql_path + self._NODES_SQL,760 rid=rid761 )762 )763 764 if not status:765 return internal_server_error(766 _(767 ERROR_FETCHING_ROLE_INFORMATION + "\n{0}").format(rset))768 769 for row in rset['rows']:770 return make_json_response(771 data=self.blueprint.generate_browser_node(772 row['oid'], sid,773 row['rolname'],774 'icon-role' if row['rolcanlogin'] else 'icon-group',775 can_login=row['rolcanlogin'],776 is_superuser=row['rolsuper']777 ),778 status=200779 )780 781 return gone()782 783 def _set_seclabels(self, row):784 785 if 'seclabels' in row and row['seclabels'] is not None:786 res = []787 for sec in row['seclabels']:788 sec = re.search(r'([^=]+)=(.*$)', sec)789 res.append({790 'provider': sec.group(1),791 'label': sec.group(2)792 })793 row['seclabels'] = res794 795 def _set_rolemembership(self, row):796 797 if 'rolmembers' in row:798 rolmembers = []799 for role in row['rolmembers']:800 role = re.search(r'([01])(.+)', role)801 rolmembers.append({802 'role': role.group(2),803 'admin': True if role.group(1) == '1' else False804 })805 row['rolmembers'] = rolmembers806 807 def transform(self, rset):808 for row in rset['rows']:809 res = []810 roles = row['rolmembership']811 row['rolpassword'] = ''812 for role in roles:813 role = re.search(r'([01])(.+)', role)814 res.append({815 'role': role.group(2),816 'admin': True if role.group(1) == '1' else False817 })818 row['rolmembership'] = res819 self._set_seclabels(row)820 self._set_rolemembership(row)821 822 @check_precondition(action='properties')823 def properties(self, gid, sid, rid):824 825 status, res = self.conn.execute_dict(826 render_template(827 self.sql_path + self._PROPERTIES_SQL,828 rid=rid829 )830 )831 832 variables = self.variables(None, None, rid)833 if not status:834 return internal_server_error(835 _(ERROR_FETCHING_ROLE_INFORMATION + "\n{0}").format(res))836 837 self.transform(res)838 if len(res['rows']) == 0:839 return gone(self.not_found_error_msg())840 841 res['rows'][0]['is_sys_obj'] = (842 res['rows'][0]['oid'] <= self._DATABASE_LAST_SYSTEM_OID or843 self.datistemplate)844 res = {**res['rows'][0], 'variables': variables['rows']}845 return ajax_response(846 response=res,847 status=200848 )849 850 @check_precondition(action='drop')851 def drop(self, gid, sid, rid=None):852 853 if rid is None:854 data = request.form if request.form else json.loads(855 request.data856 )857 else:858 data = {'ids': [rid]}859 860 for rid in data['ids']:861 status, res = self.conn.execute_dict(862 render_template(863 self.sql_path + 'permission.sql',864 rid=rid,865 conn=self.conn866 )867 )868 869 if not status:870 return internal_server_error(871 _(872 "Error retrieving the role information.\n{0}"873 ).format(res)874 )875 876 if len(res['rows']) == 0:877 return gone(878 _("Could not find the role on the database "879 "server.")880 )881 882 row = res['rows'][0]883 884 status, res = self.conn.execute_2darray(885 "DROP ROLE {0};".format(self.qtIdent(self.conn,886 row['rolname']))887 )888 if not status:889 return internal_server_error(890 _("Could not drop the role.\n{0}").format(res)891 )892 893 return success_return()894 895 @check_precondition()896 def sql(self, gid, sid, rid):897 show_password = self.conn.manager.user_info['is_superuser']898 status, res = self.conn.execute_scalar(899 render_template(900 self.sql_path + 'sql.sql', show_password=show_password901 ),902 dict({'rid': rid})903 )904 905 if not status:906 return internal_server_error(907 _("Could not generate reversed engineered query for the "908 "role.\n{0}").format(909 res910 )911 )912 913 if res is None or (len(res) == 0):914 return gone(915 _("Could not generate reversed engineered query for the role.")916 )917 918 return ajax_response(response=res.strip('\n'))919 920 @check_precondition(action='create')921 @validate_request922 def create(self, gid, sid):923 924 sql = render_template(925 self.sql_path + self._CREATE_SQL,926 data=self.request,927 dummy=False,928 conn=self.conn929 )930 931 status, msg = self.conn.execute_dict(sql)932 933 if not status:934 return internal_server_error(935 _("Could not create the role.\n{0}").format(msg)936 )937 938 status, rid = self.conn.execute_scalar(939 "SELECT oid FROM pg_catalog.pg_roles WHERE rolname = %(rolname)s",940 {'rolname': self.request['rolname']}941 )942 943 if not status:944 return internal_server_error(945 _(ERROR_FETCHING_ROLE_INFORMATION + "\n{0}").format(msg))946 947 status, rset = self.conn.execute_dict(948 render_template(self.sql_path + self._NODES_SQL,949 rid=rid950 )951 )952 953 if not status:954 return internal_server_error(955 _(956 ERROR_FETCHING_ROLE_INFORMATION + "\n{0}").format(rset))957 for row in rset['rows']:958 return jsonify(959 node=self.blueprint.generate_browser_node(960 rid, sid,961 row['rolname'],962 'icon-role' if row['rolcanlogin'] else 'icon-group',963 can_login=row['rolcanlogin']964 )965 )966 967 return gone(self.not_found_error_msg())968 969 @check_precondition(action='update')970 @validate_request971 def update(self, gid, sid, rid):972 973 sql = render_template(974 self.sql_path + self._UPDATE_SQL,975 data=self.request,976 dummy=False,977 conn=self.conn,978 role=self.role,979 rolCanLogin=self.rolCanLogin,980 rolSuper=self.rolSuper,981 alterKeys=self.alterKeys982 )983 984 status, msg = self.conn.execute_dict(sql)985 986 if not status:987 return internal_server_error(988 _("Could not create the role.\n{0}").format(msg)989 )990 991 status, rset = self.conn.execute_dict(992 render_template(self.sql_path + self._NODES_SQL,993 rid=rid994 )995 )996 997 if not status:998 return internal_server_error(999 _(ERROR_FETCHING_ROLE_INFORMATION + "\n{0}").format(rset))1000 1001 for row in rset['rows']:1002 return jsonify(1003 node=self.blueprint.generate_browser_node(1004 rid, sid,1005 row['rolname'],1006 'icon-role' if row['rolcanlogin'] else 'icon-group',1007 can_login=row['rolcanlogin'],1008 is_superuser=row['rolsuper'],1009 description=row['description']1010 )1011 )1012 1013 return gone(self.not_found_error_msg())1014 1015 @check_precondition(action='msql')1016 @validate_request1017 def msql(self, gid, sid, rid=None):1018 if rid is None:1019 return make_json_response(1020 data=render_template(1021 self.sql_path + self._CREATE_SQL,1022 data=self.request,1023 dummy=True,1024 conn=self.conn1025 ).strip('\n')1026 )1027 else:1028 return make_json_response(1029 data=render_template(1030 self.sql_path + self._UPDATE_SQL,1031 data=self.request,1032 dummy=True,1033 conn=self.conn,1034 role=self.role,1035 rolCanLogin=self.rolCanLogin,1036 rolSuper=self.rolSuper,1037 alterKeys=self.alterKeys1038 ).strip('\n')1039 )1040 1041 @check_precondition()1042 def dependencies(self, gid, sid, rid):1043 """1044 This function gets the dependencies and returns an ajax response1045 for the role.1046 1047 Args:1048 gid: Server Group ID1049 sid: Server ID1050 rid: Role ID1051 """1052 dependencies_result = self.get_dependencies(self.conn, rid)1053 return ajax_response(1054 response=dependencies_result,1055 status=2001056 )1057 1058 @check_precondition()1059 def dependents(self, gid, sid, rid):1060 """1061 This function gets the dependents and returns an ajax response1062 for the role.1063 1064 Args:1065 gid: Server Group ID1066 sid: Server ID1067 rid: Role ID1068 """1069 dependents_result = self.get_dependents(self.conn, sid, rid)1070 return ajax_response(1071 response=dependents_result,1072 status=2001073 )1074 1075 @staticmethod1076 def _handle_dependents_type(types, type_str, type_name, rel_name, row):1077 if types[type_str[0]] is None:1078 if type_str[0] == 'i':1079 type_name = 'index'1080 rel_name = row['indname'] + ' ON ' + rel_name1081 elif type_str[0] == 'o':1082 type_name = 'operator'1083 rel_name = row['relname']1084 else:1085 type_name = types[type_str[0]]1086 1087 return type_name, rel_name1088 1089 @staticmethod1090 def _handle_dependents_data(result, types, dependents, db_row):1091 for row in result['rows']:1092 rel_name = row['nspname']1093 if rel_name is not None:1094 rel_name += '.'1095 1096 if rel_name is None:1097 rel_name = row['relname']1098 else:1099 rel_name += row['relname']1100 1101 type_name = ''1102 type_str = row['relkind']1103 # Fetch the type name from the dictionary1104 # if type is not present in the types dictionary then1105 # we will continue and not going to add it.1106 if type_str[0] in types:1107 # if type is present in the types dictionary, but it's1108 # value is None then it requires special handling.1109 type_name, rel_name = RoleView._handle_dependents_type(1110 types, type_str, type_name, rel_name, row)1111 else:1112 continue1113 1114 dependents.append(1115 {1116 'type': type_name,1117 'name': rel_name,1118 'field': db_row['datname']1119 }1120 )1121 1122 def _temp_connection_check(self, rid, temp_conn, db_row, types,1123 dependents):1124 if temp_conn.connected():1125 query = render_template(1126 "/".join([self.sql_path, 'dependents.sql']),1127 fetch_dependents=True, rid=rid,1128 lastsysoid=self._DATABASE_LAST_SYSTEM_OID1129 )1130 1131 status, result = temp_conn.execute_dict(query)1132 if not status:1133 raise ExecuteError(result)1134 1135 RoleView._handle_dependents_data(result, types, dependents, db_row)1136 1137 @staticmethod1138 def _release_connection(is_connected, manager, db_row):1139 # Release only those connections which we have created above.1140 if not is_connected:1141 manager.release(db_row['datname'])1142 1143 def get_dependents(self, conn, sid, rid):1144 """1145 This function is used to fetch the dependents for the selected node.1146 1147 Args:1148 conn: Connection object1149 sid: Server Id1150 rid: Role Id.1151 1152 Returns: Dictionary of dependents for the selected node.1153 """1154 1155 # Dictionary for the object types1156 types = {1157 # None specified special handling for this type1158 'r': 'table',1159 'i': None,1160 'S': 'sequence',1161 'v': 'view',1162 'x': 'external_table',1163 'p': 'function',1164 'n': 'schema',1165 'y': 'type',1166 'd': 'domain',1167 'T': 'trigger_function',1168 'C': 'conversion',1169 'o': None1170 }1171 1172 query = render_template("/".join([self.sql_path, 'dependents.sql']),1173 fetch_database=True, rid=rid)1174 status, db_result = self.conn.execute_dict(query)1175 if not status:1176 current_app.logger.error(db_result)1177 1178 dependents = list()1179 1180 # Get the server manager1181 manager = get_driver(PG_DEFAULT_DRIVER).connection_manager(sid)1182 1183 for db_row in db_result['rows']:1184 oid = db_row['datdba']1185 if db_row['type'] == 'd':1186 if rid == oid:1187 dependents.append(1188 {1189 'type': 'database',1190 'name': '',1191 'field': db_row['datname']1192 }1193 )1194 else:1195 dependents.append(1196 {1197 'type': 'tablespace',1198 'name': db_row['datname'],1199 'field': ''1200 }