Java non-admin ad user can't login--Azure AD user token for role[******@xxxxx.onmicrosoft.com] does not match auth_oid.

Question

Hi:

Following the quick start guide, Quickstart: Use Java and JDBC with Azure Database for PostgreSQL flexible server, in the link below I was able to use psql to login to PostgreSQL using the AD admin & non-admin users (after login via az login accordingly).

https://learn.microsoft.com/en-us/azure/postgresql/flexible-server/connect-java?tabs=passwordless

However, when I tried to execute the Java code to connect to PostgreSQL, I was only able to successfully execute the code as AD admin but not the non-admin user.

The error was...

Cli execution: mvn exec:java -Dexec.mainClass="com.example.demo.DemoApplication", but failed...

Caused by: org.postgresql.util.PSQLException: FATAL: Azure AD user token for role[******@xxxx.onmicrosoft.com] does not match auth_oid.

I followed the guide to the tee, and I am thinking something is amiss here.

What am I missing here? By the documentation the non-admin AD user should work...I thank you so much for your help!

Best,

~Tim

Answer 1

Hi Lai, Tim

Greetings!

As we understand that, you are running into an issue with Azure AD authentication when trying to connect to PostgreSQL with a non-admin user. The error message "Azure AD user token for role does not match auth_oid" typically indicates that there’s a mismatch between the token generated for the user and the expected user identity for that specific database role.

Here are a few steps you can take to troubleshoot and resolve this issue:

1. Verify User Role in PostgreSQL: Make sure that the non-admin user has been granted the proper permissions for the PostgreSQL database. You can do this by connecting as an admin user and checking the roles assigned to the non-admin user.\d
2. When you run az login in Cloud Shell, the token acquired is tied to the currently signed-in user. If you’re switching between AD admin and non-admin users, make sure you’re logged in as the correct user before running the Java code. You can verify the signed-in user with:

      az account show
   

   If needed, log out and log in as the non-admin user:

      az logout
      az login --username <non-admin-user>
   

3. Cloud Shell may cache credentials. Try restarting the Cloud Shell session or explicitly refreshing the token before running your Java app.
4. Ensure the token your Java app is using was acquired for the correct resource (https://ossrdbms-aad.database.windows.net). If you're using AzureIdentity or DefaultAzureCredential, confirm it's picking up the right identity.
5. Even if the user is in Azure AD, it must also exist in the PostgreSQL database. From psql, run: SELECT * FROM pg_roles WHERE rolname = '<non-admin-user>'; If it doesn’t exist, create it:

       CREATE ROLE "<non-admin-user>" WITH LOGIN;
      GRANT CONNECT ON DATABASE <your-db> TO "<non-admin-user>";
   

6. Ensure the username in the JDBC URL matches the Azure AD user exactly, including domain. For example:
   jdbc:postgresql://<host>:5432/<db>?sslmode=require&user=******@yourtenant.onmicrosoft.com
7. Check Azure AD Group Membership: If your non-admin user is part of an Azure AD group that’s set up to have access to the PostgreSQL, ensure that the group permissions are correctly configured in PostgreSQL.
8. Azure AD Authentication Settings: Confirm that the Azure Database for PostgreSQL instance has Azure AD authentication enabled and that the non-admin user is properly configured in Azure AD with access to the database.
9. Token Scope: Ensure that the token being requested has the correct scope for accessing the Azure Database for PostgreSQL as a non-admin user. When executing the Java application, validate that the token is generated and includes the proper scope.
10. Review Connection Code: Double-check the Java connection code to ensure that it is correctly requesting the Azure AD token for the non-admin user. You can refer to the documentation for guidance on acquiring tokens properly: Quickstart: Use Java and JDBC with Azure Database for PostgreSQL flexible server
11. Cloud Shell Environment: Since you are executing this from Cloud Shell, ensure that the environment has access to the necessary Azure libraries and that you’re correctly authenticated as the non-admin user when attempting to connect.

I hope, This response will address your query and helped you to overcome on your challenges.

If this answers your query, do click Accept Answer and Yes for was this answer helpful. If you still have questions, please let us know what is needed in the comments so the question can be answered.

Thank you for helping to improve Microsoft Q&A!