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 <#207 (comment)>, 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

trino-python-client/tests/unit/test_client.py

Lines 546 to 595 in aee6064

	def test_multithreaded_oauth2_authentication_flow(sample_post_response_data):
	redirect_handler = RedirectHandler()
	auth = trino.auth.OAuth2Authentication(redirect_auth_url_handler=redirect_handler)
	token_server = MultithreadedTokenServer(sample_post_response_data)
	class RunningThread(threading.Thread):
	lock = threading.Lock()
	def __init__(self):
	super().__init__()
	self.token = None
	def run(self) -> None:
	request = TrinoRequest(
	host="coordinator",
	port=constants.DEFAULT_TLS_PORT,
	client_session=ClientSession(
	user="test",
	),
	http_scheme=constants.HTTPS,
	auth=auth)
	for i in range(10):
	# apparently HTTPretty in the current version is not thread-safe
	# https://github.com/gabrielfalcao/HTTPretty/issues/209
	with RunningThread.lock:
	response = request.post("select 1")
	self.token = response.request.headers["Authorization"].replace("Bearer ", "")
	threads = [RunningThread(), RunningThread(), RunningThread()]
	# run and join all threads
	for thread in threads:
	thread.start()
	for thread in threads:
	thread.join()
	# should issue only 1 token and each thread should reuse it
	assert len(token_server.tokens) == 1
	for thread in threads:
	assert thread.token in token_server.tokens
	# should start only 1 challenge
	assert len(token_server.challenges.keys()) == 1
	for challenge_id, challenge in token_server.challenges.items():
	assert f"{REDIRECT_RESOURCE}/{challenge_id}" in redirect_handler.redirect_server
	assert challenge.attempts == 0
	assert len(_get_token_requests(challenge_id)) == 1
	# 3 threads * (10 POST /statement each + 1 replied request by authentication)
	assert len(_post_statement_requests()) == 31

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!!