Azure Database for PostgreSQL Flexible ServerにてAzure AD認証を行う

はじめに

先日、Azure Database for MySQL Flexible ServerにてAzure AD認証を行うことを実施しました。

miyohide.hatenablog.com

そんな中、PostgreSQL Flexible ServerでもAzure AD認証がGAしたというアナウンスがあったので試してみました。

techcommunity.microsoft.com

設定

ドキュメントが以下にありますのでそれに沿って作業をします。

learn.microsoft.com

PowerShellの環境がないのでAzure CloudShellを使い、以下のコマンドを打ちます。

Connect-AzureAD

その後、読み取りアクセスを許可します。

New-AzureADServicePrincipal -AppId 5657e26c-cc92-45d9-bc47-9da6cfdb4ed9

その後はAzure Portalで設定します。今回はAzure AD上でグループを作成し、使用しているアカウントを所属させ、Azure Database for PostgreSQL Flexible Serverの管理者として設定しました。

この段階で作成したAzure Database for PostgreSQL Flexible Serverのユーザーには以下のユーザーが作成されています。myhogehogeadminはAzure Database for PostgreSQL Flexible Serverの作成時に指定した管理者、postgresadminはAzure AD上のグループです。

postgres=> \du
                                                                         List of roles
     Role name      |                         Attributes                         |                                  Member of                                  
--------------------+------------------------------------------------------------+-----------------------------------------------------------------------------
 azure_pg_admin     | Cannot login                                               | {pg_monitor}
 azuresu            | Superuser, Create role, Create DB, Replication, Bypass RLS | {}
 myhogehogeadmin | Create role, Create DB                                     | {pg_read_all_settings,pg_read_all_stats,pg_stat_scan_tables,azure_pg_admin}
 postgresadmin      | Create role, Create DB                                     | {azure_pg_admin}
 replication        | Replication                                                | {}

postgres=> 

これでpostgresadminでアクセスができます。Azure Cloud Shell上でexport PGPASSWORD=$(az account get-access-token --resource-type oss-rdbms --query "[accessToken]" -o tsv)を実行してアクセストークンを環境変数PGPASSWORDに設定したのち、psql "host=<作成したAzure Database for PostgreSQL Flexible Serverのホスト名> port=5432 dbname=postgres user=postgresadmin sslmode=require"で接続できました。

接続しているユーザーをAzure Database for PostgreSQL Flexible Serverにて確認すると、postgresadminが存在していることがわかります。

postgres=> select usename, query from pg_stat_activity where datname = 'postgres';
    usename    |                                                             query                                                             
---------------+-------------------------------------------------------------------------------------------------------------------------------
 azuresu       | update cron.job_run_details set status = 'failed', return_message = 'server restarted' where status in ('starting','running')
 azuresu       | SELECT pg_catalog.pg_is_in_recovery() as is_in_Recovery
 postgresadmin | select usename, query from pg_stat_activity where datname = 'postgres';
(3 rows)

postgres=> 

マネージドIDを使用して接続する

次に仮想マシンからアクセスをしてみます。ドキュメントは以下にあるのですが、2023年1月29日時点、色々と分かりにくいので苦労しました。

learn.microsoft.com

仮想マシン作成後、マネージドIDを有効化し、システム割り当てマネージドIDのアプリケーションIDを以下のコマンドで取得します。

az ad sp list --display-name <仮想マシン名> --query [*].appId --out tsv

その後、マネージドIDのPostgreSQLユーザーを作成します。Azure Database for PostgreSQL Flexible Serverに入り、以下のSQLを実行します。

postgres=> select * from pgaadauth_create_principal_with_oid('任意の名前', '上記で取得したアプリケーションID', 'service', false, false);
 pgaadauth_create_principal_with_oid 
-------------------------------------
 Created role for "任意の名前"
(1 row)

postgres=> 

発行するSQLは以下のドキュメントは以下を参考に。

learn.microsoft.com

無事作成できると、Azure Database for PostgreSQL Flexible Server上にユーザーが作られます。

postgres=> \du
                                                                         List of roles
     Role name      |                         Attributes                         |                                  Member of                                  
--------------------+------------------------------------------------------------+-----------------------------------------------------------------------------
 azure_pg_admin     | Cannot login                                               | {pg_monitor}
 azuresu            | Superuser, Create role, Create DB, Replication, Bypass RLS | {}
 任意の名前          |                                                            | {}
 myhogehogeadmin | Create role, Create DB                                     | {pg_read_all_settings,pg_read_all_stats,pg_stat_scan_tables,azure_pg_admin}
 postgresadmin      | Create role, Create DB                                     | {azure_pg_admin}
 replication        | Replication                                                | {}

postgres=> 

あとは作成した仮想マシンからアクセスしてみます。Azure Instance Metadata Serviceからアクセストークンを取得し、環境変数PGPASSWORDに格納します。

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.windows.net' -H Metadata:true | jq -r .access_token`

その後はpsqlコマンドで接続します。

$ psql -h <作成したAzure Database for PostgreSQL Flexible Serverのホスト名> --user <任意の名前> postgres
psql (15.1 (Ubuntu 15.1-1.pgdg20.04+1), server 14.6)
SSL connection (protocol: TLSv1.3, cipher: TLS_AES_256_GCM_SHA384, compression: off)
Type "help" for help.

postgres=>

無事接続できました。