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 theUsernameinNpgsqlConnectionStringBuilder, 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 theUsername, it always picks the user name on the machine but not from Azure credentials. But at least I made it work with theUsername. -
When I use
ManagedIdentityCredentialand 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 nameas theUsername - I tried using
Service Principal ObjectIdas theUsername - I tried using
Service Principal ApplicationIdas theUsername
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?
4
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: