diff options
Diffstat (limited to 'pgaas/src/stage/opt/app/pgaas/bin/dcae_admin_db.py')
| -rwxr-xr-x | pgaas/src/stage/opt/app/pgaas/bin/dcae_admin_db.py | 892 |
1 files changed, 892 insertions, 0 deletions
diff --git a/pgaas/src/stage/opt/app/pgaas/bin/dcae_admin_db.py b/pgaas/src/stage/opt/app/pgaas/bin/dcae_admin_db.py new file mode 100755 index 0000000..e1ac744 --- /dev/null +++ b/pgaas/src/stage/opt/app/pgaas/bin/dcae_admin_db.py @@ -0,0 +1,892 @@ +#!/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") +sys.path.append("/opt/app/dcae-commonlogging/python") +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") + + setupDictionaryDatabases(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() |