PostgreSQLのユーザをOSと別管理する方法

PostgreSQLのユーザ関連でハマりました。。。

最近業務でPostgreSQLを使うようになったので早速ローカルPCにインストールしてみたのですが、なんかPostgreSQLのシェルにログインできない。。。という状況にお陥りました。
死闘の末なんとかこの状況を打破できたので、備忘録がてら書きました。

ハマるまでにしたこと

インストール

$ sudo apt-get install postgresql

DBの置き場所環境変数設定

デフォルトのまま使うなら必要ない

echo "export PGDATA=/usr/local/var/postgres" >> ~/.bash_profile # DBのパスを環境変数記録ファイルに保存
source ~/.bash_profile # 環境変数を更新

起動

$ postgres -D /usr/local/var/postgres # パスは環境による
# または
$ brew services start postgresql # Macのbrewで入れた場合
# または
$ sudo service postgresql start # Linuxにインストールして適切にパスを通した場合

ログイン

$ psql
psql: FATAL:  database "nognog_nognog" does not exist

自分のユーザが作られていなければ何か別のデフォルトユーザがあるはず・・・！
と、ググるとpostgresというユーザが作られていることがわかりました。

$ psql -U postgres # -Uオプションでユーザを指定 指定なしの場合は$USERとなる
psql: FATAL:  password authentication failed for user "postgres"

詰んだ /(^o^)\ /(^o^)\ /(^o^)\

なんだよそのエラー

認証が通ってないような感じでした。
で、調べてみるとpostgresというユーザはOSに作られているという記事も見つけました。

？？？

OSのユーザとPostgreSQLのユーザを分離して設定

結果としては、PostgreSQLのユーザにpostgresというユーザが作られていただけでなく、OSのユーザにもpostgresが作られていました。
さらに調べてみると、OSのpostgresユーザでPostgreSQLのpostgresユーザとして、しかも同じパスワードでログインしないと弾かれてしまうそうです。こりゃわからん。。。

OSのpostgresユーザに変更

$ su postgres
su: Authentication failure

あれ、変更できない。。。
UNIXは新規ユーザはパスワードを設定しないとログインできないんですね。（知ってたはずなのにハマりました）

$ passwd postgres
Enter new UNIX password: 
Retype new UNIX password: 
passwd: password updated successfully
$ su postgres

これで成功しました。
プロンプトがpostgres $的なものになっていればOKです。

PostgreSQLユーザpostgresのパスワードを設定

OSのpostgresユーザでログインしているのでこのままPostgreSQLにログインします。

$ psql

これでPostgreSQLの対話シェルに入りました。
続いてPostgreSQLユーザpostgresのパスワードを設定します。

> ALTER USER postgres with encrypted password 'PASSWORD'; # セミコロン必要！
ALTER ROLE

ALTER ROLEが表示されれば成功です。

peer認証をパスワード認証に変更

PostgreSQLのシェルは一旦抜けます。あとOSのユーザも自分のユーザに戻ります。

> ^D
$ ^D

自分のユーザに戻ったらpeer認証をパスワード認証に変更します。
peer認証というのが、先述した「OSのユーザ名&パスワードがPostgreSQLのユーザ名&パスワードと一致していないと弾かれてしまう認証システム」です。
セキュアなのですが、実験的な使用においては邪魔でしかないので、それぞれのユーザを分離します。
と言っても、設定ファイルを一部書き換えるだけで完了です。
その設定ファイルがpg_hba.confなのですが、バージョンやOSによってパスが異なります。以下の例では、バージョンが10、OSがUbuntuの場合です。

$ sudo vim /etc/postgresql/10/main/pg_hba.conf # パスはバージョンやOSによって異なる
local   all             postgres                          peer # → md5 に変更

変更したらPostgreSQLを再起動します。

$ sudo service postgresql restart

最後にOSの自分のユーザからPostgreSQLのシェルにpostgresでログインできることを確認します。

$ psql -U postgres

ログインできれば成功です。

終わりに

いくつになってもシェル周りはスムースにこなせませんが、やっぱり解決できると楽しいですね！