はじめに

こんにちはProduct Teamのマネージャーのよしだです。弊社では、様々なサービスのバックエンドでPostgreSQLを利用しています。セキュリティを強化するためにロール管理、特に新規ロール作成時のパスワード管理は、非常に重要です。データベーススキーマの変更を行う管理者権限とBIツール連携などで利用される読み取り専用ロールを分けて運用することで、意図しないデータ変更のリスクを低減できます。読み取り専用ロールは新規作成の機会が多く、パスワード管理を適切に行わないとセキュリティリスクが発生する可能性があります。弊社では、機密情報管理に1Passwordを活用しており、PostgreSQLのパスワード管理も例外ではありません。PostgreSQL 14からは pg_read_all_dataのような定義済みロールが登場し、読み取り専用ユーザーの作成自体は容易になりました。しかし、パスワードの設定については依然として安全な管理が求められます。そこで、1Password CLIを活用しPostgreSQLのロールをセキュアに作成する方法を紹介します。

この記事はエモーションテック Advent Calendar 2024の21日目の記事です。

前提

1Password CLIがインストール済みであり、op runコマンドが利用可能な状態であること
PostgreSQLへアクセスするためにpsqlコマンドが利用可能な状態であること
1PasswordにPostgreSQLの管理者ユーザーのパスワード、新たに作成する読み取り専用ユーザーのパスワードが保存されていること

補足: op run コマンドの詳しい利用方法は、1Password CLIの公式ドキュメントをご確認ください。

1Password CLIを使ったロール作成

op run コマンドを使うと1Passwordに保存されているパスワードを安全にスクリプト内で利用できます。この機能を使ってPostgreSQLの読み取り専用ロールを作成する例を紹介します。

環境変数ファイルの準備

まず、PostgreSQLの接続情報を1Passwordに保存したアイテム（今回の場合、PostgreSQLのパスワード）の秘密参照をコピーし、 .env ファイルに記録します。

参考

秘密参照の方法
- https://developer.1password.com/docs/cli/secret-reference-syntax#get-secret-references
.env ファイルに秘密参照を貼り付ける方法
- https://developer.1password.com/docs/cli/secret-references#pass-the-secrets-to-an-application-or-script

たとえば、以下のような感じです。

PGUSER="etadmin"
PGDATABASE="et_db"
PGHOST="localhost"
PGPORT="5432"
PGPASSWORD="op://Employee/etdb/password"
READONLY_ROLE_PREFIX="et"
USER_PASSWORD="op://Employee/readonly_etdb/password"

PGPASSWORDには管理者ユーザーのパスワードを保存しているアイテムの参照先、USER_PASSWORDには読み取り専用ユーザーのパスワードを保存しているアイテムの参照先を設定します。

スクリプトの作成

次に読み取り専用ユーザーを作成するスクリプトファイルを作成します。

#!/bin/bash

# PostgreSQLの読み取り権限専用ロールを作成するスクリプト

# 環境変数からPostgreSQLのユーザー名、データベース名、ロール名のプレフィックス、ホスト名、ポート番号を取得
PGUSER=${PGUSER}
PGDATABASE=${PGDATABASE}
PGHOST=${PGHOST}
PGPORT=${PGPORT}
PGPASSWORD=${PGPASSWORD}
USER_PASSWORD=${USER_PASSWORD}
READONLY_ROLE_PREFIX=${READONLY_ROLE_PREFIX}
# 読み取り専用ロール名を設定
PGROLE="${READONLY_ROLE_PREFIX}_read_only"

# ロールが既に存在するか確認
ROLE_EXISTS=$(PGPASSWORD=$PGPASSWORD psql -h $PGHOST -p $PGPORT -U $PGUSER -d $PGDATABASE -tAc "SELECT 1 FROM pg_roles WHERE rolname='$PGROLE'")

# ロールが存在しない場合、ロールを作成
if [ -z "$ROLE_EXISTS" ]; then
    PGPASSWORD=$PGPASSWORD psql -h $PGHOST -p $PGPORT -U $PGUSER -d $PGDATABASE -c "CREATE ROLE $PGROLE WITH LOGIN PASSWORD '$USER_PASSWORD'; GRANT pg_read_all_data TO $PGROLE;"
    echo "Role $PGROLE created."
else
    echo "Role $PGROLE already exists."
fi

作成したスクリプトを op run コマンドで実行します。op runコマンドは引数に環境変数を渡しながら実行することができます。

op run --env-file="./local/et_db.env" -- bash create_pg_readonly_role.sh

1Password CLI を活用したセキュアなロール作成のメリット

安全なパスワード管理 パスワードは1Passwordの保管庫で安全に管理され、スクリプトや設定ファイルに直接記述されることはありません。

バージョン管理システムにコミット可能 .env ファイルに記述するのは1Passwordの秘密参照の情報であり、実際のパスワードではないためバージョン管理システムにコミットしても安全です。これによりチームでの共有や変更履歴の管理が容易になります。

チーム開発での安全なパスワード共有 1Password の共有保管庫を利用することでチームメンバー間でパスワードを安全に共有できます。op run コマンドと組み合わせることで実行時にパスワードを取得するためパスワードの共有漏れのリスクを最小限に抑えられます。

おわりに

本記事では1Password CLI と op run コマンドを利用して、PostgreSQL のロールをセキュアに作成する方法を紹介しました。.env ファイルと秘密参照を活用することで、パスワードをハードコードすることなく、安全にロールを作成できることを示しました。この方法は、特に新規ロール作成時のセキュリティリスクを低減し、PostgreSQL の運用をより安全に行うために役立つと思います。

エモーションテックについて

エモーションテックは顧客体験マネジメント（CXマネジメント）を通じて企業が顧客のニーズを理解し顧客に最適なサービスを提供でき、従業員体験を通じて従業員がイキイキと働けるように支援をしております。同様に、サービスの信頼性向上にも積極的に取り組んでいます。ご興味のある方はぜひ採用ページからご応募をお願いいたします。

hrmos.co