はじめに
先日、Azure Database for MySQL Flexible ServerにてAzure AD認証を行うことを実施しました。
そんな中、PostgreSQL Flexible ServerでもAzure AD認証がGAしたというアナウンスがあったので試してみました。
設定
ドキュメントが以下にありますのでそれに沿って作業をします。
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日時点、色々と分かりにくいので苦労しました。
仮想マシン作成後、マネージド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は以下のドキュメントは以下を参考に。
無事作成できると、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=>
無事接続できました。