Re: OAUTH2 Problem with AzureAD

Hi Asmita,

Thank you for the blog post.
Blog is live on https://www.pgadmin.org/.

Thanks,
Yogesh Mahajan
EnterpriseDB

On Sat, Apr 30, 2022 at 2:14 PM Asmita Thapliyal <asmita(dot)thapliyal(at)gmail(dot)com>
wrote:

> Hello Yogesh,
>
> Please check the blog post below and let me know if anything else needs to
> be added there.
>
> https://medium.com/@asmita.thapliyal/how-to-configure-oauth-2-0-with-azure-ad-in-pgadmin4-2c1500d52d9d
>
> Regards,
> Asmita
>
>
>
> On Fri, Apr 22, 2022 at 2:29 PM Yogesh Mahajan <
> yogesh(dot)mahajan(at)enterprisedb(dot)com> wrote:
>
>> Hi Asmita,
>>
>> You can write the separate blog 'How to Configure OAuth 2.0 with Azure AD
>> in pgAdmin4' which includes detailed steps about App registration in Azure.
>> You can send a blog over the same mailing list. The Community will
>> publish it on the pgadmin website.
>>
>> Thanks,
>> Yogesh Mahajan
>> EnterpriseDB
>>
>>
>> On Fri, Apr 22, 2022 at 1:09 PM Asmita Thapliyal <
>> asmita(dot)thapliyal(at)gmail(dot)com> wrote:
>>
>>> Hello Yogesh,
>>>
>>> Thanks! I would like to write blog/documentation for configuring Azure
>>> AD OAUTH2 authentication with pgadmin. Please let me know if I could add it
>>> here - https://www.pgadmin.org/blogs/? May be under the post- "How To
>>> Configure OAuth 2.0 in pgAdmin 4" or create a new one.
>>>
>>> Regards,
>>> Asmita
>>>
>>> "
>>>
>>> Regards,
>>> Asmita
>>>
>>> On Thu, Apr 21, 2022 at 1:03 PM Yogesh Mahajan <
>>> yogesh(dot)mahajan(at)enterprisedb(dot)com> wrote:
>>>
>>>> Hi Asmita,
>>>>
>>>> Kindly use the 2nd patch(RM_7325_v2.patch) shared in a later email.
>>>>
>>>> Thanks,
>>>> Yogesh Mahajan
>>>> EnterpriseDB
>>>>
>>>>
>>>> On Thu, Apr 21, 2022 at 11:59 AM Asmita Thapliyal <
>>>> asmita(dot)thapliyal(at)gmail(dot)com> wrote:
>>>>
>>>>> Thanks a lot.
>>>>>
>>>>> I was facing some minor issues with your code. Below lines of code
>>>>> works.
>>>>>
>>>>> email = None
>>>>>
>>>>> if 'email' in profile:
>>>>>
>>>>> email = profile['email']
>>>>>
>>>>> elif 'mail' in profile:
>>>>>
>>>>> email = profile['mail']
>>>>>
>>>>> if email == '':
>>>>>
>>>>> current_app.logger.exception(
>>>>>
>>>>> "An email id is required to login into pgAdmin. "
>>>>>
>>>>> "Please update your Oauth2 profile."
>>>>>
>>>>> )
>>>>>
>>>>> Other than this, the rest is working fine. I am able to authenticate
>>>>> with Azure OATH2.
>>>>>
>>>>>
>>>>> Regards,
>>>>>
>>>>> Asmita
>>>>>
>>>>> On Thu, Apr 21, 2022 at 11:09 AM Yogesh Mahajan <
>>>>> yogesh(dot)mahajan(at)enterprisedb(dot)com> wrote:
>>>>>
>>>>>> Hi Asmita,
>>>>>>
>>>>>> Here is a patch file which fixes RM7325
>>>>>> <https://redmine.postgresql.org/issues/7325> or you can use snapshot
>>>>>> build from here
>>>>>> <https://www.postgresql.org/ftp/pgadmin/pgadmin4/snapshots/> to test
>>>>>> once patch is committed.
>>>>>>
>>>>>> Thanks,
>>>>>> Yogesh Mahajan
>>>>>> EnterpriseDB
>>>>>>
>>>>>>
>>>>>> On Wed, Apr 20, 2022 at 6:17 PM Asmita Thapliyal <
>>>>>> asmita(dot)thapliyal(at)gmail(dot)com> wrote:
>>>>>>
>>>>>>> Hello Yogesh,
>>>>>>>
>>>>>>> Done.
>>>>>>> https://redmine.postgresql.org/issues/7325
>>>>>>>
>>>>>>> Meanwhile, can the below change easily be incorporated in code to
>>>>>>> check if it works? if yes, then could you provide me the details.
>>>>>>>
>>>>>>> Also, the profile returned by Azure AD has the key 'mail' and
>>>>>>> current pgadmin code checks the value with key = 'email' to retrieve user
>>>>>>> email id.
>>>>>>>
>>>>>>> Regards,
>>>>>>> Asmita
>>>>>>>
>>>>>>> On Wed, Apr 20, 2022 at 5:03 PM Yogesh Mahajan <
>>>>>>> yogesh(dot)mahajan(at)enterprisedb(dot)com> wrote:
>>>>>>>
>>>>>>>> Hi,
>>>>>>>>
>>>>>>>> I was able to reproduce the issue.
>>>>>>>> Below change is required in configuration -
>>>>>>>>
>>>>>>>> # Name of the Endpoint, ex: user
>>>>>>>>
>>>>>>>> 'OAUTH2_USERINFO_ENDPOINT': 'me',
>>>>>>>>
>>>>>>>> And
>>>>>>>> Also, the profile returned by Azure AD has the key 'mail' and
>>>>>>>> current pgadmin code checks the value with key = 'email' to retrieve user
>>>>>>>> email id.
>>>>>>>> Could you please raise a feature request to support Azure AD
>>>>>>>> authentication here
>>>>>>>> <https://redmine.postgresql.org/projects/pgadmin4>? This will be
>>>>>>>> fixed in the next release.
>>>>>>>>
>>>>>>>> Thanks,
>>>>>>>> Yogesh Mahajan
>>>>>>>> EnterpriseDB
>>>>>>>>
>>>>>>>>
>>>>>>>> On Wed, Apr 20, 2022 at 2:58 PM Khushboo Vashi <
>>>>>>>> khushboo(dot)vashi(at)enterprisedb(dot)com> wrote:
>>>>>>>>
>>>>>>>>> Hi,
>>>>>>>>>
>>>>>>>>> On Tue, Apr 19, 2022 at 11:30 PM Asmita Thapliyal <
>>>>>>>>> asmita(dot)thapliyal(at)gmail(dot)com> wrote:
>>>>>>>>>
>>>>>>>>>> Hello!
>>>>>>>>>>
>>>>>>>>>> I have configured OAUTH2 with azure ad with below config
>>>>>>>>>>
>>>>>>>>>> 'OAUTH2_NAME': "azure",
>>>>>>>>>>
>>>>>>>>>> # The display name, ex: Google
>>>>>>>>>>
>>>>>>>>>> 'OAUTH2_DISPLAY_NAME': 'MS Azure',
>>>>>>>>>>
>>>>>>>>>> # Oauth client id
>>>>>>>>>>
>>>>>>>>>> 'OAUTH2_CLIENT_ID': '<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',
>>>>>>>>>>
>>>>>>>>>> # Oauth base url, ex: https://api.github.com/
>>>>>>>>>>
>>>>>>>>>> 'OAUTH2_API_BASE_URL': 'https://graph.microsoft.com/v1.0'
>>>>>>>>>> ,
>>>>>>>>>>
>>>>>>>>>> # Name of the Endpoint, ex: user
>>>>>>>>>>
>>>>>>>>>> 'OAUTH2_USERINFO_ENDPOINT': 'profile',
>>>>>>>>>>
>>>>>>>>>> # Oauth scope, ex: 'openid email profile'
>>>>>>>>>>
>>>>>>>>>> # Note that an 'email' claim is required in the
>>>>>>>>>> resulting profile
>>>>>>>>>>
>>>>>>>>>> 'OAUTH2_SCOPE': 'User.Read email openid profile',
>>>>>>>>>>
>>>>>>>>>> # Font-awesome icon, ex: fa-github
>>>>>>>>>>
>>>>>>>>>> 'OAUTH2_ICON': 'fa-github',
>>>>>>>>>>
>>>>>>>>>> # UI button colour, ex: #0000ff
>>>>>>>>>>
>>>>>>>>>> 'OAUTH2_BUTTON_COLOR': None,
>>>>>>>>>>
>>>>>>>>>>
>>>>>>>>>> After testing I found below error
>>>>>>>>>>
>>>>>>>>>>
>>>>>>>>>> requests.exceptions.HTTPError: 400 Client Error: Bad Request for
>>>>>>>>>> url: https://graph.microsoft.com/profile
>>>>>>>>>>
>>>>>>>>>> 2022-04-19 14:34:38,717: INFO werkzeug: 49.37.172.20 - -
>>>>>>>>>> [19/Apr/2022 14:34:38] "*GET
>>>>>>>>>> /oauth2/authorize?code=0.ARsA3jZYM-9CokOxRTSMLunKW_3a3dHcJP5MrfqQQJh5-YcbAAA.AQABAAIAAAD--DLA3VO7QrddgJg7Wevrdrp6mz5VUBPbc2M4Bs4hmaPP7YfekSA8Yt9vmf1zMQFku0U1U1xfWkuaKw8eFQjW9sNkh1-Gl6XaDfqOV6NQ0dAxvBNW5K_GOC9VChtUG_s8DXVKvZ05dvryfX1K-NUgDFoXiSU7Xmyc40UWiIr1fBse7PLdvaFDL4KmUbO4Ivm6j7fuh3l0Q5sB-lMB56NmbV9NCDSoy-ccbnGwm-2pVN42HErVzE9b8P0Gowba3QWfNUvLSmbkbVv_UQHnQ2jgZfNK7oPcggZJojU8biYXJN6KcpOL7eQmP1oUjhUafRJw5TLr5LSSYGHbXVmL7zgJ7RCuWBJAS_VSrYr5hMaRhvxBMLgC6bwQmI8euv_hC9GZ0vmxqNY6T11M72Ye8NkQrA_5zM9qPiFh1bZCsLyllkxN1LCgfEI_t--qiq0N2dd-SL2hE23VUAk5Wen_nwwjJQBKTpuE4v7BwjOwfpPqniNq2xLqALaXaBZfmjmGCjfrVHlw4e5ADsxU0VBY4eH7BiKwye7o8AQdJC7w39Y8VteOJTLvCw5y0hPALIpzlCQtUtBhHjrKpzEPqgpZWfb55JSZ45YtjbZENcXyQk_sdRRo4SqNJxqU5W9yqcyY53PbtfzX0LTRTJ9FAfc5uqlgksyMcxZaXLcONWYocB1oGjaRTBbl7kZFZRScHzKNVQbXsnQVAURe3lesqOzlv--QtfMZHfYPA6igkryni8xPKETI9UyL0mRLTbxHOHZFvt0faNcvM1uCLiavNDgw4EkBljbLDDMIdCLrfOvSaIsMJA6vzRmFODq00iAZYIfqxQcgAA&state=ZmClT3NK4XExYAP8NQrdp5zIMaoM4m&session_state=ee30bdda-dd80-4ab9-aeee-1c61b8ffa63c
>>>>>>>>>> HTTP/1.1*" 500 -
>>>>>>>>>>
>>>>>>>>>> 2022-04-19 14:34:38,722: ERROR werkzeug: Error on request:
>>>>>>>>>>
>>>>>>>>>
>>>>>>>>> Looks like the profile fetch request is giving this error.
>>>>>>>>> Can you please check whether OAUTH2_API_BASE_URL is correct or
>>>>>>>>> not.
>>>>>>>>> Also try, OAUTH2_API_BASE_URL = *https://graph.microsoft.com/v1.0
>>>>>>>>> <https://graph.microsoft.com/v1.0>/ (put / at the end)*
>>>>>>>>>
>>>>>>>>> Thanks,
>>>>>>>>> Khushboo
>>>>>>>>>
>>>>>>>>>
>>>>>>>>>>
>>>>>>>>>> I tried to print response, this is the one, not sure what does it
>>>>>>>>>> mean by Invalid version. I tried to change accesstokenversion in azure ad
>>>>>>>>>> but received same issue.
>>>>>>>>>>
>>>>>>>>>>
>>>>>>>>>> {'error': {'code': 'BadRequest', 'message': 'Invalid version.',
>>>>>>>>>> 'innerError': {'date': '2022-04-19T14:34:38', 'request-id':
>>>>>>>>>> 'c2da3799-bab5-4c38-a485-78cf7b74567c', 'client-request-id':
>>>>>>>>>> 'c2da3799-bab5-4c38
>>>>>>>>>>
>>>>>>>>>> -a485-78cf7b74567c'}}}
>>>>>>>>>>
>>>>>>>>>>
>>>>>>>>>> Any clue? or is there a way I can check more details of
>>>>>>>>>> innerError?
>>>>>>>>>>
>>>>>>>>>>
>>>>>>>>>> Regards,
>>>>>>>>>>
>>>>>>>>>> Asmita
>>>>>>>>>>
>>>>>>>>>>
>>>>>>>>>> Regards,
>>>>>>>>>>
>>>>>>>>>> Asmita
>>>>>>>>>>
>>>>>>>>>