Share via

How to authenticate a 3rd party tool via Entra ID with read access over pgsql

Elgaby Mohamed 0 Reputation points
2024-12-11T15:41:41.87+00:00

I am trying to authenticate a 3rd-party tool over Azure database for PostgreSQL - Flexible server using Entra ID only. so I created multiple service principals (access-token based, password-based auth and Certificate-based auth). The access token one, works however it's temporary as it expires and that doesn't fit our business need. while the password and certificate base fails. is this a limitation?

Azure Database for PostgreSQL
Microsoft Entra ID
Microsoft Entra ID
A Microsoft Entra identity service that provides identity management and access control capabilities. Replaces Azure Active Directory.
22,649 questions
0 comments

1 answer

Sort by: Most helpful
Most helpful Newest Oldest
  1. Oury Ba-MSFT 19,831 Reputation points Microsoft Employee
    2024-12-11T19:08:10.65+00:00

    @Elgaby Mohamed Thank you for reaching out.

    You can use Azure Data Studio to eliminate the need of using token to connect to your Azure Database for PostgreSQL Flexible Server. The steps are documented in the below blog for single server and the same steps can be used to connect to your flexible server.

    New, simpler way to sign-in to Azure Database for PostgreSQL - Single Server using Azure AD - Microsoft Community Hub

    Additionally, token expiration limit is currently set to 1 hour, and this can be increased to 24 hours but there are security implications. Adjusting the lifetime of an access token is a trade-off between improving system performance and increasing the amount of time that the client retains access after the user's account is disabled.  Additionally, a malicious actor that has obtained an access token can use it for extent of its lifetime which will be now 24 hours if the new token expiry limit is set to 24 hours compared to previous value of 1 hour.

    This needs to be done by your security /Microsoft Entra team and this will impact token lifetime for all Azure OSS databases (Azure PostgreSQL /Azure MySQL/ Azure MariaDB).

    Retrieve the Microsoft Entra access token.

    while the password and certificate base fail. is this a limitation?

    Azure Database for PostgreSQL flexible server supports managed identities and service principals as group members. This is not a limitation.

    If you created your Azure Database for PostgreSQL flexible server instance with Private access (VNet Integration), you will need to connect to your server from a resource within the same VNet as your server. You can create a virtual machine and add it to the VNet created with your Azure Database for PostgreSQL flexible server instance.

    If you created your Azure Database for PostgreSQL flexible server instance with Public access (allowed IP addresses), you can add your local IP address to the list of firewall rules on your server.

    The following example shows how to connect to your server using the psql command-line interface. Use the sslmode=verify-full connection string setting to enforce TLS/SSL certificate verification. Pass the local certificate file path to the sslrootcert parameter.

     psql "sslmode=verify-full sslrootcert=c:\\ssl\DigiCertGlobalRootCA.crt.pem host=mydemoserver.postgres.database.azure.com dbname=postgres user=myadmin"

    Confirm that the value passed to sslrootcert matches the file path for the certificate you saved.

    Additional helpful docs below.

    Hope that helps, if not please do not hesitate to get back so I can provide you further assistance.

    Regards,

    Oury

Your answer

Answers can be marked as Accepted Answers by the question author, which helps users to know the answer solved the author's problem.