def get_database_uri(user='******', host='', database='datamodel', port=5432):
try:
password = '******' + getpass(host, port, database, user)
except:
password = ''
return "postgres://{user}{password}@{host}/{database}".format(
user=user, password=password, host=host, database=database)
def make_pg_connection(user=None, dbase=None, host=None, port=None):
if webspinner.WEBSPINNER_CONFIGURATION is not None:
if 'PGRES' in webspinner.WEBSPINNER_CONFIGURATION:
if user is None and 'user' in webspinner.WEBSPINNER_CONFIGURATION[
'PGRES']:
user = webspinner.WEBSPINNER_CONFIGURATION['PGRES']['user']
if dbase is None and 'dbase' in webspinner.WEBSPINNER_CONFIGURATION[
'PGRES']:
dbase = webspinner.WEBSPINNER_CONFIGURATION['PGRES']['dbase']
if host is None and 'host' in webspinner.WEBSPINNER_CONFIGURATION[
'PGRES']:
host = webspinner.WEBSPINNER_CONFIGURATION['PGRES']['host']
if port is None and 'port' in webspinner.WEBSPINNER_CONFIGURATION[
'PGRES']:
port = webspinner.WEBSPINNER_CONFIGURATION['PGRES']['port']
# get password from pgpass
pwd = pgpasslib.getpass(host, port, dbase, user)
con = psycopg2.connect(
'host={host} dbname={dbase} user={user} password={pwd}'.format(
host=host, dbase=dbase, user=user, pwd=pwd))
cur = con.cursor()
engine = create_engine(
'postgresql://{user}:{pwd}@{host}:{port}/{dbase}'.format(host=host,
dbase=dbase,
port=port,
user=user,
pwd=pwd))
return con, cur, engine
def con_args(self):
"""Return a database connection."""
# Setup Postgres Connection Paramters
user = getpass.getuser()
host = "gds_edit.nrel.gov"
dbname = "tech_potential"
port = 5432
password = pgpasslib.getpass(host, port, dbname, user)
# The user might need to set up their password
if not password:
msg = ("No password found for the PostGres database needed to "
"retrieve the transmission lines dataset. Please install "
"pgpasslib (pip) and add this line to ~/.pgpass: \n "
"gds_edit.nrel.gov:5432:tech_potential:<user_name>:"
"<password>")
raise LookupError(msg)
# Build kwargs
kwargs = {
"user": user,
"host": host,
"dbname": dbname,
"user": user,
"password": password,
"port": port
}
return kwargs
def _load_user_config_pgpass(self):
if not self._database_name or not self._database_user:
return
try:
password = pgpasslib.getpass(self._database_host,
self._database_port,
self._database_name,
self._database_user)
if password:
self._database_password = password
self.database_uri = 'postgresql://{}:{}@{}:{}/{}'.format(
self._database_user, self._database_password,
self._database_host, self._database_port,
self._database_name)
except pgpasslib.FileNotFound:
# Fail silently when no files found.
return
except pgpasslib.InvalidPermissions:
print(
"Your pgpass file has the wrong permissions, for your safety this file will be ignored. Please fix the permissions and try again."
)
return
except pgpasslib.PgPassException:
print("Unexpected error:", sys.exc_info()[0])
return
def _conn(self, dbname, silent_on_fail=False, **params):
"""Connects to the DB and tests the connection."""
if 'password' not in params:
try:
params['password'] = pgpasslib.getpass(dbname=dbname, **params)
except pgpasslib.FileNotFound:
params['password'] = None
PostgresqlDatabase.init(self, dbname, **params)
self._metadata = {}
try:
PostgresqlDatabase.connect(self)
self.dbname = dbname
except OperationalError as ee:
if not silent_on_fail:
log.warning(f'failed to connect to database {self.database!r}: {ee}')
PostgresqlDatabase.init(self, None)
if self.is_connection_usable() and self.auto_reflect:
with self.atomic():
for model in self.models.values():
if getattr(model._meta, 'use_reflection', False):
if hasattr(model, 'reflect'):
model.reflect()
if self.connected:
self.post_connect()
return self.connected
def test_getpass_returns_expected_result(self):
with mock.patch('pgpasslib._read_file') as read_file:
read_file.return_value = MOCK_CONTENT
self.assertEqual(
pgpasslib.getpass(
'foo.abjdite.us-east-1.'
'redshift.amazonaws.com', 5439, 'redshift', 'fonzy'),
'b3ar')
def get_database_uri():
database_uri = os.getenv('KINGFISHER_VIEWS_DB_URI')
if database_uri:
return database_uri
userpath = '~/.config/ocdskingfisher-views/config.ini'
fullpath = os.path.expanduser(userpath)
if not os.path.isfile(fullpath):
raise Exception(
'You must either set the KINGFISHER_VIEWS_DB_URI environment variable or create the {} file.\n'
'See https://kingfisher-views.readthedocs.io/en/latest/get-started.html'
.format(userpath))
# Same defaults as https://github.com/gmr/pgpasslib/blob/master/pgpasslib.py
default_username = getpass.getuser()
default_hostname = 'localhost'
config = configparser.ConfigParser()
config.read(fullpath)
username = config.get('DBHOST', 'USERNAME', fallback=default_username)
password = config.get('DBHOST', 'PASSWORD', fallback='')
hostname = config.get('DBHOST', 'HOSTNAME', fallback=default_hostname)
try:
port = config.getint('DBHOST', 'PORT', fallback=5432)
except ValueError as e:
raise Exception('PORT is invalid in {}. ({})'.format(userpath, e))
# We don't use the default database name (that matches the user name) as this is rarely what the user intends.
dbname = config.get('DBHOST', 'DBNAME')
# Instead of setting the database URI to "postgresql://:@:5432/dbname" (which implicitly uses the default
# username and default hostname), we set it to, for example, "postgresql://morgan:@localhost:5432/dbname".
if not username:
username = default_username
if not hostname:
hostname = default_hostname
if not dbname:
raise Exception('You must set DBNAME in {}.'.format(userpath))
# https://pgpasslib.readthedocs.io/en/latest/
try:
password_pgpass = pgpasslib.getpass(hostname, port, dbname, username)
if password_pgpass is not None:
password = password_pgpass
except pgpasslib.FileNotFound:
pass
except pgpasslib.InvalidPermissions as e:
logger.warning(
'Skipping PostgreSQL Password File: {}.\nTry: chmod 600 {}'.format(
e, e.args[0]))
except pgpasslib.InvalidEntry as e:
logger.warning('Skipping PostgreSQL Password File: {}'.format(e))
return 'postgresql://{}:{}@{}:{}/{}'.format(username, password, hostname,
port, dbname)
def _get_encrypted_password(cx, user):
dsn_complete = dict(kv.split('=') for kv in cx.dsn.split(" "))
dsn_partial = {key: dsn_complete[key] for key in ["host", "port", "dbname"]}
dsn_user = dict(dsn_partial, user=user)
password = pgpasslib.getpass(**dsn_user)
if password is None:
logger.warning("Missing line in .pgpass file: '%(host)s:%(port)s:%(dbname)s:%(user)s:<password>'", dsn_user)
raise ETLRuntimeError("password missing from PGPASSFILE for '{}'".format(user))
md5 = hashlib.md5()
md5.update((password + user).encode())
return "md5" + md5.hexdigest()
def query_missing_parameters(args):
species_db_info = \
list(filter(lambda db_info: db_info["database_name"] == args.species,
data_ops.get_species_pg_conn_info(args.metadb,
args.metauser,
args.metahost)
))[0]
complete_args = args
if args.assembly_name is None:
complete_args.assembly_name = data_ops.get_assembly_name(
species_db_info, args.build)
if args.latest_build:
complete_args.optional_build_line = ""
else:
complete_args.optional_build_line = "parameters.buildNumber={}".format(
args.build)
complete_args.taxonomy = args.species.split('_')[-1]
complete_args.dbsnp_host = species_db_info["pg_host"]
complete_args.dbsnp_port = species_db_info["pg_port"]
complete_args.dbsnp_build = species_db_info["dbsnp_build"]
complete_args.database_name = args.species
complete_args.dbsnp_password = pgpasslib.getpass(
host=complete_args.dbsnp_host,
port=complete_args.dbsnp_port,
dbname="*",
user=complete_args.dbsnp_user)
complete_args.job_tracker_password = pgpasslib.getpass(
host=complete_args.job_tracker_host,
port=complete_args.job_tracker_port,
dbname=complete_args.job_tracker_db,
user=complete_args.job_tracker_user)
return complete_args
def connect_to_redshift(
host_add=None,
dbname=None,
user=None,
port=None,
pwd=None,
):
"""Connect to Redshift server
- redshift parameter can be obtained from environment variables
- password can be obtained using pgpasslib module
parameters:
-----------
host_add: redshift host
dbname: redshift database name
user: redshift user id
port: redshift port name
pwd: redshift user password
"""
# user name need to be provided
if user is None:
raise (Exception(
"Did not provide a user_name for:\n\thost: {0}\n\tdbname: {1}".
format(host_add, dbname)))
# get password
if pwd is None:
pwd = pgpasslib.getpass(host=host_add,
port=port,
dbname=dbname,
user=user)
# connect to redshift with fixed user / password
try:
conn = psycopg2.connect(dbname=dbname,
host=host_add,
port=port,
user=user,
password=pwd)
logging.info("Successfully connected to Redshift")
return conn
except Exception as err:
logging.error(
"Unable to connect to the database with error {} of error_code: {}"
.format(err, err.code))
raise (BaseExpTaskException("Error in fetching the Redshift Connection"))
def connect(host, port, db, dbuser, table, schema, column_list,
partition_column, sort_keys, s3path, iamrole):
# get password from .pgpass or environment
try:
pg_pwd = pgpasslib.getpass(host, port, db, dbuser)
print(pg_pwd)
if pg_pwd:
pwd = pg_pwd
except pgpasslib.FileNotFound as e:
pass
# Connect to the cluster
try:
if debug:
print('Connecting to Redshift: %s' % host)
conn = pg8000.connect(database=db,
user=dbuser,
password=pwd,
host=host,
port=port,
ssl=ssl)
conn.autocommit = True
except:
print('Redshift Connection Failed: exception %s' % sys.exc_info()[1])
raise
if debug:
print('Successfully connected to Redshift cluster')
# create a new cursor
cursor = conn.cursor()
check_table_exists(cursor, conn, table, schema)
full_column_list, partition_keys, partition_column_type = get_column_list_partition_keys(
cursor, conn, table, schema, column_list, partition_column)
gen_unload(full_column_list, partition_keys, partition_column_type, schema,
table, partition_column, sort_keys, s3path, iamrole)
if execute:
print("Executing unload commands !")
execute_unload(cursor, conn)
conn.commit()
if debug:
print("Done with the script !!")
def pg8k_conn(dbname, **overrides):
# http://pythonhosted.org/pg8000/dbapi.html#pg8000.paramstyle
pg8000.paramstyle = 'pyformat' # Python format codes, eg. WHERE name=%(paramname)s
pg8000.autocommit = True # not working ..?
kwargs = defaults(dbname, **overrides)
# argh! pg8k doesn't support the effing .pgpass file.
# reason enough to swap it out when I have the time
# https://github.com/gmr/pgpasslib/blob/master/pgpasslib.py#L46
import pgpasslib
password = pgpasslib.getpass(**kwargs)
if not password:
raise ValueError('Did not find a password in the .pgpass file')
kwargs = utils.rename_keys(kwargs, [('dbname', 'database')])
kwargs['password'] = password
return pg8000.connect(**kwargs)
def get_db_uri(dbname, host=None, user=None):
server_name = host or pathlib.Path(
os.path.expanduser('~/.default.sead.server')).read_text().rstrip()
user_name = user or pathlib.Path(
os.path.expanduser('~/.default.sead.username')).read_text().rstrip()
password = pgpasslib.getpass(host=server_name,
port='5432',
dbname=dbname,
user=user_name)
if not password:
raise ValueError('Did not find a password in the .pgpass file')
uri = "postgresql://{}:{}@{}/postgres".format(user_name, password,
server_name, dbname)
return uri
def connect(self):
"""
It establishes database connection using credentials
which are set during initialization.
If password is not provided, will retreive the password from
.pgpass file using pgpasslib
"""
password = self.password
if self.password is None:
password = pgpasslib.getpass(dbname=self.dbname,
user=self.username,
host=self.host,
port=self.port)
self.conn = pg8000.connect(database=self.dbname,
user=self.username,
host=self.host,
port=self.port,
password=password)
self.conn.autocommit = True
def _get_password(self, **params):
''' Get a db password from a pgpass file
Parameters:
params (dict):
A dictionary of database connection parameters
Returns:
The database password for a given set of connection parameters
'''
password = params.get('password', None)
if not password:
try:
password = pgpasslib.getpass(params['host'], params['port'],
params['database'], params['username'])
except KeyError:
raise RuntimeError('ERROR: invalid server configuration')
return password
def connect(args):
"""
Connect to the database and save the connection as the global variable
'connection'.
"""
global engine
global db_connection
global ssh_conf
# Try getting a password from ~/.pgpass before asking the user to type it.
passw = pgpasslib.getpass(args.db_hostname, args.db_port, args.database,
args.db_user)
if not passw:
passw = getpass.getpass('Enter database password for user {}'.format(
args.db_user))
if args.skip_ssh_tunnel:
host = args.db_hostname
port = args.db_port
else:
ssh_conf['socket'] = tempfile.NamedTemporaryFile().name
ssh_conf['host'] = args.db_hostname
local_port = random.randint(10000, 60000)
if args.ssh_user is None:
args.ssh_user = args.db_user
exit_status = subprocess.call([
'ssh', '-MfN', '-S', ssh_conf['socket'], '-L',
'{}:{}:{}'.format(local_port, '127.0.0.1', args.db_port), '-o',
'ExitOnForwardFailure=yes', args.ssh_user + '@' + args.db_hostname
])
if exit_status != 0:
raise Exception(
'SSH tunnel failed with status: {}'.format(exit_status))
host = '127.0.0.1'
port = local_port
db_connection = psycopg2.connect(dbname=args.database,
user=args.db_user,
password=passw,
host=host,
port=port)
return db_connection
def __read_password(cls):
import pgpasslib
try:
cls.password = pgpasslib.getpass(cls.host, cls.port, cls.name,
cls.user)
except pgpasslib.FileNotFound:
print('.pgpass file not found. Please create and populate it.')
sys.exit(7)
except pgpasslib.InvalidEntry:
print('.pgpass file has unreadable field.')
sys.exit(7)
except pgpasslib.InvalidPermissions:
print(
'.pgpass file has invalid permissions (file as group or world readable bit set).'
)
sys.exit(7)
except pgpasslib.PgPassException as ex:
print('Error with .pgpass system')
print(ex)
sys.exit(6)\
def get_database_uri():
if os.environ.get('DB_URI'):
return os.environ.get('DB_URI')
config = configparser.ConfigParser()
# User level config file.
if os.path.isfile(os.path.expanduser('~/.config/ocdskingfisher/config.ini')):
read_files = config.read(os.path.expanduser('~/.config/ocdskingfisher/config.ini'))
elif os.path.isfile(os.path.expanduser('~/.config/ocdsdata/config.ini')):
read_files = config.read(os.path.expanduser('~/.config/ocdsdata/config.ini'))
else:
read_files = []
if len(read_files) == 0:
print("There are no config files nor DB_URI, therefore we cannot start.")
quit(-1)
# Loads database details or defaults
host = config.get('DBHOST', 'HOSTNAME')
port = config.get('DBHOST', 'PORT')
user = config.get('DBHOST', 'USERNAME')
dbname = config.get('DBHOST', 'DBNAME')
dbpass = config.get('DBHOST', 'PASSWORD', fallback='')
try:
fetched_pass = pgpasslib.getpass(host, port, user, dbname)
password = fetched_pass if fetched_pass else dbpass
database_uri = __gen_dburi(user, password, host, port, dbname)
except pgpasslib.FileNotFound:
# Fail silently when no files found.
database_uri = __gen_dburi(user, dbpass, host, port, dbname)
except pgpasslib.InvalidPermissions:
print("Your pgpass file has the wrong permissions, for your safety this file will be ignored. Please fix the permissions and try again.")
database_uri = __gen_dburi(user, dbpass, host, port, dbname)
except pgpasslib.PgPassException:
print("Unexpected error:", sys.exc_info()[0])
database_uri = __gen_dburi(user, dbpass, host, port, dbname)
return database_uri
def _get_encrypted_password(cx, user) -> Optional[str]:
"""Return MD5-hashed password if entry is found in PGPASSLIB or None otherwise."""
dsn_complete = dict(kv.split("=") for kv in cx.dsn.split(" "))
dsn_partial = {
key: dsn_complete[key]
for key in ["host", "port", "dbname"]
}
dsn_user = dict(dsn_partial, user=user)
try:
password = pgpasslib.getpass(**dsn_user)
except pgpasslib.FileNotFound as exc:
logger.info(
"Create the file using 'touch ~/.pgpass && chmod go= ~/.pgpass'")
raise ETLRuntimeError("PGPASSFILE file is missing") from exc
except pgpasslib.InvalidPermissions as exc:
logger.info("Update the permissions using: 'chmod go= ~/.pgpass'")
raise ETLRuntimeError(
"PGPASSFILE file has invalid permissions") from exc
if password is None:
return None
md5 = hashlib.md5()
md5.update((password + user).encode())
return "md5" + md5.hexdigest()
from binance.client import Client
import psycopg2
import json
import pgpasslib
import time
import random
from decimal import Decimal
from tendo import singleton
from datetime import datetime, timedelta
me = singleton.SingleInstance()
passw = pgpasslib.getpass('wytspace.cufjkhoqk6sk.us-east-1.rds.amazonaws.com',
5432, 'niexbot', 'highlander')
conn = psycopg2.connect(
host='wytspace.cufjkhoqk6sk.us-east-1.rds.amazonaws.com',
user='******',
dbname='niexbot',
password=passw,
port=5432,
connect_timeout=500)
symbols = [
'TRXBTC', 'XMRBTC', 'XRPBTC', 'ETHBTC', 'ETCBTC', 'XLMBTC', 'LTCBTC',
'RVNBTC'
]
client = Client('xxkeyxx', 'xxsecretxx')
data_dict = []
cur = conn.cursor()
insert_cmd = ""
save_balances = False
def run_analyze_vacuum(**kwargs):
global debug
if config_constants.DEBUG in os.environ:
debug = os.environ[config_constants.DEBUG]
if config_constants.DEBUG in kwargs and kwargs[config_constants.DEBUG]:
debug = True
# connect to cloudwatch
region_key = 'AWS_REGION'
if region_key in os.environ:
aws_region = os.environ[region_key]
else:
aws_region = 'us-east-1'
cw = None
if config_constants.SUPPRESS_CLOUDWATCH not in kwargs or not kwargs[config_constants.SUPPRESS_CLOUDWATCH]:
try:
cw = boto3.client('cloudwatch', region_name=aws_region)
comment("Connected to CloudWatch in %s" % aws_region)
except Exception as e:
if debug:
print(traceback.format_exc())
else:
if debug:
comment("Suppressing CloudWatch connection and metrics export")
# extract the cluster name
if config_constants.CLUSTER_NAME in kwargs:
cluster_name = kwargs[config_constants.CLUSTER_NAME]
# remove the cluster name argument from kwargs as it's a positional arg
del kwargs[config_constants.CLUSTER_NAME]
else:
cluster_name = kwargs[config_constants.DB_HOST].split('.')[0]
if debug:
comment("Using Cluster Name %s" % cluster_name)
comment("Supplied Args:")
print(kwargs)
# get the password using .pgpass, environment variables, and then fall back to config
db_pwd = None
try:
db_pwd = pgpasslib.getpass(kwargs[config_constants.DB_HOST], kwargs[config_constants.DB_PORT],
kwargs[config_constants.DB_NAME], kwargs[config_constants.DB_USER])
except pgpasslib.FileNotFound as e:
pass
if db_pwd is None:
db_pwd = kwargs[config_constants.DB_PASSWORD]
if config_constants.SCHEMA_NAME not in kwargs:
kwargs[config_constants.SCHEMA_NAME] = 'public'
# get a connection for the controlling processes
master_conn = get_pg_conn(kwargs[config_constants.DB_HOST],
kwargs[config_constants.DB_NAME],
kwargs[config_constants.DB_USER],
db_pwd,
kwargs[config_constants.SCHEMA_NAME],
kwargs[config_constants.DB_PORT],
None if config_constants.QUERY_GROUP not in kwargs else kwargs[
config_constants.QUERY_GROUP],
None if config_constants.QUERY_SLOT_COUNT not in kwargs else kwargs[
config_constants.QUERY_SLOT_COUNT],
None if config_constants.SSL not in kwargs else kwargs[config_constants.SSL])
if master_conn is None:
raise Exception("No Connection was established")
vacuum_flag = kwargs[config_constants.DO_VACUUM] if config_constants.DO_VACUUM in kwargs else False
if vacuum_flag is True:
# Run vacuum based on the Unsorted , Stats off and Size of the table
run_vacuum(master_conn, cluster_name, cw, **kwargs)
else:
comment("Vacuum flag arg is not set. Vacuum not performed.")
analyze_flag = kwargs[config_constants.DO_ANALYZE] if config_constants.DO_ANALYZE in kwargs else False
if analyze_flag is True:
if not vacuum_flag:
comment("Warning - Analyze without Vacuum may result in sub-optimal performance")
# Run Analyze based on the Stats off Metrics table
run_analyze(master_conn, cluster_name, cw, **kwargs)
else:
comment("Analyze flag arg is set as %s. Analyze is not performed." % analyze_flag)
comment('Processing Complete')
cleanup(master_conn)
return OK
def monitor_cluster(config_sources):
aws_region = get_config_value(['AWS_REGION'], config_sources)
set_debug = get_config_value(['DEBUG', 'debug', ], config_sources)
if set_debug is not None and ((isinstance(set_debug,bool) and set_debug) or set_debug.upper() == 'TRUE'):
global debug
debug = True
kms = boto3.client('kms', region_name=aws_region)
cw = boto3.client('cloudwatch', region_name=aws_region)
if debug:
print("Connected to AWS KMS & CloudWatch in %s" % aws_region)
user = get_config_value(['DbUser', 'db_user', 'dbUser'], config_sources)
host = get_config_value(['HostName', 'cluster_endpoint', 'dbHost', 'db_host'], config_sources)
port = int(get_config_value(['HostPort', 'db_port', 'dbPort'], config_sources))
database = get_config_value(['DatabaseName', 'db_name', 'db'], config_sources)
cluster = get_config_value(['ClusterName', 'cluster_name', 'clusterName'], config_sources)
global interval
interval = get_config_value(['AggregationInterval', 'agg_interval', 'aggregtionInterval'], config_sources)
set_debug = get_config_value(['debug', 'DEBUG'], config_sources)
if set_debug is not None:
global debug
debug = set_debug
# we may have been passed the password in the configuration, so extract it if we can
pwd = get_config_value(['db_pwd'], config_sources)
# override the password with the contents of .pgpass or environment variables
pwd = None
try:
pwd = pgpasslib.getpass(host, port, database, user)
except pgpasslib.FileNotFound as e:
pass
if pwd is None:
enc_password = get_config_value(['EncryptedPassword', 'encrypted_password', 'encrypted_pwd', 'dbPassword'],
config_sources)
# resolve the authorisation context, if there is one, and decrypt the password
auth_context = get_config_value('kms_auth_context', config_sources)
if auth_context is not None:
auth_context = json.loads(auth_context)
try:
if auth_context is None:
pwd = kms.decrypt(CiphertextBlob=base64.b64decode(enc_password))[
'Plaintext']
else:
pwd = kms.decrypt(CiphertextBlob=base64.b64decode(enc_password), EncryptionContext=auth_context)[
'Plaintext']
except:
print('KMS access failed: exception %s' % sys.exc_info()[1])
print('Encrypted Password: %s' % enc_password)
print('Encryption Context %s' % auth_context)
raise
# Connect to the cluster
try:
if debug:
print('Connecting to Redshift: %s' % host)
conn = pg8000.connect(database=database, user=user, password=pwd, host=host, port=port, ssl=ssl)
except:
print('Redshift Connection Failed: exception %s' % sys.exc_info()[1])
raise
if debug:
print('Successfully Connected to Cluster')
# create a new cursor for methods to run through
cursor = conn.cursor()
# set application name
set_name = "set application_name to 'RedshiftAdvancedMonitoring-v%s'" % __version__
if debug:
print(set_name)
cursor.execute(set_name)
# collect table statistics
put_metrics = gather_table_stats(cursor, cluster)
# collect service class statistics
put_metrics.extend(gather_service_class_stats(cursor, cluster))
# run the externally configured commands and append their values onto the put metrics
put_metrics.extend(run_external_commands('Redshift Diagnostic', 'monitoring-queries.json', cursor, cluster))
# run the supplied user commands and append their values onto the put metrics
put_metrics.extend(run_external_commands('User Configured', 'user-queries.json', cursor, cluster))
# add a metric for how many metrics we're exporting (whoa inception)
put_metrics.extend([{
'MetricName': 'CloudwatchMetricsExported',
'Dimensions': [
{'Name': 'ClusterIdentifier', 'Value': cluster}
],
'Timestamp': datetime.datetime.utcnow(),
'Value': len(put_metrics),
'Unit': 'Count'
}])
max_metrics = 20
group = 0
print("Publishing %s CloudWatch Metrics" % (len(put_metrics)))
for x in range(0, len(put_metrics), max_metrics):
group += 1
# slice the metrics into blocks of 20 or just the remaining metrics
put = put_metrics[x:(x + max_metrics)]
if debug:
print("Metrics group %s: %s Datapoints" % (group, len(put)))
print(put)
try:
cw.put_metric_data(
Namespace='Redshift',
MetricData=put
)
except:
print('Pushing metrics to CloudWatch failed: exception %s' % sys.exc_info()[1])
raise
cursor.close()
conn.close()
from dataframe_browser.client import Cursor
import pgpasslib
query = '''SELECT wkfnwb.storage_directory || wkfnwb.filename AS nwb_file, oe.experiment_container_id AS experiment_container_id, oe.ophys_session_id AS ophys_session_id
FROM experiment_containers ec JOIN ophys_experiments oe ON oe.experiment_container_id=ec.id AND oe.workflow_state = 'passed'
JOIN images mip ON mip.id=oe.maximum_intensity_projection_image_id
JOIN well_known_files wkfnwb ON wkfnwb.attachable_id=oe.id JOIN well_known_file_types wkft ON wkft.id=wkfnwb.well_known_file_type_id AND wkft.name = 'NWBOphys'
JOIN ophys_sessions os ON os.id=oe.ophys_session_id JOIN projects osp ON osp.id=os.project_id
WHERE osp.code = 'C600' AND ec.workflow_state NOT IN ('failed')
AND ec.workflow_state = 'published';'''
c = Cursor(session_uuid='association')
c.read(uri='postgresql://*****:*****@limsdb2:5432/lims2'.format(password=pgpasslib.getpass('limsdb2', 5432, 'lims2', 'limsreader')), query=query)
c.drop(columns='experiment_container_id')
c.hinge(column='ophys_session_id', uuid='33e6a0d43f4347f3bcc10c4a2659ba65')
c.hinge(column='nwb_file', uuid='7fa00718647f4ebcaf42246eb36eb6b1')
c.set_hinge_table(name='ophys_session')
c2 = Cursor(session_uuid='example')
c2.read(uri='postgresql://*****:*****@limsdb2:5432/lims2'.format(password=pgpasslib.getpass('limsdb2', 5432, 'lims2', 'limsreader')), query=query)
c2.drop(columns='nwb_file')
c2.hinge(column='ophys_session_id', uuid='33e6a0d43f4347f3bcc10c4a2659ba65')
def monitor_cluster(config_sources):
aws_region = get_config_value(['AWS_REGION'], config_sources)
set_debug = get_config_value(['DEBUG', 'debug', ], config_sources)
if set_debug is not None and ((isinstance(set_debug,bool) and set_debug) or set_debug.upper() == 'TRUE'):
global debug
debug = True
kms = boto3.client('kms', region_name=aws_region)
cw = boto3.client('cloudwatch', region_name=aws_region)
if debug:
print("Connected to AWS KMS & CloudWatch in %s" % aws_region)
user = get_config_value(['DbUser', 'db_user', 'dbUser'], config_sources)
host = get_config_value(['HostName', 'cluster_endpoint', 'dbHost', 'db_host'], config_sources)
port = int(get_config_value(['HostPort', 'db_port', 'dbPort'], config_sources))
database = get_config_value(['DatabaseName', 'db_name', 'db'], config_sources)
cluster = get_config_value(['ClusterName', 'cluster_name', 'clusterName'], config_sources)
global interval
interval = get_config_value(['AggregationInterval', 'agg_interval', 'aggregtionInterval'], config_sources)
set_debug = get_config_value(['debug', 'DEBUG'], config_sources)
if set_debug is not None:
global debug
debug = set_debug
pwd = None
try:
pwd = pgpasslib.getpass(host, port, database, user)
except pgpasslib.FileNotFound as e:
pass
# check if unencrypted password exists if no pgpasslib
if pwd is None:
pwd = get_config_value(['db_pwd'], config_sources)
# check for encrypted password if the above two don't exist
if pwd is None:
enc_password = get_config_value(['EncryptedPassword', 'encrypted_password', 'encrypted_pwd', 'dbPassword'],
config_sources)
# resolve the authorisation context, if there is one, and decrypt the password
auth_context = get_config_value('kms_auth_context', config_sources)
if auth_context is not None:
auth_context = json.loads(auth_context)
try:
if auth_context is None:
pwd = kms.decrypt(CiphertextBlob=base64.b64decode(enc_password))[
'Plaintext']
else:
pwd = kms.decrypt(CiphertextBlob=base64.b64decode(enc_password), EncryptionContext=auth_context)[
'Plaintext']
except:
print('KMS access failed: exception %s' % sys.exc_info()[1])
print('Encrypted Password: %s' % enc_password)
print('Encryption Context %s' % auth_context)
raise
# Connect to the cluster
try:
if debug:
print('Connecting to Redshift: %s' % host)
conn = pg8000.connect(database=database, user=user, password=pwd, host=host, port=port, ssl=ssl)
except:
print('Redshift Connection Failed: exception %s' % sys.exc_info()[1])
raise
if debug:
print('Successfully Connected to Cluster')
# create a new cursor for methods to run through
cursor = conn.cursor()
# set application name
set_name = "set application_name to 'RedshiftAdvancedMonitoring-v%s'" % __version__
if debug:
print(set_name)
cursor.execute(set_name)
# collect table statistics
put_metrics = gather_table_stats(cursor, cluster)
# collect service class statistics
put_metrics.extend(gather_service_class_stats(cursor, cluster))
# run the externally configured commands and append their values onto the put metrics
put_metrics.extend(run_external_commands('Redshift Diagnostic', 'monitoring-queries.json', cursor, cluster))
# run the supplied user commands and append their values onto the put metrics
put_metrics.extend(run_external_commands('User Configured', 'user-queries.json', cursor, cluster))
# add a metric for how many metrics we're exporting (whoa inception)
put_metrics.extend([{
'MetricName': 'CloudwatchMetricsExported',
'Dimensions': [
{'Name': 'ClusterIdentifier', 'Value': cluster}
],
'Timestamp': datetime.datetime.utcnow(),
'Value': len(put_metrics),
'Unit': 'Count'
}])
max_metrics = 20
group = 0
print("Publishing %s CloudWatch Metrics" % (len(put_metrics)))
for x in range(0, len(put_metrics), max_metrics):
group += 1
# slice the metrics into blocks of 20 or just the remaining metrics
put = put_metrics[x:(x + max_metrics)]
if debug:
print("Metrics group %s: %s Datapoints" % (group, len(put)))
print(put)
try:
cw.put_metric_data(
Namespace='Redshift',
MetricData=put
)
except:
print('Pushing metrics to CloudWatch failed: exception %s' % sys.exc_info()[1])
raise
cursor.close()
conn.close()
def configure(**kwargs):
# setup globals
global db
global db_user
global db_pwd
global db_host
global db_port
global threads
global schema_name
global table_name
global new_dist_key
global new_sort_keys
global analyze_col_width
global target_schema
global debug
global do_execute
global query_slot_count
global ignore_errors
global force
global drop_old_data
global comprows
global query_group
global ssl
global suppress_cw
global cw
global statement_timeout
# set variables
for key, value in kwargs.items():
setattr(thismodule, key, value)
if debug:
comment("%s = %s" % (key, value))
# override the password with the contents of .pgpass or environment variables
pwd = None
try:
pwd = pgpasslib.getpass(kwargs[config_constants.DB_HOST], kwargs[config_constants.DB_PORT],
kwargs[config_constants.DB_NAME], kwargs[config_constants.DB_USER])
except pgpasslib.FileNotFound as e:
pass
if pwd is not None:
db_pwd = pwd
# create a cloudwatch client
region_key = 'AWS_REGION'
aws_region = os.environ[region_key] if region_key in os.environ else 'us-east-1'
if "suppress_cw" not in kwargs or not kwargs["suppress_cw"]:
try:
cw = boto3.client('cloudwatch', region_name=aws_region)
except Exception as e:
if debug:
print(traceback.format_exc())
if debug:
comment("Redshift Column Encoding Utility Configuration")
if "suppress_cw" in kwargs and kwargs["suppress_cw"]:
comment("Suppressing CloudWatch metrics")
else:
if cw is not None:
comment("Created Cloudwatch Emitter in %s" % aws_region)
def configure(**kwargs):
# setup globals
global db
global db_user
global db_pwd
global db_host
global db_port
global threads
global schema_name
global table_name
global new_dist_key
global new_sort_keys
global analyze_col_width
global target_schema
global debug
global do_execute
global query_slot_count
global ignore_errors
global force
global drop_old_data
global comprows
global query_group
global ssl
global suppress_cw
global cw
# set variables
for key, value in kwargs.items():
setattr(thismodule, key, value)
if debug:
comment("%s = %s" % (key, value))
# override the password with the contents of .pgpass or environment variables
pwd = None
try:
pwd = pgpasslib.getpass(kwargs[config_constants.DB_HOST],
kwargs[config_constants.DB_PORT],
kwargs[config_constants.DB_NAME],
kwargs[config_constants.DB_USER])
except pgpasslib.FileNotFound as e:
pass
if pwd is not None:
db_pwd = pwd
# create a cloudwatch client
region_key = 'AWS_REGION'
aws_region = os.environ[
region_key] if region_key in os.environ else 'us-east-1'
if "suppress_cw" not in kwargs or not kwargs["suppress_cw"]:
try:
cw = boto3.client('cloudwatch', region_name=aws_region)
except Exception as e:
if debug:
print(traceback.format_exc())
if debug:
comment("Redshift Column Encoding Utility Configuration")
if "suppress_cw" in kwargs and kwargs["suppress_cw"]:
comment("Suppressing CloudWatch metrics")
else:
if cw is not None:
comment("Created Cloudwatch Emitter in %s" % aws_region)
def _get_passwd_from_pgpass(self):
return pgpasslib.getpass(self.host, self.port, self.db, self.user)
def test_getpass_returns_expected_result(self):
with mock.patch('pgpasslib._read_file') as read_file:
read_file.return_value = MOCK_CONTENT
self.assertEqual(pgpasslib.getpass('localhost', 5432,
'foo', 'kermit'), '')
def snapshot(config_sources):
aws_region = get_config_value(['AWS_REGION'], config_sources)
set_debug = get_config_value(['DEBUG', 'debug', ], config_sources)
if set_debug is not None and (set_debug or set_debug.upper() == 'TRUE'):
global debug
debug = True
kms = boto3.client('kms', region_name=aws_region)
if debug:
print("Connected to AWS KMS & CloudWatch in %s" % aws_region)
user = get_config_value(['DbUser', 'db_user', 'dbUser'], config_sources)
host = get_config_value(['HostName', 'cluster_endpoint', 'dbHost', 'db_host'], config_sources)
port = int(get_config_value(['HostPort', 'db_port', 'dbPort'], config_sources))
database = get_config_value(['DatabaseName', 'db_name', 'db'], config_sources)
cluster_name = get_config_value([config_constants.CLUSTER_NAME], config_sources)
unload_s3_location = get_config_value([config_constants.S3_UNLOAD_LOCATION], config_sources)
unload_role_arn = get_config_value([config_constants.S3_UNLOAD_ROLE_ARN], config_sources)
if unload_s3_location is not None and unload_role_arn is None:
raise Exception("If you configure S3 unload then you must also provide the UnloadRoleARN")
# we may have been passed the password in the configuration, so extract it if we can
pwd = get_config_value(['db_pwd'], config_sources)
# override the password with the contents of .pgpass or environment variables
try:
pg_pwd = pgpasslib.getpass(host, port, database, user)
if pg_pwd:
pwd = pg_pwd
except pgpasslib.FileNotFound as e:
pass
if pwd is None:
enc_password = get_config_value(['EncryptedPassword', 'encrypted_password', 'encrypted_pwd', 'dbPassword'],
config_sources)
# resolve the authorisation context, if there is one, and decrypt the password
auth_context = get_config_value('kms_auth_context', config_sources)
if auth_context is not None:
auth_context = json.loads(auth_context)
try:
if auth_context is None:
pwd = kms.decrypt(CiphertextBlob=base64.b64decode(enc_password))[
'Plaintext']
else:
pwd = kms.decrypt(CiphertextBlob=base64.b64decode(enc_password), EncryptionContext=auth_context)[
'Plaintext']
except:
print('KMS access failed: exception %s' % sys.exc_info()[1])
print('Encrypted Password: %s' % enc_password)
print('Encryption Context %s' % auth_context)
raise
# Connect to the cluster
try:
if debug:
print('Connecting to Redshift: %s' % host)
conn = pg8000.connect(database=database, user=user, password=pwd, host=host, port=port, ssl=ssl)
conn.autocommit = True
except:
print('Redshift Connection Failed: exception %s' % sys.exc_info()[1])
raise
if debug:
print('Successfully Connected to Cluster')
# create a new cursor for methods to run through
cursor = conn.cursor()
# set application name
set_name = "set application_name to 'RedshiftSystemTablePersistence-v%s'" % __version__
if debug:
print(set_name)
cursor.execute(set_name)
# load the table configuration
table_config = json.load(open(os.path.dirname(__file__) + '/lib/history_table_config.json', 'r'))
# create the dependent objects if we need to
create_schema_objects(cursor, conn)
# snapshot stats into history tables
insert_rowcounts = snapshot_system_tables(cursor, conn, table_config)
# export the data to s3 if configured
try:
if unload_s3_location is not None:
unload_stats(cursor, table_config, cluster_name, unload_s3_location, unload_role_arn)
except e:
print("Exception during System Table Detail unload to S3. This will not prevent automated cleanup.");
print(traceback.format_exc())
# cleanup history tables if requested in the configuration
delete_rowcounts = None
cleanup_after_days = get_config_value([config_constants.SYSTABLE_CLEANUP_AFTER_DAYS], config_sources)
if cleanup_after_days is not None:
try:
cleanup_after_days = int(cleanup_after_days)
except ValueError:
print("Configuration value '%s' must be an integer" % config_constants.SYSTABLE_CLEANUP_AFTER_DAYS)
raise
if cleanup_after_days > 0:
delete_rowcounts = cleanup_snapshots(cursor, conn, cleanup_after_days, table_config)
cursor.close()
conn.close()
return {"inserted": insert_rowcounts, "deleted": delete_rowcounts}
def test_getpass_returns_expected_result(self):
with mock.patch('pgpasslib._read_file') as read_file:
read_file.return_value = MOCK_CONTENT
self.assertEqual(pgpasslib.getpass('foo.abjdite.us-east-1.'
'redshift.amazonaws.com', 5439,
'redshift', 'fonzy'), 'b3ar')
def test_getpass_returns_expected_result(self):
with mock.patch('pgpasslib._read_file') as read_file:
read_file.return_value = MOCK_CONTENT
self.assertEqual(pgpasslib.getpass('foo:bar', '6000',
'corgie', 'baz'), 'qux')
def test_getpass_returns_expected_result(self):
with mock.patch('pgpasslib._read_file') as read_file:
read_file.return_value = MOCK_CONTENT
self.assertEqual(pgpasslib.getpass('bouncer', 6000,
'bumpers', 'rubber'), 'buggy')
def test_path_matches_expectation(self):
with mock.patch('pgpasslib._read_file') as read_file:
read_file.return_value = MOCK_CONTENT
self.assertRaises(pgpasslib.NoMatchingEntry,
lambda: pgpasslib.getpass('fail', '5432', 'foo',
'bar'))
quit(-1)
if not env_db_uri:
# Loads database details or defaults
host = config.get('DBHOST', 'HOSTNAME')
port = config.get('DBHOST', 'PORT')
user = config.get('DBHOST', 'USERNAME')
dbname = config.get('DBHOST', 'DBNAME')
dbpass = config.get('DBHOST', 'PASSWORD', fallback='')
def __gen_dburi(user, password, host, port, dbname):
return 'postgresql://{}:{}@{}:{}/{}'.format(user, password, host, port,
dbname)
try:
fetched_pass = pgpasslib.getpass(host, port, user, dbname)
password = fetched_pass if fetched_pass else dbpass
database_uri = __gen_dburi(user, password, host, port, dbname)
except pgpasslib.FileNotFound:
# Fail silently when no files found.
password = dbpass
database_uri = __gen_dburi(user, password, host, port, dbname)
except pgpasslib.InvalidPermissions:
print(
"Your pgpass file has the wrong permissions, for your safety this file will be ignored. Please fix the permissions and try again."
)
password = dbpass
database_uri = __gen_dburi(user, password, host, port, dbname)
except pgpasslib.PgPassException:
print("Unexpected error:", sys.exc_info()[0])
password = dbpass
try:
with open(dbconfigfile, 'r') as ff:
rawfile = ff.read()
except IOError as e:
raise RuntimeError('IOError: Could not open dbconfigfile {0}:{1}'.format(
dbconfigfile, e))
dbdict = yaml.load(rawfile)
# select the appropriate configuration from config
if config.db:
db_info = dbdict[config.db]
try:
if 'password' not in db_info:
db_info['password'] = getpass(db_info['host'], db_info['port'],
db_info['database'], db_info['user'])
except KeyError:
raise RuntimeError('ERROR: invalid server configuration')
else:
raise RuntimeError(
'Error: could not determine db to connect to: {0}'.format(config.db))
# this format is only usable with PostgreSQL 9.2+
# dsn = "postgresql://{user}:{password}@{host}:{port}/{database}".format(**db_info)
# database_connection_string = 'postgresql+psycopg2://%s:%s@%s:%s/%s' % (db_info["user"], db_info["password"], db_info["host"], db_info["port"], db_info["database"])
# Build the database connection string
if db_info["host"] == 'localhost':
database_connection_string = 'postgresql+psycopg2:///%(database)s' % db_info
else:
database_connection_string = 'postgresql+psycopg2://%(user)s:%(password)s@%(host)s:%(port)i/%(database)s' % db_info
def test_getpass_returns_expected_result(self):
with mock.patch('pgpasslib._read_file') as read_file:
read_file.return_value = MOCK_CONTENT
self.assertIsNone(pgpasslib.getpass('fail', '5432', 'foo', 'bar'))
UPLOAD_FOLDER = '/home/nicholasc/tmp/upload'
def allowed_file(filename):
return '.' in filename and \
filename.rsplit('.', 1)[1].lower() in ALLOWED_EXTENSIONS
app = Flask(__name__, template_folder='.')
app.config['UPLOAD_FOLDER'] = UPLOAD_FOLDER
app.secret_key = 'super secret key'
socketio = SocketIO(app)
dfb_dict = {}
lims_password = pgpasslib.getpass('limsdb2', 5432, 'lims2', 'limsreader')
def get_permalink(node, incoming_request, session_uuid):
if node.uuid in incoming_request.url:
return incoming_request.url
else:
return urlparse.urljoin(incoming_request.url, node.uuid)
def get_embed_cursor_text(incoming_request, node, session_uuid=None):
if node.uuid in incoming_request.url:
node_uuid = '"{node_uuid}"'.format(node_uuid=node.uuid)
else:
def run_analyze_vacuum(**kwargs):
global debug
if config_constants.DEBUG in os.environ:
debug = os.environ[config_constants.DEBUG]
if config_constants.DEBUG in kwargs and kwargs[config_constants.DEBUG]:
debug = True
# connect to cloudwatch
region_key = 'AWS_REGION'
if region_key in os.environ:
aws_region = os.environ[region_key]
else:
aws_region = 'us-east-1'
cw = None
if config_constants.SUPPRESS_CLOUDWATCH not in kwargs or not kwargs[config_constants.SUPPRESS_CLOUDWATCH]:
try:
cw = boto3.client('cloudwatch', region_name=aws_region)
comment("Connected to CloudWatch in %s" % aws_region)
except Exception as e:
if debug:
print(traceback.format_exc())
else:
if debug:
comment("Suppressing CloudWatch connection and metrics export")
# extract the cluster name
if config_constants.CLUSTER_NAME in kwargs:
cluster_name = kwargs[config_constants.CLUSTER_NAME]
# remove the cluster name argument from kwargs as it's a positional arg
del kwargs[config_constants.CLUSTER_NAME]
else:
cluster_name = kwargs[config_constants.DB_HOST].split('.')[0]
if debug:
comment("Using Cluster Name %s" % cluster_name)
comment("Supplied Args:")
print(kwargs)
# get the password using .pgpass, environment variables, and then fall back to config
db_pwd = None
try:
db_pwd = pgpasslib.getpass(kwargs[config_constants.DB_HOST], kwargs[config_constants.DB_PORT],
kwargs[config_constants.DB_NAME], kwargs[config_constants.DB_USER])
except pgpasslib.FileNotFound as e:
pass
if db_pwd is None:
db_pwd = kwargs[config_constants.DB_PASSWORD]
if config_constants.SCHEMA_NAME not in kwargs:
kwargs[config_constants.SCHEMA_NAME] = 'public'
# get a connection for the controlling processes
master_conn = get_pg_conn(kwargs[config_constants.DB_HOST],
kwargs[config_constants.DB_NAME],
kwargs[config_constants.DB_USER],
db_pwd,
kwargs[config_constants.SCHEMA_NAME],
kwargs[config_constants.DB_PORT],
None if config_constants.QUERY_GROUP not in kwargs else kwargs[
config_constants.QUERY_GROUP],
None if config_constants.QUERY_SLOT_COUNT not in kwargs else kwargs[
config_constants.QUERY_SLOT_COUNT],
None if config_constants.SSL not in kwargs else kwargs[config_constants.SSL])
if master_conn is None:
raise Exception("No Connection was established")
vacuum_flag = kwargs[config_constants.DO_VACUUM] if config_constants.DO_VACUUM in kwargs else False
if vacuum_flag is True:
# Run vacuum based on the Unsorted , Stats off and Size of the table
run_vacuum(master_conn, cluster_name, cw, **kwargs)
else:
comment("Vacuum flag arg is not set. Vacuum not performed.")
analyze_flag = kwargs[config_constants.DO_ANALYZE] if config_constants.DO_ANALYZE in kwargs else False
if analyze_flag is True:
if not vacuum_flag:
comment("Warning - Analyze without Vacuum may result in sub-optimal performance")
# Run Analyze based on the Stats off Metrics table
run_analyze(master_conn, cluster_name, cw, **kwargs)
else:
comment("Analyze flag arg is set as %s. Analyze is not performed." % analyze_flag)
comment('Processing Complete')
cleanup(master_conn)
return OK
def snapshot(config_sources):
aws_region = get_config_value(['AWS_REGION'], config_sources)
set_debug = get_config_value([
'DEBUG',
'debug',
], config_sources)
if set_debug is not None and (set_debug or set_debug.upper() == 'TRUE'):
global debug
debug = True
kms = boto3.client('kms', region_name=aws_region)
if debug:
print("Connected to AWS KMS & CloudWatch in %s" % aws_region)
user = get_config_value(['DbUser', 'db_user', 'dbUser'], config_sources)
host = get_config_value(
['HostName', 'cluster_endpoint', 'dbHost', 'db_host'], config_sources)
port = int(
get_config_value(['HostPort', 'db_port', 'dbPort'], config_sources))
database = get_config_value(['DatabaseName', 'db_name', 'db'],
config_sources)
cluster_name = get_config_value([config_constants.CLUSTER_NAME],
config_sources)
unload_s3_location = get_config_value(
[config_constants.S3_UNLOAD_LOCATION], config_sources)
unload_role_arn = get_config_value([config_constants.S3_UNLOAD_ROLE_ARN],
config_sources)
if unload_s3_location is not None and unload_role_arn is None:
raise Exception(
"If you configure S3 unload then you must also provide the UnloadRoleARN"
)
# we may have been passed the password in the configuration, so extract it if we can
pwd = get_config_value(['db_pwd'], config_sources)
# override the password with the contents of .pgpass or environment variables
try:
pg_pwd = pgpasslib.getpass(host, port, database, user)
if pg_pwd:
pwd = pg_pwd
except pgpasslib.FileNotFound as e:
pass
if pwd is None:
enc_password = get_config_value([
'EncryptedPassword', 'encrypted_password', 'encrypted_pwd',
'dbPassword'
], config_sources)
# resolve the authorisation context, if there is one, and decrypt the password
auth_context = get_config_value('kms_auth_context', config_sources)
if auth_context is not None:
auth_context = json.loads(auth_context)
try:
if auth_context is None:
pwd = kms.decrypt(
CiphertextBlob=base64.b64decode(enc_password))['Plaintext']
else:
pwd = kms.decrypt(
CiphertextBlob=base64.b64decode(enc_password),
EncryptionContext=auth_context)['Plaintext']
except:
print('KMS access failed: exception %s' % sys.exc_info()[1])
print('Encrypted Password: %s' % enc_password)
print('Encryption Context %s' % auth_context)
raise
# Connect to the cluster
try:
if debug:
print('Connecting to Redshift: %s' % host)
conn = pg8000.connect(database=database,
user=user,
password=pwd,
host=host,
port=port,
ssl=ssl)
conn.autocommit = True
except:
print('Redshift Connection Failed: exception %s' % sys.exc_info()[1])
raise
if debug:
print('Successfully Connected to Cluster')
# create a new cursor for methods to run through
cursor = conn.cursor()
# set application name
set_name = "set application_name to 'RedshiftSystemTablePersistence-v%s'" % __version__
if debug:
print(set_name)
cursor.execute(set_name)
# load the table configuration
table_config = json.load(
open(
os.path.dirname(__file__) + '/lib/history_table_config.json', 'r'))
# create the dependent objects if we need to
create_schema_objects(cursor, conn)
# snapshot stats into history tables
insert_rowcounts = snapshot_system_tables(cursor, conn, table_config)
# export the data to s3 if configured
try:
if unload_s3_location is not None:
unload_stats(cursor, table_config, cluster_name,
unload_s3_location, unload_role_arn)
except e:
print(
"Exception during System Table Detail unload to S3. This will not prevent automated cleanup."
)
print(traceback.format_exc())
# cleanup history tables if requested in the configuration
delete_rowcounts = None
cleanup_after_days = get_config_value(
[config_constants.SYSTABLE_CLEANUP_AFTER_DAYS], config_sources)
if cleanup_after_days is not None:
try:
cleanup_after_days = int(cleanup_after_days)
except ValueError:
print("Configuration value '%s' must be an integer" %
config_constants.SYSTABLE_CLEANUP_AFTER_DAYS)
raise
if cleanup_after_days > 0:
delete_rowcounts = cleanup_snapshots(cursor, conn,
cleanup_after_days,
table_config)
cursor.close()
conn.close()
return {"inserted": insert_rowcounts, "deleted": delete_rowcounts}
import datetime
import pgpasslib
from sqlalchemy import create_engine
from sqlalchemy.orm import scoped_session, sessionmaker
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.exc import OperationalError
from sqlalchemy import desc
import mydb_config
from human_uptime import human_uptime
host = mydb_config.container_host
port = mydb_config.mydb_admin_port
dbname = mydb_config.admindb_name
admin_user = mydb_config.admin_user
password = pgpasslib.getpass(host, port, dbname, admin_user)
if not password:
print('Backup Audit: Did not file a password in the .pgpass file')
sys.exit(1)
SQLALCHEMY_DATABASE_URI = "postgresql://" + admin_user + ":"
SQLALCHEMY_DATABASE_URI += password + "@"
SQLALCHEMY_DATABASE_URI += host + ":" + port + "/"
SQLALCHEMY_DATABASE_URI += dbname
engine = create_engine(SQLALCHEMY_DATABASE_URI, convert_unicode=True)
db_session = scoped_session(
sessionmaker(autocommit=False, autoflush=False, bind=engine))
Base = declarative_base()
Base.query = db_session.query_property()
