IdP url opened twice

[ivica-k]

Driver version

2.0.882

Redshift version

Not a Redshift problem

Client Operating System

MacOS Big Sur

Python version

3.7.7

Table schema

Not a Redshift problem

Problem description

Connecting to a Redshift cluster with credentials being provided by IdP (Okta in this case) results in Okta login_url page being opened twice if I have already signed in previously. To make it a bit more confusing, when redirected back to http://localhost:7890/redshift/ the page shows "This site can't be reached". Connection is established after that with no issues and I can execute the query.

I've also setup a connection in DBeaver using the same IdP and the same login_url. In this case the login_url page is opened only once and a proper message saying Thank you for using Amazon Redshift! You can now close this window. is shown.

1. Expected behaviour:
   IdP login_url is opened once. After successful login and redirect to http://localhost:7890/redshift/, the page shows the same Thank you for using Amazon Redshift! You can now close this window.

2. Actual behaviour:
   IdP login_url is opened twice. When redirected to http://localhost:7890/redshift/ it shows "This site can't be reached"

3. Error message/stack trace:
   No errors other than "This site can't be reached" in the browser

4. Any other details that can be helpful:
   

Python Driver trace logs

None

Reproduction code

import redshift_connector


conn = redshift_connector.connect(
    iam=True,
    ssl=True,
    host="REDSHIFT_ENDPOINT",
    port=5439,
    database="DB_NAME",
    db_user="IDP_USERNAME",
    region="AWS_REGION",
    cluster_identifier="CLUSTER_NAME",
    login_url="IDP_LOGIN_URL",
    credentials_provider="BrowserSamlCredentialsProvider",
    user="",
    password=""
)

cursor: redshift_connector.Cursor = conn.cursor()
cursor.execute("select current_user")

result = cursor.fetchone()
print(result)

[Brooke-white]

Hi @ivica-k ,

Thank you for reaching out. At this time, redshift_connector is not displaying the "Thank you for using Amazon Redshift! You can now close this window." message following authentication. The team plans to fix this issue as the current behavior is confusing and not inline with our Amazon Redshift JDBC driver.

Connecting to a Redshift cluster with credentials being provided by IdP (Okta in this case) results in Okta login_url page being opened twice if I have already signed in previously.

I will try and reproduce this behavior on my machine and get back to you with my findings. Thank you for including the screen capture. Aside from the additional un-expected re-direct, is the authentication process successful in this case?

[Brooke-white]

HI @ivica-k ,

I was unable to reproduce the multi-redirect behavior you provided in the screen capture. To start off, I want to confirm that a new browser tab being opened for every execution of the included Python script is to be expected.

Here are the steps I followed, please let me know if I missed something.

My machine: Mac OD 10.15.7
My browser: Google Chrome 91.0.4472.114
My Okta login URL: https://${MY_OKTA}.okta.com/app/${MY_SSO}

1. I cleared the cookies for Google Chrome
2. I navigated to https://${MY_OKTA}.okta.com/ and signed in using IDP_USERNAME and my password. This re-directed me to https://${MY_OKTA}.okta.com/UserHome. I left this browser tab open.
3. I invoked the script you provided above and authenticated with Okta

I saw the following behavior when invoking the script.

1. A new browser tab was opened, using IDP_LOGIN_URL
2. Quickly, this browser tab re-directed to http://localhost:7890/redshift/ and displayed the connection refused message. I was not prompted to authenticate with Okta, as the browser cookies were used.
3. Python console indicated the program executed without issue.

Going forward...

1. If you clear your browser's cookies and execute the Python script do you see this issue?
2. If you execute the script again, without clearing your browser's cookies do you see this issue?
3. Could there be some additional re-direct configured in the Okta IdP settings?

Looking at the BrowserSamlCredentialsProvider class, redshift_connector performs actions to:

1. open a web broswer to IDP_LOGIN_URL
2. run a server on localhost

just one time, then waits for the SAML response to be received by this localhost server -- see code here.

[ivica-k]

Hey @Brooke-white and thank you for being so prompt!

I followed your advice, which is:

1. Cleared all cookies
2. Logged into Okta.com and left https://${MY_OKTA}.okta.com/app/UserHome open
3. Invoked the same script I provided in my previous message

Unfortunately this did not help - a new browser tab opened IDP_LOGIN_URL once, displayed the connection refused message, another tab opened the same IDP_LOGIN_URL, displayed the message again and then my script completed and gave the query result.

I was not prompted to authenticate twice - but the IDP_LOGIN_URL was opened twice. After debugging I could clearly see that the def authenticate() was invoked twice.

I've turned on debug logging for that specific plugin and this is what is shown:

2021-07-05 13:39:12,517 - redshift_connector.plugin.browser_saml_credentials_provider - DEBUG - Listen_port=7890
2021-07-05 13:39:12,517 - redshift_connector.plugin.browser_saml_credentials_provider - DEBUG - Login_url=https://${MY_OKTA}.okta.com/app/${APP_ID}/sso/saml
2021-07-05 13:39:12,517 - redshift_connector.plugin.browser_saml_credentials_provider - DEBUG - Idp_response_timeout=120
2021-07-05 13:39:12,806 - redshift_connector.plugin.browser_saml_credentials_provider - DEBUG - Listening for connection on port 7890
2021-07-05 13:39:12,809 - redshift_connector.plugin.browser_saml_credentials_provider - DEBUG - SSO URI: https://${MY_OKTA}.okta.com/app/${APP_ID}/sso/saml
2021-07-05 13:39:14,303 - redshift_connector.plugin.browser_saml_credentials_provider - DEBUG - Data received contained SAMLResponse: True
2021-07-05 13:39:17,256 - redshift_connector.plugin.browser_saml_credentials_provider - DEBUG - Listening for connection on port 7890
2021-07-05 13:39:17,256 - redshift_connector.plugin.browser_saml_credentials_provider - DEBUG - SSO URI: https://${MY_OKTA}.okta.com/app/${APP_ID}/sso/saml
2021-07-05 13:39:18,081 - redshift_connector.plugin.browser_saml_credentials_provider - DEBUG - Data received contained SAMLResponse: True

Authentication works and I can see the query result.

I've also tried using a different (freshly installed) browser and the same thing happens.

[Brooke-white]

Hi @ivica-k ,

Thank you for providing the debug log. BrowserSamlCredentialsProvider utilizes a cache to store the temporary AWS credentials retrieved after authenticating with the identity provider. There are two cases where the SSO URI would be opened twice

• The credentials retrieved from Okta expired before the authentication process could complete
• The cache is misbehaving somehow -- this would cause an additional request for credentials to be made, which would open the Okta login URL again.

I've had the first case occur when debugging the Identity Provider plugin code, but I would not expect this case to typically occur as the authentication process should not take much time :)

As for a misbehaving cache, I'd be interested to learn more about the following...

If a breakpoint is set within SamlCredentialsProvider.get_credentials() on line 57 the expected behavior is that this line will be executed 2 times and both values of key will be the same. See here for how the cache key is generated.

For example:
key = '${USERNAME}${PASSWORD}None443900None'

Additionally, I'd suggest checking the contents of self.cache within this same method. Expect to see the above key value in theSamlCredentialProvider.cache. Exploring the entry for the above key, I can see an entry credentials which is a dictionary. Within that dictionary I see the following:

{
  'AccessKeyId': 'XXXX', 
  'SecretAccessKey': 'XXXX', 
  'SessionToken': 'XXXX', 
  'Expiration': datetime.datetime(2021, 7, 7, 23, 18, 37, tzinfo=tzutc())
}

Are the keys in this credentials dictionary inline with those described above? Following the second execution of Line 57 what is the value of self.cache[key].is_expired()? I would expect this to be False.

There is an entry for Expiration in SamlCredentialProvider.cache as well, it's expected to be inline with the value provided in the credentials dictionary, if present.

[ivica-k]

Thank you for the great explanation, you obviously know this library intimately. In the end, I believe timezones and timezone-aware Expiration field is the problem. As you probably know, AWS clocks are all in UTC, leading to this:

now: 2021-07-09 16:37:23.890818, expiration: 2021-07-09 15:37:18+00:00

My timezone is Europe/Belgrade at the moment and it's 16:38 (4:38 PM). Credentials are "expired" as soon as they are created.

I would love to submit a PR for this but would need a bit of guidance. Do you have a preferred timezone library, should I only use what stdlib offers etc.

:edit:

stdlib to the rescue :) there is time.localtime().tm_gmtoff which when used like time.localtime().tm_gmtoff // 60 // 60 gives the actual offset of 2 hours between my local time and UTC time.

[Brooke-white]

Hi @ivica-k ,

Thank you for taking the time to dive into this issue. I am happy we have been able to determine a root cause for this issue...this also explains why I was unable to reproduce as my timezone is PST :)

The team would be happy to have your contribution for this issue -- the edit you provided looks good!

Back to the "Connection Refused" screen in the browser following authentication, the team has merged a fix for this and it will be included in our next release, which is scheduled for July 19.

[ivica-k]

So, I was able to make it work in my timezone with this simple diff

     def is_expired(self: "CredentialsHolder") -> bool:
-        return datetime.datetime.now() > self.expiration.replace(tzinfo=None)
+        import time
+        utc_offset = time.localtime().tm_gmtoff // 60 // 60
+
+        return (datetime.datetime.now() - datetime.timedelta(hours=utc_time)) > self.expiration.replace(tzinfo=None)

What should happen with self.expiration in this case? Should it be changed based on the local timezone? Or should I maybe add self.expiration_local and change self.expiration to self.expiration_utc ?

:edit:
I'll try to create a PR today and we can discuss the above points there.

[Brooke-white]

Hey @ivica-k ,

Every CredentialsHolder object has an expiration. You can think of the CredentialsHolder class as wrapper around the temporary credentials retrieved when using IAM authentication.

What should happen with self.expiration in this case? Should it be changed based on the local timezone? Or should I maybe add self.expiration_local and change self.expiration to self.expiration_utc ?

Let's chat more in the PR, but I think we can keep expiration as is and do a conversion to the local timezone within is_expired(). We should probably add a logging.debug statement within this method which outputs the original UTC datetime and the localized one. This could be helpful to future debugging surrounding this area of the code base :)

Thanks again for diving into this issue, the team really appreciates your help!