How to Configure OAuth 2.0 with Azure AD in pgAdmin4

I struggled for few days to get OAuth 2.0 configuration works for Azure AD in pgAdmin4, which leads me to writing this blog post.

We were looking for a hosted web interface for running SELECT queries in AWS Aurora database from on premise environment. This interface would allow developers for database interaction and to perform initial troubleshooting without sharing the database credentials and whitelisting the connection from their laptops and vms.

Initially I started writing an API interface and used dynamically generated sql queries as per user input, but this approach couldn’t provide flexibility of writing varieties of queries with preventive measures associated with db interaction libraries for SQL injection attacks.

Then I started looking existing database management tools, pgAdmin was one of them. I decided to use OAuth2.0 Azure AD authentication for it, and here are the steps for configuring it.

1. Register pgAdmin app in Azure AD, Please check details here. https://docs.microsoft.com/en-us/azure/active-directory/develop/quickstart-register-app
2. The redirect url to configure for the app in Azure AD is: https://<pgAdmin Server URL>/oauth2/authorize
3. Add a client secret. In the Azure portal, in App registrations, select your application, select Certificates & secrets > Client secrets > New client secret.
4. Configure Oauth2 as authentication source in config_local.py/config_system.py (https://www.pgadmin.org/docs/pgadmin4/6.8/config_py.html) on system where pgAdmin is installed in server mode.

AUTHENTICATION_SOURCES = [‘oauth2’]

Multiple authentication sources can also be used together.

AUTHENTICATION_SOURCES = [‘oauth2’ , ‘internal’]

5. OAuth provider settings for Azure AD, Set the following parameters.

‘OAUTH2_NAME’: The name of the of the oauth provider, ex: azure

‘OAUTH2_DISPLAY_NAME’: The display name, ex: ‘MS Azure’

‘OAUTH2_CLIENT_ID’: <Client ID> from Azure AD app registration

‘OAUTH2_CLIENT_SECRET’: <Secret> from Azure AD app registration

‘OAUTH2_TOKEN_URL’: ‘https://login.microsoftonline.com/<Tenant-ID from Azure AD>/oauth2/v2.0/token ( URL to generate a token )

‘OAUTH2_AUTHORIZATION_URL’: ‘https://login.microsoftonline.com/<Tenant-ID from Azure AD>/oauth2/v2.0/authorize’, ( URL is used for authentication)

‘OAUTH2_API_BASE_URL’: ‘https://graph.microsoft.com/v1.0/', ( Oauth base url )

‘OAUTH2_USERINFO_ENDPOINT’: ‘me’, ( Name of endpoint )

‘OAUTH2_SCOPE’: ‘User.Read email openid profile’, ( Oauth scope )

‘OAUTH2_ICON’: Font-awesome icon, ex: fa-github

After successful configuration you would get the login screen as below.

When click on Login with MS Azure button, the user will be redirected to AzureAD authentication page.

When a user clicks on the Login with MS Azure button , then it would be redirected to the Azure AD authentication page.