trino python client integrating with OAUTH2 enabled trino cluster

[eadapa29]

Hello, I am trying to connect to an OAUTH2 enabled trino cluster using python client. I am getting authenticated and everything works fine but i have to click a link which is an additional step.. Is there a possibility to automatically authenticate it without having to launch the URL externally.

7:59 In [1]: import trino ...: conn = trino.dbapi.connect( ...: host='trino.somedomain.net', ...: port=443, ...: user='first.last', ...: catalog='iceberg', ...: schema='ds_scratch', ...: http_scheme='https', ...: auth=trino.auth.OAuth2Authentication(), ...: ) ...: cur = conn.cursor()

In [2]: cur.execute('SELECT * FROM system.runtime.nodes')

Open the following URL in the browser for the external authentication: https://trino.somedomain.net/oauth2/token/initiate/042f6e4167d4e6a3f70068ec4389037c2b9c34f3ec356ddc5522a3e13e179fd9

I am talking about the last step where it says "Open the following URL in the browser...." Can it be redirected within the python client ?

[mdesmet]

Note in our docs:

https://trinodb.slack.com/archives/CFPVDCDHV/p1657883633509519

A callback to handle the redirect url can be provided via param redirect_auth_url_handler of the trino.auth.OAuth2Authentication class. By default, it will try to launch a web browser (trino.auth.WebBrowserRedirectHandler) to go through the authentication flow and output the redirect url to stdout (trino.auth.ConsoleRedirectHandler). Multiple redirect handlers are combined using the trino.auth.CompositeRedirectHandler class.

[eadapa29]

Correct.. By what would be the redirect_auth_url_handler be... Should i write my own handler ?? Is that what it implies ?

[mdesmet]

True. By default it launches a webbrowser and it is up to the user to authenticate itself on the OAuth provider. Now if you want to automate that you would have to perform the authentication automatically and ensure your OAuth provider redirects to the Trino server and provides it with an access/refresh token as per the OAuth protocol. That token will be picked up by the trino-python-client

[mdesmet]

Are you saying that it doesn't launch your webbrowser?

Can you try following code:

import webbrowser

webbrowser.open_new("https://trino.io/")

If not working, could you tell us your operation system and python version? Are you on a notebook server or is it simply a local Python script?

[eadapa29]

Hello, Appreciate your response. To add more details to what i said earlier I see that the link is generated automatically and displayed in out python notebook console but we have to click it manually to launch the browser and the token delegation happens successfully. What our requirement is there a way to redirect within the code instead of a browser prompt. This requirement helps us in submitting background jobs.

regards, Eshwara

On Mon, Jul 25, 2022 at 7:53 AM Michiel De Smet @.***> wrote:

Are you saying that it doesn't launch your webbrowser?

Can you try following code:

import webbrowser

webbrowser.open_new("https://trino.io/")

If not working, could you tell us your operation system and python version?

— Reply to this email directly, view it on GitHub https://github.com/trinodb/trino-python-client/issues/207#issuecomment-1194010560, or unsubscribe https://github.com/notifications/unsubscribe-auth/AIN7PNZSSTF32UVA2CWPI6DVV2E5VANCNFSM54JQQ6MQ . You are receiving this because you authored the thread.Message ID: @.***>

[JeevansSP]

Now if you want to automate that

i got till here, but i did not get further steps, background: i have my trino clusters running on kubernetes and i also have a website that i can visit for the UI, i have configured azure active directory (Oauth2) with my trino clusters,

[mdesmet]

You need to supply your own handler as explained in the docs.

Your handler needs to ensure that the passed url is executed by the user to authenticate. After your Trino python client will poll the Trino server and receive the token when user authentication is complete and your query will be executed. Note that the caching implementation is probably not exactly what you want for a multi user web application and is currently not pluggable. PRs are welcome!

[IceS2]

Hey folks, I'm not sure I should ask this here, but I've been having an issue when caching the Tokens... At every single Query I'm prompted with the Web URL...

I'm connecting with this code:

        self._conn = trino.dbapi.connect(
            host=self.host,
            port=self.port,
            user=self.user,
            http_scheme="https",
            auth=trino.auth.OAuth2Authentication()
        )

And executing my queries like this:

with self._conn as conn:
            cur = conn.cursor()
            cur.execute(query)

I've tried it both with pip install 'trino[external-authentication-token-cache]' and without it.

Any idea of what I might be doing wrong? Thanks!

[IceS2]

Alright, found out that reusing the same cursor it actually doesn't prompt for new authentication. I thought it'd make sense to have that behavior when reusing the connection, no? Thoughts?

Thanks folks (=

[mdesmet]

HI @IceS2, that's not how it supposed to work.

There are unit tests that cover the in memory token cache with cursors over multiple threads reusing the same connection. See https://github.com/trinodb/trino-python-client/blob/aee6064c53536da6a73d0f2fa387eb2dfb222600/tests/unit/test_client.py#L546-L595

Can you create a new issue and provide reproduction steps?

[IceS2]

Thanks for the answer @mdesmet and sorry for Hijacking the thread!

I'll create some boilerplate code, add the context information and create a new issue soon!!