Managed identity in Azure Database for Postgres

Managed identity in Azure Database for Postgres

Follow the below steps which I have tried with:

Step:1

how to set up a PostgreSQL Flexible Server and enable Microsoft Entra authentication.

1. First, go to the Azure Portal.

2. Click on “Create a resource”, then search for “PostgreSQL Flexible Server” and select it.

3. On the basics tab, fill in the server name, choose your subscription, and pick the region where you want the server hosted.

4. Once you're done, click on “Create” and wait for the deployment to finish.

Now that the server is created:

5. Go to your PostgreSQL Flexible Server resource.

6. On the left-hand side, look for and click on “Authentication”.

7. Under the Microsoft Entra authentication section, select the option that says “Microsoft Entra administrator only”.

8. Then click on “+ Add Microsoft Entra administrator”.

9. A pane will pop up—here, search for and select the user you want to set as the Entra administrator.

10. Finally, click “Save” to apply the changes.

Refer the below Image:

Step:2

let’s connect to our PostgreSQL Flexible Server using Microsoft Entra authentication. Here's how to do it step by step:

1. First, head over to the Azure Portal and open the resource for your PostgreSQL Flexible Server.

2. On the Overview page, you'll see a button labeled "Azure CLI" at the top — go ahead and click on that. It’ll open up the Cloud Shell at the bottom of the screen.

3. In the Cloud Shell, we need to generate an access token that will let us authenticate using Microsoft Entra ID. Type in this command:

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

4. After a moment, this command will return a long string of text — that’s your access token. Copy it — we’ll use it as the password for the next step.

5. Now open a terminal on your machine (or any PostgreSQL client that supports Azure AD), and run this command to connect:

psql -h <servername>.postgres.database.azure.com -p 5432 -U <entra_user_from_authentication_blade> postgres

6. Hit Enter — and if everything’s set up correctly, you should now be connected to your PostgreSQL Flexible Server using Microsoft Entra authentication.

Refer the below Image:

Admins

To add Entra ID users/groups as database admin, you can use the Security -> Authentication in Azure Portal (or other IaC method). You can add user, group, service principal or managed identity. This creates new role in the database server and adds it to azure_pg_admin role.

Non-admins

Prerequisite: you need to have at least one Entra ID admin set to enable Entra ID authentication for your PostgreSQL Flexible server. You need to login as that admin.

To create Entra ID user or group as non-admin ROLE, you need to use select * from pgaadauth_create_principal('<object>', false, false).

For Entra ID users use UPN as <object>, for Entra ID groups use group name as <object> and for Managed Identities use it's name as <object>.

This command creates new role. Additionally, you need to grant permissions to the new role.

Note on groups

By default, if you add a group, members of that group need to use Group name to login.

Another option is to enable Group sync setting pgaadauth.enable_group_sync parameter to ON. Members can then use their own usernames to login. Login using group name will be still working as well and it can be disabled. More details available in docs.

Notes

The 2. link in your post describes how to add Managed Identity to the PostgreSQL Flexible server instance itself, if it needs to access other Azure Resources for some reason.