Obtain Access Token for the connection to PostgreSQL in Azure Functions

Question

I have created some Azure Functions, and I would like to connect to Postgres database using an Access Token (for the connection of the user is doing the request to the endpoint of Azure Function). I've been following this page: https://learn.microsoft.com/en-us/azure/postgresql/howto-configure-sign-in-aad-authentication

For Authentication/Authorization in my functions I have created an Azure Active Directory app (in App registrarions). In this app, (not sure if this is useful) I have given permissions to the API of OSSRDBMS:

Then, I get the Access Token of the next endpoint of my App Service (where are my Functions):

MYAPP_SERVICE.azurewebsites.net/.auth/me

And I am trying to get the connection with this Access Token, but appearly is not the good one. What am I missing or how can I get the correct Access Token? I understand how works with Azure CLI, but I am trying to use the Access Token of the endpoint of my App Service... or can I do an HTTP Request to get the correct token?
I need a bit of guidance in this matter.

Thanks beforehand

Answer 1

If you want to connect Postgres database with Azure AD auth in Azure function, we can Azure Managed Identity to do Azure AD auth then get Azure AD access token and connect database.

The detailed steps are as below

1. Enable Azure MSI fro your Azure function app

2. Get the client id of the MSI

az login
az ad sp show --id <the object id of the msi> --query appId --output tsv

3. Configure Azure AD admin in Postgres database

4. Use the Azure AD admin to connect the database. (I use PgAdmin to connect)

SET aad_validate_oids_in_tenant = off;
CREATE ROLE <userName> WITH LOGIN PASSWORD '<the appid of the MSI>' IN ROLE azure_ad_user;

5. Configure Postgres server firewall. Please add the Azure function app outbound IP addresses in the firewall. Regarding how to get the Azure function app outbound IP addresses and configure Postgres server firewall, please refer to here and here

6. If you enable SSL, please download SSL certificate via the link

7. Function. (I use .net core to write the sample)

a. sdk

 <PackageReference Include="Microsoft.Azure.Services.AppAuthentication" Version="1.5.0" />
    <PackageReference Include="Microsoft.NET.Sdk.Functions" Version="3.0.8" />
    <PackageReference Include="Npgsql" Version="4.1.3.1" />

b. add above SSL certificate in project. For example, I create a folder cert in my project and save cert in the folder

c. code

        [FunctionName("Http")]
        public static async Task<IActionResult> Run(
            [HttpTrigger(AuthorizationLevel.Anonymous, "get", "post", Route = null)] HttpRequest req,
            ILogger log, ExecutionContext context)
        {

            var azureServiceTokenProvider = new AzureServiceTokenProvider();
            string accessToken = await azureServiceTokenProvider.GetAccessTokenAsync("https://ossrdbms-aad.database.windows.net");
         
            string Host = "test05.postgres.database.azure.com";
            string User = "testuer@test05";
            string Database = "postgres";
            string connString =
                String.Format(
                    "Server={0}; User Id={1}; Database={2}; Port={3}; Password={4};SSLMode=Require",
                    Host,
                    User,
                    Database,
                    5432,
                    accessToken);
            string result = string.Empty;
            using (var conn = new NpgsqlConnection(connString))
            {
                ProvideClientCertificatesCallback provideClientCertificates = clientCerts =>
                {
                    string clientCertPath = context.FunctionAppDirectory + "\\cert\\BaltimoreCyberTrustRoot.crt.pem";
                    var cert = new X509Certificate2(clientCertPath);
                    clientCerts.Add(cert);
                };
                conn.ProvideClientCertificatesCallback += provideClientCertificates;
                Console.Out.WriteLine("Opening connection using access token...");
                conn.Open();

                using (var command = new NpgsqlCommand("SELECT version()", conn))
                {

                    var reader = command.ExecuteReader();
                    while (reader.Read())
                    {
                        Console.WriteLine("\nConnected!\n\nPostgres version: {0}", reader.GetString(0));
                        result = reader.GetString(0);
                    }
                }
            }
            return new OkObjectResult(result);

        }

For more details, please refer to here