def _conn_to_rs(self,
opt=options,
timeout=set_timeout_stmt,
database=None):
rs_conn_string = "host={host} port={port} dbname={db} user={user} password={password} {opt}".format(
host=self.get_host(),
port=self.get_port(),
db=self.get_db(),
password=self.get_password(
), # First fetch the password because temporary password updates user!
user=self.get_user(),
opt=opt)
logging.debug(GET_SAFE_LOG_STRING(rs_conn_string))
try:
# noinspection PyArgumentList
rs_conn = connect(rs_conn_string)
except pg.InternalError as ie:
if hasattr(ie, 'args') and len(ie.args) > 0 \
and ('Operation timed out' in ie.args[0] or 'timeout expired' in ie.args[0]):
msg = 'Connection timeout when connecting to {h}:{p}.\n'
msg += 'Make sure that firewalls and security groups allow connections.'
logging.fatal(msg.format(h=self.get_host(), p=self.get_port()))
else:
logging.fatal(
'Internal error encountered when trying to connect: {ie}'.
format(ie=ie))
raise sys.exc_info()[0](sys.exc_info()[1]).with_traceback(
sys.exc_info()[2])
if self._configured_timeout is not None and not self._configured_timeout == timeout:
rs_conn.query(timeout)
self.database_timeouts[database][opt] = timeout
return rs_conn
def execute_update(self,
command,
opt=options,
timeout=set_timeout_stmt,
database=None):
conn_rs = self.get_conn_to_rs(opt=opt,
timeout=timeout,
database=database)
logging.debug('Executing update:' + GET_SAFE_LOG_STRING(command))
conn_rs.query(command)
def test_redaction_of_sensitive_information_master_symmetric_key_with_single_quote(self):
input_sql = """unload ('SELECT * FROM ssb.dwdate')
to 's3://unload-copy-t4f8cltb-s3copyunloadbucket-i6exicqitkr8/scenario004/2017-12-22_16:54:39/dev.ssb.dwdate.' credentials
'aws_iam_role=arn:aws:iam::012345678910:role/unload-copy-t4f8cLtB-S3Role-S1R76KW2VMR;master_symmetric_key=fWyHGBEtRyYnjnzRpPDcXr2yuQbEfaWqMhFQuq11111='
manifest encrypted gzip delimiter '^' addquotes escape allowoverwrite"""
expected_sql = """unload ('SELECT * FROM ssb.dwdate')
to 's3://unload-copy-t4f8cltb-s3copyunloadbucket-i6exicqitkr8/scenario004/2017-12-22_16:54:39/dev.ssb.dwdate.' credentials
'aws_iam_role=arn:aws:iam::012345678910:role/unload-copy-t4f8cLtB-S3Role-S1R76KW2VMR;master_symmetric_key=REDACTED'
manifest encrypted gzip delimiter '^' addquotes escape allowoverwrite"""
self.assertEquals(GET_SAFE_LOG_STRING(input_sql), expected_sql)
def get_query_full_result_as_list_of_dict(self, sql, opt=options, timeout=set_timeout_stmt, database=None):
"""
Inefficient way to store data but nice and easy for queries with small result sets.
:return:
"""
conn_rs = self.get_conn_to_rs(opt=opt, timeout=timeout, database=database)
logging.debug('Executing query:' + GET_SAFE_LOG_STRING(sql))
result = conn_rs.query(sql)
dict_result = result.dictresult()
return dict_result
def run_query_against_resource(self, command, command_parameters=None):
command_parameters = command_parameters or dict()
command_parameters['cluster'] = self.get_cluster()
command_to_execute = self.commands[command]
update_sql_command = command_to_execute.format(**command_parameters)
logging.info('Executing query {command} against {resource}:'.format(
command=command, resource=self))
logging.info(GET_SAFE_LOG_STRING(update_sql_command))
return self.get_cluster().get_query_full_result_as_list_of_dict(
update_sql_command)
def run_command_against_resource(self, command, command_parameters=None):
command_parameters = command_parameters or dict()
command_parameters['cluster'] = self.get_cluster()
command_to_execute = self.commands[command]
if 'region' in command_parameters and command == 'copy_table' and command_parameters[
'region'] is not None:
command_to_execute += " REGION '{region}' "
update_sql_command = command_to_execute.format(**command_parameters)
logging.info('Executing {command} against {resource}:'.format(
command=command, resource=self))
logging.info(GET_SAFE_LOG_STRING(update_sql_command))
self.get_cluster().execute_update(update_sql_command)
def test_redaction_of_sensitive_information_secret_access_key_keyword(self):
input_sql = """copy table-name
from 's3://objectpath'
access_key_id '<temporary-access-key-id>'
secret_access_key '<temporary-secret-access-key>'
token '<temporary-token>';"""
expected_sql = """copy table-name
from 's3://objectpath'
access_key_id '<temporary-access-key-id>'
secret_access_key 'REDACTED'
token '<temporary-token>';"""
self.assertEquals(GET_SAFE_LOG_STRING(input_sql), expected_sql)
def test_redaction_of_sensitive_information_master_symmetric_key_copy_with_semicolon(self):
input_sql = """copy public.dwdate
from 's3://unload-copy-t4f8cltb-s3copyunloadbucket-i6exicqitkr8/scenario004/2017-12-22_16:54:39/dev.ssb.dwdate.manifest' credentials
'master_symmetric_key=fWyHGBEtRyYnjnzRpPDcXr2yuQbEfaWqMhFQuq9GeNM=;aws_iam_role=arn:aws:iam::012345678910:role/unload-copy-t4f8cLtB-S3Role-S1R76KW2VMR'
manifest
encrypted
gzip
delimiter '^' removequotes escape compupdate off REGION 'eu-west-1' """
expected_sql = """copy public.dwdate
from 's3://unload-copy-t4f8cltb-s3copyunloadbucket-i6exicqitkr8/scenario004/2017-12-22_16:54:39/dev.ssb.dwdate.manifest' credentials
'master_symmetric_key=REDACTED;aws_iam_role=arn:aws:iam::012345678910:role/unload-copy-t4f8cLtB-S3Role-S1R76KW2VMR'
manifest
encrypted
gzip
delimiter '^' removequotes escape compupdate off REGION 'eu-west-1' """
self.assertEquals(GET_SAFE_LOG_STRING(input_sql), expected_sql)
def test_redaction_of_sensitive_information_secret_access_key_keyword_UPPERCASE(self):
"""
SQL is cae insensitive code needs to be as well case insensitive
:return:
"""
input_sql = """copy table-name
from 's3://objectpath'
access_key_id '<temporary-access-key-id>'
SECRET_ACCESS_KEY '<temporary-secret-access-key>'
token '<temporary-token>';"""
expected_sql = """copy table-name
from 's3://objectpath'
access_key_id '<temporary-access-key-id>'
secret_access_key 'REDACTED'
token '<temporary-token>';"""
self.assertEquals(GET_SAFE_LOG_STRING(input_sql), expected_sql)
def test_redaction_password_in_connect_string(self):
input_string = "host=localhost port=5439 dbname=dev user=master password=MyS3cr3tPass.word option1"
expected_string = "host=localhost port=5439 dbname=dev user=master password=REDACTED option1"
self.assertEquals(GET_SAFE_LOG_STRING(input_string), expected_string)