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 pgAdmin4 User Management Utility"""11 12import json13from flask import render_template, request, \14    Response, abort, current_app, session15from flask_babel import gettext as _16from flask_security import roles_required, current_user17from pgadmin.user_login_check import pga_login_required18from flask_security.utils import hash_password19from werkzeug.exceptions import InternalServerError20 21import config22from pgadmin.utils import PgAdminModule23from pgadmin.utils.ajax import make_response as ajax_response, \24    make_json_response, bad_request, internal_server_error25from pgadmin.utils.csrf import pgCSRFProtect26from pgadmin.utils.constants import MIMETYPE_APP_JS, INTERNAL,\27    SUPPORTED_AUTH_SOURCES28from pgadmin.utils.validation_utils import validate_email29from pgadmin.model import db, Role, User, UserPreference, Server, \30    ServerGroup, Process, Setting, roles_users, SharedServer31from pgadmin.utils.paths import create_users_storage_directory32 33# set template path for sql scripts34MODULE_NAME = 'user_management'35server_info = {}36 37 38class UserManagementModule(PgAdminModule):39    """40    class UserManagementModule():41 42        It is a utility which inherits PgAdminModule43        class and define methods to load its own44        javascript file.45    """46 47    LABEL = _('Users')48 49    def show_system_objects(self):50        """51        return system preference objects52        """53        return self.pref_show_system_objects54 55    def get_exposed_url_endpoints(self):56        """57        Returns:58            list: URL endpoints for backup module59        """60        return [61            'user_management.roles', 'user_management.role',62            'user_management.users', 'user_management.user',63            current_app.login_manager.login_view,64            'user_management.auth_sources', 'user_management.change_owner',65            'user_management.shared_servers', 'user_management.admin_users',66            'user_management.save'67        ]68 69 70# Create blueprint for BackupModule class71blueprint = UserManagementModule(72    MODULE_NAME, __name__, static_url_path=''73)74 75 76@blueprint.route("/")77@pga_login_required78def index():79    return bad_request(errormsg=_("This URL cannot be called directly."))80 81 82@blueprint.route("/user_management.js")83@pga_login_required84def script():85    """render own javascript"""86    return Response(87        response=render_template(88            "user_management/js/user_management.js", _=_,89            is_admin=current_user.has_role("Administrator"),90            user_id=current_user.id91        ),92        status=200,93        mimetype=MIMETYPE_APP_JS94    )95 96 97@blueprint.route("/current_user.js")98@pgCSRFProtect.exempt99@pga_login_required100def current_user_info():101    return Response(102        response=render_template(103            "user_management/js/current_user.js",104            is_admin='true' if current_user.has_role(105                "Administrator") else 'false',106            user_id=current_user.id,107            email=current_user.email.replace("'","\\'") if current_user.email108            else current_user.email,109            name=(110                current_user.username.split('@')[0].replace("'","\\'") if111                config.SERVER_MODE is True112                else 'postgres'113            ),114            allow_save_password='true' if115            config.ALLOW_SAVE_PASSWORD and session['allow_save_password']116            else 'false',117            allow_save_tunnel_password='true' if118            config.ALLOW_SAVE_TUNNEL_PASSWORD and session[119                'allow_save_password'] else 'false',120            auth_sources=config.AUTHENTICATION_SOURCES,121            current_auth_source=session['auth_source_manager'][122                'current_source'] if config.SERVER_MODE is True else INTERNAL123        ),124        status=200,125        mimetype=MIMETYPE_APP_JS126    )127 128 129@blueprint.route(130    '/user/', methods=['GET'], defaults={'uid': None}, endpoint='users'131)132@blueprint.route('/user/<int:uid>', methods=['GET'], endpoint='user')133@roles_required('Administrator')134def user(uid):135    """136 137    Args:138      uid: User id139 140    Returns: List of pgAdmin4 users or single user if uid is provided.141 142    """143 144    if uid:145        u = User.query.get(uid)146 147        res = {'id': u.id,148               'username': u.username,149               'email': u.email,150               'active': u.active,151               'role': u.roles[0].id,152               'auth_source': u.auth_source,153               'locked': u.locked154               }155    else:156        users = User.query.all()157 158        users_data = []159        for u in users:160            users_data.append({'id': u.id,161                               'username': u.username,162                               'email': u.email,163                               'active': u.active,164                               'role': u.roles[0].id,165                               'auth_source': u.auth_source,166                               'locked': u.locked167                               })168 169        res = users_data170 171    return ajax_response(172        response=res,173        status=200174    )175 176 177@blueprint.route('/change_owner', methods=['POST'], endpoint='change_owner')178@roles_required('Administrator')179def change_owner():180    """181 182    Returns:183 184    """185 186    data = request.form if request.form else json.loads(187        request.data188    )189    try:190        new_user = User.query.get(data['new_owner'])191        old_user_servers = Server.query.filter_by(shared=True, user_id=data[192            'old_owner']).all()193        server_group_ids = [server.servergroup_id for server in194                            old_user_servers]195        server_groups = ServerGroup.query.filter(196            ServerGroup.id.in_(server_group_ids)).all()197 198        new_owner_sg = ServerGroup.query.filter_by(199            user_id=data['new_owner']).all()200        old_owner_sg = ServerGroup.query.filter_by(201            user_id=data['old_owner']).all()202        sg_data = {sg.name: sg.id for sg in new_owner_sg}203        old_sg_data = {sg.id: sg.name for sg in old_owner_sg}204 205        deleted_sg = []206        # Change server user.207        for server in old_user_servers:208            sh_servers = SharedServer.query.filter_by(209                servergroup_id=server.servergroup_id).all()210 211            if old_sg_data[server.servergroup_id] in sg_data:212 213                for sh in sh_servers:214                    sh.servergroup_id = sg_data[215                        old_sg_data[server.servergroup_id]]216                    sh.server_owner = new_user.username217                # Update Server user and server group to prevent deleting218                # shared server associated with deleting user.219                Server.query.filter_by(220                    servergroup_id=server.servergroup_id, shared=True,221                    user_id=data['old_owner']222                ).update(223                    {224                        'servergroup_id': sg_data[old_sg_data[225                            server.servergroup_id]],226                        'user_id': data['new_owner']227                    }228                )229                ServerGroup.query.filter_by(id=server.servergroup_id).delete()230                deleted_sg.append(server.servergroup_id)231            else:232                server.user_id = data['new_owner']233                for sh in sh_servers:234                    sh.server_owner = new_user.username235 236        # Change server group user.237        for server_group in server_groups:238            if server_group.id not in deleted_sg:239                server_group.user_id = data['new_owner']240 241        db.session.commit()242        return make_json_response(243            success=1,244            info=_("Owner changed successfully."),245            data={}246        )247    except Exception as e:248        msg = 'Unable to update shared server owner' + _(str(e))249        return internal_server_error(250            errormsg=msg)251 252 253@blueprint.route(254    '/shared_servers/<int:uid>', methods=['GET'], endpoint='shared_servers'255)256@roles_required('Administrator')257def get_shared_servers(uid):258    """259 260    Args:261      uid:262 263    Returns:264 265    """266    usr = User.query.get(uid)267 268    if not usr:269        abort(404)270 271    try:272        shared_servers_count = 0273        admin_role = Role.query.filter_by(name='Administrator')[0]274        # Check user has admin role.275        for role in usr.roles:276            if role.id == admin_role.id:277                # get all server created by user.278                servers = Server.query.filter_by(user_id=usr.id).all()279                for server in servers:280                    if server.shared:281                        shared_servers_count += 1282                break283 284        if shared_servers_count:285            return make_json_response(286                success=1,287                info=_(288                    "{0} Shared servers are associated with this user."289                    "".format(shared_servers_count)290                ),291                data={292                    'shared_servers': shared_servers_count293                }294            )295 296        return make_json_response(297            success=1,298            info=_("No shared servers found"),299            data={'shared_servers': 0}300        )301    except Exception as e:302        return internal_server_error(errormsg=str(e))303 304 305@blueprint.route(306    '/admin_users/<int:uid>', methods=['GET'], endpoint='admin_users'307)308@roles_required('Administrator')309def admin_users(uid=None):310    """311 312    Args:313      uid:314 315    Returns:316 317    """318    admin_role = Role.query.filter_by(name='Administrator')[0]319 320    admin_users = db.session.query(roles_users).filter_by(321        role_id=admin_role.id).all()322 323    if uid:324        admin_users = [user[0] for user in admin_users if user[0] != uid]325    else:326        admin_users = [user[0] for user in admin_users]327 328    admin_list = User.query.filter(User.id.in_(admin_users)).all()329 330    user_list = [{'value': admin.id, 'label': admin.username} for admin in331                 admin_list]332 333    return make_json_response(334        success=1,335        info=_("No shared servers found"),336        data={337            'status': 'success',338            'msg': 'Admin user list',339            'result': {340                'data': user_list,341            }342        }343    )344 345 346@blueprint.route(347    '/role/', methods=['GET'], defaults={'rid': None}, endpoint='roles'348)349@blueprint.route('/role/<int:rid>', methods=['GET'], endpoint='role')350@roles_required('Administrator')351def role(rid):352    """353 354    Args:355      rid: Role id356 357    Returns: List of pgAdmin4 users roles or single role if rid is provided.358 359    """360 361    if rid:362        r = Role.query.get(rid)363 364        res = {'id': r.id, 'name': r.name}365    else:366        roles = Role.query.all()367 368        roles_data = []369        for r in roles:370            roles_data.append({'id': r.id,371                               'name': r.name})372 373        res = roles_data374 375    return ajax_response(376        response=res,377        status=200378    )379 380 381@blueprint.route(382    '/auth_sources/', methods=['GET'], endpoint='auth_sources'383)384def auth_sources():385    sources = []386    for source in SUPPORTED_AUTH_SOURCES:387        sources.append({'label': source, 'value': source})388 389    return ajax_response(390        response=sources,391        status=200392    )393 394 395@blueprint.route('/save', methods=['POST'], endpoint='save')396@roles_required('Administrator')397def save():398    """399    This function is used to add/update/delete users.400    """401    data = request.form if request.form else json.loads(402        request.data403    )404 405    try:406        # Delete Users407        if 'deleted' in data:408            for item in data['deleted']:409                status, res = delete_user(item['id'])410                if not status:411                    return internal_server_error(errormsg=res)412        # Create Users413        if 'added' in data:414            for item in data['added']:415                status, res = create_user(item)416                if not status:417                    return internal_server_error(errormsg=res)418        # Modify Users419        if 'changed' in data:420            for item in data['changed']:421                status, res = update_user(item['id'], item)422                if not status:423                    return internal_server_error(errormsg=res)424    except Exception as e:425        return internal_server_error(errormsg=str(e))426 427    return ajax_response(428        status=200429    )430 431 432def validate_password(data, new_data):433    """434    Check password new and confirm password match. If both passwords are not435    match raise exception.436    :param data: Data.437    :param new_data: new data dict.438    """439    if ('newPassword' in data and data['newPassword'] != "" and440            'confirmPassword' in data and data['confirmPassword'] != ""):441 442        if data['newPassword'] == data['confirmPassword']:443            new_data['password'] = hash_password(data['newPassword'])444        else:445            raise InternalServerError(_("Passwords do not match."))446 447 448def validate_user(data):449    new_data = dict()450 451    validate_password(data, new_data)452 453    if 'email' in data and data['email'] and data['email'] != "":454        if validate_email(data['email']):455            new_data['email'] = data['email']456        else:457            raise InternalServerError(458                _("Invalid email address {0}.").format(data['email']))459 460    if 'role' in data and data['role'] != "":461        new_data['roles'] = int(data['role'])462 463    if 'active' in data and data['active'] != "":464        new_data['active'] = data['active']465 466    if 'username' in data and data['username'] != "":467        new_data['username'] = data['username']468 469    if 'auth_source' in data and data['auth_source'] != "":470        new_data['auth_source'] = data['auth_source']471 472    if 'locked' in data and isinstance(data['locked'], bool):473        new_data['locked'] = data['locked']474        if data['locked']:475            new_data['login_attempts'] = config.MAX_LOGIN_ATTEMPTS476        else:477            new_data['login_attempts'] = 0478 479    return new_data480 481 482def _create_new_user(new_data):483    """484    Create new user.485    :param new_data: Data from user creation.486    :return: Return new created user.487    """488    auth_source = new_data['auth_source'] if 'auth_source' in new_data \489        else INTERNAL490    username = new_data['username'] if \491        'username' in new_data and auth_source != \492        INTERNAL else new_data['email']493    email = new_data['email'] if 'email' in new_data else None494    password = new_data['password'] if 'password' in new_data else None495 496    usr = User(username=username,497               email=email,498               roles=new_data['roles'],499               active=new_data['active'],500               password=password,501               auth_source=auth_source)502    db.session.add(usr)503    db.session.commit()504    # Add default server group for new user.505    server_group = ServerGroup(user_id=usr.id, name="Servers")506    db.session.add(server_group)507    db.session.commit()508 509 510def create_user(data):511    if 'auth_source' in data and data['auth_source'] != \512            INTERNAL:513        req_params = ('username', 'role', 'active', 'auth_source')514    else:515        req_params = ('email', 'role', 'active', 'newPassword',516                      'confirmPassword')517 518    for f in req_params:519        if f in data and data[f] != '':520            continue521        else:522            return False, _("Missing field: '{0}'").format(f)523 524    try:525        new_data = validate_user(data)526 527        if 'roles' in new_data:528            new_data['roles'] = [Role.query.get(new_data['roles'])]529 530    except Exception as e:531        return False, str(e.description)532 533    try:534        _create_new_user(new_data)535    except Exception as e:536        return False, str(e)537 538    # Create users storage directory539    create_users_storage_directory()540 541    return True, ''542 543 544def update_user(uid, data):545    """546    This function is used to update the users.547    """548 549    usr = User.query.get(uid)550    if not usr:551        return False, _("Unable to update user '{0}'").format(uid)552 553    # Username and email can not be changed for internal users554    if usr.auth_source == INTERNAL:555        non_editable_params = ('username', 'email')556    else:557        non_editable_params = ('username',)558 559    for f in non_editable_params:560        if f in data:561            return False, _("'{0}' is not allowed to modify.").format(f)562 563    try:564        new_data = validate_user(data)565        if 'roles' in new_data:566            new_data['roles'] = [Role.query.get(new_data['roles'])]567    except Exception as e:568        return False, str(e.description)569 570    try:571        for k, v in new_data.items():572            setattr(usr, k, v)573 574        db.session.commit()575    except Exception as e:576        return False, str(e)577 578    return True, ''579 580 581def delete_user(uid):582    """583    This function is used to delete the users584    """585    usr = User.query.get(uid)586 587    if not usr:588        return False, _("Unable to update user '{0}'").format(uid)589 590    try:591        server_groups = ServerGroup.query.filter_by(user_id=uid).all()592        sg = [server_group.id for server_group in server_groups]593 594        Setting.query.filter_by(user_id=uid).delete()595 596        UserPreference.query.filter_by(uid=uid).delete()597 598        Server.query.filter_by(user_id=uid).delete()599 600        ServerGroup.query.filter_by(user_id=uid).delete()601 602        Process.query.filter_by(user_id=uid).delete()603        # Delete Shared servers for current user.604        SharedServer.query.filter_by(user_id=uid).delete()605 606        SharedServer.query.filter(SharedServer.servergroup_id.in_(sg)).delete(607            synchronize_session=False)608 609        # Finally delete user610        db.session.delete(usr)611 612        db.session.commit()613    except Exception as e:614        return False, str(e)615 616    return True, ''617