PostgreSQL のユーザーとロールについて

MySQL | PostgreSQL | SQL Server

ここでは、Cloud SQL での PostgreSQL ユーザーとロールの操作方法について説明します。PostgreSQL のロールを使用すると、PostgreSQL インスタンスにアクセスするユーザーのアクセス権と機能を制御できます。

PostgreSQL のロールの詳細については、PostgreSQL ドキュメントのデータベースロールをご覧ください。Cloud SQL ユーザーの作成と管理について詳しくは、ユーザーを作成して管理するをご覧ください。

ユーザーとロールの違い

PostgreSQL のロールは、単一のロールにすることも、複数のロールのグループとして機能させることもできます。ユーザーとは、ログインする機能を持つロールです（ロールが LOGIN 属性を持つ）。Cloud SQL で作成されるすべてのロールは LOGIN 属性を持つため、Cloud SQL ではロールとユーザーという言葉が同じ意味で使用されます。ただし、psql クライアントでロールを作成する場合、そのロールは必ずしも LOGIN 属性を持つわけではありません。

すべての PostgreSQL ユーザーにはパスワードが必要です。パスワードを持たないユーザーでのログインはできません。

スーパーユーザーの制限と権限

Cloud SQL for PostgreSQL はマネージドサービスであるため、高度な権限が必要な特定のシステムプロシージャやテーブルへのアクセスを制限します。Cloud SQL では、スーパーユーザー属性を持つユーザーの作成、またそのようなユーザーへのアクセスはできません。

スーパーユーザー権限を持つデータベースユーザーは作成できません。ただし、cloudsqlsuperuser ロールを持つデータベースユーザーは作成できます。このロールには、次のようなスーパーユーザー権限があります。

スーパーユーザー権限が必要な拡張機能の作成。
イベントトリガーの作成。
レプリケーションユーザーの作成。
レプリケーションパブリケーションとサブスクリプションの作成。
cloudsqlsuperuser ロールを持つデータベースユーザーとして CREATE CAST ステートメントと DROP CAST ステートメントの実行。ただし、このユーザーには、ソースとターゲットの両方のデータ型に対して USAGE 権限が必要です。たとえば、ユーザーは、ソースの int データ型をターゲットの boolean データ型に変換するキャストを作成できます。

注: Cloud SQL は、WITHOUT FUNCTION キャストメソッドをサポートしていません。
pg_largeobject カタログテーブルに対する完全アクセス権。

デフォルトの PostgreSQL ユーザー

新しい Cloud SQL for PostgreSQL インスタンスを作成すると、デフォルトの管理ユーザー postgres が作成されますが、パスワードは作成されません。ログインするには、このユーザーのパスワードを設定する必要があります。設定するには、Google Cloud Console または次の gcloud コマンドを使用します。

gcloud sql users set-password postgres \
--instance=INSTANCE_NAME \
--password=PASSWORD

postgres ユーザーは cloudsqlsuperuser ロールに含まれ、CREATEROLE、CREATEDB、LOGIN 属性（特権）を持っています。SUPERUSER または REPLICATION 属性は持っていません。

デフォルトの cloudsqlimportexport ユーザーは、CSV のインポート / エクスポートオペレーションに必要な最小限の権限で作成されます。これらのオペレーションを実行する独自のユーザーを作成することもできますが、作成しない場合は、デフォルトの cloudsqlimportexport ユーザーが使用されます。cloudsqlimportexport ユーザーはシステムユーザーであり、ユーザーが直接使用することはできません。

IAM 認証のための Cloud SQL IAM ユーザー

Identity and Access Management（IAM）は、IAM 認証と呼ばれる機能で Cloud SQL と統合されています。この機能を使用してインスタンスを作成すると、IAM ユーザーはその IAM ユーザー名とパスワードを使用してインスタンスにログインできます。IAM 認証を使用する利点は、データベースへのアクセスを許可する際に、ユーザーの既存の IAM 認証情報を使用できることです。ユーザーが組織を離れた場合、ユーザーの IAM アカウントは一時停止され、自動的にアクセス権が削除されます。

その他の PostgreSQL ユーザー

その他の PostgreSQL ユーザーを作成、またはその他の PostgreSQL ロールを作成できます。Cloud SQL を使用して作成されるユーザーはすべて、作成時に cloudsqlsuperuser 役割が設定され、postgres ユーザーと同じ属性セット（CREATEROLE、CREATEDB、LOGIN）が設定されます。ALTER ROLE コマンドを使用すると、任意のユーザーの属性を変更できます。

psql クライアントで新しいユーザーを作成する場合、そのユーザーを別のロールに関連付けることや、別の属性を指定することができます。

`pg_shadow` ビューと `pg_authid` テーブルへのアクセス権

pg_shadow ビューを使用すると、pg_authid カタログテーブルで rolcanlogin とマークされたロールのプロパティを操作できます。

pg_shadow ビューには、クラスタへのログインが許可されたロール（ユーザー）のハッシュされたパスワードと、その他のプロパティが含まれます。pg_authid カタログテーブルには、すべてのデータベースロールのハッシュされたパスワードとその他のプロパティが含まれています。

Cloud SQL では、デフォルトの権限を使用して pg_shadow ビューや pg_authid テーブルにアクセスすることはできません。ただし、次のような場合は、ロール名とハッシュされたパスワードへのアクセスが有用です。

既存のユーザーとパスワードを使用してプロキシまたはロードバランシングを設定する
パスワードを変更せずにユーザーを移行する
パスワードポリシー管理用のカスタムソリューションを実装する

`pg_shadow` ビューと `pg_authid` テーブルのフラグの設定

pg_shadow ビューにアクセスするには、cloudsql.pg_shadow_select_role フラグを PostgreSQL ロール名に設定します。pg_authid テーブルにアクセスするには、cloudsql.pg_authid_select_role フラグを PostgreSQL ロール名に設定します。

cloudsql.pg_shadow_select_role が存在する場合は、pg_shadow ビューへの読み取り専用（SELECT）アクセス権が付与されています。cloudsql.pg_authid_select_role が存在する場合は、pg_authid テーブルに対する SELECT アクセス権が付与されています。

どちらのロールも存在しない場合、設定は無効となりますがエラーも発生しません。ただし、ユーザーがビューまたはテーブルにアクセスしようとすると、エラーがログに記録されます。このエラーは、PostgreSQL データベースログ（cloudsql.googleapis.com/postgres.log）に記録されます。このログの表示の詳細については、インスタンスのログを表示するをご覧ください。

構成されたロールが存在し、cloudsql.pg_shadow_select_role フラグまたは cloudsql.pg_authid_select_role フラグの値に入力ミスがないことを確認します。また、pg_has_role 関数を使用して、ユーザーがロールのメンバーであることを確認することもできます。この関数の詳細については、システム情報関数と演算子のページをご覧ください。

PostgreSQL ロールメンバーシップで cloudsql.pg_shadow_select_role フラグまたは cloudsql.pg_authid_select_role フラグを使用すると、複数のユーザーの pg_shadow アクセス権または pg_authid アクセス権を管理できます。

どちらのフラグを変更しても、データベースの再起動は必要ありません。

サポートされているフラグの詳細について詳しくは、データベースフラグを構成するをご覧ください。

パスワードの保存形式の選択

Cloud SQL for PostgreSQL は、ユーザーのパスワードをハッシュ形式で保存します。password_encryption フラグを使用して、暗号化アルゴリズムを md5 または scram-sha-256 に設定できます。md5 アルゴリズムには幅広い互換性があります。一方、scram-sha-256 はより安全ですが、古いクライアントとは互換性がない場合があります。

Cloud SQL インスタンスからロールのプロパティをエクスポートするために pg_shadow アクセスを有効にする場合は、クライアントでサポートされている最も安全なアルゴリズムの使用を検討してください。

PostgreSQL のドキュメントで、以下の内容をご覧ください。