pgadmin not able to authenticate via OAuth2

[andres-chavez-bi]

Please note that security bugs or issues should be reported to security@pgadmin.org.

Describe the bug

We have an Openshift pod running pgadmin 4 8.7 "server-mode" and the issue we're facing is that while trying to use OAuth2 for authentication, pgadmin seems to not be recognizing the upn claim, the error we get is:

172.25.54.9 - - [22/Aug/2024:15:36:50 +0000] "POST /authenticate/login HTTP/1.1" 302 959 "https://pgadmin-eu-pgtrain-test.apps.eu-dev.ocp.aws.example.com/login?next=%2F" "Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/127.0.0.0 Safari/537.36 Edg/127.0.0.0"
2024-08-22 15:36:55,590: ERROR  pgadmin:    The claim 'upn' is required to login into pgAdmin. Please update your OAuth2 profile.

If we leave the OAUTH2_USERNAME_CLAIM to "None", the user is able to login correctly - the ones that have Email in their profile.

We have some accounts in Azure AD that do not have Email in their profile therefore we need the upn which is correctly set up in all account profiles 100%.

We've also tested using OAUTH2_ADDITIONAL_CLAIMS parameter, but same error is produced.

All claims have been created in the Application Token Configuration in Azure as follows:

upn - ID - Default upn - Access - Default

To Reproduce

This is our OAuth config:

OAUTH2_CONFIG = [ {

The name of the of the oauth provider, ex: github, google

    'OAUTH2_NAME': 'Azure',
    # The display name, ex: Google
    'OAUTH2_DISPLAY_NAME': 'Azure AD',
    # Oauth client id
    'OAUTH2_CLIENT_ID': '{CLIENT_ID}',
    # Oauth secret
    'OAUTH2_CLIENT_SECRET': '{SECRET}',
    # URL to generate a token,
    # Ex: https://github.com/login/oauth/access_token
    'OAUTH2_TOKEN_URL': 'https://login.microsoftonline.com/{TENANT_ID}/oauth2/v2.0/token',
    # URL is used for authentication,
    # Ex: https://github.com/login/oauth/authorize
    'OAUTH2_AUTHORIZATION_URL': 'https://login.microsoftonline.com/{TENANT_ID}/oauth2/v2.0/authorize',
    # server metadata url might optional for your provider
    'OAUTH2_SERVER_METADATA_URL': 'https://login.microsoftonline.com/{TENANT_ID}/v2.0/.well-known/openid-configuration',
    # Oauth base url, ex: https://api.github.com/
    'OAUTH2_API_BASE_URL': 'https://graph.microsoft.com/',
    # Name of the Endpoint, ex: user
    'OAUTH2_USERINFO_ENDPOINT': 'oidc/userinfo',
    # Oauth scope, ex: 'openid email profile'
    # Note that an 'email' claim is required in the resulting profile
    'OAUTH2_SCOPE': 'openid email profile User.Read',
    # The claim which is used for the username. If the value is empty the
    # email is used as username, but if a value is provided,
    # the claim has to exist.
    'OAUTH2_USERNAME_CLAIM': 'upn',
    # Font-awesome icon, ex: fa-github
    'OAUTH2_ICON': None,
    # UI button colour, ex: #0000ff
    'OAUTH2_BUTTON_COLOR': '#0000ff',
}

]

Expected behavior

The user trying to access browser (server mode) pgadmin4 should be able to log in authentincatin via OAuth2 (Azure AD)

Error message

172.25.54.9 - - [22/Aug/2024:15:36:50 +0000] "POST /authenticate/login HTTP/1.1" 302 959 "https://pgadmin-eu-pgtrain-test.apps.eu-dev.ocp.aws.example.com/login?next=%2F" "Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/127.0.0.0 Safari/537.36 Edg/127.0.0.0"
2024-08-22 15:36:55,590: ERROR  pgadmin:    The claim 'upn' is required to login into pgAdmin. Please update your OAuth2 profile.

Screenshots

Here're the permissions in Azure:

Here're the claims:

Desktop (please complete the following information):

• OS: apline (containers in Openshift)
• Version: 8.7
• Mode: Server
• Browser (if running in server mode): Edge
• Package type: Container

Additional context

I'm using helm to deploy the application.

I know there's another issue open regarding OAuth, but I cannot see it being related. I've tried to look into the oauth.py file but I can't see anything that might cause the issue, but please let me know if there's anything I missed (I'm not familiar with python) - I'm attaching the .py file as well. oauth_pgadmin.txt

We have also decoded the payload of the token and the upn value is correctly sent, I removed all IDs and hashes just in case.

{
   "aud": "{CLIENT_ID}",
   "iss": "https://login.microsoftonline.com/{TENANT_ID}/v2.0",
   "iat": 0123456789,
   "nbf": 0123456789,
   "exp": 9876543210,
   "aio": "{HASHED}",
   "groups": [
      "{GROUP_ID}",
      "{GROUP_ID}"
   ],
   "name": "{USER}",
   "nonce": "{HASHED}",
   "oid": "{HASHED}",
   "preferred_username": "user_without_email@example.com",
   "rh": "{HASHED}.",
   "sub": "{HASHED}",
   "tid": "{TENANT_ID}",
   "upn": "user_without_email@example.com",
   "uti": "{HASHED}",
   "ver": "2.0",
   "userpn": "user_without_email@example.com"
}

Please let me know if you need more information.

[adityatoshniwal]

Hi @andres-chavez-bi, Can you please try with preferred_username or userpn to check if they work?

[andres-chavez-bi]

hello @adityatoshniwal the error seems to be the same, now it's complaining about the preferred_username or the userpn (either config is the same error).

172.25.54.9 - - [23/Aug/2024:13:00:22 +0000] "POST /authenticate/login HTTP/1.1" 302 961 "https://bi-pgadmin4-pgtrain-test.apps.eu-dev.ocp.aws.example.com/login?next=/" "Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/127.0.0.0 Safari/537.36 Edg/127.0.0.0"
2024-08-23 13:00:25,361: ERROR  pgadmin:    The claim 'preferred_username' is required to login into pgAdmin. Please update your OAuth2 profile.
NoneType: None
172.25.54.9 - - [23/Aug/2024:13:00:25 +0000] "GET /oauth2/authorize?code=

[yogeshmahajan-1903]

@andres-chavez-bi Can you please try

 OAUTH2_USERINFO_ENDPOINT='me'

Kindly refer this issue from Azure AD.

[andres-chavez-bi]

Hi @yogeshmahajan-1903 I've tried it and now it throws this error:

2024-08-27 14:25:53,585: ERROR pgadmin: 400 Client Error: Bad Request for url: https://graph.microsoft.com/me
Traceback (most recent call last):
File "/venv/lib/python3.12/site-packages/flask/app.py", line 880, in full_dispatch_request
rv = self.dispatch_request()
^^^^^^^^^^^^^^^^^^^^^^^
File "/venv/lib/python3.12/site-packages/flask/app.py", line 865, in dispatch_request
return self.ensure_sync(self.view_functions[rule.endpoint])(**view_args) # type: ignore[no-any-return]
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
File "/pgadmin4/pgadmin/authenticate/oauth2.py", line 57, in oauth_authorize
status, msg = auth_obj.login()
^^^^^^^^^^^^^^^^
File "/pgadmin4/pgadmin/authenticate/__init__.py", line 299, in login
status, msg = self.source.login(self.form)
^^^^^^^^^^^^^^^^^^^^^^^^^^^^
File "/pgadmin4/pgadmin/authenticate/oauth2.py", line 137, in login
profile = self.get_user_profile()
^^^^^^^^^^^^^^^^^^^^^^^
File "/pgadmin4/pgadmin/authenticate/oauth2.py", line 227, in get_user_profile
resp.raise_for_status()
File "/venv/lib/python3.12/site-packages/requests/models.py", line 1024, in raise_for_status
raise HTTPError(http_error_msg, response=self)
requests.exceptions.HTTPError: 400 Client Error: Bad Request for url: https://graph.microsoft.com/me

Looking into the issue you provided and the documentation linked, I see that they mention that this is related to an ID Token, but following pgadmin's authentication process, we see this:

does it make sense to you, shouldn't this response type be token in order to parse the information correctly?

[yogeshmahajan-1903]

@andres-chavez-bi Can you please change base url to

OAUTH2_API_BASE_URL': 'https://graph.microsoft.com/v1.0/

Here is my config -

        'OAUTH2_NAME': "azure",
        'OAUTH2_DISPLAY_NAME': 'MS Azure',
        'OAUTH2_CLIENT_ID': '<client id>',
        'OAUTH2_CLIENT_SECRET': '<client secret>',
        'OAUTH2_TOKEN_URL': 'https://login.microsoftonline.com/<tenant_id>/oauth2/v2.0/token',
        'OAUTH2_AUTHORIZATION_URL': 'https://login.microsoftonline.com/<tenant_id>/oauth2/v2.0/authorize',
        'OAUTH2_SCOPE': 'User.Read email openid profile',
        'OAUTH2_API_BASE_URL': 'https://graph.microsoft.com/v1.0/',
        'OAUTH2_USERINFO_ENDPOINT': 'me',
        'OAUTH2_USERNAME_CLAIM': 'mail',
        'OAUTH2_ICON': 'fa-github',
        'OAUTH2_BUTTON_COLOR': None,
        'OAUTH2_SERVER_METADATA_URL': 'https://login.microsoftonline.com/<tenant_id>/v2.0/.well-known/openid-configuration',

[andres-chavez-bi]

Sorry @yogeshmahajan-1903 but same error:

172.25.23.42 - - [28/Aug/2024:12:37:08 +0000] "GET /oauth2/authorize?code=0.AS8Ahq...
172.25.23.42 - - [28/Aug/2024:12:37:08 +0000] "GET /browser/ HTTP/1.1" 302 229 "https://login.microsoftonline.com/" "Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/128.0.0.0 Safari/537.36 Edg/128.0.0.0"
172.25.23.42 - - [28/Aug/2024:12:37:08 +0000] "GET /login?next=/browser/ HTTP/1.1" 200 2834 "https://login.microsoftonline.com/" "Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/128.0.0.0 Safari/537.36 Edg/128.0.0.0"
172.25.23.42 - - [28/Aug/2024:12:37:08 +0000] "GET /browser/js/endpoints.js?ver=81100 HTTP/1.1" 200 16363 "https://bi-pgadmin4-pgtrain-test.apps.eu-dev.ocp.aws.example.com/login?next=/browser/" "Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/128.0.0.0 Safari/537.36 Edg/128.0.0.0"
172.25.23.42 - - [28/Aug/2024:12:37:08 +0000] "GET /tools/translations.js?ver=81100 HTTP/1.1" 200 321 "https://bi-pgadmin4-pgtrain-test.apps.eu-dev.ocp.aws.example.com/login?next=/browser/" "Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/128.0.0.0 Safari/537.36 Edg/128.0.0.0"
172.25.23.42 - - [28/Aug/2024:12:37:10 +0000] "POST /authenticate/login HTTP/1.1" 302 961 "https://bi-pgadmin4-pgtrain-test.apps.eu-dev.ocp.aws.example.com/login?next=/browser/" "Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/128.0.0.0 Safari/537.36 Edg/128.0.0.0"
2024-08-28 12:37:56,408: ERROR  pgadmin:    The claim 'preferred_username' is required to login into pgAdmin. Please update your OAuth2 profile.
NoneType: None
172.25.23.42 - - [28/Aug/2024:12:37:56 +0000] "GET /oauth2/authorize?code=0.AS8Ahq_44.....

My claims:

My config:

        'OAUTH2_NAME': 'Azure',
        'OAUTH2_DISPLAY_NAME': 'Azure AD',
        'OAUTH2_CLIENT_ID': 'nnnn',
        'OAUTH2_CLIENT_SECRET': 'nnnn',
        'OAUTH2_TOKEN_URL': 'https://login.microsoftonline.com/nnnn/oauth2/v2.0/token',
        'OAUTH2_AUTHORIZATION_URL': 'https://login.microsoftonline.com/nnnn/oauth2/v2.0/authorize',
        'OAUTH2_SERVER_METADATA_URL': 'https://login.microsoftonline.com/nnnn/v2.0/.well-known/openid-configuration',
        'OAUTH2_API_BASE_URL': 'https://graph.microsoft.com/v1.0/',
        'OAUTH2_USERINFO_ENDPOINT': 'me',
        'OAUTH2_SCOPE': 'User.Read openid profile email',
        'OAUTH2_USERNAME_CLAIM': 'preferred_username',
        'OAUTH2_ICON': None,
        'OAUTH2_BUTTON_COLOR': '#0000ff',

[yogeshmahajan-1903]

@andres-chavez-bi With above config, I do not see any obvious reason other than profile does not have said claim. May can we connect over the call between 10 AM - 7 PM IST?

Also can you please login to pgadmin with admin user (created while continuer start) and check oAuth2 config in About > Help? I could reproduce the error only if

'OAUTH2_API_BASE_URL': 'https://graph.microsoft.com/',
'OAUTH2_USERINFO_ENDPOINT': 'oidc/userinfo',

With above config, profile returned is

 {'sub': 'IM-DnKx7Ba2aRdc.....', 'name': '<name>', 'picture': 'https://graph.microsoft.com/v1.0/me/photo/$value', 'email': '<email>'}

Default OAUTH2_USERNAME_CLAIM is email hence, it works with OAUTH2_USERNAME_CLAIM=None

[andres-chavez-bi]

Hello @yogeshmahajan-1903 Yes, that's our current config, so users with Email in their profile can connect, but if you have a user without an Email, and try to use another OAUTH2_USERNAME_CLAIM then my errors are the ones on this issue.

We can definately connect, here's my availability https://outlook.office.com/bookwithme/user/39dd21536c1d4c86aae1f55f529c117e@boehringer-ingelheim.com?anonymous&ep=plink

Feel free to book the date and time you need.

[yogeshmahajan-1903]

@andres-chavez-bi Can you please pull today's snapshot image 'dpage/pgadmin4:snapshot' with PGADMIN_CONFIG_CONSOLE_LOG_LEVEL: 10 and try once? You should see value for profile logged in the pgadmin container logs something as below -

profile : {'sub': 'IM-**************', 'name': '<name>', 'picture': 'https://graph.microsoft.com/v1.0/me/photo/$value', 'email': '<email>'}

And if login is successful then in the logs you should be able to see -

Found username claim in .....

[yogeshmahajan-1903]

This is working fine.