guidance: Managed identity with database providers

[weikanglim]

Related to Azure/azure-dev#4481

Hi all, I'm opening this issue to seek guidance and start a discussion on how users can adopt managed identity with database providers today without having to implement the application code necessary to perform the connection refresh due to short-lived managed identity token constraints.

I believe that there are some Azure SDK client strategies currently in place (Java's AzurePostgresqlAuthenticationPlugin comes to mind) that provides a managed experience, but I'm curious if there's an overall strategy in-place.

To set up some context, I moved and rewrote parts of the core considerations in Azure/azure-dev#4481 as below:

Managed identity with database providers

With managed identity, a key exchange is performed to retrieve a short-lived access token. Since this token is short-lived, it necessarily requires periodic refresh. For interacting with the general Azure data plane, this refresh is managed entirely by design via ManagedIdentityAzureCredential and the respective clients.

However, in many DBMS (Postgres, MySQL) provider implementations for Azure, there is an existing database connection protocol. For legacy reasons, these protocols generally involves a pre-negotiated, long-lived key in the form of a connection string. In these cases, for backwards compatibility, the short-lived managed identity token is used as the password field for the connection string that is validated by the DBMS.

This sets up the following scenario:

1. A user that uses the native DBMS client, is also responsible for fetching the token periodically before expiry, and refreshing the native DBMS client.
2. In Redis, a different approach is available, where a periodic AUTH command is issued to refresh the connection.

Overall, an end user adopting DBMS solutions in Azure may need to implement this connection refresh in a concurrently safe, timely, and resilient manner to avoid any downtimes that could occur. I am wondering if there is room for simplification here to reduce the overall onboarding cost and application complexity when adopting managed identity with database providers.

Examples

The following describes the current behavior for different database providers:

• Azure Cache for Redis: AUTH command requires to be issued periodically.

  • Ensure that your client application sends a new Microsoft Entra token at least three minutes before token expiry to avoid connection disruption.
  • When you call the Redis server AUTH command periodically, consider adding a random delay so that the AUTH commands are staggered. In this way, your Redis server doesn't receive too many AUTH commands at the same time.

• Postgres: Token is used as the password.

  export PGPASSWORD=`curl -s 'http://169.254.169.254/metadata/identity/oauth2/token?api-version=2018-02-01&resource=https%3A%2F%2Fossrdbms-aad.database.windows.net&client_id=CLIENT_ID' -H Metadata:true | jq -r .access_token`
  
  psql -h SERVER --user USER DBNAME

  This password would expire after some time.

• MySql: Token is used as password.

  The access token validity is anywhere between 5 minutes to 60 minutes. We recommend you get the access token before initiating the sign-in to Azure Database for MySQL flexible server.

• Cosmos:

  • NoSQL, Table: Fully supported via CosmosClient and TableClient.
  • MongoDB: Not supported natively. One common path is that a user can choose to assign a managed identity to fetch MongoDB keys and build connection strings.
  • Cassandra / Gremlin: Unsure about current implementation.
  • PostgreSQL: Token is used as password.

Questions for discussion

1. In today's world, is there a managed option for different language users to leverage Managed Identity for their Azure-hosted DBMS systems without handling the token and connection refresh in application code?
2. If tenable, should we look forward to protocol simplification (if such exists), or some forms of wrapper clients that perhaps manage this behavior?