JDBC client does not authenticate during connection creation

[jitheshtr]

We have a use case where users are required to do 2-Factor-Authentication to connect to the Trino server. This 2FA token for a user is valid only for a minute. We accept the username and password from the user and forward this to JDBC driver while creating the Connection object, but this user credentials are not authenticated immediately today - username and password are accepted, but will be authenticated at server side when a query is issued by the user. In applications, there typically will be a delay between creating the connection (when the user opens the app), and issuing the query (for eg, user may take some time to compose the query) and by this time the saved password that includes 2FA token will become invalid and an authentication error is thrown to the user in this case.

The problem we have is this delay in authentication at the server side. Could Trino start authenticating user credentials as part of creating the Connection object? These user credentials need to be validated anyway for the user to query Trino, so why do we delay it?

[kokosing]

Is 2FA token part of password in your case?

[jitheshtr]

yes, 2FA is added to the password input

[jitheshtr]

Mentioning couple of options discussed -

Option 1: One option is to add an endpoint specifically to authenticate the user - say “/v1/authenticate”. This should authenticate user credentials and report back with any authentication errors. JDBC connection creation workflow will authenticate using this endpoint. So, instead of lazily authenticating on the first query, now we will start authenticating as part of connection creation.

Option 2: Another option is to submit a dummy query while creating the connection (eg: “select 1”) to force authentication. But this will go through the full query processing workflow which may not be required/desirable for authentication use case (for eg, may show this as a user query in the UI, even though the user explicitly did not send any query).

cc @dain @electrum

[kokosing]

Option 1 sounds like an intuitive thing for user. If users credentials are wrong then one should not a get a Connection object. Current model, when we fail the query sounds like it is too late.

Also notice that also affect CLI.

[jitheshtr]

For CLI, looks like behavior is the same - authentication happens during query time.

Also noticed that for CLI, when a --schema option is provided, CLI issues SHOW FUNCTIONS and SELECT table_name FROM information_schema.tables WHERE table_schema = '<schema>' that is seen in the Trino UI. This is before any user queries are submitted. This triggers the credentials authentication workflow immediately after the CLI prompt is thrown to user. Noticed that if a bad password is provided, the authentication error is displayed back to user only as a result of user issuing first query (that also fails in auth).

[ssainz]

Hello there, we are also having this same issue. Can we submit a select 123; right before returning the connection object to user to test out the username and password? (See https://github.com/trinodb/trino/blob/388/client/trino-jdbc/src/test/java/io/trino/jdbc/TestJdbcExternalAuthentication.java#L137)

[xsgao-github]

Option 1: One option is to add an endpoint specifically to authenticate the user - say “/v1/authenticate”

I think this is a better option.

We also run into a couple of other issues with Cognos and Tableau because TrinoDriver.connect() does not really authenticate users or even verify the connectivity and does not throw SQLException when it should.

[xsgao-github]

Starburst ODBC driver actually uses v1/info endpoint to validate the connection and it makes perfect sense.