This article is contributed. See the original author and article here.
Managed identity is a feature that enables you to authenticate to Azure resources securely without needing to insert credentials into your code. Managed identities are automatically managed by Azure and enable you to authenticate to services that support Azure Active Directory authentication, like Azure Database for PostgreSQL – Single Server.
Today we’ll create a managed identity for an Azure Function app and connect to an Azure Database for PostgreSQL server. (We also have a tutorial for connecting from a VM with managed identity).
In this blog, we’ll be going through the following steps:
- Step 1: Configure Azure AD Authentication for PostgreSQL
- Step 2: Enable managed identity for the Function app
- Step 3: Use the managed identity ID to create a user in Postgres
- Step 4: Write code for function app
- Step 5: Test the function app and connect to Postgres
Step 1: Configure Azure AD Authentication for Azure Database for PostgreSQL – Single Server
First, we need to make sure that our Azure Database for PostgreSQL server is configured for Azure Active Directory authentication. The Use Azure Active Directory for authentication with PostgreSQL walkthrough shows you how to do so.
Step 2: Enable a managed identity for the Function app
Next is to enable a system-assigned managed identify for the Azure Function app. A system-assigned managed identity is an Active Directory identity that’s created by Azure for a specific resource.
Sign in to the Azure portal and select the Function app you’d like to use. Select Identity under Settings. In the System assigned tab, set Status to On.
Step 3: Use the managed identity ID to create a user in Postgres
Now we will create a Postgres user for your managed identity. For this we need to get the application’s ID. Search and open Azure Active Directory in the Azure portal. Select Enterprise Applications.
Select your Function app and copy its Application ID.
Now, connect to your Azure Database for PostgreSQL server using your Azure AD administrator user (from Step 1). Run the following in Postgres, substituting in your application ID:
SET aad_validate_oids_in_tenant = off;
CREATE ROLE myuser WITH LOGIN PASSWORD '<APPLICATION_ID>' IN ROLE azure_ad_user;
The managed identity now has access when authenticating to Postgres with the username myuser.
Step 4: Write code for the Function app
Next let’s see how to get an access token using the Function app’s system-managed identity. We’ll use that token to call Azure Database for PostgreSQL. Azure Database for PostgreSQL – Single Server natively supports Azure AD authentication, so it can directly accept access tokens obtained using managed identities for Azure resources. When creating a connection to PostgreSQL, you pass the access token in the password field.
Here’s a .NET code example of opening a connection to PostgreSQL using an access token. This code must run on the Function app to access the system-assigned managed identity’s endpoint. .NET Framework 4.6 or higher or .NET Core 2.2 or higher is required to use the access token method.
Replace the values of Servername, User, and Database to match yours.
using System;
using System.IO;
using System.Threading.Tasks;
using Microsoft.AspNetCore.Mvc;
using Microsoft.Azure.WebJobs;
using Microsoft.Azure.WebJobs.Extensions.Http;
using Microsoft.AspNetCore.Http;
using Microsoft.Extensions.Logging;
using Newtonsoft.Json;
using Npgsql;
using Microsoft.Azure.Services.AppAuthentication;
namespace myfunctionappsudi
{
public static class Function1
{
/*Declare Database Details*/
private static string Host = "<Servername>.postgres.database.azure.com";/*Database FQDN*/
private static string User = "myuser@<Servername>"; /*User Created with managed identity*/
private static string DBname = "<DB Name>";/*Database Name*/
private static string Port = "5432";/*Database Port*/
static string responsemsg;
[FunctionName("Function1")]
public static async Task<IActionResult> Run(
[HttpTrigger(AuthorizationLevel.Function, "get", "post", Route = null)] HttpRequest req,
ILogger log)
{
log.LogInformation("C# HTTP trigger function processed a request.");
/*Gettting the Token*/
var sqlServerTokenProvider = new AzureServiceTokenProvider();
var SqlAccessToken = await sqlServerTokenProvider.GetAccessTokenAsync("https://ossrdbms-aad.database.windows.net");
//log.LogInformation (SqlAccessToken); /*For troubleshooting in case you need to print the token */
log.LogInformation("Connecting to database.");
string connString = String.Format(
"Server={0}; User Id={1}; Database={2}; Port={3}; Password={4};SSLMode=Prefer",
Host,
User,
DBname,
Port,
SqlAccessToken);
/*Connecting to PostgreSQL*/
using (var conn = new NpgsqlConnection(connString))
{
log.LogInformation("Opening connection");
log.LogInformation(connString);
conn.Open();
log.LogInformation("Opening connection using access token....");
/*Query the Database */
using (var command = new NpgsqlCommand("SELECT version()", conn))
{
var reader = command.ExecuteReader();
while (reader.Read())
{
log.LogInformation("nConnected!nnPostgres version: {0}", reader.GetString(0));
responsemsg = reader.GetString(0);
}
}
}
responsemsg = "The version of the Azure Database for PostgreSQL is : " + responsemsg;
return new OkObjectResult(responsemsg);
}
}
}
Step 5: Test the Function app and connect to Postgres
Once you publish the Function app, you can test it. In the Azure portal, go to the Function app you published and select Functions.
Once the Function is selected you can choose Code+Test and then Test/Run
Select Run in the pop-up window.
You can see the function’s output in terminal for App Insights.
We can see that the function connected to the Postgres database with managed identity and could successfully run the query.
You no longer have to keep credentials in code
Managed identity sets you free from storing credentials in code or source control. If you’re interested in how to use managed identity to connect from an Azure VM to Azure Database for PostgreSQL – Single Server, check out our walkthrough.
Brought to you by Dr. Ware, Microsoft Office 365 Silver Partner, Charleston SC.
Recent Comments