Password authentication failed Azure Function as Managed Identity with Azure Database for PostgreSQL flexible server

Question

I have Azure Function with System Identity, Azure Database for PostgreSQL flexible server with the identity in Azure. For the test purposese PosttgreSQL allows public access.

• I am able to connect to PostgreSQL from my computer using DefaultAzureCredential (az login) and specifying my email as the Username in NpgsqlConnectionStringBuilder, even though I assumed that this should have worked without user name, since it is relying othe AcceessToken and data in it. If I do not specify the Username, it always picks the user name on the machine but not from Azure credentials. But at least I made it work with the Username.

• When I use ManagedIdentityCredential and deploy the code to the function, it finds the PostgreSQL, tries to connect but fails due to the user name. Here is the code and error message:

    [Function("TestOpenConnection")]
    public async Task<IActionResult> RunTestOpenConnectionMisc([HttpTrigger(AuthorizationLevel.Function, "get")] HttpRequest req, CancellationToken cancellationToken)
    {
        _logger.LogInformation(3009, "C# HTTP trigger TestOpenConnection function processed a request.");
        
        string connectionString = new NpgsqlConnectionStringBuilder
        {
            Host = "my-xyz-pgsql.postgres.database.azure.com",
            Port = 5432,
            Database = "my_test_db",
            SslMode = SslMode.Require,
            // ?? Username
        }.ToString();

        NpgsqlDataSourceBuilder dataSourceBuilder = new(connectionString);

        TimeSpan successRefreshInterval = TimeSpan.FromMinutes(55);
        TimeSpan failureRefreshInterval = TimeSpan.FromSeconds(30);

        dataSourceBuilder.UsePeriodicPasswordProvider(async (_, ct)
            => await GetAccessTokenAsync(ct), successRefreshInterval, failureRefreshInterval);

        await using var dataSource = dataSourceBuilder.Build();

        try
        {
            await dataSource.OpenConnectionAsync(cancellationToken);
        }
        catch (Exception e)
        {
            _logger.LogError(2004, e, "FAILED TO OPEN DATABASE CONNECTION");
            return new BadRequestObjectResult(e);
        }
        
        _logger.LogInformation(3011, "Connection opened successfully");
        return new OkObjectResult("Connection opened successfully");
    }
    
    private async Task<string> GetAccessTokenAsync(CancellationToken cancellationToken)
    {
        ManagedIdentityCredential credential = new();
        string resource = "https://ossrdbms-aad.database.windows.net";
        TokenRequestContext tokenRequestContext = new([$"{resource}/.default"]);
        AccessToken fullToken = await credential.GetTokenAsync(tokenRequestContext, cancellationToken).ConfigureAwait(false);
        return fullToken.Token;
    }

Error:

[Error]   FAILED TO OPEN DATABASE CONNECTION
password authentication failed for user "funct-test-postrgre-xyz"

I have tested the getting of the token, and it works and gets the proper access token for the Function's identity. I tried several options on how to connect but I have run out of ideas:

• I tried using Function name as the Username
• I tried using Service Principal ObjectId as the Username
• I tried using Service Principal ApplicationId as the Username

every time I would get:

28P01: password authentication failed for user "name/objectId/applicationId"

Is it on the PostreSQL side that I need to setup something else?

I performed the following commands:

-- Create a Role for the Enterprise Application:
CREATE ROLE "00000000-0000-0000-0000-000000000000" WITH LOGIN;

-- Grant Privileges to the Role:
GRANT CONNECT ON DATABASE my_test_db TO "00000000-0000-0000-0000-000000000000";
GRANT USAGE ON SCHEMA public TO "00000000-0000-0000-0000-000000000000";
GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA public TO "00000000-0000-0000-0000-000000000000";

-- Configure Default Privileges (Optional):
ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT SELECT, INSERT, UPDATE, DELETE ON TABLES TO "00000000-0000-0000-0000-000000000000";

how can I veriry that PostreSQL is setup properly? And is it Service Principal ApplicationId or ObjectId?

Answer 1

I was also getting same error.

To control you authentication process to access token use ``

For reference check this MS Document

DefaultAzureCredential uses all methods for authentication order wise. For order check this MS Document.

This Query worked for me.

SELECT * FROM pgaadauth_create_principal('<identity_name>', false, false);

#This query assigns these access("create User","Assign Superuser", "Assign Admin")

For reference check this MS Document.

Pass the valueUsername = <Identity_name>

My Code:

• I am using Dynamic model for username using string User = req.Query["User"].FirstOrDefault() ?? Environment.GetEnvironmentVariable("User");
• Make sure you have created the user using the query in Postgresql

using Azure.Core;
using Azure.Identity;
using Microsoft.AspNetCore.Http;
using Microsoft.AspNetCore.Mvc;
using Microsoft.Azure.Functions.Worker;
using Microsoft.Extensions.Logging;
using Npgsql;
using System.Threading;

namespace FunctionApp5
{
    public class Function
    {
        private readonly ILogger<Function> _logger;

        public Function(ILogger<Function> logger)
        {
            _logger = logger;
        }

        [Function("Function")]
        public async Task<IActionResult> Run([HttpTrigger(AuthorizationLevel.Anonymous, "get", "post")] HttpRequest req, CancellationToken cancellationToken)
        {
            _logger.LogInformation(3009, "C# HTTP trigger TestOpenConnection function processed a request.");

            string User = req.Query["User"].FirstOrDefault() ?? Environment.GetEnvironmentVariable("User");

            string connectionString = new NpgsqlConnectionStringBuilder
            {
                Host = "postgre16sep.postgres.database.azure.com",
                Port = 5432,
                Database = "postgres",
                Username = User,
                SslMode = SslMode.Require,
            }.ToString();

            NpgsqlDataSourceBuilder dataSourceBuilder = new (connectionString);

            TimeSpan successRefreshInterval = TimeSpan.FromMinutes(55);
            TimeSpan failureRefreshInterval = TimeSpan.FromSeconds(30);

            dataSourceBuilder.UsePeriodicPasswordProvider(async (_, ct)
                => await GetAccessTokenAsync(ct), successRefreshInterval, failureRefreshInterval);

            await using var dataSource = dataSourceBuilder.Build();

            try
            {
                await dataSource.OpenConnectionAsync(cancellationToken);
            }
            catch (Exception e)
            {
                _logger.LogError(2004, e, "FAILED TO OPEN DATABASE CONNECTION");
                return new BadRequestObjectResult(e.Message);
            }

            _logger.LogInformation(3011, "Connection opened successfully");
            return new OkObjectResult("Connection opened successfully");
        }

        private async Task<string> GetAccessTokenAsync(CancellationToken cancellationToken)
        {
            var credential = new DefaultAzureCredential();
            string resource = "https://ossrdbms-aad.database.windows.net";
            TokenRequestContext tokenRequestContext = new TokenRequestContext( new[] {$"{resource}/.default" });
            AccessToken fullToken = await credential.GetTokenAsync(tokenRequestContext, cancellationToken).ConfigureAwait(false);
            return fullToken.Token;
        }
    }
}

OUTPUT: