def test_cleanup_temporary_query_tables(self, mock_connections, mock_databases_data):
mock_cursor = Mock()
mock_connection = Mock()
mock_cursor_ctx_manager = MagicMock()
mock_cursor_ctx_manager.__enter__.return_value = mock_cursor
mock_connection.cursor.return_value = mock_cursor_ctx_manager
mock_connections.__getitem__.return_value = mock_connection
mock_databases_data.__getitem__.return_value = {
"USER": "******",
"NAME": "my_database" "",
}
user = UserFactory()
user.profile.sso_id = (
"00000000-0000-0000-0000-000000000000" # yields a short hexdigest of 12b9377c
)
user.profile.save()
# last run 1 day and 1 hour ago so its materialized view should be deleted
with freeze_time(datetime.utcnow() - timedelta(days=1, hours=1)):
query_log_1 = QueryLogFactory.create(run_by_user=user)
# last run 2 hours ago so its materialized view should be kept
with freeze_time(datetime.utcnow() - timedelta(hours=2)):
QueryLogFactory.create(run_by_user=user)
cleanup_temporary_query_tables()
expected_calls = [
call("GRANT _user_12b9377c TO postgres"),
call(f"DROP TABLE IF EXISTS _user_12b9377c._data_explorer_tmp_query_{query_log_1.id}"),
call("REVOKE _user_12b9377c FROM postgres"),
]
mock_cursor.execute.assert_has_calls(expected_calls)
def test_log_saves_duration(self):
user = UserFactory()
q = SimpleQueryFactory()
res, _ = q.execute_with_logging(user, None, 10, 10000)
log = QueryLog.objects.first()
assert log.duration == pytest.approx(res.duration, rel=1e-9)
def test_new_credentials_have_pgaudit_configuration(self):
ensure_databases_configured().handle()
user = UserFactory(email="*****@*****.**")
st = SourceTableFactory(dataset=MasterDataSetFactory.create(
user_access_type=UserAccessType.REQUIRES_AUTHENTICATION))
source_tables = source_tables_for_user(user)
db_role_schema_suffix = db_role_schema_suffix_for_user(user)
user_creds_to_drop = new_private_database_credentials(
db_role_schema_suffix,
source_tables,
postgres_user(user.email),
user,
valid_for=datetime.timedelta(days=1),
)
connections[st.database.memorable_name].cursor().execute("COMMIT")
rolename = user_creds_to_drop[0]["db_user"]
query = f"SELECT rolname, rolconfig FROM pg_roles WHERE rolname = '{rolename}';"
with connections[st.database.memorable_name].cursor() as cursor:
cursor.execute(query)
results = cursor.fetchall()
assert "pgaudit.log=ALL" in results[0][1]
assert "pgaudit.log_catalog=off" in results[0][1]
def test_cannot_open_playground_with_another_users_query(
self, staff_client):
other_user = UserFactory(email='*****@*****.**')
query = SimpleQueryFactory(sql="select 1;", created_by_user=other_user)
resp = staff_client.get('%s?query_id=%s' %
(reverse("explorer:index"), query.id))
assert resp.status_code == 404
def test_cannot_view_another_users_query(self, staff_user, staff_client):
other_user = UserFactory(email="*****@*****.**")
other_query = SimpleQueryFactory(created_by_user=other_user)
resp = staff_client.get(
reverse("explorer:query_detail", kwargs={"query_id": other_query.id})
)
assert resp.status_code == 404
def test_user_can_only_see_their_own_queries_on_log_page(self, staff_user, staff_client):
other_user = UserFactory(email="*****@*****.**")
QueryLogFactory(sql="select 1234", run_by_user=other_user)
QueryLogFactory(sql="select 9876", run_by_user=staff_user)
resp = staff_client.get(reverse("explorer:explorer_logs"))
assert "select 9876" in resp.content.decode(resp.charset)
assert "select 1234" not in resp.content.decode(resp.charset)
def test_cannot_post_to_another_users_query(self, staff_client):
other_user = UserFactory(email="*****@*****.**")
query = SimpleQueryFactory(sql="select 1;", created_by_user=other_user)
resp = staff_client.post(
reverse("explorer:index") + f"?query_id={query.id}",
{"title": "test", "sql": "select 1+3400;", "action": "save"},
)
assert resp.status_code == 404
def test_cant_query_with_unregistered_connection(self):
from dataworkspace.apps.explorer.utils import ( # pylint: disable=import-outside-toplevel
InvalidExplorerConnectionException, )
user = UserFactory()
q = SimpleQueryFactory.create(sql="select '$$foo:bar$$', '$$qux$$';",
connection='not_registered')
with pytest.raises(InvalidExplorerConnectionException):
q.execute(user, None, 10, 10000)
def test_query_owned_by_other_user(self, staff_user, staff_client):
QueryLogFactory(sql="select 123", run_by_user=staff_user)
query_log = QueryLogFactory(
sql="select 456", run_by_user=UserFactory(email='*****@*****.**'))
resp = staff_client.get(
reverse('explorer:querylog_results', args=(query_log.id, )))
assert resp.status_code == 200
assert (resp.json()['error'] ==
'Error fetching results. Please try running your query again.')
def setUp(self):
fetch_query_results_patcher = patch(
"dataworkspace.apps.explorer.exporters.fetch_query_results")
mock_fetch_query_results = fetch_query_results_patcher.start()
self.mock_fetch_query_results = mock_fetch_query_results
self.user = UserFactory()
self.request = MagicMock(user=self.user)
yield
fetch_query_results_patcher.stop()
def test_deletes_expired_and_unused_users(self):
ensure_databases_configured().handle()
user = UserFactory(email='*****@*****.**')
st = SourceTableFactory(
dataset=MasterDataSetFactory.create(
user_access_type='REQUIRES_AUTHENTICATION'
)
)
source_tables = source_tables_for_user(user)
db_role_schema_suffix = db_role_schema_suffix_for_user(user)
user_creds_to_drop = new_private_database_credentials(
db_role_schema_suffix,
source_tables,
postgres_user(user.email),
user,
valid_for=datetime.timedelta(days=31),
)
qs_creds_to_drop = new_private_database_credentials(
db_role_schema_suffix,
source_tables,
postgres_user(user.email, suffix='qs'),
user,
valid_for=datetime.timedelta(seconds=0),
)
qs_creds_to_keep = new_private_database_credentials(
db_role_schema_suffix,
source_tables,
postgres_user(user.email, suffix='qs'),
user,
valid_for=datetime.timedelta(minutes=1),
)
connections[st.database.memorable_name].cursor().execute('COMMIT')
# Make sure that `qs_creds_to_drop` has definitely expired
time.sleep(1)
with mock.patch('dataworkspace.apps.applications.utils.gevent.sleep'):
delete_unused_datasets_users()
with connections[st.database.memorable_name].cursor() as cursor:
cursor.execute(
"SELECT usename FROM pg_catalog.pg_user WHERE usename IN %s",
[
(
user_creds_to_drop[0]['db_user'],
qs_creds_to_drop[0]['db_user'],
qs_creds_to_keep[0]['db_user'],
)
],
)
assert cursor.fetchall() == [(qs_creds_to_keep[0]['db_user'],)]
def test_can_only_load_query_log_run_by_current_user(self, staff_user, staff_client):
user = UserFactory(email="*****@*****.**")
my_querylog = QueryLogFactory(run_by_user=staff_user)
other_querylog = QueryLogFactory(run_by_user=user)
resp = staff_client.get("%s?querylog_id=%s" % (reverse("explorer:index"), my_querylog.id))
assert resp.status_code == 200
assert "FOUR" in resp.content.decode(resp.charset)
resp = staff_client.get(
"%s?querylog_id=%s" % (reverse("explorer:index"), other_querylog.id)
)
assert resp.status_code == 404
def test_cannot_download_someone_elses_querylog(self, staff_user,
staff_client):
other_user = UserFactory(email='*****@*****.**')
my_querylog = QueryLogFactory(sql="select 1,2", run_by_user=other_user)
create_temporary_results_table(my_querylog)
url = (reverse(
"explorer:download_querylog",
kwargs=dict(querylog_id=my_querylog.id),
) + '?format=json')
response = staff_client.get(url)
assert response.status_code == 404
def setUp(self):
self.mock_cursor = MagicMock() # pylint: disable=attribute-defined-outside-init
# Mock the return value of SELECT COUNT(*) FROM {query}
self.mock_cursor.fetchone.return_value.__getitem__.return_value = 1
mock_connection = Mock()
mock_connection.cursor.return_value = self.mock_cursor
user_explorer_connection_patcher = patch(
"dataworkspace.apps.explorer.tasks.user_explorer_connection"
)
mock_user_explorer_connection = user_explorer_connection_patcher.start()
mock_user_explorer_connection.return_value.__enter__.return_value = mock_connection
self.user = UserFactory()
self.request = MagicMock(user=self.user)
yield
user_explorer_connection_patcher.stop()
def test_custom_delimiter(self):
user = UserFactory()
q = SimpleQueryFactory(sql='select 1, 2')
exporter = CSVExporter(user=user, query=q)
res = exporter.get_output(delim='|')
assert res == '?column?|?column?\r\n1|2\r\n'
