使用托管标识连接到 Azure Database for PostgreSQLConnect with Managed Identity to Azure Database for PostgreSQL

本文介绍如何使用 Azure 虚拟机 (VM) 的用户分配标识来访问 Azure Database for PostgreSQL 服务器。This article shows you how to use a user-assigned identity for an Azure Virtual Machine (VM) to access an Azure Database for PostgreSQL server. 托管服务标识由 Azure 自动管理,可用于向支持 Azure AD 身份验证的服务进行身份验证,这样就无需在代码中插入凭据了。Managed Service Identities are automatically managed by Azure and enable you to authenticate to services that support Azure AD authentication, without needing to insert credentials into your code.

学习如何:You learn how to:

  • 授予 VM 对 Azure Database for PostgreSQL 服务器的访问权限Grant your VM access to an Azure Database for PostgreSQL server
  • 在数据库中创建一个代表 VM 用户分配标识的用户Create a user in the database that represents the VM's user-assigned identity
  • 使用 VM 标识获取访问令牌,并使用它查询 Azure Database for PostgreSQL 服务器Get an access token using the VM identity and use it to query an Azure Database for PostgreSQL server
  • 在 C# 示例应用程序中实现令牌检索Implement the token retrieval in a C# example application

先决条件Prerequisites

为 VM 创建用户分配托管标识Creating a user-assigned managed identity for your VM

使用 az identity create 命令在订阅中创建标识。Create an identity in your subscription using the az identity create command. 可以使用虚拟机所在的相同资源组,也可以使用其他资源组。You can use the same resource group that your virtual machine runs in, or a different one.

az identity create --resource-group myResourceGroup --name myManagedIdentity

要在以下步骤中配置标识,请使用 az identity show 命令将标识的资源 ID 和客户端 ID 存储在变量中。To configure the identity in the following steps, use the az identity show command to store the identity's resource ID and client ID in variables.

# Get resource ID of the user-assigned identity
resourceID=$(az identity show --resource-group myResourceGroup --name myManagedIdentity --query id --output tsv)

# Get client ID of the user-assigned identity
clientID=$(az identity show --resource-group myResourceGroup --name myManagedIdentity --query clientId --output tsv)

现在,我们使用 az vm identity assign 命令将用户分配标识分配给 VM:We can now assign the user-assigned identity to the VM with the az vm identity assign command:

az vm identity assign --resource-group myResourceGroup --name myVM --identities $resourceID

要完成设置,请显示客户端 ID 的值,在接下来的几个步骤中需要该值:To finish setup, show the value of the Client ID, which you'll need in the next few steps:

echo $clientID

为托管标识创建 PostgreSQL 用户Creating a PostgreSQL user for your Managed Identity

现在,作为 Azure AD 管理员用户连接到 PostgreSQL 数据库,并运行以下 SQL 语句:Now, connect as the Azure AD administrator user to your PostgreSQL database, and run the following SQL statements:

SET aad_validate_oids_in_tenant = off;
CREATE ROLE myuser WITH LOGIN PASSWORD 'CLIENT_ID' IN ROLE azure_ad_user;

使用用户名 myuser(替换为你选择的名称)进行身份验证时,托管标识现在具有访问权限。The managed identity now has access when authenticating with the username myuser (replace with a name of your choice).

从 Azure 实例元数据服务检索访问令牌Retrieving the access token from Azure Instance Metadata service

应用程序现在可以从 Azure 实例元数据服务检索访问令牌,并将其用于向数据库进行身份验证。Your application can now retrieve an access token from the Azure Instance Metadata service and use it for authenticating with the database.

此令牌检索是通过向 http://169.254.169.254/metadata/identity/oauth2/token 发出 HTTP 请求并传递以下参数来完成的:This token retrieval is done by making an HTTP request to http://169.254.169.254/metadata/identity/oauth2/token and passing the following parameters:

  • api-version = 2018-02-01
  • resource = https://ossrdbms-aad.database.chinacloudapi.cn
  • client_id = CLIENT_ID(之前检索到的)client_id = CLIENT_ID (that you retrieved earlier)

将返回包含 access_token 字段的 JSON 结果 - 此长文本值是托管标识访问令牌,在连接到数据库时应将其用作密码。You'll get back a JSON result that contains an access_token field - this long text value is the Managed Identity access token, that you should use as the password when connecting to the database.

出于测试目的,可以在 shell 中运行以下命令。For testing purposes, you can run the following commands in your shell. 请注意,需要安装 curljqpsql 客户端。Note you need curl, jq, and the psql client installed.

# Retrieve the access token
export PGPASSWORD=`curl -s 'http://169.254.169.254/metadata/identity/oauth2/token?api-version=2018-02-01&resource=https%3A%2F%2Fossrdbms-aad.database.chinacloudapi.cn&client_id=CLIENT_ID' -H Metadata:true | jq -r .access_token`

# Connect to the database
psql -h SERVER --user USER@SERVER DBNAME

你现在已连接到之前配置的数据库。You are now connected to the database you've configured earlier.

通过 C# 使用托管标识进行连接Connecting using Managed Identity in C#

本部分介绍如何使用 VM 的用户分配托管标识获取访问令牌,并使用它调用 Azure Database for PostgreSQL。This section shows how to get an access token using the VM's user-assigned managed identity and use it to call Azure Database for PostgreSQL. Azure Database for PostgreSQL 以原生方式支持 Azure AD 身份验证,因此可以直接接受使用 Azure 资源托管标识获取的访问令牌。Azure Database for PostgreSQL natively supports Azure AD authentication, so it can directly accept access tokens obtained using managed identities for Azure resources. 创建到 PostgreSQL 的连接时,在密码字段中传递访问令牌。When creating a connection to PostgreSQL, you pass the access token in the password field.

以下 .NET 代码示例使用访问令牌来与 PostgreSQL 建立连接。Here's a .NET code example of opening a connection to PostgreSQL using an access token. 此代码必须在 VM 上运行,以访问 VM 用户分配托管标识的终结点。This code must run on the VM to access the VM's user-assigned managed identity's endpoint. 使用访问令牌方法需要 .NET Framework 4.6 或更高版本或者 .NET Core 2.2 或更高版本。.NET Framework 4.6 or higher or .NET Core 2.2 or higher is required to use the access token method. 替换 HOST、USER、DATABASE 和 CLIENT_ID 的值。Replace the values of HOST, USER, DATABASE, and CLIENT_ID.

using System;
using System.Net;
using System.IO;
using System.Collections;
using System.Collections.Generic;
using System.Text.Json;
using System.Text.Json.Serialization;
using Npgsql;

namespace Driver
{
    class Script
    {
        // Obtain connection string information from the portal
        //
        private static string Host = "HOST";
        private static string User = "USER";
        private static string Database = "DATABASE";
        private static string ClientId = "CLIENT_ID";

        static void Main(string[] args)
        {
            //
            // Get an access token for PostgreSQL.
            //
            Console.Out.WriteLine("Getting access token from Azure Instance Metadata service...");

            // Azure AD resource ID for Azure Database for PostgreSQL is https://ossrdbms-aad.database.chinacloudapi.cn/
            HttpWebRequest request = (HttpWebRequest)WebRequest.Create("http://169.254.169.254/metadata/identity/oauth2/token?api-version=2018-02-01&resource=https%3A%2F%2Fossrdbms-aad.database.chinacloudapi.cn&client_id=" + ClientId);
            request.Headers["Metadata"] = "true";
            request.Method = "GET";
            string accessToken = null;

            try
            {
                // Call managed identities for Azure resources endpoint.
                HttpWebResponse response = (HttpWebResponse)request.GetResponse();

                // Pipe response Stream to a StreamReader and extract access token.
                StreamReader streamResponse = new StreamReader(response.GetResponseStream());
                string stringResponse = streamResponse.ReadToEnd();
                var list = JsonSerializer.Deserialize<Dictionary<string, string>>(stringResponse);
                accessToken = list["access_token"];
            }
            catch (Exception e)
            {
                Console.Out.WriteLine("{0} \n\n{1}", e.Message, e.InnerException != null ? e.InnerException.Message : "Acquire token failed");
                System.Environment.Exit(1);
            }

            //
            // Open a connection to the PostgreSQL server using the access token.
            //
            string connString =
                String.Format(
                    "Server={0}; User Id={1}; Database={2}; Port={3}; Password={4};SSLMode=Prefer",
                    Host,
                    User,
                    Database,
                    5432,
                    accessToken);

            using (var conn = new NpgsqlConnection(connString))
            {
                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));
                    }
                }
            }
        }
    }
}

运行时,此命令将返回如下所示的输出:When run, this command will give an output like this:

Getting access token from Azure Instance Metadata service...
Opening connection using access token...

Connected!

Postgres version: PostgreSQL 11.6, compiled by Visual C++ build 1800, 64-bit

后续步骤Next steps