How to authenticate Azure PostgreSQL with token retrieved from Azure AD with username & password?

Question

I have used the documentation at https://docs.microsoft.com/en-us/azure/postgresql/howto-configure-sign-in-aad-authentication to enable users in Azure AD to authenticate to an Azure PostgreSQL instance using a token. I would like for users of a Java desktop app to be able to authenticate using their username and password and automate getting a token. To this end, I have created a public app in my tenant. I have no trouble authenticating with username and password to get an access token, but the token is apparently not suitable for authenticating against https://ossrdbms-aad.database.windows.net.

I get the error, "FATAL: The access token does not have a valid signature or is expired. Please acquire a new token and retry."

This is the code I am using:

import com.microsoft.aad.msal4j.IAuthenticationResult;
import com.microsoft.aad.msal4j.PublicClientApplication;
import com.microsoft.aad.msal4j.UserNamePasswordParameters;

import java.sql.Connection;
import java.sql.DriverManager;
import java.util.Properties;
import java.util.Set;

public class AuthTest {

    private final static String APP_ID = "096f6bc7-cf30-4b06-9f0e-093aaf3f9a4c";
    private final static String AUTHORITY = "https://login.microsoftonline.com/organizations";

    private static Connection getConnection(final String serverName, final String userName, final String password)
            throws Exception {
        final String url = String.format("jdbc:postgresql://%s.postgres.database.azure.com/postgres", serverName);
        final Properties props = new Properties();
        props.setProperty("user", userName + "@" + serverName);
        props.setProperty("password", password);
        props.setProperty("sslmode", "require");
        return DriverManager.getConnection(url, props);
    }

    public static void main(final String[] args) throws Exception {
        final String serverName = args[0];
        final String userName = args[1];
        final String password = args[2];
        final PublicClientApplication pca = PublicClientApplication.builder(APP_ID).authority(AUTHORITY).build();
        final Set<String> scopes = Set.of(
                "User.Read"
        );
        final UserNamePasswordParameters parameters =
                UserNamePasswordParameters.builder(scopes, userName, password.toCharArray()).build();
        final IAuthenticationResult result = pca.acquireToken(parameters).get();
        final String token = result.accessToken();

        final Connection connection = getConnection(serverName, userName, token);
        System.out.println("connection=" + connection);
    }

}

I think the problem is that I need to specify https://ossrdbms-aad.database.windows.net/default (or https://ossrdbms-aad.database.windows.net//.default) as a resource or scope. When I replace User.Read with this, I get the error "AADSTS65001: The user or administrator has not consented to use the application with ID '096f6bc7-cf30-4b06-9f0e-093aaf3f9a4c' named 'AuthTest'. Send an interactive authorization request for this user and resource.", which is understandable, given that I have not granted consent through the portal as I have for User.Read. But I have no idea how to grant this consent.

Could you please tell me if you have added the user in your Azure PostgreSQL server? — Jim Xu
Good question. Yes, I have added the user as an administrator using the Azure console and have no trouble connecting using Azure Data Studio. — Gene McCulley
Have you added the permissions to the AD application you use? — Jim Xu
I added the User.Read permission. I don’t see how to add the ossrdbms permission. — Gene McCulley

Gene McCulley Gene McCulley · Accepted Answer · 2020-11-24T15:16:14

Thanks to Jim Xu, I have this working now. I had to add the "Azure OSSRDBMS Database" permission as illustrated at https://i.stack.imgur.com/o4QO4.png, grant consent as an administrator, and then set the scope in my application to https://ossrdbms-aad.database.windows.net/user_impersonation.

How to authenticate Azure PostgreSQL with token retrieved from Azure AD with username & password?

1 Answers