#!/usr/bin/env python3
# -*- coding:utf-8 -*-
#############################################################################
# Copyright (c): 2012-2017, Huawei Tech. Co., Ltd.
# Description  : ResourceControl.py is a utility to manage and control resource during data redistribution.
#############################################################################

try:
    import os
    import sys
    import pwd

    sys.path.append(sys.path[0] + "/../../")
    from gspylib.common.Common import ClusterCommand
    from gspylib.common.ErrorCode import ErrorCode

except ImportError as ie:
    sys.exit("[GAUSS-52200] : Unable to import module: %s." % str(ie))


class ResourceControl():
    """
    This class is used for resource control
    User: redisuser
    level    memPercent       CpuResourceLevel         IOLimit
     F         0               DefaultClass:High         0
     H         50              DefaultClass:High
     M         30              DefaultClass:Medium
     L         10              DefaultClass:Low          10
    """
    # self.defaultuser will be set to the default database superuser which is always the os user
    REDIS_DEFAULT_USER_NAME = pwd.getpwuid(os.getuid()).pw_name

    #############################################################################
    # init a resource control instance
    #############################################################################

    def __init__(self, redisInstance):
        """
        function: initialize the parameters for a resource control instance
        input : NA
        output: NA
        """

        self.redisInstanceForResCtrl = redisInstance

        self.redisuser = self.redisInstanceForResCtrl.redisuser
        self.redispassword = self.redisInstanceForResCtrl.redispasswd
        self.defaultuser = ResourceControl.REDIS_DEFAULT_USER_NAME

        self.reslevel = self.redisInstanceForResCtrl.resourcelevel
        self.action = self.redisInstanceForResCtrl.action
        self.redisCN = self.redisInstanceForResCtrl.redisNode

        # flags for objects in database
        self.redisuserexist = False
        self.redisrespoolexist = False
        self.redisrelation = False

        # in default setting, enableSeparationOfDuty is off
        self.separationofduty = False

        # Mark whether it is virtual cluster mode
        self.isElasticGroup = False
        # Indicates the corresponding nodegroup
        self.vcGroupName = ""
        self.containerized_mode = self.redisInstanceForResCtrl.containerized_mode

        self.resourcePercentDict = {"Full": 0,
                                    "High": 50,
                                    "Medium": 30,
                                    "Low": 10}

    #############################################################################
    # run a resource control instance
    #############################################################################

    def run(self, vcMode=False, groupname=""):
        """
        function: perform the whole resource control process
        input : NA
        output: NA
        """

        try:
            # init variable
            self.isElasticGroup = vcMode
            self.vcGroupName = groupname
            self.redisInstanceForResCtrl.logger.debug("message is :%d   %s" % (self.isElasticGroup, self.vcGroupName))
            # check validity of parameters
            self.resourceControlStart()

            if (self.action == "redistribute"):
                self.redisInstanceForResCtrl.logger.debug("The action is: %s" % self.action)
                # - If the command executed is "gs_expand -t residtribution",
                #   resource level is fixed to "f" (full).
                if (self.reslevel != 'f'):
                    raise Exception("The resource level for starting redistribution should be f.")
                else:
                    self.resourceControlForRedistribute()
            if (self.action == "resourcectl"):
                self.redisInstanceForResCtrl.logger.debug("The action is: %s" % self.action)
                self.resourceControlForResourcectl()
        except Exception as e:
            if (str(e).find("GAUSS-5") > 0):
                raise Exception(str(e))
            else:
                raise Exception(ErrorCode.GAUSS_516["GAUSS_51644"] + " Error:\n%s" % str(e))

    #############################################################################
    # abstract interfaces to achieve resource control functionality
    #############################################################################
    def resourceControlStart(self):
        """
        function: check validity of parameters, contains:
                  1.check whether the action is valid
                  2.check whether the resouce level is valid
                  3.Check GUCs
                  4.Check whether GUC enableSeparationOfDuty is on
                  5.Check the existence of redisuser
                  6.Check the existence of redisrespool
                  7.Check the association relation of redisuser and redisrespool
        input : NA
        output: NA
        """

        self.checkActionIsLegal()
        self.checkResourceLevelIsLegal()
        if not self.containerized_mode:
            self.checkGUCsAreOn()
            self.checkGUCForSeparationOfDutyIsOn()
            self.checkIfRedisRespoolExists()
            self.checkRelationOfRedisuserAndRedisrespool()

        self.checkIfRedisUserExists()

    def resourceControlEnd(self):
        """
        function: clear up redisuser related info after redistribution succeeds:
                  1.Alter the owner of tables for statistic purpose
                  2.Revoke all schemas and tables of these schemas to redistribution user
                    if and only if the GUC enableSeparationOfDuty is on
                  3.Clear resource control related objects
        input : NA
        output: NA
        """

        # - Assume that buildTable is true, it means there will exist a pgxc_redistb
        #   table in each database.
        # - If you want to drop redisuser, you MUST drop these pgxc_redistb tables.
        # - This action will not be accordance with the meaning of buildTable parameter.

        # - Alter the owner of left tables which are for statistics purpose
        #   including redis_status, redis_progress_detail and redis_stage_detail.
        self.alterOwnerOfTablesForStatistics()

        # - This branch is to deal with the situation where enableSeparationOfDuty is on.
        # - More specifically, it revokes all schema relevant privileges after redistribution succeeds.
        if self.separationofduty:
            self.revokeAllSchemaPrivilegesToRedisuser()

        # Clear resource control related objects
        self.clearResourceControlObjects()

    def resourceControlForRedistribute(self):
        """
        function: entry for ResourceControl.run() methond when the parameter value after "-t" is "redistribute"
                  1.deal with redisuser, redisrespool
                  2.After prepare phase, change resource level to f
        input : NA
        output: NA
        """
        if self.containerized_mode:
            if self.redisuserexist:
                self.resetRedisUser()
            else:
                self.prepareResourceControlObjects('redisuser')
            return
        # Part 1: deal with redisuser, redisrespool
        if not self.redisuserexist and not self.redisrespoolexist:
            # prepare resource pool and associate to the correct workload group
            self.prepareResourceControlObjects('resourcepool')

            # prepare redisuser and grant enough privileges to it
            self.prepareResourceControlObjects('redisuser')

            # this branch is to deal with the situation where enableSeparationOfDuty is on
            if self.separationofduty:
                self.grantAllSchemaPrivilegesToRedisuser()
        elif not self.redisuserexist and self.redisrespoolexist:
            # prepare redisuser
            self.prepareResourceControlObjects('redisuser')

            # this branch is to deal with the situation where enableSeparationOfDuty is on
            if self.separationofduty:
                self.grantAllSchemaPrivilegesToRedisuser()
        elif self.redisuserexist and self.redisrespoolexist:
            if not self.redisrelation:
                raise Exception("The two resource control objects for redistribution (redisuser and redisrespool) "
                                "are not correctly associated. Please resolve the conflict at first since data "
                                "redistribution needs the two objects to finish redis task.")
            # reset the (randomized) passwd of redisuser
            self.resetRedisUser()
            # this branch is to deal with the situation where enableSeparationOfDuty is on
            if self.separationofduty:
                self.grantAllSchemaPrivilegesToRedisuser()
        # - It is impossible that redisuser exists but redisrespool does not exist
        #   because redisuser depends on redisrespool in terms of resource control logic.
        # - So in this scenario, we are able to be sure that the current redisuser in the database
        #   was not created by redistribution, instead, it already existed before.
        # - We deal with this issue by: exit and prompt a hint to the client, who will decide how to
        #   deal with this situation.
        else:
            raise Exception("The database user for redistribution (redisuser) has already existed. Please resolve "
                            "the conflict at first since data redistribution needs redisuser to finish redis task.")

    def resourceControlForResourcectl(self):
        """
        function: entry for ResourceControl.run() methond when the parameter value after "-t" is "resourcectl"
        input : NA
        output: NA
        """

        # This branch is mainly used to detect the incorrect situation for resourcectl.
        if not self.redisuserexist or not self.redisrespoolexist or not self.redisrelation:
            message = "The resource control objects are not correctly established, detailed information is as follows:"
            if not self.redisuserexist:
                message += "\nThe special user (redisuser) for redistribution does not exist!"
            if not self.redisrespoolexist:
                message += "\nThe special resource pool (redisrespool) for redistribution does not exist!"
            if not self.redisrelation:
                message += "\nThe two objects (redisuser and redisrespool) are not correctly associated!"
            raise Exception(message)

        # alter redisrespool's memory_limit to 1GB, to avoid ccn queue
        self.alterResourcePoolMemLimit()

        # 'f' means 'full (0%) resource level'
        if (self.reslevel == 'f'):
            self.changeResourceLevelToF()
        # 'h' means 'high (50%) resource level'
        elif (self.reslevel == 'h'):
            self.changeResourceLevelToH()
        # 'm' means 'mediate (30%) resource level'
        elif (self.reslevel == 'm'):
            self.changeResourceLevelToM()
        # 'l' means 'low (10%) resource level'
        elif (self.reslevel == 'l'):
            self.changeResourceLevelToL()
        else:
            raise Exception("Error in resource level, program will exit!")

    def checkActionIsLegal(self):
        """
        function: check whether the action is valid
        input : NA
        output: NA
        """
        try:
            if (self.action != "redistribute" and self.action != "resourcectl"):
                raise Exception("Parameter value of '-t' is incorrect! It must be redistribute or resourcectl")
        except Exception as e:
            raise Exception(str(e))

    def checkResourceLevelIsLegal(self):
        """
        function: check whether the resouce level is valid
        input : NA
        output: NA
        """

        try:
            if (self.reslevel not in ['f', 'l']):
                raise Exception("parameter value of resource level is incorrect! It must be f(full), l(low)")
        except Exception as e:
            raise Exception(str(e))

    def __executeSqlCommand(self, sql, ignoreError=False, database="postgres", log_sql=""):
        """
        """
        try:
            (status, output) = ClusterCommand.remoteSQLCommand(sql,
                                                               self.defaultuser,
                                                               self.redisCN.hostname,
                                                               self.redisCN.port,
                                                               ignoreError,
                                                               database,
                                                               enable_retry=True)
            if (status != 0):
                self.redisInstanceForResCtrl.logger.log("Failed to execute sql %s." % log_sql if log_sql else sql)
                raise Exception(ErrorCode.GAUSS_514["GAUSS_51400"] % sql + " Error:\n%s" % str(output))
            return output
        except Exception as e:
            raise Exception(str(e))

    def isGucParameterEnabled(self, param):
        """
        """
        try:
            sql = "SHOW %s;" % param
            self.redisInstanceForResCtrl.logger.debug("Sql command to check GUC %s is: %s" % (param, sql))
            output = self.__executeSqlCommand(sql)
            if output == "on":
                return True
            else:
                return False
        except Exception as e:
            raise Exception(str(e))

    def checkGUCForSeparationOfDutyIsOn(self):
        """
        function: Check whether GUC enableSeparationOfDuty is on
        input : NA
        output: NA
        """
        # check GUC enableSeparationOfDuty
        try:
            if self.isGucParameterEnabled("enableSeparationOfDuty"):
                self.separationofduty = True
        except Exception as e:
            raise Exception(str(e))

    def checkGUCsAreOn(self):
        """
        function: Check GUCs,
        input : NA
        output: NA
        """
        gucList = ['enable_memory_limit', 'use_workload_manager', 'enable_control_group',
                   'enable_backend_control', 'enable_vacuum_control']
        try:
            for guc in gucList:
                if not self.isGucParameterEnabled(guc):
                    if guc == 'enable_memory_limit':
                        if self.action == "resourcectl":
                            raise Exception("GUC parameter %s is not on! "
                                            "Please turn it on first for resource control functionality." % guc)
                        else:
                            self.redisInstanceForResCtrl.logger.log(
                                "Warning! GUC parameter enable_memory_limit is not on! "
                                "Can not use redistribute resource control! "
                                "Please turn it on first for resource control functionality.")
                    else:
                        raise Exception("GUC parameter %s is not on! "
                                        "Please turn it on first for resource control functionality." % guc)
        except Exception as e:
            raise Exception(str(e))

    def checkIfSqlResultExists(self, sql):
        """
        """
        try:
            output = self.__executeSqlCommand(sql)
            if output == '1':
                return True
            else:
                return False
        except Exception as e:
            raise Exception(str(e))

    def checkIfRedisUserExists(self):
        """
        function: Check the existence of redisuser
        input : NA
        output: NA
        """

        try:
            sql = "SELECT COUNT(USENAME) FROM pg_catalog.PG_USER WHERE USENAME = 'redisuser';"
            self.redisInstanceForResCtrl.logger.debug("Sql command to check if redisuser exists: %s" % sql)
            self.redisuserexist = self.checkIfSqlResultExists(sql)
        except Exception as e:
            raise Exception(str(e))

    def checkIfRedisRespoolExists(self):
        """
        function: Check the existence of redisrespool
        input : NA
        output: NA
        """

        try:
            sql = "SELECT COUNT(RESPOOL_NAME) FROM pg_catalog.PG_RESOURCE_POOL WHERE RESPOOL_NAME = 'redisrespool';"
            self.redisInstanceForResCtrl.logger.debug("Sql command to check if redisrespool exists: %s" % sql)
            self.redisrespoolexist = self.checkIfSqlResultExists(sql)
        except Exception as e:
            raise Exception(str(e))

    def checkRelationOfRedisuserAndRedisrespool(self):
        """
        function: Check the association relation of redisuser and redisrespool
        input : NA
        output: NA
        """

        try:
            sql = "SELECT COUNT(USENAME) FROM pg_catalog.PG_USER " \
                  "WHERE USENAME = 'redisuser' AND RESPOOL = 'redisrespool';"
            self.redisInstanceForResCtrl.logger.debug("Sql command to check the relation "
                                                      "between redisuser and redisrespool: %s" % sql)
            self.redisrelation = self.checkIfSqlResultExists(sql)
        except Exception as e:
            raise Exception(str(e))

    def changeResourceLevelToF(self):
        """
        function: change Resource Level To F:
                1.Alter memory percentage "mem_percent" to 0%
                2.Alter workload group to CPU resource level "f"
                3.alter the resource pool to limit the io to level f
        input : NA
        output: NA
        """
        self.alterResourcePoolMemPercent("Full")
        self.alterResourcePoolControlGroup("DefaultClass:High")

    def changeResourceLevelToH(self):
        """
        function: change Resource Level To H:
                  1.Alter memory percentage "mem_percent" to 50%
                  2.Alter workload group to CPU resource level "h"
        input : NA
        output: NA
        """
        self.alterResourcePoolMemPercent("High")
        self.alterResourcePoolControlGroup("DefaultClass:High")

    def changeResourceLevelToM(self):
        """
        function: change Resource Level To M:
                  1.Alter memory percentage "mem_percent" to 30%
                  2.Alter workload group to CPU resource level "m"
        input : NA
        output: NA
        """
        self.alterResourcePoolMemPercent("Medium")
        self.alterResourcePoolControlGroup("DefaultClass:Medium")

    def changeResourceLevelToL(self):
        """
        function: change Resource Level To L:
                  1.Alter memory percentage "mem_percent" to 10%
                  2.Alter workload group to CPU resource level "l"
                  3.alter the resource pool to limit the io to level l
        input : NA
        output: NA
        """
        self.alterResourcePoolMemPercent("Low")
        self.alterResourcePoolControlGroup("DefaultClass:Low")

    def prepareResourceControlObjects(self, objType):
        """
        function: Prepare resource control related objects according to the type of objects.
                  More specifically, the input parameter objType has three values:
                  - resourcepool: this value means that next the program
                  will prepare a resource pool called 'redisrespool'
                                  for redistribution.
                  - redisuser: this value means that next the program
                  will prepare a database user (with enough privilege)
                                called 'redisrespool' for redistribution.
        input : objType
        output: NA
        """

        # check validity of objType
        if (objType not in ['resourcepool', 'redisuser']):
            raise Exception("The value of resource control objType (%s) is not legal. "
                            "It should be 'resourcepool' or 'redisuser'."
                            % objType)

        # prepare resource control objects in terms of objType
        if ('resourcepool' == objType):
            self.prepareResourcePool()
        elif ('redisuser' == objType):
            self.prepareRedisUser()
        else:
            raise Exception("The type (%s) of resource control objects need to be prepared is incorrect!" % objType)

    def clearResourceControlObjects(self):
        """
        function: Clear resource control related objects:
                  1.Drop the user redisuser for redistribution
                  2.Drop the resource pool redisrespool
                  3.drop the workload group
                  4.drop the subclass resource group for redistribution
        input : NA
        output: NA
        """

        self.dropRedisUser()
        # - If a certain role is associated with a resource pool,
        #   the pool can not be dropped.
        # - So we should drop redisuser at first.
        self.dropResourcePool()

    #############################################################################
    # concrete interfaces to achieve resource control functionality
    #############################################################################
    def prepareResourcePool(self):
        """
        function: Create the resource pool redisrespool for resource control in redistribution
        input : NA
        output: NA
        """

        try:
            # sql for a transaction block
            START_TRANSACTION_SQL = "START TRANSACTION;"
            END_TRANSACTION_SQL = "COMMIT;"

            # sql to create the resource pool
            # set ACTIVE_STATEMENTS=-1 to avoid potential high concurrency issue
            # Physical cluster mode
            if not self.isElasticGroup:
                # Create a default resource pool
                CREATE_POOL_SQL = "CREATE RESOURCE POOL redisrespool with (" \
                                  "ACTIVE_STATEMENTS=-1, " \
                                  "control_group='DefaultClass:High', " \
                                  "mem_percent=0);"
                # combine them to form the final sql
                sql = "%s%s%s" % \
                      (START_TRANSACTION_SQL, CREATE_POOL_SQL, END_TRANSACTION_SQL)
            else:
                # sql to associate redisrespool to workload group
                CREATE_POOL_SQL = "CREATE RESOURCE POOL redisrespool with (" \
                                  "ACTIVE_STATEMENTS=-1, " \
                                  "control_group='DefaultClass:High', " \
                                  "nodegroup= '%s', " \
                                  "mem_percent=0);" % self.vcGroupName
                # combine them to form the final sql
                sql = "%s%s%s" % (START_TRANSACTION_SQL, CREATE_POOL_SQL, END_TRANSACTION_SQL)

            self.redisInstanceForResCtrl.logger.debug("Sql command to create the resource pool "
                                                      "for redistribution is: %s" % sql)
            _ = self.__executeSqlCommand(sql)

            # - A potential problem is that either this combined sql succeeds or fails, the program will continue.
            # - This is not the correct case in which the resource pool has not been established.
            # - So after executing the command, we further check whether resource pool exists, if not, the program will
            #   exit with an error.
            self.checkIfRedisRespoolExists()
            if not self.redisrespoolexist:
                raise Exception("Create the resource pool for redistribution failed. Please check:\n"
                                "1.Cgroup class group 'DefaultClass' and workload group 'High' "
                                "are correct in each node.\n"
                                "2.If the resource pool name 'redisrespool' already exists in the database.")
        except Exception as e:
            raise Exception(str(e))

    def prepareRedisUser(self):
        """
        function: Create a user and grant enough privilege to her for redistribution
        input : NA
        output: NA
        """

        try:
            # sql for a transaction block
            START_TRANSACTION_SQL = "START TRANSACTION;"
            END_TRANSACTION_SQL = "COMMIT;"

            # sql to create the redisuser in physical cluster mode
            CREATE_USER_SQL = "CREATE USER redisuser PASSWORD '%s';" % self.redispassword
            # sql to create the redisuser in virtual cluster mode
            # NOTICE: The execution of the 'create role' command is inconsistent in the two system
            # tables pg_authid and pg_user.
            # The execution of the 'create user' command is consistent in the pg_authid and pg_user system tables.
            CREATE_USER_VC_MODE_SQL = "CREATE USER redisuser node group \"%s\" PASSWORD '%s';" % \
                                      (self.vcGroupName, self.redispassword)
            # this is for protecting sensitive information
            CREATE_USER_SQL_WITHOUT_PASSWD = "CREATE USER redisuser PASSWORD 'xxx';"
            CREATE_USER_VC_MODE_NO_PASSWD = "CREATE USER redisuser node group \"%s\" PASSWORD 'xxx';" % self.vcGroupName

            # sql to drop the schema with the same name of redisuser
            # the reason for this operation is:
            # - This is because if we do not drop this schema, some tables for statistic purpose in database postgres
            #   will be created on schema 'redisuser', which will incur problem in dropping resource control objects
            #   after redistribution succeeds.
            # - After this step, all the tables in DB postgres will be created on schema 'public' or 'data_redis'.
            DROP_SCHEMA_SQL = "DROP SCHEMA IF EXISTS redisuser;"

            ASSOCIATE_TO_POOL_SQL = ""
            if not self.containerized_mode:
                # sql to associate redisuser to redisrespool
                ASSOCIATE_TO_POOL_SQL = "ALTER USER redisuser WITH RESOURCE POOL 'redisrespool';"

            # sql to grant all privilege to redisuser
            GRANT_ALL_PRIVILEGE_SQL = "GRANT ALL PRIVILEGE TO redisuser;"

            # Physical cluster mode
            if not self.isElasticGroup:
                # combine them to form the final sql
                sql = "%s%s%s%s%s%s" % \
                      (START_TRANSACTION_SQL, CREATE_USER_SQL,
                       DROP_SCHEMA_SQL, ASSOCIATE_TO_POOL_SQL,
                       GRANT_ALL_PRIVILEGE_SQL, END_TRANSACTION_SQL)

                # sqlforlog is only used to prevent from printing plaintext password message in the log
                sqlforlog = "%s%s%s%s%s%s" % \
                            (START_TRANSACTION_SQL, CREATE_USER_SQL_WITHOUT_PASSWD,
                             DROP_SCHEMA_SQL, ASSOCIATE_TO_POOL_SQL,
                             GRANT_ALL_PRIVILEGE_SQL, END_TRANSACTION_SQL)
            else:
                # combine them to form the final sql
                sql = "%s%s%s%s%s%s" % \
                      (START_TRANSACTION_SQL, CREATE_USER_VC_MODE_SQL,
                       DROP_SCHEMA_SQL, ASSOCIATE_TO_POOL_SQL,
                       GRANT_ALL_PRIVILEGE_SQL, END_TRANSACTION_SQL)

                # sqlforlog is only used to prevent from printing plaintext password message in the log
                sqlforlog = "%s%s%s%s%s%s" % \
                            (START_TRANSACTION_SQL, CREATE_USER_VC_MODE_NO_PASSWD,
                             DROP_SCHEMA_SQL, ASSOCIATE_TO_POOL_SQL,
                             GRANT_ALL_PRIVILEGE_SQL, END_TRANSACTION_SQL)

            self.redisInstanceForResCtrl.logger.debug("Sql command to create the special user "
                                                      "to conduct redistribution: %s" % sqlforlog)
            _ = self.__executeSqlCommand(sql, log_sql=sqlforlog)

            # - A potential problem is that either this combined sql succeeds or fails, the program will continue.
            # - This is not the correct case in which the redistribution user has not been established.
            # - So after executing the command, we further check whether redistribution user exists,
            # if not, the program will
            #   exit with an error.
            self.checkIfRedisUserExists()
            if not self.redisuserexist:
                raise Exception("Create the user 'redisuser' for redistribution failed. Please check:\n"
                                "1.If the resource pool 'redisrespool' to which the user "
                                "will associate does not exist.\n"
                                "2.If the user name 'redisuser' already exists in the database.")
        except Exception as e:
            raise Exception(str(e))

    def resetRedisUser(self):
        """
        function: - Reset the password of the redisuser for redistribution.
                  - This method is needed for the reentry of redistribution, because after we discard the -W parameter,
                  we should reset the randomized password to be the newly generated one.
        input : NA
        output: NA
        """

        try:
            sql = "ALTER USER redisuser IDENTIFIED BY '%s';" % self.redispassword
            self.redisInstanceForResCtrl.logger.debug("Resetting password of the special redis user.")
            _ = self.__executeSqlCommand(sql)
        except Exception as e:
            raise Exception(str(e))

    def dropRedisUser(self):
        """
        function: Drop the user redisuser for redistribution
        input : NA
        output: NA
        """
        try:
            sql = "DROP USER IF EXISTS redisuser CASCADE;"
            self.redisInstanceForResCtrl.logger.debug("Sql command to drop the special user is: %s" % sql)
            _ = self.__executeSqlCommand(sql)
        except Exception as e:
            raise Exception(str(e))

    def grantAllSchemaPrivilegesToRedisuser(self):
        """
        function: Grant all schemas and tables of these schemas to redistribution user if and only if
                  the GUC enableSeparationOfDuty is on!!!
                  This is because in this mode, even sysadmin is not enough for redisuser to conduct redistribution.
        input : NA
        output: NA
        """
        # define some essential variables
        schemalist = []
        dbname = ""
        schemaname = ""

        # obtain the non-template databases
        dbnamelist = self.obtainDatabaseInfo()
        self.redisInstanceForResCtrl.logger.debug("The database list is: %s" % dbnamelist)

        for dbname in dbnamelist:
            self.redisInstanceForResCtrl.logger.debug("The current working database is: %s" % dbname)
            # obtain the non-system schemas
            schemalist = self.obtainSchemaInfoPerDatabase(dbname)
            self.redisInstanceForResCtrl.logger.debug("The schema list in this database is: %s" % schemalist)
            for schemaname in schemalist:
                self.redisInstanceForResCtrl.logger.debug("The current working schema is: %s" % schemaname)
                self.grantTablesAndSchemaToRedisuserPerSchema(dbname, schemaname)

    def grantTablesAndSchemaToRedisuserPerSchema(self, dbname, schemaname):
        """
        function: Grant all privileges on the input schema and the tables
                  belonging to this schema to redistribution user
        input : NA
        output: NA
        """

        # check the validity of input parameter dbname
        if (dbname == ""):
            raise Exception("The input database name should not be an empty string!")

        # check the validity of input parameter schemaname
        if (schemaname == ""):
            self.redisInstanceForResCtrl.logger.debug("There is no non-system schema in database %s." % dbname)
            return

        try:
            # sql to grant related tables and schema privileges to the user
            # - Notice that GRANT operations can be repeatedly executed,
            # thus there is no need for them to use transaction protection
            # like "START TRANSACTION;GRANT ...;END;"
            if schemaname != schemaname.lower():
                # If schema name contains upper letters, need to add "" when the SQL statement is used.
                schemaname = "\"%s\"" % schemaname
            sql = "GRANT ALL PRIVILEGES ON SCHEMA %s TO redisuser;" \
                  "GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA %s TO redisuser;" % (schemaname, schemaname)
            self.redisInstanceForResCtrl.logger.debug("Sql command to grant ALL TABLES and "
                                                      "the corresponding schema to redisuser is: %s" % sql)
            _ = self.__executeSqlCommand(sql, False, dbname)
        except Exception as e:
            raise Exception(str(e))

    def revokeAllSchemaPrivilegesToRedisuser(self):
        """
        function: Revoke all schemas and tables of these schemas to redistribution user if and only if
                  the GUC enableSeparationOfDuty is on!!!
                  This is because in this mode, when you try to clear resource control objects after redistribution
                  succeeds, there will be privilege problem due to previous grant privilege operations in method
                  grantAllSchemaPrivilegesToRedisuser().
        input : NA
        output: NA
        """

        # define some essential variables
        schemalist = []
        dbname = ""
        schemaname = ""

        # obtain the non-template databases
        dbnamelist = self.obtainDatabaseInfo()
        self.redisInstanceForResCtrl.logger.debug("The database list is: %s" % dbnamelist)

        for dbname in dbnamelist:
            self.redisInstanceForResCtrl.logger.debug("The current working database is: %s" % dbname)
            # obtain the non-system schemas
            schemalist = self.obtainSchemaInfoPerDatabase(dbname)
            self.redisInstanceForResCtrl.logger.debug("The schema list in this database is: %s" % schemalist)
            for schemaname in schemalist:
                self.redisInstanceForResCtrl.logger.debug("The current working schema is: %s" % schemaname)
                self.revokeTablesAndSchemaToRedisuserPerSchema(dbname, schemaname)

    def revokeTablesAndSchemaToRedisuserPerSchema(self, dbname, schemaname):
        """
        function: Revoke all privileges on the input schema and the tables
                  belonging to this schema to redistribution user
        input : NA
        output: NA
        """

        # check the validity of input parameter dbname
        if (dbname == ""):
            raise Exception("The input database name should not be an empty string!")

        # check the validity of input parameter schemaname
        if (schemaname == ""):
            self.redisInstanceForResCtrl.logger.debug("There is no non-system schema in database %s." % dbname)
            return

        try:
            # sql to revoke related tables and schema privileges to the user
            sql = "REVOKE ALL PRIVILEGES ON SCHEMA %s FROM redisuser CASCADE;" \
                  "REVOKE ALL PRIVILEGES ON ALL TABLES IN SCHEMA %s FROM redisuser CASCADE;" % (schemaname, schemaname)
            self.redisInstanceForResCtrl.logger.debug("Sql command to revoke ALL TABLES "
                                                      "and the corresponding schema to redisuser is: %s" % sql)
            _ = self.__executeSqlCommand(sql, False, dbname)
        except Exception as e:
            raise Exception(str(e))

    def obtainDatabaseInfo(self):
        """
        function: To obtain the database information of the cluster, except template database template0
        input : NA
        output: NA
        """

        try:
            # sql to obtain the DBs in the cluster
            sql = "SELECT datname FROM pg_catalog.pg_database WHERE datname NOT IN ('template0');"
            self.redisInstanceForResCtrl.logger.debug("Sql command to obtain the database information is: %s" % sql)
            output = self.__executeSqlCommand(sql)
            dbnamelist = []
            # use the split method of string to transform the output string to a list
            dbnamelist = output.split("\n")
        except Exception as e:
            raise Exception(str(e))

        return dbnamelist

    def obtainSchemaInfoPerDatabase(self, dbname):
        """
        function: To obtain the non-system schema information of the input database
        input : NA
        output: NA
        """

        # check the validity of input parameter
        if (dbname == ""):
            raise Exception("The input database name should not be an empty string!")

        try:
            # sql to obtain the schemas in the input database
            sql = "SELECT nspname FROM pg_catalog.pg_namespace WHERE nspname NOT IN ('cstore', 'dbms_job', " \
                  "'dbms_lob', 'dbms_output', 'dbms_random', 'information_schema', 'pg_catalog', 'pg_toast', " \
                  " 'sys', 'utl_raw');"
            self.redisInstanceForResCtrl.logger.debug("Sql command to obtain the schema information in "
                                                      "database %s is: %s" % (dbname, sql))
            output = self.__executeSqlCommand(sql, False, dbname)
            schemalist = []
            # use the split method of string to transform the output string to a list
            schemalist = output.split("\n")
        except Exception as e:
            raise Exception(str(e))

        return schemalist

    def alterOwnerOfTablesForStatistics(self):
        """
        function: Alter the owner of tables for statistic purpose
        input : NA
        output: NA
        """
        try:
            sql = "ALTER TABLE IF EXISTS redis_status OWNER TO \"%s\";" % self.defaultuser
            self.redisInstanceForResCtrl.logger.debug("Sql command to alter the owner of table redis_status: %s" % sql)
            _ = self.__executeSqlCommand(sql)
        except Exception as e:
            raise Exception(str(e))

        try:
            sql = "ALTER TABLE IF EXISTS redis_progress_detail OWNER TO \"%s\";" % self.defaultuser
            self.redisInstanceForResCtrl.logger.debug("Sql command to alter the owner of "
                                                      "table redis_progress_detail: %s" % sql)
            _ = self.__executeSqlCommand(sql)
        except Exception as e:
            raise Exception(str(e))

        try:
            sql = "ALTER TABLE IF EXISTS redis_stage_detail OWNER TO \"%s\";" % self.defaultuser
            self.redisInstanceForResCtrl.logger.debug("Sql command to alter the owner of "
                                                      "table redis_stage_detail: %s" % sql)
            _ = self.__executeSqlCommand(sql)
        except Exception as e:
            raise Exception(str(e))

    def dropResourcePool(self):
        """
        function: Drop the resource pool redisrespool
        input : NA
        output: NA
        """

        try:
            sql = "DROP RESOURCE POOL IF EXISTS redisrespool;"
            self.redisInstanceForResCtrl.logger.debug("Sql command to drop the resource pool: %s" % sql)
            _ = self.__executeSqlCommand(sql)
        except Exception as e:
            raise Exception(str(e))

    def alterResourcePoolMemLimit(self):
        """
        function: Alter memory limit "memory_limit" to 1GB
        input : NA
        output: NA
        """
        try:
            sql = "ALTER RESOURCE POOL redisrespool WITH (memory_limit = '1GB');"
            self.redisInstanceForResCtrl.logger.debug("Sql command to alter the resource pool is: %s" % sql)
            _ = self.__executeSqlCommand(sql)
        except Exception as eMemLimit:
            raise Exception(str(eMemLimit))

    def alterResourcePoolMemPercent(self, level):
        """
        function: Alter memory percentage "mem_percent" percent%
        input : NA
        output: NA
        """
        try:
            if level in self.resourcePercentDict.keys():
                sql = "ALTER RESOURCE POOL redisrespool WITH (mem_percent = %d);" % int(self.resourcePercentDict[level])
                self.redisInstanceForResCtrl.logger.debug("Sql command to alter the resource pool is: %s" % sql)
                _ = self.__executeSqlCommand(sql)
        except Exception as e:
            raise Exception(str(e))

    def alterResourcePoolControlGroup(self, controlGroup):
        """
        """
        try:
            sql = "ALTER RESOURCE POOL redisrespool WITH (control_group = '%s');" % controlGroup
            self.redisInstanceForResCtrl.logger.debug("Sql command to alter control_group: %s" % sql)
            _ = self.__executeSqlCommand(sql)
        except Exception as e:
            raise Exception(str(e))
