Azure托管标识函数连接PostgreSQL弹性服务器时密码认证失败

我有配置了系统托管标识的Azure Function，以及已配置Azure AD身份的Azure Database for PostgreSQL弹性服务器（测试时允许公网访问）：

• 本地通过DefaultAzureCredential（az login）连接时，在NpgsqlConnectionStringBuilder指定邮箱作为Username可以正常连接；不指定的话会用本地机器用户名，无法使用Azure凭据。
• 部署到函数后，用ManagedIdentityCredential尝试连接时，能获取有效访问令牌，但始终因用户名问题认证失败。

相关代码 #

[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]   FAILED TO OPEN DATABASE CONNECTION
password authentication failed for user "funct-test-postrgre-xyz"

尝试过函数名称、服务主体ObjectId、ApplicationId作为用户名，均报错28P01: password authentication failed for user "xxx"。已执行以下SQL配置：

-- 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";

提问：是否需要在PostgreSQL端进行额外配置？应使用服务主体的ApplicationId还是ObjectId？如何验证PostgreSQL配置是否正确？

1. 正确的用户名选择 #

必须使用托管标识的ObjectId作为PostgreSQL的用户名：

• 创建角色时的ObjectId必须和函数托管标识的ObjectId完全一致，且必须用双引号包裹（PostgreSQL会将无引号的UUID转为小写，导致与Azure AD传递的ObjectId不匹配）
• 函数名称、ApplicationId均不被Azure AD认证PostgreSQL时认可，请勿使用

2. 修正连接字符串配置 #

必须在NpgsqlConnectionStringBuilder中明确指定Username为托管标识的ObjectId，否则Npgsql会默认使用函数名称作为用户名，这就是报错的直接原因：

string connectionString = new NpgsqlConnectionStringBuilder
{
    Host = "my-xyz-pgsql.postgres.database.azure.com",
    Port = 5432,
    Database = "my_test_db",
    SslMode = SslMode.Require,
    Username = "你的托管标识ObjectId" // 必须添加该行，替换为实际的ObjectId
}.ToString();

3. 验证PostgreSQL配置正确性 #

执行以下SQL命令检查配置：

-- 检查角色是否存在
SELECT rolname FROM pg_roles WHERE rolname = '你的托管标识ObjectId';

-- 检查数据库连接权限
SELECT has_database_privilege('你的托管标识ObjectId', 'my_test_db', 'CONNECT');

-- 检查schema使用权限
SELECT has_schema_privilege('你的托管标识ObjectId', 'public', 'USAGE');

-- 检查表权限
SELECT tablename, 
       has_table_privilege('你的托管标识ObjectId', tablename, 'SELECT') AS select_perm,
       has_table_privilege('你的托管标识ObjectId', tablename, 'INSERT') AS insert_perm,
       has_table_privilege('你的托管标识ObjectId', tablename, 'UPDATE') AS update_perm,
       has_table_privilege('你的托管标识ObjectId', tablename, 'DELETE') AS delete_perm
FROM pg_tables WHERE schemaname = 'public';

所有查询返回true则说明权限配置正确。

4. 额外检查项 #

• 确认PostgreSQL服务器已启用Azure AD认证：在Azure门户的服务器「身份验证」选项卡中，已设置「Azure Active Directory管理员」（用户或服务主体均可）
• 无需给函数托管标识分配Azure层面的PostgreSQL权限，仅需PostgreSQL内部角色配置正确即可
• 你的GetAccessTokenAsync中使用的令牌资源https://ossrdbms-aad.database.windows.net是正确的，无需修改

内容的提问来源于stack exchange，提问作者tridy