Re: OAUTH2 Problem with AzureAD

[Asmita Thapliyal]

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.maha...@enterprisedb.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.thapli...@gmail.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.maha...@enterprisedb.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.thapli...@gmail.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.maha...@enterprisedb.com> wrote:

> Hi Asmita,
>
> Here is a patch file which fixes RM7325
>  or you can use snapshot
> build from here
>  to test
> once patch is committed.
>
> Thanks,
> Yogesh Mahajan
> EnterpriseDB
>
>
> On Wed, Apr 20, 2022 at 6:17 PM Asmita Thapliyal <
> asmita.thapli...@gmail.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.maha...@enterprisedb.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
>>> ? This will be
>>> fixed in the next release.
>>>
>>> Thanks,
>>> Yogesh Mahajan
>>> EnterpriseDB
>>>
>>>
>>> On Wed, Apr 20, 2022 at 2:58 PM Khushboo Vashi <
>>> khushboo.va...@enterprisedb.com> wrote:
>>>
 Hi,

 On Tue, Apr 19, 2022 at 11:30 PM Asmita Thapliyal <
 asmita.thapli...@gmail.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': '',
>
> # Oauth secret
>
> 'OAUTH2_CLIENT_SECRET': '',
>
> # URL to generate a token,
>
> # Ex: https://github.com/login/oauth/access_token
>
> 'OAUTH2_TOKEN_URL': 'https://login.microsoftonline.com/
> /oauth2/v2.0/token',
>
> # URL is used for auth

Adding new server in pgadmin from command line

[Asmita Thapliyal]

Hello Team,

I would like to perform server addition activity in pgadmin from the
command line.
I tried to add newserver in pgadmin following below procedure.

Create a migration file with the following command:

(venv) $ FLASK_APP=pgAdmin4.py flask db revision

This will create a file in: $PGADMIN4_SRC/web/migrations/versions/ . Add
any changes to the 'upgrade' function. Increment the SCHEMA_VERSION in
$PGADMIN4_SRC/web/pgadmin/model/init.py file.
Below is the upgrade function command I am using.

db.engine.execute("""
INSERT INTO server (
id, user_id, servergroup_id, name, host, port, maintenance_db,
username, password, ssl_mode
) VALUES ( 1, 1, 1, "stagdb", "", 5454, "mpsdb", "mproot",
"", "prefer" )
""" )
and restart pgadmin.

But during pgdmin restart I get a db migration exception without any error
details. Is this the correct procedure I am following? can we add servers
in pgadmin from the command line instead of UI?

Regards,
Asmita

Re: Adding new server in pgadmin from command line

[Yogesh Mahajan]

Hi Asmita,

Here

are the steps to add servers using command line. Inserting data entries
directly may lead to unexpected behaviour.


Thanks,
Yogesh Mahajan
EnterpriseDB


On Tue, May 3, 2022 at 2:39 PM Asmita Thapliyal 
wrote:

> Hello Team,
>
> I would like to perform server addition activity in pgadmin from the
> command line.
> I tried to add newserver in pgadmin following below procedure.
>
> Create a migration file with the following command:
>
> (venv) $ FLASK_APP=pgAdmin4.py flask db revision
>
> This will create a file in: $PGADMIN4_SRC/web/migrations/versions/ . Add
> any changes to the 'upgrade' function. Increment the SCHEMA_VERSION in
> $PGADMIN4_SRC/web/pgadmin/model/init.py file.
> Below is the upgrade function command I am using.
>
> db.engine.execute("""
> INSERT INTO server (
> id, user_id, servergroup_id, name, host, port, maintenance_db,
> username, password, ssl_mode
> ) VALUES ( 1, 1, 1, "stagdb", "", 5454, "mpsdb", "mproot",
> "", "prefer" )
> """ )
> and restart pgadmin.
>
> But during pgdmin restart I get a db migration exception without any error
> details. Is this the correct procedure I am following? can we add servers
> in pgadmin from the command line instead of UI?
>
> Regards,
> Asmita
>

2 pgadmin4 instances

[David Bear]

I have pgadmin4 running on a remote system which I shell into and forward
the port required. This works great. I forward port 15050 to the remote
system in order to connect to pgadmin on that system.

I also run postgresql locally on my machine. When I started firefox to
connect to the local pgadmin instance running on port 5050 I got the error
that the CSRF tokens didn't match. I assume this is just the way pgadmin
security works when connecting to 2 different running instances with the
same browser.

Fortunately, I can spin up a different browser (in this case opera) and
connect to my local instance of pgadmin.

I just wanted to verify that this was the expected behavior.

-- 

Kind Regards,
* David Bear *


Heritage Academy
32 S Center, Mesa AZ
480-969-5641
db...@heritageacademyaz.com
hamesa.com





This email signature generated by Signite.io

Re: OAUTH2 Problem with AzureAD

[Yogesh Mahajan]

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 
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.maha...@enterprisedb.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.thapli...@gmail.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.maha...@enterprisedb.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.thapli...@gmail.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.maha...@enterprisedb.com> wrote:
>
>> Hi Asmita,
>>
>> Here is a patch file which fixes RM7325
>>  or you can use snapshot
>> build from here
>>  to test
>> once patch is committed.
>>
>> Thanks,
>> Yogesh Mahajan
>> EnterpriseDB
>>
>>
>> On Wed, Apr 20, 2022 at 6:17 PM Asmita Thapliyal <
>> asmita.thapli...@gmail.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.maha...@enterprisedb.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
 ? This will be
 fixed in the next release.

 Thanks,
 Yogesh Mahajan
 EnterpriseDB


 On Wed, Apr 20, 2022 at 2:58 PM Khushboo Vashi <
 khushboo.va...@enterprisedb.com> wrote:

> Hi,
>
> On Tue, Apr 19, 2022 at 11:30 PM Asmita Thapliyal <
> asmita.thapli...@gmail.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': '',
>>
>>