def test_databaseview_edit(self, username='******'):
# validate that sending a password-masked uri does not over-write the decrypted
# uri
self.login(username=username)
database = get_main_database(db.session)
sqlalchemy_uri_decrypted = database.sqlalchemy_uri_decrypted
url = 'databaseview/edit/{}'.format(database.id)
data = {k: database.__getattribute__(k) for k in DatabaseView.add_columns}
data['sqlalchemy_uri'] = database.safe_sqlalchemy_uri()
self.client.post(url, data=data)
database = get_main_database(db.session)
self.assertEqual(sqlalchemy_uri_decrypted, database.sqlalchemy_uri_decrypted)
def test_testconn(self, username='******'):
self.login(username=username)
database = get_main_database(db.session)
# validate that the endpoint works with the password-masked sqlalchemy uri
data = json.dumps({
'uri': database.safe_sqlalchemy_uri(),
'name': 'main',
'impersonate_user': False,
})
response = self.client.post(
'/superset/testconn',
data=data,
content_type='application/json')
assert response.status_code == 200
assert response.headers['Content-Type'] == 'application/json'
# validate that the endpoint works with the decrypted sqlalchemy uri
data = json.dumps({
'uri': database.sqlalchemy_uri_decrypted,
'name': 'main',
'impersonate_user': False,
})
response = self.client.post(
'/superset/testconn',
data=data,
content_type='application/json')
assert response.status_code == 200
assert response.headers['Content-Type'] == 'application/json'
def test_run_async_query_with_lower_limit(self):
main_db = get_main_database(db.session)
db_id = main_db.id
self.drop_table_if_exists('tmp_async_2', main_db)
sql_where = "SELECT name FROM ab_role WHERE name='Alpha' LIMIT 1"
result = self.run_sql(
db_id, sql_where, '5', async_='true', tmp_table='tmp_async_2',
cta='true')
assert result['query']['state'] in (
QueryStatus.PENDING, QueryStatus.RUNNING, QueryStatus.SUCCESS)
time.sleep(CELERY_SLEEP_TIME)
query = self.get_query_by_id(result['query']['serverId'])
self.assertEqual(QueryStatus.SUCCESS, query.status)
self.assertTrue('FROM tmp_async_2' in query.select_sql)
self.assertEqual(
'CREATE TABLE tmp_async_2 AS \nSELECT name FROM ab_role '
"WHERE name='Alpha' LIMIT 1", query.executed_sql)
self.assertEqual(sql_where, query.sql)
self.assertEqual(0, query.rows)
self.assertEqual(1, query.limit)
self.assertEqual(True, query.select_as_cta)
self.assertEqual(True, query.select_as_cta_used)
def test_sql_json_has_access(self):
main_db = get_main_database(db.session)
security_manager.add_permission_view_menu('database_access', main_db.perm)
db.session.commit()
main_db_permission_view = (
db.session.query(ab_models.PermissionView)
.join(ab_models.ViewMenu)
.join(ab_models.Permission)
.filter(ab_models.ViewMenu.name == '[main].(id:1)')
.filter(ab_models.Permission.name == 'database_access')
.first()
)
astronaut = security_manager.add_role('Astronaut')
security_manager.add_permission_role(astronaut, main_db_permission_view)
# Astronaut role is Gamma + sqllab + main db permissions
for perm in security_manager.find_role('Gamma').permissions:
security_manager.add_permission_role(astronaut, perm)
for perm in security_manager.find_role('sql_lab').permissions:
security_manager.add_permission_role(astronaut, perm)
gagarin = security_manager.find_user('gagarin')
if not gagarin:
security_manager.add_user(
'gagarin', 'Iurii', 'Gagarin', '*****@*****.**',
astronaut,
password='******')
data = self.run_sql('SELECT * FROM ab_user', '3', user_name='gagarin')
db.session.query(Query).delete()
db.session.commit()
self.assertLess(0, len(data['data']))
def create_table(
self, name, schema='', id=0, cols_names=[], metric_names=[]):
database_name = 'main'
name = '{0}{1}'.format(NAME_PREFIX, name)
params = {DBREF: id, 'database_name': database_name}
dict_rep = {
'database_id': get_main_database(db.session).id,
'table_name': name,
'schema': schema,
'id': id,
'params': json.dumps(params),
'columns': [{'column_name': c}
for c in cols_names],
'metrics': [{'metric_name': c} for c in metric_names],
}
table = SqlaTable(
id=id,
schema=schema,
table_name=name,
params=json.dumps(params),
)
for col_name in cols_names:
table.columns.append(TableColumn(column_name=col_name))
for metric_name in metric_names:
table.metrics.append(SqlMetric(metric_name=metric_name))
return table, dict_rep
def test_single_statement(self):
main_db = get_main_database(db.session)
if main_db.backend == 'mysql':
df = main_db.get_df('SELECT 1', None)
self.assertEquals(df.iat[0, 0], 1)
df = main_db.get_df('SELECT 1;', None)
self.assertEquals(df.iat[0, 0], 1)
def test_multi_statement(self):
main_db = get_main_database(db.session)
if main_db.backend == 'mysql':
df = main_db.get_df('USE superset; SELECT 1', None)
self.assertEquals(df.iat[0, 0], 1)
df = main_db.get_df("USE superset; SELECT ';';", None)
self.assertEquals(df.iat[0, 0], ';')
def test_run_sync_query_cta_no_data(self):
main_db = get_main_database(db.session)
db_id = main_db.id
sql_empty_result = 'SELECT * FROM ab_user WHERE id=666'
result3 = self.run_sql(db_id, sql_empty_result, '3')
self.assertEqual(QueryStatus.SUCCESS, result3['query']['state'])
self.assertEqual([], result3['data'])
self.assertEqual([], result3['columns'])
query3 = self.get_query_by_id(result3['query']['serverId'])
self.assertEqual(QueryStatus.SUCCESS, query3.status)
def load_test_users_run():
"""
Loads admin, alpha, and gamma user for testing purposes
Syncs permissions for those users/roles
"""
if config.get('TESTING'):
security_manager.sync_role_definitions()
gamma_sqllab_role = security_manager.add_role('gamma_sqllab')
for perm in security_manager.find_role('Gamma').permissions:
security_manager.add_permission_role(gamma_sqllab_role, perm)
utils.get_or_create_main_db()
db_perm = utils.get_main_database(security_manager.get_session).perm
security_manager.merge_perm('database_access', db_perm)
db_pvm = security_manager.find_permission_view_menu(
view_menu_name=db_perm, permission_name='database_access')
gamma_sqllab_role.permissions.append(db_pvm)
for perm in security_manager.find_role('sql_lab').permissions:
security_manager.add_permission_role(gamma_sqllab_role, perm)
admin = security_manager.find_user('admin')
if not admin:
security_manager.add_user(
'admin', 'admin', ' user', '*****@*****.**',
security_manager.find_role('Admin'),
password='******')
gamma = security_manager.find_user('gamma')
if not gamma:
security_manager.add_user(
'gamma', 'gamma', 'user', '*****@*****.**',
security_manager.find_role('Gamma'),
password='******')
gamma2 = security_manager.find_user('gamma2')
if not gamma2:
security_manager.add_user(
'gamma2', 'gamma2', 'user', '*****@*****.**',
security_manager.find_role('Gamma'),
password='******')
gamma_sqllab_user = security_manager.find_user('gamma_sqllab')
if not gamma_sqllab_user:
security_manager.add_user(
'gamma_sqllab', 'gamma_sqllab', 'user', '*****@*****.**',
gamma_sqllab_role, password='******')
alpha = security_manager.find_user('alpha')
if not alpha:
security_manager.add_user(
'alpha', 'alpha', 'user', '*****@*****.**',
security_manager.find_role('Alpha'),
password='******')
security_manager.get_session.commit()
def test_custom_password_store(self):
database = get_main_database(db.session)
conn_pre = sqla.engine.url.make_url(database.sqlalchemy_uri_decrypted)
def custom_password_store(uri):
return 'password_store_test'
models.custom_password_store = custom_password_store
conn = sqla.engine.url.make_url(database.sqlalchemy_uri_decrypted)
if conn_pre.password:
assert conn.password == 'password_store_test'
assert conn.password != conn_pre.password
# Disable for password store for later tests
models.custom_password_store = None
def run_sql(self, sql, client_id=None, user_name=None, raise_on_error=False,
query_limit=None):
if user_name:
self.logout()
self.login(username=(user_name if user_name else 'admin'))
dbid = get_main_database(db.session).id
resp = self.get_json_resp(
'/superset/sql_json/',
raise_on_error=False,
data=dict(database_id=dbid, sql=sql, select_as_create_as=False,
client_id=client_id, queryLimit=query_limit),
)
if raise_on_error and 'error' in resp:
raise Exception('run_sql failed')
return resp
def test_table_metadata(self):
maindb = get_main_database(db.session)
backend = maindb.backend
data = self.get_json_resp(
'/superset/table/{}/ab_user/null/'.format(maindb.id))
self.assertEqual(data['name'], 'ab_user')
assert len(data['columns']) > 5
assert data.get('selectStar').startswith('SELECT')
# Engine specific tests
if backend in ('mysql', 'postgresql'):
self.assertEqual(data.get('primaryKey').get('type'), 'pk')
self.assertEqual(
data.get('primaryKey').get('column_names')[0], 'id')
self.assertEqual(len(data.get('foreignKeys')), 2)
if backend == 'mysql':
self.assertEqual(len(data.get('indexes')), 7)
elif backend == 'postgresql':
self.assertEqual(len(data.get('indexes')), 5)
def test_select_star(self):
main_db = get_main_database(db.session)
table_name = 'energy_usage'
sql = main_db.select_star(
table_name, show_cols=False, latest_partition=False)
expected = textwrap.dedent(f"""\
SELECT *
FROM {table_name}
LIMIT 100""")
assert sql.startswith(expected)
sql = main_db.select_star(
table_name, show_cols=True, latest_partition=False)
expected = textwrap.dedent(f"""\
SELECT source,
target,
value
FROM energy_usage
LIMIT 100""")
assert sql.startswith(expected)
def test_run_sync_query_cta(self):
main_db = get_main_database(db.session)
db_id = main_db.id
eng = main_db.get_sqla_engine()
tmp_table_name = 'tmp_async_22'
self.drop_table_if_exists(tmp_table_name, main_db)
perm_name = 'can_sql_json'
sql_where = (
"SELECT name FROM ab_permission WHERE name='{}'".format(perm_name))
result2 = self.run_sql(
db_id, sql_where, '2', tmp_table=tmp_table_name, cta='true')
self.assertEqual(QueryStatus.SUCCESS, result2['query']['state'])
self.assertEqual([], result2['data'])
self.assertEqual([], result2['columns'])
query2 = self.get_query_by_id(result2['query']['serverId'])
# Check the data in the tmp table.
df2 = pd.read_sql_query(sql=query2.select_sql, con=eng)
data2 = df2.to_dict(orient='records')
self.assertEqual([{'name': perm_name}], data2)
def test_run_sync_query_cta(self):
main_db = get_main_database(db.session)
backend = main_db.backend
db_id = main_db.id
tmp_table_name = 'tmp_async_22'
self.drop_table_if_exists(tmp_table_name, main_db)
perm_name = 'can_sql_json'
sql_where = (
"SELECT name FROM ab_permission WHERE name='{}'".format(perm_name))
result2 = self.run_sql(
db_id, sql_where, '2', tmp_table=tmp_table_name, cta='true')
self.assertEqual(QueryStatus.SUCCESS, result2['query']['state'])
self.assertEqual([], result2['data'])
self.assertEqual([], result2['columns'])
query2 = self.get_query_by_id(result2['query']['serverId'])
# Check the data in the tmp table.
if backend != 'postgresql':
# TODO This test won't work in Postgres
results = self.run_sql(db_id, query2.select_sql, 'sdf2134')
self.assertEquals(results['status'], 'success')
self.assertGreater(len(results['data']), 0)
def insert_default_dataset(self):
return self.insert_dataset("ab_permission", "",
[self.get_user("admin").id],
get_main_database())
def test_run_sync_query_dont_exist(self):
main_db = get_main_database(db.session)
db_id = main_db.id
sql_dont_exist = 'SELECT name FROM table_dont_exist'
result1 = self.run_sql(db_id, sql_dont_exist, '1', cta='true')
self.assertTrue('error' in result1)
def get_main_database(self):
return get_main_database(db.session)
def load_test_users_run():
"""
Loads admin, alpha, and gamma user for testing purposes
Syncs permissions for those users/roles
"""
if config.get("TESTING"):
security_manager.sync_role_definitions()
gamma_sqllab_role = security_manager.add_role("gamma_sqllab")
for perm in security_manager.find_role("Gamma").permissions:
security_manager.add_permission_role(gamma_sqllab_role, perm)
utils.get_or_create_main_db()
db_perm = utils.get_main_database().perm
security_manager.add_permission_view_menu("database_access", db_perm)
db_pvm = security_manager.find_permission_view_menu(
view_menu_name=db_perm, permission_name="database_access"
)
gamma_sqllab_role.permissions.append(db_pvm)
for perm in security_manager.find_role("sql_lab").permissions:
security_manager.add_permission_role(gamma_sqllab_role, perm)
admin = security_manager.find_user("admin")
if not admin:
security_manager.add_user(
"admin",
"admin",
" user",
"*****@*****.**",
security_manager.find_role("Admin"),
password="******",
)
gamma = security_manager.find_user("gamma")
if not gamma:
security_manager.add_user(
"gamma",
"gamma",
"user",
"*****@*****.**",
security_manager.find_role("Gamma"),
password="******",
)
gamma2 = security_manager.find_user("gamma2")
if not gamma2:
security_manager.add_user(
"gamma2",
"gamma2",
"user",
"*****@*****.**",
security_manager.find_role("Gamma"),
password="******",
)
gamma_sqllab_user = security_manager.find_user("gamma_sqllab")
if not gamma_sqllab_user:
security_manager.add_user(
"gamma_sqllab",
"gamma_sqllab",
"user",
"*****@*****.**",
gamma_sqllab_role,
password="******",
)
alpha = security_manager.find_user("alpha")
if not alpha:
security_manager.add_user(
"alpha",
"alpha",
"user",
"*****@*****.**",
security_manager.find_role("Alpha"),
password="******",
)
security_manager.get_session.commit()
def test_extra_table_metadata(self):
self.login("admin")
dbid = utils.get_main_database().id
self.get_json_resp(f"/superset/extra_table_metadata/{dbid}/"
"ab_permission_view/panoramix/")
def test_run_sync_query_dont_exist(self):
main_db = get_main_database(db.session)
db_id = main_db.id
sql_dont_exist = 'SELECT name FROM table_dont_exist'
result1 = self.run_sql(db_id, sql_dont_exist, '1', cta='true')
self.assertTrue('error' in result1)
def test_process_template(self):
maindb = get_main_database(db.session)
sql = "SELECT '{{ datetime(2017, 1, 1).isoformat() }}'"
tp = jinja_context.get_template_processor(database=maindb)
rendered = tp.process_template(sql)
self.assertEqual("SELECT '2017-01-01T00:00:00'", rendered)
def test_template_kwarg(self):
maindb = utils.get_main_database()
s = "{{ foo }}"
tp = jinja_context.get_template_processor(database=maindb)
rendered = tp.process_template(s, foo="bar")
self.assertEqual("bar", rendered)
def test_get_dataset_distinct_schema(self):
"""
Dataset API: Test get dataset distinct schema
"""
def pg_test_query_parameter(query_parameter, expected_response):
uri = f"api/v1/dataset/distinct/schema?q={prison.dumps(query_parameter)}"
rv = self.client.get(uri)
response = json.loads(rv.data.decode("utf-8"))
assert rv.status_code == 200
assert response == expected_response
example_db = get_example_database()
datasets = []
if example_db.backend == "postgresql":
datasets.append(
self.insert_dataset("ab_permission", "public", [],
get_main_database()))
datasets.append(
self.insert_dataset("columns", "information_schema", [],
get_main_database()))
schema_values = [
"",
"admin_database",
"information_schema",
"public",
]
expected_response = {
"count": 4,
"result": [{
"text": val,
"value": val
} for val in schema_values],
}
self.login(username="******")
uri = "api/v1/dataset/distinct/schema"
rv = self.client.get(uri)
response = json.loads(rv.data.decode("utf-8"))
assert rv.status_code == 200
assert response == expected_response
# Test filter
query_parameter = {"filter": "inf"}
pg_test_query_parameter(
query_parameter,
{
"count":
1,
"result": [{
"text": "information_schema",
"value": "information_schema"
}],
},
)
query_parameter = {"page": 0, "page_size": 1}
pg_test_query_parameter(
query_parameter,
{
"count": 4,
"result": [{
"text": "",
"value": ""
}]
},
)
query_parameter = {"page": 1, "page_size": 1}
pg_test_query_parameter(
query_parameter,
{
"count":
4,
"result": [{
"text": "admin_database",
"value": "admin_database"
}],
},
)
for dataset in datasets:
db.session.delete(dataset)
db.session.commit()
def load_test_users_run():
"""
Loads admin, alpha, and gamma user for testing purposes
Syncs permissions for those users/roles
"""
if config.get('TESTING'):
security_manager.sync_role_definitions()
gamma_sqllab_role = security_manager.add_role('gamma_sqllab')
for perm in security_manager.find_role('Gamma').permissions:
security_manager.add_permission_role(gamma_sqllab_role, perm)
utils.get_or_create_main_db()
db_perm = utils.get_main_database(security_manager.get_session).perm
security_manager.merge_perm('database_access', db_perm)
db_pvm = security_manager.find_permission_view_menu(
view_menu_name=db_perm, permission_name='database_access')
gamma_sqllab_role.permissions.append(db_pvm)
for perm in security_manager.find_role('sql_lab').permissions:
security_manager.add_permission_role(gamma_sqllab_role, perm)
admin = security_manager.find_user('admin')
if not admin:
security_manager.add_user('admin',
'admin',
' user',
'*****@*****.**',
security_manager.find_role('Admin'),
password='******')
gamma = security_manager.find_user('gamma')
if not gamma:
security_manager.add_user('gamma',
'gamma',
'user',
'*****@*****.**',
security_manager.find_role('Gamma'),
password='******')
gamma2 = security_manager.find_user('gamma2')
if not gamma2:
security_manager.add_user('gamma2',
'gamma2',
'user',
'*****@*****.**',
security_manager.find_role('Gamma'),
password='******')
gamma_sqllab_user = security_manager.find_user('gamma_sqllab')
if not gamma_sqllab_user:
security_manager.add_user('gamma_sqllab',
'gamma_sqllab',
'user',
'*****@*****.**',
gamma_sqllab_role,
password='******')
alpha = security_manager.find_user('alpha')
if not alpha:
security_manager.add_user('alpha',
'alpha',
'user',
'*****@*****.**',
security_manager.find_role('Alpha'),
password='******')
security_manager.get_session.commit()
def test_run_sync_query_dont_exist(self):
main_db = get_main_database()
db_id = main_db.id
sql_dont_exist = "SELECT name FROM table_dont_exist"
result1 = self.run_sql(db_id, sql_dont_exist, "1", cta="true")
self.assertTrue("error" in result1)
def test_extra_table_metadata(self):
self.login('admin')
dbid = get_main_database(db.session).id
self.get_json_resp(
'/superset/extra_table_metadata/{dbid}/'
'ab_permission_view/panoramix/'.format(**locals()))
def test_extra_table_metadata(self):
self.login('admin')
dbid = get_main_database(db.session).id
self.get_json_resp(
f'/superset/extra_table_metadata/{dbid}/'
'ab_permission_view/panoramix/')
def test_template_kwarg(self):
maindb = get_main_database(db.session)
s = '{{ foo }}'
tp = jinja_context.get_template_processor(database=maindb)
rendered = tp.process_template(s, foo='bar')
self.assertEqual('bar', rendered)
def test_process_template(self):
maindb = get_main_database(db.session)
sql = "SELECT '{{ datetime(2017, 1, 1).isoformat() }}'"
tp = jinja_context.get_template_processor(database=maindb)
rendered = tp.process_template(sql)
self.assertEqual("SELECT '2017-01-01T00:00:00'", rendered)
def test_template_kwarg(self):
maindb = get_main_database(db.session)
s = '{{ foo }}'
tp = jinja_context.get_template_processor(database=maindb)
rendered = tp.process_template(s, foo='bar')
self.assertEqual('bar', rendered)
def quote_name(self, name: str):
if get_main_database().backend in {"presto", "hive"}:
return get_example_database(
).inspector.engine.dialect.identifier_preparer.quote_identifier(
name)
return name
