pgadmin not able to authenticate via OAuth2 #7839

ghost · 2024-08-23T09:26:36Z

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.

The text was updated successfully, but these errors were encountered:

adityatoshniwal · 2024-08-23T11:03:49Z

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

ghost · 2024-08-23T13:01:58Z

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 · 2024-08-27T11:01:06Z

@andres-chavez-bi
Can you please try

 OAUTH2_USERINFO_ENDPOINT='me'

Kindly refer this issue from Azure AD.

ghost · 2024-08-27T14:39:34Z

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 · 2024-08-28T03:50:57Z

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

ghost · 2024-08-28T12:39:31Z

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 · 2024-08-29T09:44:53Z

@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

ghost · 2024-08-30T07:29:22Z

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 · 2024-09-05T03:59:36Z

@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 · 2024-09-23T08:42:54Z

This is working fine.

ghost added the Bug label Aug 23, 2024

akshay-joshi added this to Not Refined Aug 23, 2024

adityatoshniwal assigned ghost Aug 23, 2024

ghost removed their assignment Aug 23, 2024

RohitBhati8269 assigned yogeshmahajan-1903 Aug 30, 2024

RohitBhati8269 added this to Current Sprint (180) Aug 30, 2024

RohitBhati8269 removed this from Not Refined Aug 30, 2024

yogeshmahajan-1903 added a commit to yogeshmahajan-1903/pgadmin4 that referenced this issue Sep 10, 2024

Support OIDC protocol authentication in OAuth2.pgadmin-org#7839

36e55f5

akshay-joshi added the EDB Sprint 177 label Sep 11, 2024

yogeshmahajan-1903 added a commit to yogeshmahajan-1903/pgadmin4 that referenced this issue Sep 11, 2024

Support OIDC protocol authentication in OAuth2.pgadmin-org#7839

9265628

yogeshmahajan-1903 added a commit to yogeshmahajan-1903/pgadmin4 that referenced this issue Sep 11, 2024

Support OIDC protocol authentication in OAuth2.pgadmin-org#7839

2981f82

khushboovashi pushed a commit that referenced this issue Sep 16, 2024

Support OIDC in OAuth2 authentication. #7839

dd6f6cf

akshay-joshi assigned khushboovashi and unassigned yogeshmahajan-1903 Sep 16, 2024

yogeshmahajan-1903 closed this as completed Sep 23, 2024

dasunsrule32 mentioned this issue Sep 23, 2024

Login issues in 8.12 #7965

Closed

akshay-joshi removed this from Current Sprint (180) Sep 25, 2024

Provide feedback

Saved searches

Use saved searches to filter your results more quickly

pgadmin not able to authenticate via OAuth2 #7839

pgadmin not able to authenticate via OAuth2 #7839

ghost commented Aug 23, 2024

adityatoshniwal commented Aug 23, 2024

ghost commented Aug 23, 2024

yogeshmahajan-1903 commented Aug 27, 2024 •

edited

Loading

ghost commented Aug 27, 2024 •

edited by ghost

Loading

yogeshmahajan-1903 commented Aug 28, 2024

ghost commented Aug 28, 2024 •

edited by ghost

Loading

yogeshmahajan-1903 commented Aug 29, 2024 •

edited

Loading

ghost commented Aug 30, 2024

yogeshmahajan-1903 commented Sep 5, 2024

yogeshmahajan-1903 commented Sep 23, 2024

pgadmin not able to authenticate via OAuth2 #7839

pgadmin not able to authenticate via OAuth2 #7839

Comments

ghost commented Aug 23, 2024

adityatoshniwal commented Aug 23, 2024

ghost commented Aug 23, 2024

yogeshmahajan-1903 commented Aug 27, 2024 • edited Loading

ghost commented Aug 27, 2024 • edited by ghost Loading

yogeshmahajan-1903 commented Aug 28, 2024

ghost commented Aug 28, 2024 • edited by ghost Loading

yogeshmahajan-1903 commented Aug 29, 2024 • edited Loading

ghost commented Aug 30, 2024

yogeshmahajan-1903 commented Sep 5, 2024

yogeshmahajan-1903 commented Sep 23, 2024

yogeshmahajan-1903 commented Aug 27, 2024 •

edited

Loading

ghost commented Aug 27, 2024 •

edited by ghost

Loading

ghost commented Aug 28, 2024 •

edited by ghost

Loading

yogeshmahajan-1903 commented Aug 29, 2024 •

edited

Loading