def set_sqlalchemy_uri(self, uri: str) -> None: conn = make_url_safe(uri.strip()) if conn.password != PASSWORD_MASK and not custom_password_store: # do not over-write the password with the password mask self.password = conn.password conn.password = PASSWORD_MASK if conn.password else None self.sqlalchemy_uri = str(conn) # hides the password
def test_make_url_safe_url(session: Session) -> None: """ Test converting a url to a safe uri """ uri = make_url("postgresql+psycopg2://superset:***@127.0.0.1:5432/superset") uri_safe = make_url_safe(uri) assert uri_safe == uri
def update_impersonation_config( cls, connect_args: Dict[str, Any], uri: str, username: Optional[str], ) -> None: """ Update a configuration dictionary that can set the correct properties for impersonating users :param connect_args: :param uri: URI string :param impersonate_user: Flag indicating if impersonation is enabled :param username: Effective username :return: None """ url = make_url_safe(uri) backend_name = url.get_backend_name() # Must be Hive connection, enable impersonation, and set optional param # auth=LDAP|KERBEROS # this will set hive.server2.proxy.user=$effective_username on connect_args['configuration'] if backend_name == "hive" and username is not None: configuration = connect_args.get("configuration", {}) configuration["hive.server2.proxy.user"] = username connect_args["configuration"] = configuration
def sqlalchemy_uri_validator(uri: str, exception: Type[ValidationError] = ValidationError ) -> None: """ Check if a user has submitted a valid SQLAlchemy URI """ try: make_url_safe(uri.strip()) except DatabaseInvalidError as ex: raise exception([ _("Invalid connection string, a valid string usually follows:" "'DRIVER://*****:*****@DB-HOST/DATABASE-NAME'" "<p>" "Example:'postgresql://*****:*****@your-postgres-db/database'" "</p>") ]) from ex
def test_make_url_safe_string(session: Session) -> None: """ Test converting a string to a safe uri """ uri_string = "postgresql+psycopg2://superset:***@127.0.0.1:5432/superset" uri_safe = make_url_safe(uri_string) assert str(uri_safe) == uri_string assert uri_safe == make_url(uri_string)
def get_parameters_from_uri( cls, uri: str, encrypted_extra: Optional[Dict[str, str]] = None ) -> Any: value = make_url_safe(uri) # Building parameters from encrypted_extra and uri if encrypted_extra: return {**encrypted_extra, "query": value.query} raise ValidationError("Invalid service credentials")
def parameters(self) -> Dict[str, Any]: uri = make_url_safe(self.sqlalchemy_uri_decrypted) encrypted_extra = self.get_encrypted_extra() try: # pylint: disable=useless-suppression parameters = self.db_engine_spec.get_parameters_from_uri( # type: ignore uri, encrypted_extra=encrypted_extra) except Exception: # pylint: disable=broad-except parameters = {} return parameters
def validate_password(self, data: Dict[str, Any], **kwargs: Any) -> None: """If sqlalchemy_uri has a masked password, password is required""" uuid = data["uuid"] existing = db.session.query(Database).filter_by(uuid=uuid).first() if existing: return uri = data["sqlalchemy_uri"] password = make_url_safe(uri).password if password == PASSWORD_MASK and data.get("password") is None: raise ValidationError("Must provide a password for the database")
def get_sqla_engine( self, schema: Optional[str] = None, nullpool: bool = True, user_name: Optional[str] = None, source: Optional[utils.QuerySource] = None, ) -> Engine: extra = self.get_extra() sqlalchemy_url = make_url_safe(self.sqlalchemy_uri_decrypted) self.db_engine_spec.adjust_database_uri(sqlalchemy_url, schema) effective_username = self.get_effective_user(sqlalchemy_url, user_name) # If using MySQL or Presto for example, will set url.username # If using Hive, will not do anything yet since that relies on a # configuration parameter instead. self.db_engine_spec.modify_url_for_impersonation( sqlalchemy_url, self.impersonate_user, effective_username ) masked_url = self.get_password_masked_url(sqlalchemy_url) logger.debug("Database.get_sqla_engine(). Masked URL: %s", str(masked_url)) params = extra.get("engine_params", {}) if nullpool: params["poolclass"] = NullPool connect_args = params.get("connect_args", {}) if self.impersonate_user: self.db_engine_spec.update_impersonation_config( connect_args, str(sqlalchemy_url), effective_username ) if connect_args: params["connect_args"] = connect_args self.update_encrypted_extra_params(params) if DB_CONNECTION_MUTATOR: if not source and request and request.referrer: if "/superset/dashboard/" in request.referrer: source = utils.QuerySource.DASHBOARD elif "/superset/explore/" in request.referrer: source = utils.QuerySource.CHART elif "/superset/sqllab/" in request.referrer: source = utils.QuerySource.SQL_LAB sqlalchemy_url, params = DB_CONNECTION_MUTATOR( sqlalchemy_url, params, effective_username, security_manager, source ) try: return create_engine(sqlalchemy_url, **params) except Exception as ex: raise self.db_engine_spec.get_dbapi_mapped_exception(ex)
def sqlalchemy_uri_decrypted(self) -> str: try: conn = make_url_safe(self.sqlalchemy_uri) except (ArgumentError, ValueError): # if the URI is invalid, ignore and return a placeholder url # (so users see 500 less often) return "dialect://invalid_uri" if custom_password_store: conn.password = custom_password_store(conn) else: conn.password = self.password return str(conn)
def get_parameters_from_uri( cls, uri: str, encrypted_extra: Optional[Dict[str, str]] = None) -> Any: value = make_url_safe(uri) # Building parameters from encrypted_extra and uri if encrypted_extra: # ``value.query`` needs to be explicitly converted into a dict (from an # ``immutabledict``) so that it can be JSON serialized return {**encrypted_extra, "query": dict(value.query)} raise ValidationError("Invalid service credentials")
def _pre_add_update(self, database: Database) -> None: if app.config["PREVENT_UNSAFE_DB_CONNECTIONS"]: check_sqlalchemy_uri(make_url_safe(database.sqlalchemy_uri)) self.check_extra(database) self.check_encrypted_extra(database) if database.server_cert: utils.parse_ssl_cert(database.server_cert) database.set_sqlalchemy_uri(database.sqlalchemy_uri) security_manager.add_permission_view_menu("database_access", database.perm) # adding a new database we always want to force refresh schema list for schema in database.get_all_schema_names(): security_manager.add_permission_view_menu( "schema_access", security_manager.get_schema_perm(database, schema) )
def get_parameters_from_uri( cls, uri: str, encrypted_extra: Optional[ # pylint: disable=unused-argument Dict[str, str]] = None, ) -> Any: url = make_url_safe(uri) query = dict(url.query.items()) return { "username": url.username, "password": url.password, "account": url.host, "database": url.database, "role": query.get("role"), "warehouse": query.get("warehouse"), }
def sqlalchemy_uri_validator(value: str) -> str: """ Validate if it's a valid SQLAlchemy URI and refuse SQLLite by default """ try: uri = make_url_safe(value.strip()) except DatabaseInvalidError as ex: raise ValidationError([ _("Invalid connection string, a valid string usually follows: " "driver://*****:*****@database-host/database-name") ]) from ex if current_app.config.get("PREVENT_UNSAFE_DB_CONNECTIONS", True): try: check_sqlalchemy_uri(uri) except SupersetSecurityException as ex: raise ValidationError([str(ex)]) from ex return value
def update_impersonation_config( cls, connect_args: Dict[str, Any], uri: str, username: Optional[str], ) -> None: """ Update a configuration dictionary that can set the correct properties for impersonating users :param connect_args: config to be updated :param uri: URI string :param username: Effective username :return: None """ url = make_url_safe(uri) backend_name = url.get_backend_name() # Must be Trino connection, enable impersonation, and set optional param # auth=LDAP|KERBEROS # Set principal_username=$effective_username if backend_name == "trino" and username is not None: connect_args["user"] = username
def run(self) -> None: self.validate() uri = self._properties.get("sqlalchemy_uri", "") if self._model and uri == self._model.safe_sqlalchemy_uri(): uri = self._model.sqlalchemy_uri_decrypted # context for error messages url = make_url_safe(uri) context = { "hostname": url.host, "password": url.password, "port": url.port, "username": url.username, "database": url.database, } try: database = DatabaseDAO.build_db_for_connection_test( server_cert=self._properties.get("server_cert", ""), extra=self._properties.get("extra", "{}"), impersonate_user=self._properties.get("impersonate_user", False), encrypted_extra=self._properties.get("encrypted_extra", "{}"), ) database.set_sqlalchemy_uri(uri) database.db_engine_spec.mutate_db_for_connection_test(database) engine = database.get_sqla_engine() event_logger.log_with_context( action="test_connection_attempt", engine=database.db_engine_spec.__name__, ) def ping(engine: Engine) -> bool: with closing(engine.raw_connection()) as conn: return engine.dialect.do_ping(conn) try: alive = func_timeout( int(app.config["TEST_DATABASE_CONNECTION_TIMEOUT"]. total_seconds()), ping, args=(engine, ), ) except (sqlite3.ProgrammingError, RuntimeError): # SQLite can't run on a separate thread, so ``func_timeout`` fails # RuntimeError catches the equivalent error from duckdb. alive = engine.dialect.do_ping(engine) except FunctionTimedOut as ex: raise SupersetTimeoutException( error_type=SupersetErrorType.CONNECTION_DATABASE_TIMEOUT, message= ("Please check your connection details and database settings, " "and ensure that your database is accepting connections, " "then try connecting again."), level=ErrorLevel.ERROR, extra={"sqlalchemy_uri": database.sqlalchemy_uri}, ) from ex except Exception: # pylint: disable=broad-except alive = False if not alive: raise DBAPIError(None, None, None) # Log succesful connection test with engine event_logger.log_with_context( action="test_connection_success", engine=database.db_engine_spec.__name__, ) except (NoSuchModuleError, ModuleNotFoundError) as ex: event_logger.log_with_context( action=f"test_connection_error.{ex.__class__.__name__}", engine=database.db_engine_spec.__name__, ) raise DatabaseTestConnectionDriverError( message=_("Could not load database driver: {}").format( database.db_engine_spec.__name__), ) from ex except DBAPIError as ex: event_logger.log_with_context( action=f"test_connection_error.{ex.__class__.__name__}", engine=database.db_engine_spec.__name__, ) # check for custom errors (wrong username, wrong password, etc) errors = database.db_engine_spec.extract_errors(ex, context) raise DatabaseTestConnectionFailedError(errors) from ex except SupersetSecurityException as ex: event_logger.log_with_context( action=f"test_connection_error.{ex.__class__.__name__}", engine=database.db_engine_spec.__name__, ) raise DatabaseSecurityUnsafeError(message=str(ex)) from ex except SupersetTimeoutException as ex: event_logger.log_with_context( action=f"test_connection_error.{ex.__class__.__name__}", engine=database.db_engine_spec.__name__, ) # bubble up the exception to return a 408 raise ex except Exception as ex: event_logger.log_with_context( action=f"test_connection_error.{ex.__class__.__name__}", engine=database.db_engine_spec.__name__, ) errors = database.db_engine_spec.extract_errors(ex, context) raise DatabaseTestConnectionUnexpectedError(errors) from ex
def get_dialect(self) -> Dialect: sqla_url = make_url_safe(self.sqlalchemy_uri_decrypted) return sqla_url.get_dialect()()
def grains(self): url = make_url_safe(self.sqlalchemy_uri) backend = url.get_backend_name() db_engine_spec = db_engine_specs.engines.get( backend, db_engine_specs.BaseEngineSpec) return db_engine_spec.get_time_grains()
def get_password_masked_url_from_uri( # pylint: disable=invalid-name cls, uri: str) -> URL: sqlalchemy_url = make_url_safe(uri) return cls.get_password_masked_url(sqlalchemy_url)
def run(self) -> None: engine = self._properties["engine"] engine_specs = get_engine_specs() if engine in BYPASS_VALIDATION_ENGINES: # Skip engines that are only validated onCreate return if engine not in engine_specs: raise InvalidEngineError( SupersetError( message=__( 'Engine "%(engine)s" is not a valid engine.', engine=engine, ), error_type=SupersetErrorType.GENERIC_DB_ENGINE_ERROR, level=ErrorLevel.ERROR, extra={ "allowed": list(engine_specs), "provided": engine }, ), ) engine_spec = engine_specs[engine] if not hasattr(engine_spec, "parameters_schema"): raise InvalidEngineError( SupersetError( message=__( 'Engine "%(engine)s" cannot be configured through parameters.', engine=engine, ), error_type=SupersetErrorType.GENERIC_DB_ENGINE_ERROR, level=ErrorLevel.ERROR, extra={ "allowed": [ name for name, engine_spec in engine_specs.items() if issubclass(engine_spec, BasicParametersMixin) ], "provided": engine, }, ), ) # perform initial validation errors = engine_spec.validate_parameters( # type: ignore self._properties.get("parameters", {})) if errors: event_logger.log_with_context(action="validation_error", engine=engine) raise InvalidParametersError(errors) serialized_encrypted_extra = self._properties.get( "encrypted_extra", "{}") try: encrypted_extra = json.loads(serialized_encrypted_extra) except json.decoder.JSONDecodeError: encrypted_extra = {} # try to connect sqlalchemy_uri = engine_spec.build_sqlalchemy_uri( # type: ignore self._properties.get("parameters"), encrypted_extra, ) if self._model and sqlalchemy_uri == self._model.safe_sqlalchemy_uri(): sqlalchemy_uri = self._model.sqlalchemy_uri_decrypted database = DatabaseDAO.build_db_for_connection_test( server_cert=self._properties.get("server_cert", ""), extra=self._properties.get("extra", "{}"), impersonate_user=self._properties.get("impersonate_user", False), encrypted_extra=serialized_encrypted_extra, ) database.set_sqlalchemy_uri(sqlalchemy_uri) database.db_engine_spec.mutate_db_for_connection_test(database) username = self._actor.username if self._actor is not None else None engine = database.get_sqla_engine(user_name=username) try: with closing(engine.raw_connection()) as conn: alive = engine.dialect.do_ping(conn) except Exception as ex: url = make_url_safe(sqlalchemy_uri) context = { "hostname": url.host, "password": url.password, "port": url.port, "username": url.username, "database": url.database, } errors = database.db_engine_spec.extract_errors(ex, context) raise DatabaseTestConnectionFailedError(errors) from ex if not alive: raise DatabaseOfflineError( SupersetError( message=__("Database is offline."), error_type=SupersetErrorType.GENERIC_DB_ENGINE_ERROR, level=ErrorLevel.ERROR, ), )
def backend(self) -> str: sqlalchemy_url = make_url_safe(self.sqlalchemy_uri_decrypted) return sqlalchemy_url.get_backend_name()
def url_object(self) -> URL: return make_url_safe(self.sqlalchemy_uri_decrypted)