betodealmeida opened a new pull request, #27631: URL: https://github.com/apache/superset/pull/27631
<!--- Please write the PR title following the conventions at https://www.conventionalcommits.org/en/v1.0.0/ Example: fix(dashboard): load charts correctly --> ### SUMMARY <!--- Describe the change below, including rationale and design decisions --> This PR introduces a new table called `database_user_oauth2_tokens`. The table is used for storing personal user tokens associated with a given database: ```sql # \d database_user_oauth2_tokens Table "public.database_user_oauth2_tokens" Column | Type | Collation | Nullable | Default -------------------------+-----------------------------+-----------+----------+--------------------------------------------------------- created_on | timestamp without time zone | | | changed_on | timestamp without time zone | | | id | integer | | not null | nextval('database_user_oauth2_tokens_id_seq'::regclass) user_id | integer | | not null | database_id | integer | | not null | access_token | bytea | | | access_token_expiration | timestamp without time zone | | | refresh_token | bytea | | | created_by_fk | integer | | | changed_by_fk | integer | | | Indexes: "database_user_oauth2_tokens_pkey" PRIMARY KEY, btree (id) Foreign-key constraints: "database_user_oauth2_tokens_changed_by_fk_fkey" FOREIGN KEY (changed_by_fk) REFERENCES ab_user(id) "database_user_oauth2_tokens_created_by_fk_fkey" FOREIGN KEY (created_by_fk) REFERENCES ab_user(id) "database_user_oauth2_tokens_database_id_fkey" FOREIGN KEY (database_id) REFERENCES dbs(id) "database_user_oauth2_tokens_user_id_fkey" FOREIGN KEY (user_id) REFERENCES ab_user(id) ``` Whenever a SQLAlchemy engine is instantiated, the personal user token will be passed to the `get_url_for_impersonation` method in the DB engine spec, if available, so that a custom URL can be built for the user. For example, for GSheets: ```python def get_url_for_impersonation( cls, url: URL, impersonate_user: bool, username: str | None, access_token: str | None, # <== here ) -> URL: if not impersonate_user: return url if username is not None: user = security_manager.find_user(username=username) if user and user.email: url = url.update_query_dict({"subject": user.email}) if access_token: url = url.update_query_dict({"access_token": access_token}) return url ``` The change allows users to login to databases like BigQuery, Snowflake, Dremio, Databricks, Google Sheets, etc. using their own credentials. This makes it easier to set up databases, since service accounts are no longer required, and provides better isolation of data between users. In order to populate the table with personal access tokens, the DB engine spec checks for a specific exception that signals that OAuth2 should start: ```python class BaseEngineSpec: def execute(...) -> None: try: cursor.execute(query) except cls.oauth2_exception as ex: # <== here if cls.is_oauth2_enabled() and g.user: cls.start_oauth2_dance(database_id) raise cls.get_dbapi_mapped_exception(ex) from ex except Exception as ex: raise cls.get_dbapi_mapped_exception(ex) from ex ``` When called, the `start_oauth2_dance` method will return the error `OAUTH2_REDIRECT` to the frontend. The errors is captured by the `ErrorMessageWithStackTrace` component, which provides a link to the user so they can start the OAuth2 authentication. Since this is implemented at the DB engine spec level, any query will trigger it — in SQL Lab, Explore, or dashboards. See the screenshots below for the UX. ### BEFORE/AFTER SCREENSHOTS OR ANIMATED GIF <!--- Skip this if not applicable --> ### TESTING INSTRUCTIONS <!--- Required! What steps can be taken to manually verify the changes? --> 1. Create a Google Sheets database. 2. Create a Google OAuth2 application at https://console.cloud.google.com/apis/credentials/oauthclient/ of type "Web application" 3. Edit `superset_config.py` and add the client ID and secret: ```python DATABASE_OAUTH2_CREDENTIALS = { "Google Sheets": { "CLIENT_ID": "XXX.apps.googleusercontent.com", "CLIENT_SECRET": "GOCSPX-YYY", }, } ``` 4. In SQL Lab, try to query a sheet that is not shared publicly. It should trigger OAuth2. 5. Add the sheet as a dataset and create a chart. 6. Delete the tokens from the database: ```sql DELETE FROM database_user_oauth2_tokens; ``` 7. Reload the chart. It should trigger OAuth2. 8. Add the chart to a dashboard, delete the tokens, and reload the dashboard. It should trigger OAuth2. ### ADDITIONAL INFORMATION <!--- Check any relevant boxes with "x" --> <!--- HINT: Include "Fixes #nnn" if you are fixing an existing issue --> - [x] Has associated issue: https://github.com/apache/superset/issues/20300 - [ ] Required feature flags: - [ ] Changes UI - [x] Includes DB Migration (follow approval process in [SIP-59](https://github.com/apache/superset/issues/13351)) - [x] Migration is atomic, supports rollback & is backwards-compatible - [x] Confirm DB migration upgrade and downgrade tested - [ ] Runtime estimates and downtime expectations provided - [x] Introduces new feature or API - [ ] Removes existing feature or API -- This is an automated message from the Apache Git Service. To respond to the message, please log on to GitHub and use the URL above to go to the specific comment. To unsubscribe, e-mail: notifications-unsubscr...@superset.apache.org For queries about this service, please contact Infrastructure at: us...@infra.apache.org --------------------------------------------------------------------- To unsubscribe, e-mail: notifications-unsubscr...@superset.apache.org For additional commands, e-mail: notifications-h...@superset.apache.org
