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 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()