pgadmin not able to authenticate via OAuth2

[andres-chavez-bi]

pgadmin not able to authenticate via OAuth2

[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',