Azure PostgreSQL Flexible Server and Entra Groups

Source: https://learn.microsoft.com/en-us/azure/postgresql/flexible-server/overview

My company is moving from an Azure PostgreSQL single server to a flexible server. We want to set up managed identity/Entra access instead of having roles with passwords for every person/app. In particular, we want to use Entra groups.

Table of Contents

What is Azure PostgreSQL Flex?

It is “a fully managed database service designed to provide more granular control and flexibility over database management functions and configuration settings.” You can learn more about how to set it up here and more about its features here.

As of March 2025, single server is going away, so anything you have on that must move off. For a comparison of features, visit this link. You get a lot more with flex. Newer PG versions, PgBouncer, more connections, burstable, stop/start, higher max storage, and major version upgrades are some of the more exciting options.

Plus, with Flex, you get more options for Entra authentication, such as multiple Entra admins, logging of Entra logins, and full support for managed identities. For more details, visit this link.

Setting up a group

I will set up a group via the Azure portal and add some Entra users to it so I can set that group as the Entra admin on my PG Flex server.

I created this group with a couple of Entra users. For guidance on how to do this, visit this link.

I need to add it to my PG Flex server as an Entra admin. I did this by navigating to my PG Flex server and adding that group under Security -> Authentication -> Microsoft Entra Admins.

Connecting as an Entra admin

This is where you might think Azure Data Studio is seamless. It’s not hard, but it’s not apparent. You will need a token.

Source: https://learn.microsoft.com/en-us/azure/postgresql/flexible-server/how-to-configure-sign-in-azure-ad-authentication#connect-to-azure-database-for-postgresql-by-using-microsoft-entra-id

The link above also helps you if you want to connect via a different client. Here is more information on connecting with managed identities.

Getting your token

Using the Azure Cloud Shell, I got a token with this code:

az account get-access-token --resource https://ossrdbms-aad.database.windows.net

Note: If you use something other than cloud shell, you must first execute az login.

That az account get-access-token will provide you with a token you will use as your password.

Logging in with Azure Data Studio

You need your server name, group name, and token, which you put in ADS using an authentication type password. Your group name is your user name, and your token is your password.

Now, you are connected as the admin if you are in the group you set as the Entra admin above.

Token issues

Note: You have to refresh the token periodically. This is not super optimal for easy connecting in Azure Data Studio, so we will probably add individuals as Entra admins until the authentication is a lot easier with groups in ADS.

As an individual Entra admin, you can log in using Azure Active Directory authentication and forego the token process.

One thing to note: I’m having an issue with Azure Active Directory auth in ADS on Windows, as documented here. I’ve had to delete the files each time I close out of ADS and go back in to reconnect.

Source: https://github.com/microsoft/azuredatastudio/issues/25671

Adding another group to PG Flex

You will invariably have apps and users that may need to connect with non-elevated perms. To add an Azure AD group to an Azure PostgreSQL Flexible Server, you need to be logged in as Entra admin for the flexible server. Note: You can’t be logged in as a group member Entra admin, but only as an individual admin.

Run this query first:

select * from  pgaadauth_create_principal('your-ad-group-name', false, false).

Then grant the rest of the perms to it after that. Side note: don’t put stuff in the public schema.

GRANT CONNECT ON DATABASE your_database TO "your-ad-group-name";
GRANT USAGE ON SCHEMA your_schema TO "your-ad-group-name";
GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA your_schema TO "your-ad-group-name";

Replace "your-ad-group-name" with the name of your Azure AD group and your_database and your_schema with the appropriate values. Modify the role with the perms your role needs, always choosing the least privileges.

The same thing applies to authenticating with this group as the Entra admin:

Password auth
Use the group name for the user name
Get a token for the password

I’ll be really honest here. I’m not super jazzed with the ease of use with Entra groups or even some of the issues with ADS and Entra user logins, but I’m hoping improvements will be made soon.

The post Azure PostgreSQL Flexible Server and Entra Groups appeared first on sqlkitty.