はじめに
先日、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=>
無事接続できました。
