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

# 何がしたかったのか？

タイトルそのまま。

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

# GCP 側の設定

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

## GCP 接続設定

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

- パブリック IP ：チェックあり
    - 「承認済みネットワーク」
        - 開発環境の WAN 側 IP アドレスを設定する[^wan_ip]。
        - 目的の PostgreSQL に **アクセス可能な発信元を制限する** ことで、セキュリティリスクを避ける。

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



[^wan_ip]: WAN 側の IP アドレスを調べるのは、ルーターの「WAN側IPアドレス」やサーバー情報を見ることができるサービス（「[サーバー監視/ネットワーク監視サービス ](http://www.cman.jp/network/support/go_access.cgi)」 など）を利用する。



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

[^download_pem]: 「client-key.pem」は、このダイアログを閉じると確認できなくなる。また、このファイルをダウンロードしないと右下の「閉じる」リンクが機能しない（親切？）。
[^access_command]: 最下段に PostgreSQL クライアントからの接続方法（コマンドライン）が表示されているので、参照すると良い（親切！）。

## PostgreSQL 接続確認

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

``` shell
$ 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」の権限（ロール）が付与される[^cloudsqlsuperuser]。
権限をより狭める必要があれば[^revoke_role?]、別に接続したユーザーから権限をいじる。

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

前節の「PostgreSQL 接続確認」で接続した手順で 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 のメンバー権限（ロール）が外れている。

```

[^cloudsqlsuperuser]: 「cloudsqlsuperuser」に付与された権限は「Create role, Create DB」。
[^revoke_role?]: 「Create role」権限を外しておいたほうがいいのか？

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

## Gemfile 修正
Gemfile に ```gem 'pg'``` を追加。

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

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

``` ruby: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 やシェルスクリプトを利用して環境を整える。

``` shell:.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 形式で記述できることを **最近** 知って感動した（←「己の無知を恥じた」とも言う）。