Sagart-cactus opened a new pull request, #44453: URL: https://github.com/apache/superset/pull/44453
<!--- 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 --> Adds per-user OAuth2 authentication to the BigQuery engine spec, completing the work that #30674 ("adding necessary changes to support bigquery oauth") left as a follow-up. With this change each user authorizes Superset against their own Google account once, and every BigQuery query (SQL Lab, charts, catalog and schema listing, cost estimation, partition lookups, uploads) runs with **that user's** IAM permissions instead of a shared service account or the host's Application Default Credentials. Google Sheets, Trino and Databricks already support this through SIP-85 (#20300); BigQuery is the odd one out even though the OAuth2 plumbing for it was merged in 2024. The implementation was extracted from a config-level adapter we have been running against Superset 6.1.0 in production, reduced to the engine-agnostic parts. **Design decisions** - **Bring-your-own client.** `impersonate_user` wraps the access token in a `bigquery.Client` and passes it via `connect_args["client"]` with `user_supplied_client=true` in the URL. That is sqlalchemy-bigquery's documented way of supplying credentials, and it leaves the service-account / ADC path untouched when no token is present. - **Stable engine cache key.** `Database._get_sqla_engine` caches engines keyed on `repr(engine_kwargs)`. A plain `bigquery.Client` reprs with its memory address, which would cache one engine per request. The client subclass reprs a SHA-256 fingerprint of the token, so the cache holds one engine per user token, matching what Google Sheets gets by putting the token in the URL. - **No silent fallback.** Unlike Trino (401) or Databricks (auth error), a BigQuery connection without a token silently succeeds using the shared credentials, i.e. it runs the query as Superset rather than as the user. When OAuth2 is configured for the database and there is a saved database and a logged-in user, `impersonate_user` starts the OAuth2 dance instead. Background jobs (no user) and test-connection on an unsaved database keep the previous behaviour. - **`_get_client` honours the user client** so `get_catalog_names`, `get_default_catalog`, `estimate_query_cost` and `get_time_partition_column` also run as the user, and it raises rather than falling back to ADC if the flagged connection has no client. - **`needs_oauth2`** unwraps the BigQuery DBAPI's `DatabaseError(args[0])` and SQLAlchemy's `DBAPIError.orig` to find `RefreshError` (expired bare token) and `Unauthorized` (revoked token). `Forbidden` is a genuine IAM denial for that user and deliberately does not trigger re-authorization. - **Secure extra.** `oauth2_client_info` is stripped before the remaining secure extra is forwarded to the dialect (which rejects unknown engine arguments) and its `secret` is masked. Uploads via `df_to_sql` use the user's token so they do not fall back to ADC either. - **Scope** defaults to `https://www.googleapis.com/auth/bigquery`; operators can widen it in `DATABASE_OAUTH2_CLIENTS` (for example `drive.readonly` for external tables backed by Google Sheets). **Backwards compatibility.** Nothing changes unless an operator registers an OAuth2 client for `Google BigQuery` (or `oauth2_client_info` in the database's secure extra) *and* enables "Impersonate logged in user" on the connection. Existing service-account and ADC connections behave exactly as before. No migration, no new feature flag. The engine-spec README feature tables were regenerated with `python -m superset.db_engine_specs.lib`; only the two Google BigQuery rows affected by this change (score and User Impersonation) were updated, because the tables have drifted for many other engines on `master` and regenerating them all would swamp this diff. ### BEFORE/AFTER SCREENSHOTS OR ANIMATED GIF <!--- Skip this if not applicable --> N/A (backend only). Before: BigQuery jobs appear in `INFORMATION_SCHEMA.JOBS_BY_PROJECT` under the service account. After: they appear under each user's own `user_email`. ### TESTING INSTRUCTIONS <!--- Required! What steps can be taken to manually verify the changes? --> Unit tests: `pytest tests/unit_tests/db_engine_specs/test_bigquery.py` (16 new tests covering the class attributes, the Google authorization URI parameters, `impersonate_user` with and without a token, the stable client repr, `_get_client` with a user-supplied client, `needs_oauth2` through the DBAPI and SQLAlchemy wrappers, `update_params_from_encrypted_extra`, and `df_to_sql`). `pre-commit run --files superset/db_engine_specs/bigquery.py tests/unit_tests/db_engine_specs/test_bigquery.py` passes (mypy, ruff, pylint, engine-spec metadata validation). Manual, end to end: 1. In Google Cloud console create an OAuth 2.0 client (Web application) with the redirect URI `https://<superset-host>/api/v1/database/oauth2/`. 2. In `superset_config.py`: ```python DATABASE_OAUTH2_CLIENTS = { "Google BigQuery": { "id": "XXX.apps.googleusercontent.com", "secret": "GOCSPX-YYY", }, } DATABASE_OAUTH2_REDIRECT_URI = "https://<superset-host>/api/v1/database/oauth2/" ``` 3. Create a BigQuery database with the SQLAlchemy URI `bigquery://<project>` (optionally `?location=<region>`), **no** service-account JSON in Secure Extra, and tick **Impersonate logged in user** under Advanced → Security. 4. Open SQL Lab and run any query. Superset returns the `OAUTH2_REDIRECT` error, the frontend opens the Google consent tab, and after consent the query re-runs. 5. Verify the job ran as the user: `SELECT user_email FROM region-<region>.INFORMATION_SCHEMA.JOBS_BY_PROJECT ORDER BY creation_time DESC LIMIT 5`. 6. Query a table the user is not allowed to read: a normal permission error (403) is shown, not a new consent prompt. 7. Revoke the app in the user's Google account security settings and run a query again: the consent prompt is shown once more. 8. Sign in as a second user and repeat step 4: they get their own consent prompt and their own `user_email`. ### ADDITIONAL INFORMATION <!--- Check any relevant boxes with "x" --> <!--- HINT: Include "Fixes #nnn" if you are fixing an existing issue --> - [x] Has associated issue: [SIP-85] OAuth2 for databases #20300 (follow-up to #30674) - [ ] Required feature flags: - [ ] Changes UI - [ ] Includes DB Migration (follow approval process in [SIP-59](https://github.com/apache/superset/issues/13351)) - [ ] Migration is atomic, supports rollback & is backwards-compatible - [ ] Confirm DB migration upgrade and downgrade tested - [ ] Runtime estimates and downtime expectations provided - [x] Introduces new feature or API - [ ] Removes existing feature or API 🤖 Generated with [Claude Code](https://claude.com/claude-code) -- 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: [email protected] For queries about this service, please contact Infrastructure at: [email protected] --------------------------------------------------------------------- To unsubscribe, e-mail: [email protected] For additional commands, e-mail: [email protected]
