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
- PowerShell v7
- Microsoft “Az” PowerShell Modules (make sure you have the latest)
- 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.
- “Password exec command”, this is used to execute a script, to retreive a password.
- “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.
- The script must exit with a code “0”.
- 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.
- On the Connection tab, make sure the Save Password? is not enabled.
- On the Connection tab, don’t enter anything in the Password field.
- On the Advanced, you can set the Password exec command property to something like this
pwsh.exe -file "%USERPROFILE%\Desktop\GetAzADToken.ps1"
. - 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.
Comments