psql実行時のパスワード入力省略方法

タイトルでもう分かっちゃてるんですが、
GitLabを用いたCIで、DBへのデータ投入しようとして上手くいかなかったので
その原因と対策をまとめました。

$ psql -h <ホスト名> -U <ユーザー名> < ./test/db.sql
ユーザ <ユーザー名> のパスワード: 
psql: FATAL:  password authentication failed for user "<ユーザー名>"
ERROR: Job failed: exit code 1

対象者

1. GitLab CIの動きをある程度理解している人
2. GitLabの.gitlab-ci.ymlを書いたことがある人
3. PostgreSQLを使ったことがある人

環境

1. GitLab Enterprise Edition 11.6.0-rc7-ee
2. CI用コンテナイメージ python:3.6 / postgres:9.6

原因

上記のログを見たら、「そりゃそうだ」という感じなんですが
パスワード入力を求められてるのに.gitlab-ci.ymlでパスワードを入力するスクリプトがなかったからでした

使用しているPostgreSQLコンテナではデフォルトで
ホスト名・DB名・ログインユーザー・パスワードが設定されています

.gitlab-ci.ymlでデプロイも行っている関係上、デフォルトの設定を変更していました

.gitlab-ci.yml

variables:
    POSTGRES_DB: <DB名>
    POSTGRES_USER: <ユーザー名>
    POSTGRES_PASSWORD: <パスワード>

※ホスト名は変更するのが大変そうと同僚から聞いたので
今回は調査対象から外しました

対策

次の2つを案として検討しました

1. .pgpassまたはPGPASSFILEを利用
2. PGPASSWORDを利用

.pgpassまたはPGPASSFILEを利用

.pgpassや環境変数PGPASSFILEで指定したファイルにDB接続情報を記載すると
対話式でパスワードを聞かれることなくpsqlコマンドを実行することができます

<ホスト名>:<ポート番号>:<DB名>:<ユーザー名>:<パスワード>

この時、ファイル権限を600に変更しないとエラーが発生しちゃいます

WARNING: password file "<ファイルパス>" has group or world access; permissions should be u=rw (0600) or less

PGPASSWORDを利用

PostgreSQL 9.6.5文書の32.14. 環境変数によると

PGPASSWORDはpassword接続パラメータと同様に動作します。 この環境変数は、一部のオペレーティングシステムではroot以外のユーザがpsコマンド経由で環境変数を見ることができるなど、セキュリティ上の理由から現在では推奨されていません。 代わりに~/.pgpass(32.15. パスワードファイルを参照してください)を使用することを検討してください。

ps eコマンド実行で環境変数見えちゃうからなるべく使わんといてねって事ですね

採用した対策案

最終的には「PGPASSWORDを利用」を採用しました
公式ドキュメントは非推奨って書いてるけど
CIだし本番環境に適用するわけではないので今回のケースでは良しとしました

最初は「.pgpassまたはPGPASSFILEを利用」にしようと思ったのですが
上手くいかなくて「PGPASSWORDを利用」にしました

でも...

実は...

「PGPASSWORDを利用」に変更して、GitLab上でCI出来ること確認した後に判明したのですが、
「.pgpassまたはPGPASSFILEを利用」のやり方を間違えてました💦

/opt/.pgpassに接続情報を書いて、パーミッションも600にしてるのにパスワード入力を求められてました

ただ、後々よく確認するとポート番号が抜けてました😱
かなりショックを受けました。
自分、何してんの...

まとめ

1. 環境変数やファイルに設定することでDB接続時の対話式ログインを回避することができる
2. ドキュメントは落ち着いて読みましょう
   ほんまコレ...

DB周りは知識が浅いのでノウハウ貯めていきたいと思います！