Connect from Function app with managed identity to Azure Database for PostgreSQL
Published Jul 22 2020 04:46 PM 22.6K Views

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;


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>";/*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;
        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("");
            //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",
            /*Connecting to PostgreSQL*/
            using (var conn = new NpgsqlConnection(connString))
                log.LogInformation("Opening connection");
                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!\n\nPostgres 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


 Step-5b-Function Code-Test-portal-page.png


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.





Version history
Last update:
‎Jul 22 2020 04:46 PM
Updated by: