You are viewing a plain text version of this content. The canonical link for it is here.
Posted to commits@superset.apache.org by hu...@apache.org on 2022/11/30 19:31:22 UTC
[superset] 01/08: SSH Tunnel:
This is an automated email from the ASF dual-hosted git repository.
hugh pushed a commit to branch ssh-tunnel-testing
in repository https://gitbox.apache.org/repos/asf/superset.git
commit ba164aa6db7e0dbf7ddfe9f2f8eeaf0f3df89808
Author: Antonio Rivero <an...@gmail.com>
AuthorDate: Tue Nov 22 15:54:21 2022 -0300
SSH Tunnel:
- Initial DELETE and CREATE ssh tunnel APIs
- Add happy path tests for each API
NOTE:
- Updating a DB might also result in creating a Tunnel, that flow will get tested in the UPDATE API changes.
---
superset/constants.py | 1 +
superset/databases/api.py | 62 ++++++++++++++++++++++
superset/databases/commands/create.py | 13 +++++
superset/databases/schemas.py | 41 ++++++++-------
tests/integration_tests/databases/api_tests.py | 31 +++++++++++
tests/unit_tests/databases/api_test.py | 72 ++++++++++++++++++++++++++
6 files changed, 200 insertions(+), 20 deletions(-)
diff --git a/superset/constants.py b/superset/constants.py
index 7d759acf67..b775926b21 100644
--- a/superset/constants.py
+++ b/superset/constants.py
@@ -136,6 +136,7 @@ MODEL_API_RW_METHOD_PERMISSION_MAP = {
"validate_sql": "read",
"get_data": "read",
"samples": "read",
+ "delete_ssh_tunnel": "write",
}
EXTRA_FORM_DATA_APPEND_KEYS = {
diff --git a/superset/databases/api.py b/superset/databases/api.py
index aced8e7c6f..9cab40ede4 100644
--- a/superset/databases/api.py
+++ b/superset/databases/api.py
@@ -72,6 +72,12 @@ from superset.databases.schemas import (
ValidateSQLRequest,
ValidateSQLResponse,
)
+from superset.databases.ssh_tunnel.commands.create import CreateSSHTunnelCommand
+from superset.databases.ssh_tunnel.commands.delete import DeleteSSHTunnelCommand
+from superset.databases.ssh_tunnel.commands.exceptions import (
+ SSHTunnelDeleteFailedError,
+ SSHTunnelNotFoundError,
+)
from superset.databases.utils import get_table_metadata
from superset.db_engine_specs import get_available_engine_specs
from superset.errors import ErrorLevel, SupersetError, SupersetErrorType
@@ -107,6 +113,7 @@ class DatabaseRestApi(BaseSupersetModelRestApi):
"available",
"validate_parameters",
"validate_sql",
+ "delete_ssh_tunnel",
}
resource_name = "database"
class_permission_name = "Database"
@@ -1204,3 +1211,58 @@ class DatabaseRestApi(BaseSupersetModelRestApi):
command = ValidateDatabaseParametersCommand(payload)
command.run()
return self.response(200, message="OK")
+
+ @expose("/<int:pk>/ssh_tunnel/", methods=["DELETE"])
+ @protect()
+ @safe
+ @statsd_metrics
+ @event_logger.log_this_with_context(
+ action=lambda self, *args, **kwargs: f"{self.__class__.__name__}"
+ f".delete_ssh_tunnel",
+ log_to_statsd=False,
+ )
+ def delete_ssh_tunnel(self, pk: int) -> Response:
+ """Deletes a SSH Tunnel
+ ---
+ delete:
+ description: >-
+ Deletes a SSH Tunnel.
+ parameters:
+ - in: path
+ schema:
+ type: integer
+ name: pk
+ responses:
+ 200:
+ description: SSH Tunnel deleted
+ content:
+ application/json:
+ schema:
+ type: object
+ properties:
+ message:
+ type: string
+ 401:
+ $ref: '#/components/responses/401'
+ 403:
+ $ref: '#/components/responses/403'
+ 404:
+ $ref: '#/components/responses/404'
+ 422:
+ $ref: '#/components/responses/422'
+ 500:
+ $ref: '#/components/responses/500'
+ """
+ try:
+ DeleteSSHTunnelCommand(pk).run()
+ return self.response(200, message="OK")
+ except SSHTunnelNotFoundError:
+ return self.response_404()
+ except SSHTunnelDeleteFailedError as ex:
+ logger.error(
+ "Error deleting SSH Tunnel %s: %s",
+ self.__class__.__name__,
+ str(ex),
+ exc_info=True,
+ )
+ return self.response_422(message=str(ex))
diff --git a/superset/databases/commands/create.py b/superset/databases/commands/create.py
index 4dc8e8eda4..421e060a4b 100644
--- a/superset/databases/commands/create.py
+++ b/superset/databases/commands/create.py
@@ -31,6 +31,9 @@ from superset.databases.commands.exceptions import (
)
from superset.databases.commands.test_connection import TestConnectionDatabaseCommand
from superset.databases.dao import DatabaseDAO
+from superset.databases.ssh_tunnel.commands.exceptions import SSHTunnelInvalidError
+from superset.databases.ssh_tunnel.dao import SSHTunnelDAO
+from superset.databases.ssh_tunnel.models import SSHTunnel
from superset.exceptions import SupersetErrorsException
from superset.extensions import db, event_logger, security_manager
@@ -77,6 +80,16 @@ class CreateDatabaseCommand(BaseCommand):
security_manager.add_permission_view_menu(
"schema_access", security_manager.get_schema_perm(database, schema)
)
+
+ if self._properties.get("ssh_tunnel"):
+ SSHTunnelDAO.create(
+ {
+ **self._properties.get("ssh_tunnel"),
+ "database_id": database.id,
+ },
+ commit=False,
+ )
+
db.session.commit()
except DAOCreateFailedError as ex:
db.session.rollback()
diff --git a/superset/databases/schemas.py b/superset/databases/schemas.py
index fad2da0fee..ce137f1c38 100644
--- a/superset/databases/schemas.py
+++ b/superset/databases/schemas.py
@@ -365,6 +365,26 @@ class DatabaseValidateParametersSchema(Schema):
)
+class DatabaseSSHTunnel(Schema):
+ id = fields.Integer()
+ database_id = fields.Integer()
+
+ server_address = fields.String()
+ server_port = fields.Integer()
+ username = fields.String()
+
+ # Basic Authentication
+ password = fields.String(required=False)
+
+ # password protected private key authentication
+ private_key = fields.String(required=False)
+ private_key_password = fields.String(required=False)
+
+ # remote binding port
+ bind_host = fields.String()
+ bind_port = fields.Integer()
+
+
class DatabasePostSchema(Schema, DatabaseParametersSchemaMixin):
class Meta: # pylint: disable=too-few-public-methods
unknown = EXCLUDE
@@ -409,6 +429,7 @@ class DatabasePostSchema(Schema, DatabaseParametersSchemaMixin):
is_managed_externally = fields.Boolean(allow_none=True, default=False)
external_url = fields.String(allow_none=True)
uuid = fields.String(required=False)
+ ssh_tunnel = fields.Nested(DatabaseSSHTunnel, allow_none=True)
class DatabasePutSchema(Schema, DatabaseParametersSchemaMixin):
@@ -456,26 +477,6 @@ class DatabasePutSchema(Schema, DatabaseParametersSchemaMixin):
external_url = fields.String(allow_none=True)
-class DatabaseSSHTunnel(Schema):
- id = fields.Integer()
- database_id = fields.Integer()
-
- server_address = fields.String()
- server_port = fields.Integer()
- username = fields.String()
-
- # Basic Authentication
- password = fields.String(required=False)
-
- # password protected private key authentication
- private_key = fields.String(required=False)
- private_key_password = fields.String(required=False)
-
- # remote binding port
- bind_host = fields.String()
- bind_port = fields.Integer()
-
-
class DatabaseTestConnectionSchema(Schema, DatabaseParametersSchemaMixin):
rename_encrypted_extra = pre_load(rename_encrypted_extra)
diff --git a/tests/integration_tests/databases/api_tests.py b/tests/integration_tests/databases/api_tests.py
index 9698c7e42a..7821900c87 100644
--- a/tests/integration_tests/databases/api_tests.py
+++ b/tests/integration_tests/databases/api_tests.py
@@ -35,6 +35,7 @@ from sqlalchemy.sql import func
from superset import db, security_manager
from superset.connectors.sqla.models import SqlaTable
+from superset.databases.utils import make_url_safe
from superset.db_engine_specs.mysql import MySQLEngineSpec
from superset.db_engine_specs.postgres import PostgresEngineSpec
from superset.db_engine_specs.redshift import RedshiftEngineSpec
@@ -279,6 +280,36 @@ class TestDatabaseApi(SupersetTestCase):
db.session.delete(model)
db.session.commit()
+ def test_create_database_with_ssh_tunnel(self):
+ """
+ Database API: Test create with SSH Tunnel
+ """
+ self.login(username="admin")
+ example_db = get_example_database()
+ if example_db.backend == "sqlite":
+ return
+ ssh_tunnel_properties = {
+ "server_address": "123.132.123.1",
+ "bind_host": "localhost",
+ "bind_port": "5432",
+ "username": "foo",
+ "password": "bar",
+ }
+ database_data = {
+ "database_name": "test-create-db-with-ssh-tunnel",
+ "sqlalchemy_uri": example_db.sqlalchemy_uri_decrypted,
+ "ssh_tunnel": ssh_tunnel_properties,
+ }
+
+ uri = "api/v1/database/"
+ rv = self.client.post(uri, json=database_data)
+ response = json.loads(rv.data.decode("utf-8"))
+ self.assertEqual(rv.status_code, 201)
+ # Cleanup
+ model = db.session.query(Database).get(response.get("id"))
+ db.session.delete(model)
+ db.session.commit()
+
def test_create_database_invalid_configuration_method(self):
"""
Database API: Test create with an invalid configuration method.
diff --git a/tests/unit_tests/databases/api_test.py b/tests/unit_tests/databases/api_test.py
index d6f8897c4a..73e79f3a82 100644
--- a/tests/unit_tests/databases/api_test.py
+++ b/tests/unit_tests/databases/api_test.py
@@ -191,3 +191,75 @@ def test_non_zip_import(client: Any, full_api_access: None) -> None:
}
]
}
+
+
+def test_delete_ssh_tunnel(
+ mocker: MockFixture,
+ app: Any,
+ session: Session,
+ client: Any,
+ full_api_access: None,
+) -> None:
+ """
+ Test that we can delete SSH Tunnel
+ """
+ with app.app_context():
+ from superset.databases.api import DatabaseRestApi
+ from superset.databases.dao import DatabaseDAO
+ from superset.databases.ssh_tunnel.models import SSHTunnel
+ from superset.models.core import Database
+
+ DatabaseRestApi.datamodel.session = session
+
+ # create table for databases
+ Database.metadata.create_all(session.get_bind()) # pylint: disable=no-member
+
+ # Create our Database
+ database = Database(
+ database_name="my_database",
+ sqlalchemy_uri="gsheets://",
+ encrypted_extra=json.dumps(
+ {
+ "service_account_info": {
+ "type": "service_account",
+ "project_id": "black-sanctum-314419",
+ "private_key_id": "259b0d419a8f840056158763ff54d8b08f7b8173",
+ "private_key": "SECRET",
+ "client_email": "google-spreadsheets-demo-servi@black-sanctum-314419.iam.gserviceaccount.com",
+ "client_id": "SSH_TUNNEL_CREDENTIALS_CLIENT",
+ "auth_uri": "https://accounts.google.com/o/oauth2/auth",
+ "token_uri": "https://oauth2.googleapis.com/token",
+ "auth_provider_x509_cert_url": "https://www.googleapis.com/oauth2/v1/certs",
+ "client_x509_cert_url": "https://www.googleapis.com/robot/v1/metadata/x509/google-spreadsheets-demo-servi%40black-sanctum-314419.iam.gserviceaccount.com",
+ },
+ }
+ ),
+ )
+ session.add(database)
+ session.commit()
+
+ # mock the lookup so that we don't need to include the driver
+ mocker.patch("sqlalchemy.engine.URL.get_driver_name", return_value="gsheets")
+ mocker.patch("superset.utils.log.DBEventLogger.log")
+
+ # Create our SSHTunnel
+ tunnel = SSHTunnel(
+ database_id=1,
+ database=database,
+ )
+
+ session.add(tunnel)
+ session.commit()
+
+ # Get our recently created SSHTunnel
+ response_tunnel = DatabaseDAO.get_ssh_tunnel(1)
+ assert response_tunnel
+ assert isinstance(response_tunnel, SSHTunnel)
+ assert 1 == response_tunnel.database_id
+
+ # Delete the recently created SSHTunnel
+ response_delete_tunnel = client.delete("/api/v1/database/1/ssh_tunnel/")
+ assert response_delete_tunnel.json["message"] == "OK"
+
+ response_tunnel = DatabaseDAO.get_ssh_tunnel(1)
+ assert response_tunnel is None