Question 90197 in PostgreSQL Question posted in
The official documentation can be found here.

Postgres with Azure Active Directory Authentication – Postgresql

In our organization, we are having common credentials to access the postgres databases, which every developers know, as it is hardcoded in application’s connection string. Due to which, whenever a DML/DDL changes happens on databases, it is hard for us trace, as the developers use to make changes on their own. We can’t have individual logins for each developers which is tedious to manage.

Note: Also, we can’t ensure that the credentials won’t be shared with the peer developers.

To get rid of this, we thought of integrating Postgres with Azure Active Directory, for Authentication.
If we can map Azure AD group/users to Postgres, security will be tightened as well as maintenance overhead will also reduce.

But, I couldn’t find a article to implement this, since most of the articles says the integration for Azure managed postgresql with Azure AD, and not for the postgres running on VMs.

Can anyone guide me or share a detailed article to implement the Azure AD integration for Postgres running on a VM(IaaS)

Tags:

2

Answers


  1. 0

    In Azure portal go to the postgresql database select Authentication and set active directory admin.

    enter image description here

    You can specify an Azure AD group instead of an individual user to have multiple administrators.

    Connecting to postgresql :

    1.Login to Azure subscription.
    2.Get the access token of the postgresql serverusing below command:

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

    3.Use that token as password for login with postgresql server.

    Creating user

    CREATE USER "[email protected]" IN ROLE azure_ad_user;

    Token validation:

    • Token is signed by Azure AD and has not been tampered with
    • Token was issued by Azure AD for the tenant associated with the server
    • Token has not expired
    • Token is for the Azure Database for PostgreSQL resource (and not another Azure resource)

    Reference Link: Use Azure Active Directory – Azure Database for PostgreSQL – Single Server | Microsoft Learn

    Login or Signup to reply.
  2. 0

    Using Azure Active Directory is a great idea for the reasons you specified, but unfortunately there’s no native support for connection to Azure Active Directory with a local Postgres database (which is essentially what you have with Postgres in a VM). It can be done through the LDAP protocol, however.

    FULL DISCLOSURE: I haven’t actually done this part myself (or used the steps in the tutorial link), but this is my understanding from working with system operators. Use LDAP to connect to Azure AD then Postgres to connect via LDAP. More information on LDAP authentication in Postgres can be found here.

    Bhavani’s answer is about Azure Database for PostgreSQL, which is a Azure-native database service. This part I have used and I highly recommend it; you get Azure AD integration and can manage the database performance and connectivity specifically without having to also manage VM performance. Note that their screenshot is for the Flexible Server while the reference link says ‘Single Server’; I recommend Flexible Server.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search