Just do IT

思うは招く

Postgresql + Ubuntu + vagrant でコマンドラインからSQLファイルを実行する

PostgreSQL

環境

問題

vagrantにログインし、該当ディレクトリへ移動。 次のコマンドを叩く。

  • ユーザー: postgres
  • dbname: memo_app
  • 実行ファイル名: memos.sql
psql -U postgres -d memo_app -f memos.sql

次のエラーが返る。

psql: FATAL:  Peer authentication failed for user "postgres"

なんじゃこりゃ。 調べてみると、次のことがわかった。

  • postgreSQLはhbaという機能が動いている
    • host base authentication
  • postgreSQLはデフォルトでpeer認証がオンになっている
    • peer認証では、postgreSQLのユーザー名と、Unix（ここではUbuntu）側のユーザー名が一致していないとエラーになって実行できない
  • つまり、peer認証を使わない設定にすればいい

解決

postgreSQLの認証設定を変更することで解決した。

前準備

vagrantにログインし、postgresユーザーとして、sudo権限でログインする。 ※初期設定で出来るようになっているはず。

sudo su - postgres

PostgreSQLのフロントエンドを起動。

psql

postgresユーザーのパスワードを変更。

alter role postgres with password 'パスワード';

※自分なりのパスワードで。

これが出たら成功。

ALTER ROLE

peer認証を変更する

以下のファイルを見てみる。

sudo cat /etc/postgresql/10/main/pg_hba.conf

※私はPostgreSQL10を使用しているため上記のファイルパスになっているが、もしPostgreSQL9.6を使用しているなら、次のようなパスになる。

/etc/postgresql/9.6/main/pg_hba.conf

するとこんなのが表示される。

# DO NOT DISABLE!
# If you change this first entry you will need to make sure that the
# database superuser can access the database using some other method.
# Noninteractive access to all databases is required during automatic
# maintenance (custom daily cronjobs, replication, and similar tasks).
#
# Database administrative login by Unix domain socket
local   all             postgres                                peer

# TYPE  DATABASE        USER            ADDRESS                 METHOD

# "local" is for Unix domain socket connections only
local   all             all                                     peer
# IPv4 local connections:
host    all             all             127.0.0.1/32            md5
# IPv6 local connections:
host    all             all             ::1/128                 md5
# Allow replication connections from localhost, by a user with the
# replication privilege.
local   replication     all                                     peer
host    replication     all             127.0.0.1/32            md5
host    replication     all             ::1/128                 md5

開いていたファイルを閉じて、sudo権限で編集する。vimを使用。

sudo vim  /etc/postgresql/10/main/pg_hba.conf

2箇所を変更

変更箇所は2つ。

# Database administrative login by Unix domain socket
local   all             postgres                                peer

これを次のように変更。

# Database administrative login by Unix domain socket
# local   all             postgres                                peer
local all postgres md5

※もともとあったpeer認証の部分をコメントアウトし、md5という認証に変更している。

同様に、以下の箇所を見つけて。

# "local" is for Unix domain socket connections only
local   all             all                                     peer

このように変更する。

# "local" is for Unix domain socket connections only
# local   all             all                                     peer
local all all md5

変更前と変更後でスペースに違いがあるが気にしなくていい。 もちろん、気になるならもともとの設定みたいにスペースを合わせてもいい。

postgreSQLを再起動

restartする。

sudo service postgresql restart

最初にやりたかったコマンドを叩く。

psql -U postgres -d memo_app -f memos.sql

エラーは返らず、成功する。

postgreSQLログイン後にSQLファイルを実行する方法

psqlでログインすると、以下のコマンドでヘルプが見れる。

\?

するとこれが見つかる。

\i FILE                execute commands from file

参照