How to enable Azure Active Directory Authentication for Azure Database for PostgreSQL flexible server

Question

How to enable Azure Active Directory Authentication for Azure Database for PostgreSQL flexible server

Naveen Kumar Kakarla (Quadrant Resource) 201

How to enable Azure Active Directory Authentication for Azure Database for PostgreSQL flexible server?

Answer accepted by question author

1 additional answer

Your answer

Answer 1

GeethaThatipatri-MSFT 29,587 Microsoft Employee Moderator

Hi @Naveen Kumar Kakarla (Quadrant Resource)

Here is the document to configure Azure Postgres with Entra ID

https://learn.microsoft.com/en-us/azure/postgresql/flexible-server/how-to-configure-sign-in-azure-ad-authentication

If you have already had Postgres created, then you can configure to switch to Entra ID by selecting Postgres and Microsoft Entra authentication under security Authentication and Save the changes.

User's image

Regards

Geetha

Joshua Head 0 Reputation points

2024-01-03T18:45:34.12+00:00

@GeethaThatipatri-MSFT Could you be able to point me to a Bicep example the documentation seems to be lacking on how to do this on IaC level?
GeethaThatipatri-MSFT 29,587 Reputation points Microsoft Employee Moderator

2024-01-04T14:56:05.2533333+00:00

@Joshua Head Can you please provide more information on your use case or create a new thread.

Regards

Geetha
Joshua Head 0 Reputation points

2024-01-04T15:30:11.78+00:00

Yea sure,

Currently the documentation isn't very clear on how to do a full entra integrated authentication for the admin including entra app service => db using IaC with bicep.
For starters the https://learn.microsoft.com/en-us/azure/postgresql/flexible-server/how-to-configure-sign-in-azure-ad-authentication
Doesn't describe the becip only the Gui way, I was however able to piece together from a number of different documentation that there is administrator child resource on the DbForPSQL which can be set to a group, so that solves part one of the problem.

But now we are looking to solve the App -> Db using entra.

I first went down the route of service connector but that doesn't work completely because of the vNet.
The other parts of the service connector we can handle (firewall + private endpoint etc) where we are stuck is on the attaching the system-assigned (or user assigned) identity of the app service to the db so it can get an access token and then query the database.

I've found a number of pieces, one of them being after we configured the role assignment relationship, we have to connect to the PostgresSql database (hopefully using the entra admin context) and add a security label (or use the stored proc) to add the identity to the db?

If the thinking is correct, we are lacking examples on how this could be done using the IaC approach.
Do we have to use a deploymentScript? Does MS have an example of what that would look like. (We were confused because some docs use the stored proc others use the SQL text)

This deployment has an extra complexity by the the fact the DB is in a vNet. But we may be able to work around that because the deploymentScript recently got the ability to run a script inside a vNet.

What would be awesome, is to have azure sample that has a bicep of (vNet integrated App service connecting to Azura Database for PostgresSql via a private endpoint using completely entra authentication (App access + admin access)

Our requirement is regulation based so we want to avoid using any static secrets

Answer 2

Go to Azure Portal:
- Open the Azure portal.
Find Your PostgreSQL Flexible Server:
- Locate and select the Azure Database for PostgreSQL flexible server you want to configure.
Configure Authentication:
- In the left menu, navigate to "Settings" and then click "Authentication."
  - Choose "Azure Active Directory" as the authentication method.
Set Azure AD Admin:
- Specify an Azure AD admin for the server. This admin will have superuser privileges on the PostgreSQL server.
  - You can use an existing Azure AD user or create a new one.
Save Changes:
- Save your changes to apply the Azure AD authentication settings.
Restart Server (if needed):
- Depending on the changes, you may need to restart the PostgreSQL server for the new settings to take effect.
Connect Using Azure AD Credentials:
- When connecting to the PostgreSQL server, use your Azure AD credentials for authentication. This typically involves providing the Azure AD username and password.

Or if you prefer using the CLI

Log in to Azure

az login

Set your subscription

az account set --subscription <your-subscription-id>

Set the admin user for Azure AD authentication

az postgres flexible-server update --resource-group <your-resource-group> --name <your-server-name> --admin-user <azure-ad-admin-username>

Enable Azure AD authentication

az postgres flexible-server update --resource-group <your-resource-group> --name <your-server-name> --authentication-mode "AzureADOnly"

Replace placeholders like <your-subscription-id>, <your-resource-group>, <your-server-name>, and <azure-ad-admin-username> with your actual details.

Share via

How to enable Azure Active Directory Authentication for Azure Database for PostgreSQL flexible server

1 additional answer

Log in to Azure

Set your subscription

Set the admin user for Azure AD authentication

Enable Azure AD authentication

Your answer