Dynamically provide database connection strings

[VinayakGupta5]

I have 1000s of databases which share a common schema, When client logs in, I provide API with connection strings to target the connection. In apache superset, how do I provide connection string when I request dashboard via guest_token and provide it with target SQL database to connect with without saving database connection strings in Apache Superset.

[dosubot[bot]]

Hey there, @VinayakGupta5! 👋 I'm here to help you with any bugs, questions, or contributions while you wait for a human maintainer.

Currently, Apache Superset does not support dynamically providing database connection strings directly through the API when requesting a dashboard via a guest token. Superset typically requires database connections to be predefined and stored within its configuration.

However, you can programmatically update or modify database connection settings using the set_database_uri command, which allows you to update a database connection URI by specifying the database name and the new URI ^[1][2]. This approach involves updating the connection settings in Superset before accessing the dashboard, but it does not dynamically change the connection per request without storing it.

For your use case, you might need to implement a custom solution that updates the connection settings in Superset based on the client's login and then accesses the dashboard. This would involve some orchestration outside of Superset to manage these updates efficiently.

To continue talking to Dosu, mention @dosu.

---

^{Help Dosu learn! Give it feedback: Great Response | Irrelevant Answer | Incorrect Sources | Too Verbose | Hallucination | Other}

[VinayakGupta5]

if we need to implement custom solution in superset, would we have to clone repo & make it or we can push custom code via docker itself?

Can you give an example of custom solution which could do this

[dosubot[bot]]

You can implement a custom solution in Apache Superset to dynamically provide database connection strings without cloning the repository. This can be achieved by using Docker and configuring environment variables within the superset_config.py file.

Here's an example of how you can set up dynamic database connection strings using environment variables:

import os

DATABASE_DIALECT = os.getenv("DATABASE_DIALECT")
DATABASE_USER = os.getenv("DATABASE_USER")
DATABASE_PASSWORD = os.getenv("DATABASE_PASSWORD")
DATABASE_HOST = os.getenv("DATABASE_HOST")
DATABASE_PORT = os.getenv("DATABASE_PORT")
DATABASE_DB = os.getenv("DATABASE_DB")

# The SQLAlchemy connection string.
SQLALCHEMY_DATABASE_URI = (
    f"{DATABASE_DIALECT}://"
    f"{DATABASE_USER}:{DATABASE_PASSWORD}@"
    f"{DATABASE_HOST}:{DATABASE_PORT}/{DATABASE_DB}"
)

In your Docker setup, you can pass these environment variables to the container, which will then be used by Superset to construct the connection string dynamically. This can be done by setting the environment variables in your docker-compose.yml file or by using Docker's -e flag when running the container ^[1].

To continue talking to Dosu, mention @dosu.

---

^{Help Dosu learn! Give it feedback: Great Response | Irrelevant Answer | Incorrect Sources | Too Verbose | Hallucination | Other}

[VinayakGupta5]

is there a way to change connection strings of a query before they are executed on a dashboard request?

[dosubot[bot]]

To request a dashboard via the frontend using a guest token and pass encrypted connection strings as parameters, you can follow this flow:

1. Backend Preparation:

   • Implement a backend service that can generate guest tokens and handle encrypted connection strings. This service will interact with Superset's API to create guest tokens and manage connection strings.

2. Generate Guest Token:

   • When a user logs in, your backend service should generate a guest token by making a POST request to Superset's /security/guest_token endpoint. This token will include user-specific access rights and any necessary row-level security (RLS) rules.

3. Encrypt Connection Strings:

   • Encrypt the database connection strings using a secure method before sending them to the frontend. This ensures that sensitive information is protected during transmission.

4. Frontend Request:

   • The frontend application requests the dashboard by embedding it using the Superset Embedded SDK. It will call a function like fetchGuestToken to retrieve the guest token from your backend service.

5. Embed Dashboard:

   • Use the embedDashboard function from the Superset Embedded SDK to embed the dashboard in an iframe. Pass the guest token and any additional parameters, such as encrypted connection strings, to the iframe.

6. Decrypt and Use Connection Strings:

   • On the backend, use the DB_CONNECTION_MUTATOR hook to decrypt the connection strings and modify the connection parameters dynamically before executing queries. This hook allows you to adjust the connection settings based on the decrypted information.

7. Dashboard Rendering:

   • The iframe loads the dashboard using the guest token, and the backend ensures that the correct database connection is used for executing queries based on the decrypted connection strings.

This flow allows you to securely pass and use encrypted connection strings while leveraging guest tokens for authentication and access control in Apache Superset.

To continue talking to Dosu, mention @dosu.

---

^{Help Dosu learn! Give it feedback: Great Response | Irrelevant Answer | Incorrect Sources | Too Verbose | Hallucination | Other}

[VinayakGupta5]

@dosu can you give detail on DB_Connection_Mutator, what should be input of the function and return of the function

[dosubot[bot]]

The DB_CONNECTION_MUTATOR function in Apache Superset is designed to allow dynamic modification of database connection strings at runtime. Here's a detailed explanation of its inputs and expected return values:

Inputs:

1. uri: This is an object representing the database connection URI. It can be modified to change parts of the connection string, such as the username, password, host, etc.

2. params: A dictionary of additional connection parameters. These can be altered to adjust the connection settings as needed.

3. username: The username of the user making the request. This can be used to apply user-specific logic, such as impersonation.

4. security_manager: An object that provides access to security-related functions, such as finding user details. This can be used to retrieve additional user information if needed.

5. source: This indicates the source of the connection request. The specific values it can take are not detailed in the available information.

Return:

The function should return a tuple containing:

• The potentially modified uri object.
• The potentially modified params dictionary.

Example:

Here's a simple example of how the DB_CONNECTION_MUTATOR function might be implemented:

def DB_CONNECTION_MUTATOR(uri, params, username, security_manager, source):
    user = security_manager.find_user(username=username)
    if user and user.email:
        uri.username = user.email  # Modify the URI to use the user's email as the username
    return uri, params

In this example, the function modifies the uri to use the user's email as the username if available, and then returns the modified uri and params ^[1].

To continue talking to Dosu, mention @dosu.

---

^{Help Dosu learn! Give it feedback: Great Response | Irrelevant Answer | Incorrect Sources | Too Verbose | Hallucination | Other}

[VinayakGupta5]

how are urlParams usable in DB_Connection_Mutator & what is uri & params sample object like for mssql connection strings @dosu

[dosubot[bot]]

I wasn't able to find specific details about the structure of the uri object or the params dictionary for an MSSQL connection string in the Apache Superset repository. However, typically, the uri object in a database connection context is a parsed representation of a connection string, which includes components like the dialect, username, password, host, port, and database name.

For an MSSQL connection string, the uri might look something like this in a structured form:

• Dialect: mssql+pyodbc
• Username: your_username
• Password: your_password
• Host: your_host
• Port: 1433 (default for MSSQL)
• Database: your_database

The params dictionary typically contains additional connection options, such as driver information or other SQLAlchemy-specific parameters.

Regarding urlParams, they are not explicitly detailed in the available information, but they are generally used to pass additional query parameters or options that modify the behavior of the connection or query execution.

If you need to implement custom logic in the DB_CONNECTION_MUTATOR, you would modify these components based on your requirements, such as changing the username or adding specific parameters to the params dictionary.

To continue talking to Dosu, mention @dosu.

---

^{Help Dosu learn! Give it feedback: Great Response | Irrelevant Answer | Incorrect Sources | Too Verbose | Hallucination | Other}