aminghadersohi commented on code in PR #43309:
URL: https://github.com/apache/superset/pull/43309#discussion_r3816121483
##########
superset/db_engine_specs/gsheets.py:
##########
@@ -351,6 +351,56 @@ def parameters_json_schema(cls) -> Any:
spec.components.schema(cls.__name__, schema=cls.parameters_schema)
return spec.to_dict()["components"]["schemas"][cls.__name__]
+ @classmethod
+ def _get_validation_connections(
+ cls,
+ encrypted_credentials: dict[str, Any],
+ ) -> list[Connection]:
+ """
+ Build the connections used to validate spreadsheet URLs, in priority
order.
+
+ Passing a `subject` makes Google authenticate through domain-wide
delegation,
+ impersonating that user. Service accounts without domain-wide
delegation
+ configured — the common setup, where the spreadsheet is simply shared
with the
+ service account email — get back `invalid_grant`, so validation could
never
+ succeed for them. We validate as the service account itself first,
which is
+ also how the connection behaves at query time, and only fall back to
+ impersonating the current user so that domain-wide delegation keeps
working:
+ there, the admin is able to add sheets that only they have access to,
even if
+ later users are not able to access them.
+ """
+ subjects: list[str | None] = [None]
+ if g.user and g.user.email:
+ subjects.append(g.user.email)
+
+ return [
+ create_engine(
+ "gsheets://",
+ connect_args={
+ "adapter_kwargs": {
+ "gsheetsapi": {
+ "service_account_info": encrypted_credentials,
+ "subject": subject,
+ }
+ }
+ },
+ future=True,
+ ).connect()
+ for subject in subjects
+ ]
Review Comment:
This one isn't accurate — `.connect()` doesn't authenticate against Google,
so it can't raise `invalid_grant`.
shillelagh's GSheets dialect `create_connect_args` returns `path=":memory:"`
plus `adapters`/`adapter_kwargs`; the `GSheetsAPI` adapter is only instantiated
when a query references a spreadsheet table. So connecting is a local apsw
operation and the credentials aren't exercised until `_can_read_url` runs the
`SELECT`. Verified against the pinned shillelagh with a deliberately invalid
service account:
```python
fake_sa = {"type": "service_account", "client_email":
"[email protected]",
"token_uri": "https://oauth2.googleapis.com/token";,
"private_key": "not-a-key"}
for subject in (None, "[email protected]"):
create_engine("gsheets://", connect_args={"adapter_kwargs":
{"gsheetsapi":
{"service_account_info": fake_sa, "subject": subject}}},
future=True).connect()
# both succeed; no auth attempted
```
Auth errors surface from the `SELECT` inside `_can_read_url`, which already
catches them and returns `False`, so a failed delegated identity is treated as
a failed candidate rather than propagating.
The resource-cleanup point in the sibling comment is fair on its own terms —
these connections aren't closed, though that matches the pre-existing code. Not
fixing it here, because this PR is being reworked in response to [the review
feedback
above](https://github.com/apache/superset/pull/43309#issuecomment-5344617406):
validation currently diverges from query-time behavior, and the fix likely
needs to key off `impersonate_user` instead. I'll fold cleanup into whatever
shape that lands as.
--
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]
