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

@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.

• Encrypted connectivity using TLS/SSL1
• Networking overview using SSL and TLS

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

Regards,

Oury