Enable Entra ID authentication for PostgreSQL servers in pgAdmin

[skel0]

Using Microsoft Azure Database for PostgreSQL flexible server. It is possible to use pgAdmin to connect to the databases providing either an user name or group name in Entra ID and the token of the user. It is explained here:

https://learn.microsoft.com/en-us/azure/postgresql/flexible-server/security-entra-configure

This only works with pgAdmin running in desktop mode. It is still possible to connect to the databases using pgAdmin in server mode but the problem is the token expires frequently and makes this almost unusable.

The feature requested is to make pgAdmin have one more authentication method (Entra ID) and then it takes care of the tokens and keep the connection to PosgreSQL flexible server opened.

[adityatoshniwal]

@skel0 Did you try password exec command - https://ericcsinger.com/pgadmin-azure-ad-authentication-token-renewal/?

[skel0]

Hi @adityatoshniwal, thanks for the answer! I think this only works when pgAdmin is running in desktop mode on top a Windows OS. In my case it is pgAdmin running in server mode on a container. It is true that since few versions back, now it is possible to edit password exec command in server mode, earlier it was even not possible to edit it in server mode. In any case, the user is authenticated in the browser of the client, not in the pgadmin server, and the script runs on the server, so I think is not possible to do a token renewal in that situation using the password exec command. Don't you think?

[adityatoshniwal]

Hi @skel0,
I don't think it is possible to generate the token just by using the browser. I'm not aware of any API other than az cli, which cannot be run from a browser.
I'm closing this. If you have a way to get this done then feel free to reopen.
Thanks.

[skel0]

Hi @adityatoshniwal,

As requested, I am providing an example of how to implement this feature using python. Below the instructions and the script. I hope this helps. I would love to have this feature built into pgAdmin to transparently login to databases using Entra ID. Following steps could be extra functionality to make pgadmin manage the creation of users and roles from Entra ID. I have some suggestions but maybe that can be later :)

Should you have further questions please let me know.

1. Description of sample python script pgaztoken.py:

pgaztoken.py

It shows only the essentials needed: User authentication and OnBehalfOfCredential (OBO) token adquisition, what parameters are required by the library (credential, scope), and how the UPN is passed.
A real database listing using psycopg with the AAD token as the password.

It includes a token endpoint (/gettoken) so you can validate the loging using a CURL-based Password Exec flow quickly.
These are the endpoints descriptions:

GET /login : Starts the OIDC login to obtain an access token for your API (the user_assertion for OBO).
GET /callback : Stores api_access_token and user UPN in the session.
GET /connectdb : OBO => PostgreSQL token => connects with psycopg => lists databases.
GET /gettoken?upn=... : Returns a fresh PostgreSQL AAD token (designed for pgAdmin’s Password Exec Command).

How OBO flow works in the code:

/login requests openid profile email and your API scope api://<client_id>/access_as_user so the returned access token’s audience is your API.

/callback stores that access token as api_access_token (the user_assertion).

OBO: Build OnBehalfOfCredential(user_assertion=<api_access_token>) and call get_entra_token(credential=..., scope=..., server=..., database=..., upn=...). 
The library requires credential and scope for postgres.

2. Python requirements:

Install in a Python 3.10 or later. I used Python 3.11 and these libraries (requirements.txt):

Flask>=3.0.0
requests>=2.31.0
azure-identity>=1.25.0
azure-postgresql-auth>=1.0.1
psycopg[binary]>=3.1 

3. How to run the script:

3.1) Make necessary changes for Azure Entra ID configuration (must-do as pre-requirement)

  3.1.1) Changes needed to "App Registration" (the broker app that runs this script), go to "Manage" subtree and perform the following steps:

      3.1.1.1) Expose an API. 
        Set Application ID URI to api://<client_id> (or your custom URI).
        Add a Delegated scope: access_as_user.


      3.1.1.2) Authentication
        Add Redirect URI (Web): https://<broker-host>:<port>/callback
        Check Access tokens and ID tokens.

      3.1.1.3) API permissions
        3.1.1.3.1) User impersonation in Azure Database:
            Click "Add a permission"
            In "APIs my organization uses" tab
            Search for "Azure OSSRDBMS Database" and click on it
            Delegated Permissions
            user_impersonation
            You need to grant admin consent even it may say it is not required.
        3.1.1.3.2) User.read in Graph:
            Click "Add a permission"
            In "Microsoft APIs" tab
            Search for "Microsoft Graph" and click on it
            Delegated permissions
            Search for "User.Read"
            You need to grant admin consent even it may say it is not required.
        
      3.1.1.4) Certificates&Secrets
            Click on "New client secret"
            Provide description and expiration
            Take note of the secret value after creation since it will not be visible any more.

  3.1.2) Changes needed to "Azure Database for PostgreSQL Flexible server" (target database)

    AAD integration must be enabled: Go to "Security" / "Authentication" then set "Authentication Method" to "Microsoft Entra authentication only" or "PostgreSQL and Microsoft Entra authentication" and save it.
    User’s UPN must be provisioned/authorized. Example below from psql. You can also test by adding the user as Entra Administrators when configuring the autnentication method.
    
      CREATE ROLE "<user@domain>"
      WITH LOGIN
      IN ROLE azure_ad_user
      FROM EXTERNAL PROVIDER;

3.2) Prepare the venv and libraries and run the script in broker-host.
NOTE: For simplicity, IF PGADMIN IS RUNNING AS CONTAINER RUN THE SCRIPT IN THE SAME HOST AS PGADMIN IS RUNNING

  python3.11 -m venv .venv

  source .venv/bin/activate

  pip install --upgrade pip
  pip install -r requirements.txt

  export AZURE_TENANT_ID="..."
  export AZURE_CLIENT_ID="..."
  export AZURE_CLIENT_SECRET="..."
  export BASE_URL="https://<broker-host>:<port>"
  export PG_SERVER_NAME="<server>.postgres.database.azure.com"
  export PG_DATABASE="postgres"
  export TOKEN_API_KEY="<a_long_random_secret>"
  export CERT_FILE="/path/certificate.pem"
  export PRIVKEY_FILE="/path/privatekey.pem"

  python pgaztoken.py

3.3) Using a browser, login by opening below URL. You’ll be redirected to /connectdb and you should see UPN and a database list (from pg_database) as example. Also the application will keep the token in the store and will serve it to pgadmin when login thorugh the /gettoken endpoint.

  https://<broker-host>:<port>/login

3.4) pgAdmin configuration to make it pulls the token transparently from the python script pgaztoken.py

Create a server and configure as needed but keep following settings:

Username: user’s UPN (typically it is the email)

Password Exec Command (replace <host>,<port> and <API_TOKEN> accordingly): 

  wget -qO- --header="X-API-KEY: <API_KEY>" "<host>:<port>/gettoken?upn="%USERNAME%

  NOTE 1: If pgAdmin is running as container, use "host.containers.internal" as <host> to refer to the host where the pgadmin container is running.
  NOTE 2: that if using self-signed certificate add also option "--no-check-certificate" to ignore the certificate validation)

Password Exec Timeout/Refresh: set an interval (e.g., 900 seconds). pgAdmin will re-run the command and replace the password before token expiry.

3.5) Access the database thorugh pgAdmin, it should loging transparently using the token form the broker app. The token will be refreshed automatically.

3.6) You can also get the token from Linux CLI like this:

  wget -qO- --header="X-API-KEY: <API_KEY>" "https://<broker-host>:<port>/gettoken?upn=<user@domain>"
  
  NOTE : that if using self-signed certificate add also option "--no-check-certificate" to ignore the certificate validation)

  OR
  
  curl -s -H "X-API-KEY: <API_KEY>" \  "https://<broker-host>:<port>/gettoken?upn=<user@domain>"; echo

  NOTE : that if using self-signed certificate add also option "-k" to ignore the certificate validation)