Introduction

Microsoft recently released Azure Active Directory (AzureAD) authentication support for Azure PostgreSQL Flexible Server (AzPG). I’m a huge proponent of leveraging AzureAD whenever possible, over local accounts, keys, etc. There all sorts of pros to this approach, none of which I’m going to dive into.

What we will dive into, is the fact that PGAdmin, the most popular GUI for PostgreSQL, doesn’t natively support Azure AD authentication, and how I worked around that. It’s not that you can’t login to AzPG with AzureAD. Rather the issue is that the token expires after 60 minutes (max), and you have to reconnect to the server. This is frustrating when dealing with a single server, and unusable when dealing with many.

Recently, I was pressed to come up with some solution, because Azure Data Studio wasn’t cutting it. Despite it’s native support for renewing tokens automatically, it completely lacked the GUI completeness found in PGAdmin. The good news is that the PGAdmin developers released some enhancements to the connection properties and I’m going to show you how to leverage that feature for automatic token refresh.

Prerequisites

  1. PowerShell v7
  2. Microsoft “Az” PowerShell Modules (make sure you have the latest)
  3. PGAdmin 4.7.3 or later

How it logically works

When you setup a new connection in PGAdmin, there is an Advanced tab. In that Advanced tab there are two relevant properties.

  1. “Password exec command”, this is used to execute a script, to retreive a password.
  2. “Password exec expiration”, this is used to set a maximum time that that password is used, before the Password exec command is run again.

You can read in more detail about the above properties (and others) here.

I’m sure you can see where this is going. We’re going to utilize a PowerShell script to grab a token, and then with that same PowerShell script, continue to refresh the token. Now, if you’re more of an AzCLI person, feel free to adapt this functionality accordingly.

Simplified Sequence Diagram

The below diagram, shows the various steps taken. This showing what happens, during the first connection, but the same steps are repeated once the password reaches the Password exec expiration time.


sequenceDiagram
    autonumber
    actor Human
    Human->>PGAdmin: Connect to PostgreSQL
    PGAdmin->>PowerShellScript: Launch PowerShell Script
    alt is NOT authenticated
        PowerShellScript ->> WebBrowser: Launch Login Page
        Human ->> WebBrowser: Login        
    end
    PowerShellScript ->> AzureAD: Get Token
    PowerShellScript ->> PGAdmin: Return Token as password
    PGAdmin ->> PostgreSQL: Send login token
    PostgreSQL ->> AzureAD: Login with token
    alt token IS valid
        PostgreSQL ->>Human: Connected!
    else token is NOT valid
        PostgreSQL ->>Human: Login Failed!
    end

The PowerShell Script

The script its self, at least in our case, is actually really simple. Here is how ours looks.

If ($null -eq (Get-AzContext)) {
    # User Account
    Connect-AzAccount -WarningAction SilentlyContinue | Out-Null
}
 
$AzAccessTokenSplat = $null
$AzAccessTokenSplat = @{
    ResourceUrl = "https://ossrdbms-aad.database.windows.net"
}
  
$AzAccessToken = $null
$AzAccessToken = Get-AzAccessToken @AzAccessTokenSplat
  
$AzAccessToken.Token

The critical things to keep in mind, no matter what method you use to generate the token.

  1. The script must exit with a code “0”.
  2. The script must only return the token. No other text can be present in the stdout.

Configuring PGAdmin Connection

Now that we have a script, there are only a few things to keep in mind with your PGAdmin connection properties.

  1. On the Connection tab, make sure the Save Password? is not enabled.
  2. On the Connection tab, don’t enter anything in the Password field.
  3. On the Advanced, you can set the Password exec command property to something like this pwsh.exe -file "%USERPROFILE%\Desktop\GetAzADToken.ps1".
  4. On the Advanced tab, you can set the Password exec expiration field to 3480, which is a few minutes less than an hour.

Conclusion

That’s all there is to it. At this point, you should now have PGAdmin automatically refreshing the token for you.