Google Cloud Platform (GCP) で運用している PostgreSQL に Rails からアクセスする（できるだけセキュアに）

何がしたかったのか？

タイトルそのまま。

• Google Cloud Platform (GCP) で、PostgreSQL を運用している。
• セキュリティ確保のために、SSL 接続のみを許可している。
• 開発中の Ruby on Rails アプリから、上記の条件のデータベースにアクセスする。

GCP 側の設定

バックアップや高可用性については、今回は関係ないが、必要であれば別途設定する。

GCP 接続設定

Google Cloud Platform > SQL > [インスタンス] > 「接続」

• パブリック IP ：チェックあり

  • 「承認済みネットワーク」
    • 開発環境の WAN 側 IP アドレスを設定する¹。
    • 目的の PostgreSQL に アクセス可能な発信元を制限する ことで、セキュリティリスクを避ける。

• SSL

  • 「SSL 接続のみ許可」ボタンを押して SSL接続だけを許可する。

• 「SSL クライアント証明書を作成」 ボタン

  • 「SSL クライアント証明書を作成」 ボタンをクリックすると「クライアント証明書の作成」ダイアログが表示される。
  • 分かりやすい名前を付ける。（例：「rails-client」）。
  • 「作成」リンクが青くなるのでクリックする。
  • 「新しい SSL 証明書を作成しました」ダイアログが開くので、表示されている3つの「.pem」ファイルをダウンロードもしくはコピーしておく²。
    • client-key.pem
    • client-cert.pem
    • server-ca.pem
  • 右下の「閉じる」リンクをクリックする³。

PostgreSQL 接続確認

前節で設定した内容で PostgreSQL にアクセスできるかを確認しておく。

$ export SERVER_CA=PATH/TO/server-ca.pem
$ export SSL_CERT=PATH/TO/client-cert.pem
$ export SSL_KEY=PATH/TO/client-key.pem
$ export HOST=xxx.xxx.xxx.xxx # PostgreSQL の「パブリック IP アドレス」
$ export USER=postgres # PostgreSQL の接続ユーザー名（デフォルト: postgres）
$ export DATABASE=postgres # PostgreSQL の接続先データベース（デフォルト: postgres）

$ psql "\
  sslmode=verify-ca \
  sslrootcert=${SERVER_CA} \
  sslcert=${SSL_CERT} \
  sslkey=${SSL_KEY} \
  hostaddr=${HOST} \
  user=${USER} \
  dbname=${DATABASE} \
"
# データベースに接続されているか確認。

PostgreSQL 接続ユーザー設定（GCP ユーザー設定から）

Google Cloud Platform > SQL > [インスタンス] > 「ユーザー」

自分で PostgreSQL 環境から接続用ユーザーの設定をしても大丈夫だと思うけど、GCP 側から設定することもできた。
ただし、GCP 側からユーザーを作成すると「cloudsqlsuperuser」の権限（ロール）が付与される⁴。
権限をより狭める必要があれば⁵、別に接続したユーザーから権限をいじる。

• 「ユーザーアカウントを作成」ボタンをクリックする。
  • 「ユーザー名」と「パスワード」を入力する。
  • 右下の「作成」リンクをクリックする。

前節の「PostgreSQL 接続確認」で接続した手順で PostgreSQL データベースに接続する。

postgres=> \du # ユーザーの情報を表示する
                                              List of roles
       Role name       |                         Attributes                         |      Member of      
-----------------------+------------------------------------------------------------+---------------------
 cloudsqlsuperuser     | Create role, Create DB                                     | {}
 <作成したユーザー名>     | Create role, Create DB                                     | {cloudsqlsuperuser}


# ↑ cloudsqlsuperuser のメンバーになっている
# cloudsqlsuperuser は、Create role, Create DB の権限を持つ。

# ユーザーから権限（ロール）を外す
postgres=> REVOKE cloudsqlsuperuser FROM <作成したユーザー名>;

postgres=> \du
       Role name       |                         Attributes                         |      Member of      
-----------------------+------------------------------------------------------------+---------------------
 cloudsqlsuperuser     | Create role, Create DB                                     | {}
 <作成したユーザー名>     | Create role, Create DB                                     | {}

# ↑ 作成したユーザーから cloudsqlsuperuser のメンバー権限（ロール）が外れている。

Rails アプリケーション側の設定

Gemfile 修正

Gemfile に gem 'pg' を追加。

Gemfile

# ...
gem 'pg' # , '~> x.x.x' # 必要に応じてバージョン制限

config/database.yml 修正

SSL 接続と各種証明書を設定する。
ここでもセキュリティリスクを減らすために環境変数から情報を取得するようにする。

config/database.yml

#...

development:
  <<: *default
  database: RAILS_DEVELOPMENT_DATABASE # 開発用データベース名
  username: RAILS_DEVELOPMENT_USER_NAME # 開発用ユーザー名
  password: RAILS_DEVELOPMENT_PASSWORD # 開発用ユーザーパスワード
  host: <%= ENV.fetch("RAILS_DATABASE_HOST_DEVELOPMENT"){ "localhost" } %>

  # SSL 関連
  sslmode: 'require'
  sslrootcert: <%= ENV.fetch("RAILS_DATABASE_SSL_ROOT_CERT_DEVELOPMENT"){ "server-ca.pem" } %>
  sslcert: <%= ENV.fetch("RAILS_DATABASE_SSL_CERT_DEVELOPMENT"){ "client-cert.pem" } %>
  sslkey: <%= ENV.fetch("RAILS_DATABASE_SSL_KEY_DEVELOPMENT"){ "client-key.pem" } %>

test:
  <<: *default
  database: RAILS_TEST_DATABASE # テスト用データベース名
  username: RAILS_TEST_USER_NAME # テスト用ユーザー名
  password: RAILS_TEST_PASSWORD # テスト用ユーザーパスワード
  host: <%= ENV.fetch("RAILS_DATABASE_HOST_TEST"){ "localhost" } %>

  # SSL 関連
  sslmode: 'require'
  sslrootcert: <%= ENV.fetch("RAILS_DATABASE_SSL_ROOT_CERT_TEST"){ "server-ca.pem" } %>
  sslcert: <%= ENV.fetch("RAILS_DATABASE_SSL_CERT_TEST"){ "client-cert.pem" } %>
  sslkey: <%= ENV.fetch("RAILS_DATABASE_SSL_KEY_TEST"){ "client-key.pem" } %>

環境変数設定

環境変数から設定を読み込むようにしたので、.bashrc やシェルスクリプトを利用して環境を整える。

.bashrc

# PostgreSQL on GCP for Rails
# 各種証明書へのファイルパスは、環境に応じて修正が必要。

# development 環境
export RAILS_DATABASE_HOST_DEVELOPMENT=xxx.xxx.xxx.xxx # PostgreSQL の「パブリック IP アドレス」
export RAILS_DATABASE_SSL_ROOT_CERT_DEVELOPMENT=server-ca.pem
export RAILS_DATABASE_SSL_CERT_DEVELOPMENT=client-cert.pem
export RAILS_DATABASE_SSL_KEY_DEVELOPMENT=client-key.pem

# test 環境
export RAILS_DATABASE_HOST_TEST=xx.xxx.xxx.xxx # PostgreSQL の「パブリック IP アドレス」
export RAILS_DATABASE_SSL_ROOT_CERT_TEST=server-ca.pem
export RAILS_DATABASE_SSL_CERT_TEST=client-cert.pem
export RAILS_DATABASE_SSL_KEY_TEST=client-key.pem

そのほか

• 必要（予算）に応じて、開発環境・テスト環境・本番環境用に GCP の PostgreSQL インスタンスを分けるなど実施する。

• 開発・テスト用に わざわざ GCP の PostgreSQL インスタンスを使う必要があるのか、熟考する。

• Rails の「config/database.yml」の設定ファイルは、コマンドラインに流し込むオプションを（ほぼ）そのまま yaml 形式にしておけば OK のようで、ここでも「設定より規約」になっていることに感心した。

• 「config/database.yml」ファイル内でも erb 形式で記述できることを 最近 知って感動した（←「己の無知を恥じた」とも言う）。

---

1. WAN 側の IP アドレスを調べるのは、ルーターの「WAN側IPアドレス」やサーバー情報を見ることができるサービス（「サーバー監視/ネットワーク監視サービス 」 など）を利用する。 ↩

2. 「client-key.pem」は、このダイアログを閉じると確認できなくなる。また、このファイルをダウンロードしないと右下の「閉じる」リンクが機能しない（親切？）。 ↩

3. 最下段に PostgreSQL クライアントからの接続方法（コマンドライン）が表示されているので、参照すると良い（親切！）。 ↩

4. 「cloudsqlsuperuser」に付与された権限は「Create role, Create DB」。 ↩

5. 「Create role」権限を外しておいたほうがいいのか？ ↩