path: root/pgaas/src/stage/opt/app/pgaas/bin/dcae_admin_db.py

#!/usr/bin/python3
# -*- indent-tabs-mode: nil -*-
# Copyright (C) 2017 AT&T Intellectual Property. All rights reserved. 
#
# Licensed under the Apache License, Version 2.0 (the "License");
# you may not use this code except in compliance
# with the License. You may obtain a copy of the License
# at http://www.apache.org/licenses/LICENSE-2.0
#
# Unless required by applicable law or agreed to in writing, software
# distributed under the License is distributed on an "AS IS" BASIS,
# WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or
# implied. See the License for the specific language governing
# permissions and limitations under the License.


"""

NAME
    dcae_admin_db.py - given a database description json file, update the current VM accordingly

USAGE
    dcae_admin_db.py [options] configurationChanged json-file
    dcae_admin_db.py [options] suspend
    dcae_admin_db.py [options] resume
    dcae_admin_db.py [options] test

    options:

        -H / --dbhost= - host name, defaults to CFG['dcae_admin_db_hostname']
        -d / --dbdir= - database directory path, defaults to CFG['db_directory']
        -c / --dbconf= - database configuration path, defaults to CFG['db_configuration']
        -D / --dbname= - database name, defaults to CFG['dcae_admin_db_databasename']
        -U / --user= - user to login as, defaults to CFG['dcae_admin_db_username']
        -P / --password= - password for user, defaults to CFG['dcae_admin_db_password']
        -B / --bindir= - postgresql bin directory, defaults to CFG['pg_bin_directory']
        -i / --ignorefile= - skip configuration if this file is present, defaults to CFG['skip_configuration_file']
        -n / --nocreate - do not create the databases / users
        -I / --ignoredb - ignore current state of database
        -R / --remove - remove old databases / users
        -J / --jsontop= - top of json tree, as in \"['pgaas']\"
        -e / --errors= - where to redirect error output, defaults to CFG['dcae_admin_db_errors_file'] then stderr
        -t / --trace= - where to redirect trace output, defaults to CFG['dcae_admin_db_trace_file'] then stderr
        -v / --verbose - verbose, defaults to CFG['dcae_admin_db_verbosity']

DESCRIPTION
    This program is intended to be executed by the DCAE controller manager.

When creating a database and set of users, execute the equivalent of this:

    CREATE USER tstdb_admin  WITH PASSWORD 'tst';
    CREATE USER tstdb_user   WITH PASSWORD 'tst';
    CREATE USER tstdb_viewer WITH PASSWORD 'tst';

    CREATE ROLE testdb_common_user_role;
    CREATE ROLE testdb_common_viewer_role;

    CREATE DATABASE testdb with owner tstdb_admin;

    \connect testdb

    REVOKE ALL on DATABASE testdb FROM testdb_common_viewer_role;
    REVOKE ALL on DATABASE testdb FROM testdb_common_user_role;
    REVOKE ALL on DATABASE testdb FROM tstdb_user;
    REVOKE ALL on DATABASE testdb FROM tstdb_viewer;

    GRANT testdb_common_viewer_role TO testdb_common_user_role; /* user can do everything viewer can */
    GRANT testdb_common_user_role  TO tstdb_admin; /* admin can do everything user and viewer can */

    GRANT CONNECT ON DATABASE testdb TO testdb_common_viewer_role; /* viewer, user, admin can connect */

    CREATE SCHEMA testdb_db_common AUTHORIZATION tstdb_admin; /* create a schema we can optionally use */

    ALTER ROLE tstdb_admin               IN DATABASE testdb SET search_path = public, testdb_db_common; /* search_path is not inherited, so set it here */
    ALTER ROLE testdb_common_user_role   IN DATABASE testdb SET search_path = public, testdb_db_common; /* search_path is not inherited, so set it here */
    ALTER ROLE testdb_common_viewer_role IN DATABASE testdb SET search_path = public, testdb_db_common; /* search_path is not inherited, so set it here */

    GRANT USAGE  ON SCHEMA testdb_db_common TO testdb_common_viewer_role;  /* viewer,user can select from schema */
    GRANT CREATE ON SCHEMA testdb_db_common TO tstdb_admin;  /* admin can create on schema */

    ALTER DEFAULT PRIVILEGES FOR ROLE tstdb_admin GRANT SELECT ON TABLES TO testdb_common_viewer_role;                  /* viewer, user, admin can select on tables */
    ALTER DEFAULT PRIVILEGES FOR ROLE tstdb_admin GRANT INSERT, UPDATE, DELETE, TRUNCATE ON TABLES TO testdb_common_user_role; /* user, admin can ins/upd/del/tru on tables */
    ALTER DEFAULT PRIVILEGES FOR ROLE tstdb_admin GRANT USAGE, SELECT, UPDATE ON SEQUENCES TO testdb_common_user_role;         /* user, admin can update on sequences */

    GRANT TEMP ON DATABASE testdb TO testdb_common_user_role; /* user, admin can create temp tables */

    GRANT testdb_common_user_role TO tstdb_user;
    GRANT testdb_common_viewer_role TO tstdb_viewer;
    ALTER ROLE tstdb_user   IN DATABASE testdb SET search_path = public, testdb_db_common; /* search_path is not inherited, so set it here */
    ALTER ROLE tstdb_viewer IN DATABASE testdb SET search_path = public, testdb_db_common; /* search_path is not inherited, so set it here */

"""

import getopt
import psycopg2
import sys
import re
import subprocess
import json
import os
import time

sys.path.append("/opt/app/pgaas/lib")
import CommonLogger

verbose = 0
quiet = False
errorOutput = sys.stderr
traceOutput = sys.stderr
errorLogger = debugLogger = auditLogger = metricsLogger = None

def usage(msg = None):
    """
    Print a usage message and exit
    """
    sys.stdout = sys.stderr
    if msg != None:
        print(msg)
    print("Usage:")
    print("dcae_admin_db.py [options] configurationChanged json-file")
    print("dcae_admin_db.py [options] suspend")
    print("dcae_admin_db.py [options] resume")
    print("dcae_admin_db.py [options] test")
    print("dcae_admin_db.py [options] newdb dbname admin-pswd user-pswd viewer-pswd")
    print("")
    print("options:")
    print("-H / --dbhost= - host name, defaults to CFG['dcae_admin_db_hostname']")
    print("-d / --dbdir= - database directory path, defaults to CFG['db_directory']")
    print("-c / --dbconf= - database directory path, defaults to CFG['db_configuration']")
    print("-D / --dbname= - database name, defaults to CFG['dcae_admin_db_databasename']")
    print("-n / --nocreate - do not create the databases / users")
    print("-I / --ignoredb - ignore current state of database")
    print("-U / --user= - user to login as, defaults to CFG['dcae_admin_db_username']")
    print("-P / --password= - password for user, defaults to CFG['dcae_admin_db_password']")
    print("-B / --bindir= - postgresql bin directory, defaults to CFG['pg_bin_directory']")
    print("-i / --ignorefile= - skip configuration if this file is present, defaults to CFG['skip_configuration_file']")
    print("-R / --remove - remove old databases / users")
    print("-J / --jsontop= - top of json tree, as in \"['pgaas']\"")
    print("-l / --logcfg= - ECOMP DCAE Common Logging configuration file")
    print("-e / --errors= - where to redirect error output, defaults to CFG['dcae_admin_db_errors_file'] then stderr")
    print("-t / --trace= - where to redirect trace output, defaults to CFG['dcae_admin_db_trace_file'] then stderr")
    print("-v - verbose")
    sys.exit(2)

def checkOption(options, name, propname, optletter, encrypted=False, cdfPropname = None):
    """
    Check if the specified option exists. If not, grab it from the configuration file.
    Complain if it still does not exist.
    """
    if name not in options:
        ret = getPgaasPropValue(propname, encrypted=encrypted, dflt=None, skipComplaining=True)
        if ret is None and cdfPropname is not None:
            ret = getCdfPropValue(cdfPropname, encrypted=encrypted)
        options[name] = ret
    requireOption("either %s or config[%s]" % (optletter, propname), options[name])

def reviewOpts():
    """
    Parse the options passed to the command, and return them in the dictionary
    """
    try:
        opts, args = getopt.getopt(sys.argv[1:], "B:c:D:d:e:H:IJ:l:nP:Rt:U:hv?",
                                   [ "dbhost=", "dbdir=", "dbconf=",
                                     "dbname=", "dbuser=", "dbpassword=",
                                     "bindir=", "errors=", "trace=", "logcfg=",
                                     "nocreate", "ignoredb", "remove", "ignorefile=",
                                     "jsontop=",
                                     "help", "verbose"])
    except getopt.GetoptError as err:
        usage(str(err))

    propVerbosity = getPgaasPropValue("dcae_admin_db_verbosity", dflt='0')
    if propVerbosity is not None:
        global verbose
        verbose = int(propVerbosity)
    retOptions = { }
    ignoreFile = getPgaasPropValue("skip_configuration_file", dflt=None)
    for o, a in opts:
        if o in ("-v", "--verbose"):
            # global verbose
            verbose += 1
        elif o in ("-c", "--dbconf"):
            retOptions["dbconf"] = a
        elif o in ("-H", "--dbhost"):
            retOptions["dbhost"] = a
        elif o in ("-d", "--dbdir"):
            retOptions["dbdir"] = a
        elif o in ("-D", "--dbname"):
            retOptions["dbname"] = a
        elif o in ("-U", "--dbuser"):
            retOptions["dbuser"] = a
        elif o in ("-P", "--dbpassword"):
            retOptions["dbpassword"] = a
        elif o in ("-B", "--bindir"):
            retOptions["bindir"] = a
        elif o in ("-n", "--nocreate"):
            retOptions["nocreate"] = True
        elif o in ("-I", "--ignoredb"):
            retOptions["ignoredb"] = True
        elif o in ("-R", "--remove"):
            retOptions["noremove"] = True
        elif o in ("-J", "--jsontop"):
            retOptions["jsontop"] = a
        elif o in ("-l", "--logcfg"):
            retOptions["logcfg"] = a
        elif o in ("-e", "--errors"):
            retOptions["errors"] = a
        elif o in ("-i", "--ignorefile"):
            ignoreFile = a
        elif o in ("-t", "--trace"):
            retOptions["trace"] = a
        elif o in ("-h", "--help"):
            usage()
        else:
            usage("unhandled option: %s" % o)
    if "errors" not in retOptions:
        retOptions["errors"] = getPgaasPropValue("dcae_admin_db_errors_file")
    if "errors" in retOptions and retOptions["errors"] is not None:
        try:
            errorOutput = open(retOptions["errors"], "a")
        except Exception as e:
            die("Cannot open errors file '%s': %s" % (retOptions["errors"], e))
    if ignoreFile is not None:
        trace("checking to see if skip_configuration_file(%s) exists" % ignoreFile)
        retOptions["ignorefile"] = "yes" if os.path.isfile(ignoreFile) else "no"
        trace("ignorefile=%s" % retOptions["ignorefile"])
    else:
        retOptions["ignorefile"] = None
    if "trace" not in retOptions:
        retOptions["trace"] = getPgaasPropValue("dcae_admin_db_trace_file")
    if "trace" in retOptions and retOptions["trace"] is not None:
        try:
            traceOutput = open(retOptions["trace"], "a")
        except Exception as e:
            die("Cannot open trace file '%s': %s" % (retOptions["trace"], e))
    if "logcfg" not in retOptions:
        retOptions["logcfg"] = getPgaasPropValue("dcae_admin_db_common_logger_config")
    if "logcfg" in retOptions and retOptions["logcfg"] is not None:
        logcfg = retOptions["logcfg"]
        import uuid
        instanceUUID = uuid.uuid1()
        serviceName = "DCAE/pgaas"
        # print(">>>>>>>>>>>>>>>> using common logger. UUID=%s, serviceName=%s, cfg=%s" % (instanceUUID, serviceName, logcfg))
        global errorLogger, debugLogger, auditLogger, metricsLogger
        errorLogger = CommonLogger.CommonLogger(logcfg, "error", instanceUUID=instanceUUID, serviceName=serviceName)
        debugLogger = CommonLogger.CommonLogger(logcfg, "debug", instanceUUID=instanceUUID, serviceName=serviceName)
        auditLogger = CommonLogger.CommonLogger(logcfg, "audit", instanceUUID=instanceUUID, serviceName=serviceName)
        metricsLogger = CommonLogger.CommonLogger(logcfg, "metrics", instanceUUID=instanceUUID, serviceName=serviceName)
        auditLogger.info("using common logger. UUID=%s, serviceName=%s, cfg=%s" % (instanceUUID, serviceName, logcfg))

    checkOption(retOptions, "dbname", "dcae_admin_db_databasename", "-D")
    checkOption(retOptions, "dbuser", "dcae_admin_db_username", "-U")
    checkOption(retOptions, "dbpassword", "dcae_admin_db_password", "-P", encrypted=True, cdfPropname="postgres")
    checkOption(retOptions, "dbhost", "dcae_admin_db_hostname", "-H")
    checkOption(retOptions, "dbdir", "db_directory", "-d")
    checkOption(retOptions, "bindir", "pg_bin_directory", "-B")
    if "jsontop" not in retOptions:
        retOptions["jsontop"] = getPgaasPropValue("dcae_admin_db_jsontop")
    trace("env=%s" % str(os.environ))
    trace("ignorefile=%s" % ignoreFile)
    return retOptions, args

def main():
    keyedOptions, args = reviewOpts()
    trace("Invoked as: %s" % str(sys.argv))
    audit("Invoked as: %s" % str(sys.argv))

    if len(args) == 0:
        usage("no operation specified")
    elif args[0] == "configurationChanged":
        if len(args) != 2:
            usage("too many arguments")
        configurationChanged(keyedOptions, args[1])
    elif args[0] == "suspend":
        if len(args) != 1:
            usage("too many arguments")
        suspendOperations(keyedOptions)
    elif args[0] == "resume":
        if len(args) != 1:
            usage("too many arguments")
        resumeOperations(keyedOptions)
    elif args[0] == "test":
        if len(args) != 1:
            usage("too many arguments")
        testOperations(keyedOptions)
    elif args[0] == "newdb":
        if len(args) != 5:
            usage("wrong number of arguments")
        newDb(keyedOptions, args[1], args[2], args[3], args[4])
    else:
        usage("unrecognized operation '%s'" % args[0])

def suspendOperations(options):
    """
    Execute the "suspend" sub-command.
    """
    runProgram(["pkill", "repmgrd"])
    program = options["bindir"] + "/pg_ctl"
    cmd = [program, "stop", "-D", options["dbdir"]]
    runProgram(cmd)
    audit("suspendOperations")

def resumeOperations(options):
    """
    Execute the "resume" sub-command.
    """
    cmd = [options["bindir"] + "/pg_ctl", "start", "-D", options["dbdir"], "-o", "configfile=" + options["dbconf"]]
    runProgram(cmd)
    runProgram(["/opt/app/pgaas/bin/repmgrcd", "-d"])
    audit("resumeOperations")

def testOperations(options):
    """
    Respond to the "test" sub-command.
    """
    program = options["bindir"] + "/pg_ctl"
    cmd = [program, "status", "-D", options["dbdir"]]
    ret = runProgram(cmd)
    # pg_ctl: no server running
    # pg_ctl: server is running (PID: 13988)
    # does /var/run/postgresql/inmaintenance exist? -> YELLOW
    cmdRepmgr = ["pgrep", "repmgrd"]
    retRepmgr = runProgram(cmdRepmgr)
    
    msg = "????"
    if os.path.isfile("/var/run/postgresql/inmaintenance"):
        msg = "YELLOW: in maintenance mode"
    elif re.search("no server running", ret):
        msg = "RED: no PG server running"
    elif re.search("server is running", ret) and re.search("[0-9]+", retRepmgr):
        msg = "GREEN"
    elif re.search("server is running", ret):
        msg = "YELLOW: no repmgrd running"
    elif re.search("[0-9]+", retRepmgr):
        msg = "YELLOW: no PG server running"
    else:
        msg = "YELLOW: neither PG server nor repmgrd are running"
    audit("test: " + msg)
    print(msg, end="")

def runProgram(cmd):
    """
    Run the given command, returning the standard output as a string.
    If there is an error, return None.
    """
    try:
        p=subprocess.Popen(cmd,shell=False,stdout=subprocess.PIPE,stderr=subprocess.PIPE)
        (stdout, stderr) = p.communicate()
    except Exception as e:
        print("Error running program because: {0}".format(e), file=errorOutput)
        return None
    else:
        if stderr:
            print("Error running program because: {0} ".format(stderr), file=errorOutput)
            return None
        else:
            trace("runProgram() => " + str(stdout), minLevel=2)
            return stdout.decode('utf-8').rstrip('\n')

def configurationChanged(options, jsonFile):
    """
    We received a new JSON configuration file
    """
    audit("configurationChanged " + jsonFile)
    if options["ignorefile"] == "yes":
        trace("skipping database reconfiguration because skip_configuration_file exists")
        return

    if not os.path.isfile(jsonFile):
        die("json file %s does not exist" % jsonFile)

    try:
        inp = json.load(open(jsonFile,"r"))
    except Exception as e:
        die("Cannot open jsonFile '%s': %s" % (jsonFile, e))

    if verbose:
        dumpJSON(inp, "incoming JSON")

    jsonTop = options["jsontop"]
    if not jsonTop is None:
        e = "inp" + jsonTop
        trace("eval(%s)" % e)
        inp = eval(e,{"__builtins__":None},{"inp":inp})
        if verbose:
            dumpJSON(inp, "modified JSON")

    setupJsonDatabases(options, inp)

def setupDictionaryDatabases(options, inp):
    """
    Set up the databases listed in the dictionary
    """

    # trace("version=%s" % requireJSON("version", inp, "version"))
    requireJSON("databases", inp, "databases")
    con = None
    try:
        con = dbConnect(database = options["dbname"], user = options["dbuser"], password = options["dbpassword"], host = options["dbhost"])
        setupDatabases(con, options, requireJSON("databases", inp, "databases"))

    except psycopg2.DatabaseError as e:
        die('Error %s' % e)

    finally:
        if con:
            con.commit()
            con.close()

def newDb(options, dbName, adminPswd, userPswd, viewerPswd):
    """
    Given the database name and passwords, set up a database and corresponding users.
    For example, with dbname="foo", adminPswd="fooa", userPswd="foou" and viewerPswd="foov",
    act the same as if we had received the json configuration:
        {
            'databases': {
                'foo': {
                    'ownerRole': 'foo_admin',
                    'roles': {
                        'foo_admin': {
                            'password': 'fooa',
                            'role': 'admin'
                        },
                        'foo_user': {
                            'password': 'foou',
                            'role': 'writer'
                        },
                        'foo_viewer': {
                            'password': 'foov',
                            'role': 'reader'
                        }
                    }
                }
            }
        }
    """
    if not re.match("^[A-Za-z][A-Za-z0-9_]*$", dbName):
        errorPrint("'%s' is not a valid database name" % dbName)
        return

    adminName = dbName + "_admin"
    userName = dbName + "_user"
    viewerName = dbName + "_viewer"

    setupDictionaryDatabases(options, {
            'databases': {
                dbName: {
                    'ownerRole': adminName,
                    'roles': {
                        adminName: {
                            'password': adminPswd,
                            'role': 'admin'
                        },
                        userName: {
                            'password': userPswd,
                            'role': 'writer'
                        },
                        viewerName: {
                            'password': viewerPswd,
                            'role': 'reader'
                        }
                    }
                }
            }
        })

def dumpJSON(js, msg):
        tracePrint("vvvvvvvvvvvvvvvv %s" % msg)
        tracePrint(json.dumps(js, indent=4))
        tracePrint("^^^^^^^^^^^^^^^^ %s" % msg)

def setupDatabases(con, options, dbList):
    """
    Do what is needed to set up all of the databases
    """
    currentDatabases = dbGetFirstColumnAsMap(con, "select datname from pg_database where datistemplate = false")
    currentRolenames = dbGetFirstColumnAsMap(con, "select rolname from pg_roles")
    trace("currentDatabases = " + str(currentDatabases))
    for dbName in dbList:
        trace("dbName='%s'" % str(dbName))
        setupDatabase(con, options, currentDatabases, currentRolenames, dbName, dbList[dbName])

def setupDatabase(con, options, currentDatabases, currentRolenames, dbName, dbInfo):
    """
    Do what is needed to set up a given databases and its users
    """
    
    dbOwnerRole = requireJSON("databases[].ownerRole", dbInfo, "ownerRole")
    trace("dbName='%s', dbOwnerRole='%s'" % (dbName, dbOwnerRole))
    doesDbExist = dbName in currentDatabases
    trace("does %s exist? %s" % (dbName, doesDbExist))
    foundOwnerRole = False
    dbRoles = dbInfo["roles"]
    for name in dbRoles:
        u = dbRoles[name]
        if name == dbOwnerRole and u["role"] == "admin":
            foundOwnerRole = True
        if u["role"] not in ("admin","writer","reader"):
            die("For database %s, the role '%s' is not one of admin/writer/reader" % (dbName, u.role))
    if not foundOwnerRole:
        die("For database %s, information on the ownerRole '%s' was not found" % (dbName, dbOwnerRole))
    for name in dbRoles:
        userInfo = dbRoles[name]
        if name in currentRolenames and ("ignoredb" not in options or not options["ignoredb"]):
            trace("The role %s already exists, skipping" % name)
            updatePassword(con, options, dbName, name, userInfo)
        else:
            setupUser(con, options, dbName, name, userInfo)
    if doesDbExist and ("ignoredb" not in options or not options["ignoredb"]):
        trace("The database %s already exists, skipping" % dbName)
    else:
        makeDatabase(con, options, dbName, dbOwnerRole, dbInfo, dbRoles)
    for name in dbRoles:
        userInfo = dbRoles[name]
        if name in currentRolenames and ("ignoredb" not in options or not options["ignoredb"]):
            trace("The role %s already exists, skipping grants" % name)
        else:
            modifyGrants(con, options, dbName, name, userInfo)

def makeDatabase(con, options, dbName, dbOwnerRole, dbInfo, dbRoles):
    """
    Execute the SQL to create a database

    TODO: verify grants against what is actually there
    """
    ownerRole = dbInfo["ownerRole"]
    userRole = "{0}_common_user_role".format(dbName)
    viewerRole = "{0}_common_viewer_role".format(dbName)
        
    optionalDbExecute(con, options, "CREATE ROLE {0}".format(userRole))
    optionalDbExecute(con, options, "CREATE ROLE {0}".format(viewerRole))

    trace("Creating database %s with owner '%s'" % (dbName, ownerRole))
    optionalDbExecute(con, options, "CREATE DATABASE %s WITH OWNER %s" % (dbName, ownerRole))
    con2 = None
    try:
        con2 = dbConnect(database = dbName, user = options["dbuser"], password = options["dbpassword"], host = options["dbhost"])

        optionalDbExecute(con2, options, "REVOKE ALL on DATABASE {0} FROM {1}".format(dbName, viewerRole))
        optionalDbExecute(con2, options, "REVOKE ALL on DATABASE {0} FROM {1}".format(dbName, userRole))
        for name in dbRoles:
            userInfo = dbRoles[name]
            if userInfo["role"] == "writer" or userInfo["role"] == "reader":
                optionalDbExecute(con2, options, "REVOKE ALL on DATABASE {0} FROM {1}".format(dbName, name))

        #  user can do everything viewer can
        optionalDbExecute(con2, options, "GRANT {0} TO {1}".format(viewerRole, userRole))
        #  admin can do everything user and viewer can
        optionalDbExecute(con2, options, "GRANT {0} TO {1}".format(userRole, ownerRole))

        # viewer, user, admin can connect
        optionalDbExecute(con2, options, "GRANT CONNECT ON DATABASE {0} TO {1}".format(dbName, viewerRole))

        # create a schema we can optionally use *
        schemaName = "{0}_db_common".format(dbName)
        optionalDbExecute(con2, options, "CREATE SCHEMA if not exists {0} AUTHORIZATION {1}".format(schemaName, ownerRole))

        # search_path is not inherited, so set it here
        for role in [ ownerRole, userRole, viewerRole ]:
            optionalDbExecute(con2, options, "ALTER ROLE {1} IN DATABASE {0} SET search_path = public, {2}".format(dbName, role, schemaName))

        # viewer,user can select from schema
        optionalDbExecute(con2, options, "GRANT USAGE  ON SCHEMA {0} TO {1}".format(schemaName, viewerRole))
        # admin can create on schema
        optionalDbExecute(con2, options, "GRANT CREATE ON SCHEMA {0} TO {1}".format(schemaName, ownerRole))

        # viewer, user, admin can select on tables
        optionalDbExecute(con2, options, "ALTER DEFAULT PRIVILEGES FOR ROLE {1} GRANT SELECT ON TABLES TO {0}".format(viewerRole, ownerRole))
        # user, admin can ins/upd/del/tru on tables
        optionalDbExecute(con2, options, "ALTER DEFAULT PRIVILEGES FOR ROLE {1} GRANT INSERT, UPDATE, DELETE, TRUNCATE ON TABLES TO {0}".format(userRole, ownerRole))
        # user, admin can update on sequences
        optionalDbExecute(con2, options, "ALTER DEFAULT PRIVILEGES FOR ROLE {1} GRANT USAGE, SELECT, UPDATE ON SEQUENCES TO {0}".format(userRole, ownerRole))

        # user, admin can create temp tables
        optionalDbExecute(con2, options, "GRANT TEMP ON DATABASE {0} TO {1}".format(dbName, userRole))

        for name in dbRoles:
            userInfo = dbRoles[name]
            if userInfo["role"] == "writer":
                optionalDbExecute(con2, options, "GRANT {0} TO {1}".format(userRole, name))
            elif userInfo["role"] == "reader":
                optionalDbExecute(con2, options, "GRANT {0} TO {1}".format(viewerRole, name))

            # search_path is not inherited, so set it here
            optionalDbExecute(con2, options, "ALTER ROLE {1} IN DATABASE {0} SET search_path = public, {2}".format(dbName, name, schemaName))

    except psycopg2.DatabaseError as e:
        die('Error %s' % e)

    finally:
        if con2:
            con2.commit()
            con2.close()

def checkUsername(userName):
    """
    A value of type name is a string of 63 or fewer characters1. A name must start
    with a letter or an underscore; the rest of the string can contain letters,
    digits, and underscores.
    """
    trace("checkUsername(%s)" % userName)
    if re.match("[A-Za-z_][A-Za-z0-9_]*$", userName):
        return True
    else:
        errorPrint("%s is not a valid userName" % userName)
        return False

def setupUser(con, options, dbName, userName, userInfo):
    """
    Do what is needed to to set up a user for a database
    """
    if checkUsername(userName):
        trace("For dbName='%s', create user '%s'" % (dbName, userName))
        userPassword = userInfo["password"]
        optionalDbExecute(con, options, "create user %s with password '%s'" % (userName, userPassword))

def updatePassword(con, options, dbName, userName, userInfo):
    """
    Do what is needed to update a user's password 
    """
    if checkUsername(userName):
        trace("For dbName='%s', alter user '%s' password" % (dbName, userName))
        userPassword = userInfo["password"]
        optionalDbExecute(con, options, "alter user %s with password '%s'" % (userName, userPassword))

def modifyGrants(con, options, dbName, userName, userInfo):
    """
    Do what is needed to to set up a user for a database with the proper grants

    TODO: if user exist, verify current grants
    """
    if checkUsername(userName):
        userRole = userInfo["role"]
        trace("For dbName='%s', set up user '%s' as a '%s'" % (dbName, userName, userRole))
        if userRole == "writer":
            optionalDbExecute(con, options, "grant %s_common_user_role to %s" % (dbName, userName))
        elif userRole == "reader":
            optionalDbExecute(con, options, "grant %s_common_viewer_role to %s" % (dbName, userName))
        # elif userRole == "admin":
        #     optionalDbExecute(con, options, "grant %s_common_admin_role to %s" % (dbName, userName))
        else:
            trace("nothing to grant %s" % userName)

def optionalDbExecute(con, options, cmd):
    if "nocreate" in options and options["nocreate"]:
        print(cmd)
    else:
        audit("Running: " + cmd)
        dbExecute(con, cmd)

"""
database utility functions
"""

# def dbGetMap(con, cmd, args=[], skipTrace=False):
# def dbGetOneRowMap(con, cmd, args=[], skipTrace=False):

def dbGetFirstRowOneValue(con, cmd, args=[], skipTrace=False):
    """
    Do a select and return a single value from the first row
    """
    row = dbGetFirstRow(con, cmd, args, skipTrace)
    trace("row=" + str(row))
    if row is not None and len(row) > 0:
        return row[0]
    return None

def dbGetFirstRow(con, cmd, args=[], skipTrace=False):
    """
    Do a select and return the values from the first row
    """
    cursor = dbExecute(con, cmd, args, skipTrace)
    return cursor.fetchone()

def dbGetFirstColumn(con, cmd, args=[], skipTrace=False):
    """
    Do a select and return the first column's value from each row
    """
    ret = []
    cursor = dbExecute(con, cmd, args, skipTrace)
    for row in cursor:
        for col in row:
            ret.append(col)
            break
    return ret

def dbGetFirstColumnAsMap(con, cmd, args=[], skipTrace=False, val=1):
    """
    Do a select and return the first column's value from each row
    """
    ret = {}
    cursor = dbExecute(con, cmd, args, skipTrace)
    for row in cursor:
        for col in row:
            ret[col] = val
            break
    return ret

def dumpTable(con, tableName, max=-1):
    """
    If being extra verbose, print out the entire table
    """
    if verbose < 2:
        return
    print("================ " + tableName + " ================", file=traceOutput)

    cols = dbGetFirstColumn(con, "select column_name from information_schema.columns where table_name='" + tableName + "'", skipTrace=True)
    print("num", end="|", file=traceOutput)
    for col in cols:
        print(col, end="|", file=traceOutput)
    print("", file=traceOutput)

    if max > -1:
        cursor = dbExecute(con, "select * from " + tableName + " limit " + str(max), skipTrace=True)
    else:
        cursor = dbExecute(con, "select * from " + tableName, skipTrace=True)
    i = 0
    for row in cursor:
        print("%d" % i, end="|", file=traceOutput)
        i += 1
        for col in row:
            print("%s" % (col), end="|", file=traceOutput)
        print("", file=traceOutput)
    print("================================================", file=traceOutput)

def dbExecute(con, statement, args=[], skipTrace=False):
    """
    Create a cursor, instantiate the arguments into a statement, trace print the statement, and execute the statement.
    Return the cursor
    """
    cursor = con.cursor()
    stmt = cursor.mogrify(statement, args);
    if not skipTrace:
        trace("executing:" + str(stmt))
    cursor.execute(stmt)
    global quiet
    if not skipTrace:
        trace("statusmessage=" + cursor.statusmessage + ", rowcount=" + str(cursor.rowcount))
    return cursor

def dbConnect(database, user, password, host, autocommit = True):
    """
    Create a connection, logging it in the process
    Return the connection
    """
    trace("connecting to database %s as %s on host %s" % (database, user, host))
    con =psycopg2.connect(database = database, user = user, password = password, host = host)
    con.autocommit = autocommit
    return con

"""
Utility functions
"""

def die(msg):
    """
    Print a message to the error file and exit.
    """
    errorPrint(msg)
    sys.exit(1)

def errorPrint(msg, file=errorOutput):
    """
    Print a message to the error file.
    """
    global errorLogger
    # print("----------------> errorLogger=%s" % str(errorLogger))
    if errorLogger is not None:
        errorLogger.error(msg)
    else:
        taggedPrint("ERROR", msg, file=file)
        

def tracePrint(msg, file=traceOutput):
    """
    Print a message to the trace file.
    """
    global debugLogger
    # print("----------------> debugLogger=%s" % str(debugLogger))
    if debugLogger is not None:
        debugLogger.debug(msg)
    else:
        taggedPrint("DEBUG", msg, file=file)

def taggedPrint(tag, msg, file):
    """
    Print a message to the trace file.
    """
    dt = time.strftime('%Y-%m-%d %T', time.localtime())
    print("%s %s: %s" % (dt, tag, msg), file=file)

def requireOption(nm, val):
    """
    Die if a program parameter is not set
    """
    return require("option", nm, val)

def requireJSON(prnm, dict, nm):
    """
    Die if a JSON value is not set
    """
    if nm not in dict:
        die("The JSON value '%s' is missing" % prnm)
    return dict[nm]

def require(type, nm, val):
    """
    Die if a value is not set
    """
    if val is None:
        die("The %s '%s' is missing" % (type, nm))
    return val

def trace(msg, minLevel=1):
    """
    Print a message to trace output if verbose is turned on.
    """
    global verbose
    if verbose >= minLevel:
        tracePrint(msg)

def audit(msg):
    """
    Print a message to audit log if one is being used
    """
    global auditLogger
    if auditLogger is not None:
        auditLogger.info(msg)

def getCdfPropValue(nm, encrypted=False, cfg="/opt/app/cdf/lib/cdf.cfg", dflt=None, skipComplaining=False):
    """
    Return a value from the configuration file /opt/app/cdf/lib/cdf.cfg
    """
    return getPropValue(nm=nm, encrypted=encrypted, cfg=cfg, dflt=dflt, skipComplaining=skipComplaining)

def getPgaasPropValue(nm, encrypted=False, cfg="/opt/app/pgaas/lib/pgaas.cfg", dflt=None, skipComplaining=False):
    """
    Return a value from the configuration file /opt/app/pgaas/lib/pgaas.cfg
    """
    return getPropValue(nm=nm, encrypted=encrypted, cfg=cfg, dflt=dflt, skipComplaining=skipComplaining)

getPropDict = { }

def getPropValue(nm, encrypted=False, cfg=None, dflt=None, skipComplaining=False):
    """
    Return a value from the specified configuration file 
    """
    if cfg is None:
        return None
    global getPropDict
    if getPropDict.get(cfg):
        savedDate = getPropDict[cfg]
        # trace("getPropValue: savedDate[" + cfg + "]=" + str(savedDate))
        cfgDate = os.path.getmtime(cfg)
        # trace("getPropValue: cfgDate=" + str(cfgDate))
        if float(savedDate) >= float(cfgDate): # cfg has not changed
            val = getPropDict.get(cfg + ":" + nm)
            # trace("getPropValue: val=" + val)
            if val is not None:
                # trace("getPropValue: getPropValue(saved) => '%s'" % str(val))
                return val
        else: # clear out any previously saved keys
            cfgcolon = cfg + ":"
            for k in list(getPropDict.keys()):
                if re.match(cfgcolon, k):
                    del getPropDict[k]
    getPropValueProgram = '/opt/app/cdf/bin/getpropvalue'
    if encrypted:
        cmd = [getPropValueProgram, "-f", cfg, "-x", "-n", nm]
    else:
        cmd = [getPropValueProgram, "-f", cfg, "-n", nm]
    # trace("getPgaasPropValue: cmd=" + str(cmd))

    try:
        with subprocess.Popen(cmd,shell=False,stdout=subprocess.PIPE,stderr=subprocess.PIPE) as p:
            (origString, stderrString) = p.communicate()
    except Exception as e:
        traceback.print_exc()
        print("Error decoding string because {0}".format(e), file=errorOutput)
        return None
    else:
        if stderrString:
            if not re.search("Configuration property .* must be defined", stderrString.decode('utf-8')) and not skipComplaining:
                print("Error decoding string because: {0} ".format(stderr), file=errorOutput)
            return dflt
        else:
            trace("getPgaasPropValue() => " + str(origString), minLevel=2)
            return origString.decode('utf-8').rstrip('\n')

if __name__ == "__main__":
    main()