def rootconn(data, dbname='postgres', initialpassword=None):
"""
connect to a given server as postgres,
connecting to the specified database
"""
debug("rootconn(..data..,{0})".format(safestr(dbname)))
return doconn(rootdesc(data, dbname, initialpassword))
def dbexecute(crx, cmd, args=None):
"""
executes the SQL statement
Prints the entire command for debugging purposes
"""
debug("executing {}".format(cmd))
crx.execute(cmd, args)
def dbexecute_trunc_print(crx, cmd, args=None):
"""
executes the SQL statement.
Will print only the first 30 characters in the command
Use this function if you are executing an SQL cmd with a password
"""
debug("executing {}".format(cmd[:30]))
crx.execute(cmd, args)
def doconn(desc):
"""
open an SQL connection to the PG server
"""
debug("doconn({},{},{})".format(desc['host'], desc['user'], desc['database']))
# debug("doconn({},{},{},{})".format(desc['host'], desc['user'], desc['database'], desc['password']))
ret = psycopg2.connect(**desc)
ret.autocommit = True
return ret
def getclusterinfo(wfqdn, reuse, rfqdn, initialpassword, related):
"""
Retrieve all of the information specific to a cluster.
if reuse, retrieve it
else create and store it
"""
# debug("getclusterinfo({}, {}, {}, {}, ..related..)".format(safestr(wfqdn), safestr(reuse), safestr(rfqdn), safestr(initialpassword)))
debug("getclusterinfo({}, {}, {}, ..related..)".format(
safestr(wfqdn), safestr(reuse), safestr(rfqdn)))
if not chkfqdn(wfqdn):
raiseNonRecoverableError(
'Invalid FQDN specified for admin/read-write access, fqdn={0}'.
format(safestr(wfqdn)))
if reuse:
return get_existing_clusterinfo(wfqdn, rfqdn, related)
if rfqdn == '':
rfqdn = wfqdn
elif not chkfqdn(rfqdn):
raiseNonRecoverableError(
'Invalid FQDN specified for read-only access, fqdn={0}'.format(
safestr(rfqdn)))
if len(related) != 1:
raiseNonRecoverableError(
'Cluster SSH keypair must be specified using a dcae.relationships.pgaas_cluster_uses_sshkeypair '
+ 'relationship to a dcae.nodes.sshkeypair node')
data = {
'ro': rfqdn,
'pubkey': related[0].instance.runtime_properties['public'],
'data': related[0].instance.runtime_properties['base64private'],
'hash': 'sha256'
}
os.umask(0o77)
try:
os.makedirs('{0}'.format(OPT_MANAGER_RESOURCES_PGAAS))
except: # pylint: disable=bare-except
pass
try:
with open('{0}/{1}'.format(OPT_MANAGER_RESOURCES_PGAAS, wfqdn.lower()),
'w') as f:
f.write(json.dumps(data))
except Exception as e: # pylint: disable=broad-except
warn("Error: {0}".format(e))
warn("Stack: {0}".format(traceback.format_exc()))
raiseNonRecoverableError(
'Cannot write cluster information to {0}: fqdn={1}, err={2}'.
format(OPT_MANAGER_RESOURCES_PGAAS, safestr(wfqdn), e))
data['rw'] = wfqdn
if initialpassword:
with rootconn(data, initialpassword=initialpassword) as conn:
crr = conn.cursor()
dbexecute_trunc_print(
crr, "ALTER USER postgres WITH PASSWORD %s",
(getpass(data, 'postgres', wfqdn, 'postgres'), ))
crr.close()
return data
def rootdesc(data, dbname, initialpassword=None):
"""
return the postgres connection information
"""
debug("rootdesc(..data..,{0})".format(safestr(dbname)))
# pylint: disable=bad-continuation
return {
'database': dbname,
'host': hostportion(data['rw']),
'port': portportion(data['rw']),
'user': '******',
'password': initialpassword if initialpassword else getpass(data, 'postgres', data['rw'], 'postgres')
}
def delete_database(**kwargs): # pylint: disable=unused-argument
"""
dcae.nodes.pgaas.database:
Delete a database from a cluster
"""
try:
debug("delete_database() invoked")
dbname = ctx.node.properties['name']
warn("delete_database({0})".format(safestr(dbname)))
if not chkdbname(dbname):
return
debug('delete_database(): dbname checked out')
if ctx.node.properties['use_existing']:
return
debug('delete_database(): !use_existing')
dbinfo = dbgetinfo(ctx)
debug('Got db server info')
with rootconn(dbinfo) as conn:
crx = conn.cursor()
admu = ctx.instance.runtime_properties['admin']['user']
usru = ctx.instance.runtime_properties['user']['user']
vwru = ctx.instance.runtime_properties['viewer']['user']
cusr = '******'.format(dbname)
cvwr = '{0}_common_viewer_role'.format(dbname)
dbexecute(crx, 'DROP DATABASE IF EXISTS {0}'.format(dbname))
for r in [usru, vwru, admu, cusr, cvwr]:
dbexecute(crx, 'DROP ROLE IF EXISTS {0}'.format(r))
warn('All gone')
except Exception as e: # pylint: disable=broad-except
ctx.logger.warn("Error: {0}".format(e))
ctx.logger.warn("Stack: {0}".format(traceback.format_exc()))
raise e
def waithp(host, port):
"""
do a test connection to a host and port
"""
debug("waithp({0},{1})".format(safestr(host), safestr(port)))
sock = socket.socket(socket.AF_INET, socket.SOCK_STREAM)
try:
sock.connect((host, int(port)))
except: # pylint: disable=bare-except
a, b, c = sys.exc_info()
traceback.print_exception(a, b, c)
sock.close()
raiseRecoverableError('Server at {0}:{1} is not ready'.format(safestr(host), safestr(port)))
sock.close()
def update_database(refctx, **kwargs):
"""
dcae.nodes.pgaas.database:
Update the password for a database from a cluster
refctx is auto injected into the function when called as a workflow
"""
try:
debug("update_database() invoked")
################################################
# Verify refctx contains the <nodes> attribute. #
# The workflow context might not be consistent #
# across different cloudify versions #
################################################
if not hasattr(refctx, 'nodes'):
raiseNonRecoverableError(
'workflow context does not contain attribute=<nodes>. dir(refctx)={}'
.format(dir(refctx)))
############################################
# Verify that refctx.nodes is iterable #
############################################
if not isinstance(refctx.nodes, collections.Iterable):
raiseNonRecoverableError(
"refctx.nodes is not an iterable. Type={}".format(
type(refctx.nodes)))
ctx_node = None
##############################################
# Iterate through the nodes until we find #
# one with the properties we are looking for #
##############################################
for i in refctx.nodes:
############################################
# Safeguard: If a given node doesn't have #
# properties then skip it. #
# Don't cause an exception since the nodes #
# entry we are searching might still exist #
############################################
if not hasattr(i, 'properties'):
warn(
'Encountered a ctx node that does not have attr=<properties>. dir={}'
.format(dir(i)))
continue
debug("ctx node has the following Properties: {}".format(
list(i.properties.keys())))
if ('name' in i.properties) and ('writerfqdn' in i.properties):
ctx_node = i
break
###############################################
# If none of the nodes have properties: #
# <name> and <writerfqdn> then fatal error #
###############################################
if not ctx_node:
raiseNonRecoverableError(
'Either <name> or <writerfqdn> is not found in refctx.nodes.properties.'
)
debug("name is {}".format(ctx_node.properties['name']))
debug("host is {}".format(ctx_node.properties['writerfqdn']))
dbname = ctx_node.properties['name']
debug("update_database({0})".format(safestr(dbname)))
###########################
# dbname must be valid #
###########################
if not chkdbname(dbname):
raiseNonRecoverableError('dbname is null')
hostport = ctx_node.properties['writerfqdn']
debug('update_database(): wfqdn={}'.format(hostport))
dbinfo = dbgetinfo_for_update(hostport)
#debug('Got db server info={}'.format(dbinfo))
hostPortDbname = '{0}/{1}:{2}'.format(OPT_MANAGER_RESOURCES_PGAAS,
hostport.lower(), dbname.lower())
debug('update_database(): hostPortDbname={}'.format(hostPortDbname))
try:
appended = False
with open(hostPortDbname, "a") as fp:
with open("/dev/urandom", "rb") as rp:
b = rp.read(16)
print(binascii.hexlify(b).decode('utf-8'), file=fp)
appended = True
if not appended:
ctx.logger.warn(
"Error: the password for {} {} was not successfully changed"
.format(hostport, dbname))
except Exception as e: # pylint: disable=broad-except
ctx.logger.warn("Error: {0}".format(e))
ctx.logger.warn("Stack: {0}".format(traceback.format_exc()))
raise e
descs = dbdescs(dbinfo, dbname)
##########################################
# Verify we have expected keys #
# <admin>, <user>, and <viewer> as well #
# as "sub-key" <user> #
##########################################
if not isinstance(descs, dict):
raiseNonRecoverableError(
'db descs has unexpected type=<{}> was expected type dict'.
format(type(descs)))
for key in ("admin", "user", "viewer"):
if key not in descs:
raiseNonRecoverableError(
'db descs does not contain key=<{}>. Keys found for descs are: {}'
.format(key, list(descs.keys())))
if 'user' not in descs[key]:
raiseNonRecoverableError(
'db descs[{}] does not contain key=<user>. Keys found for descs[{}] are: {}'
.format(key, key, list(descs[key].keys())))
with rootconn(dbinfo) as conn:
crx = conn.cursor()
admu = descs['admin']['user']
usru = descs['user']['user']
vwru = descs['viewer']['user']
for r in [usru, vwru, admu]:
dbexecute_trunc_print(
crx, "ALTER USER {} WITH PASSWORD '{}'".format(
r, getpass(dbinfo, r, hostport, dbname)))
#debug("user={} password={}".format(r, getpass(dbinfo, r, hostport, dbname)))
warn('All users updated for database {}'.format(dbname))
except Exception as e: # pylint: disable=broad-except
ctx.logger.warn("Error: {0}".format(e))
ctx.logger.warn("Stack: {0}".format(traceback.format_exc()))
raise e
def create_database(**kwargs):
"""
dcae.nodes.pgaas.database:
Create a database on a cluster
"""
try:
debug("create_database() invoked")
dbname = ctx.node.properties['name']
warn("create_database({0})".format(safestr(dbname)))
if not chkdbname(dbname):
raiseNonRecoverableError(
'Unacceptable or missing database name: {0}'.format(
safestr(dbname)))
debug('create_database(): dbname checked out')
dbinfo = dbgetinfo(ctx)
debug('Got db server info')
descs = dbdescs(dbinfo, dbname)
ctx.instance.runtime_properties['admin'] = descs['admin']
ctx.instance.runtime_properties['user'] = descs['user']
ctx.instance.runtime_properties['viewer'] = descs['viewer']
with rootconn(dbinfo) as conn:
crx = conn.cursor()
dbexecute(
crx,
'SELECT datname FROM pg_database WHERE datistemplate = false')
existingdbs = [x[0] for x in crx]
if ctx.node.properties['use_existing']:
if dbname not in existingdbs:
raiseNonRecoverableError(
'use_existing specified but database does not exist, dbname={0}'
.format(safestr(dbname)))
return
dbexecute(crx, 'SELECT rolname FROM pg_roles')
existingroles = [x[0] for x in crx]
admu = descs['admin']['user']
usru = descs['user']['user']
vwru = descs['viewer']['user']
cusr = '******'.format(dbname)
cvwr = '{0}_common_viewer_role'.format(dbname)
schm = '{0}_db_common'.format(dbname)
if admu not in existingroles:
dbexecute_trunc_print(
crx, 'CREATE USER {0} WITH PASSWORD %s'.format(admu),
(descs['admin']['password'], ))
if usru not in existingroles:
dbexecute_trunc_print(
crx, 'CREATE USER {0} WITH PASSWORD %s'.format(usru),
(descs['user']['password'], ))
if vwru not in existingroles:
dbexecute_trunc_print(
crx, 'CREATE USER {0} WITH PASSWORD %s'.format(vwru),
(descs['viewer']['password'], ))
if cusr not in existingroles:
dbexecute(crx, 'CREATE ROLE {0}'.format(cusr))
if cvwr not in existingroles:
dbexecute(crx, 'CREATE ROLE {0}'.format(cvwr))
if dbname not in existingdbs:
dbexecute(
crx,
'CREATE DATABASE {0} WITH OWNER {1}'.format(dbname, admu))
crx.close()
with rootconn(dbinfo, dbname) as dbconn:
crz = dbconn.cursor()
for r in [cusr, cvwr, usru, vwru]:
dbexecute(
crz,
'REVOKE ALL ON DATABASE {0} FROM {1}'.format(dbname, r))
dbexecute(crz, 'GRANT {0} TO {1}'.format(cvwr, cusr))
dbexecute(crz, 'GRANT {0} TO {1}'.format(cusr, admu))
dbexecute(
crz,
'GRANT CONNECT ON DATABASE {0} TO {1}'.format(dbname, cvwr))
dbexecute(
crz,
'CREATE SCHEMA IF NOT EXISTS {0} AUTHORIZATION {1}'.format(
schm, admu))
for r in [admu, cusr, cvwr, usru, vwru]:
dbexecute(
crz,
'ALTER ROLE {0} IN DATABASE {1} SET search_path = public, {2}'
.format(r, dbname, schm))
dbexecute(crz,
'GRANT USAGE ON SCHEMA {0} to {1}'.format(schm, cvwr))
dbexecute(crz,
'GRANT CREATE ON SCHEMA {0} to {1}'.format(schm, admu))
dbexecute(
crz,
'ALTER DEFAULT PRIVILEGES FOR ROLE {0} GRANT SELECT ON TABLES TO {1}'
.format(admu, cvwr))
dbexecute(
crz,
'ALTER DEFAULT PRIVILEGES FOR ROLE {0} GRANT INSERT, UPDATE, DELETE, TRUNCATE ON TABLES TO {1}'
.format(admu, cusr))
dbexecute(
crz,
'ALTER DEFAULT PRIVILEGES FOR ROLE {0} GRANT USAGE, SELECT, UPDATE ON SEQUENCES TO {1}'
.format(admu, cusr))
dbexecute(crz,
'GRANT TEMP ON DATABASE {0} TO {1}'.format(dbname, cusr))
dbexecute(crz, 'GRANT {0} to {1}'.format(cusr, usru))
dbexecute(crz, 'GRANT {0} to {1}'.format(cvwr, vwru))
crz.close()
warn('All done')
except Exception as e: # pylint: disable=broad-except
ctx.logger.warn("Error: {0}".format(e))
ctx.logger.warn("Stack: {0}".format(traceback.format_exc()))
raise e