I’m a big fan of using cloud services if you are going to use open source databases like PostgreSQL or MySQL. The cloud services abstract away a lot of the messiness around high availability and backups that are commonly associated with, well frankly clustering on Linux. (I’ve built some really nice MySQL clusters on Windows […]
I’m a big fan of using cloud services if you are going to use open source databases like PostgreSQL or MySQL. The cloud services abstract away a lot of the messiness around high availability and backups that are commonly associated with, well frankly clustering on Linux. (I’ve built some really nice MySQL clusters on Windows Server Failover Clusters, believe it or not). They also have some value added features that you can’t easily get running your own solutions–in the case of Azure, that would be the query store and Entra authentication (amongst other features like AI connectivity).
Postgres 18 adds built-in support for OAuth, but the experience can still be a little rough around the edges. As I’ve mentioned here in the past my current project runs on Amazon RDS, and while we do use IAM auth, getting it up and running was a couple of days of work, particularly around making Oauth work with SQLAlchemy, the ORM we are using on the project. What made that harder, was that we couldn’t use OAuth in our local dev environments, so all of the code I wrote had to be conditional based on whether it was running in a cloud or not (thank you https://169.254.169.254).
Entra (the artist formerly known as Azure Active Directory) authentication for databases has come a long way. I remember in Azure SQL Database, when it first launched, it was an absolutely ordeal to configure, which I somewhat appreciate as it forced me to learn a lot of intricacies of the authentication service. Azure PostgreSQL similarly had a multi-step process. Fortunately, things have improved for the better and enabling Entra auth is simply clicking a radio button in the Azure portal, or a flag in your Terraform/Bicep/PowerShell code.
One of the limitations of Azure PostgreSQL’s Entra integration was group login. The login process for members of a group required the user to user the group name as their login id, and get a bearer token which was used as the password.
One logged in, the user was shown in Postgres system views as the group name.
As you can imagine, in firms that have lots of regulations and auditors, this could problematic. Well this week, Microsoft fixed this problem–there is a new server parameter for your Azure PostgreSQL servers, called pgaadauth.enable_group_sync.
After enabling this parameter, you can wait 30 minutes, or call the function it uses
SELECT * FROM pgaadauth_sync_roles_for_group_members();
And your group membership will be synced with your PostgreSQL server. The docs on this are still a bit of a work in progress. They are here–but let me give you a quick walk through because I was confused.
- The only real change to the login process is that instead of using the group name as your login (like above where I used PG_DBA), you are using your EntraID that is the member of the group.
- You still need to authenticate to Azure/Entra using your favorite CLI, and get the bearer token value to use as a password.
Now that I’ve logged in as a group member, I can see that I’m logged in as [email protected] who only has access through the PG_DBA group.
This is big improvement–while using Oauth based authentication to Postgres still isn’t as easy as SQL Server, we now have similar levels of audibility, which is a huge help, even to a non-regulated organization.
