既に出尽くした感はありますが、昔からの PC ユーザほどハマりやすいのではないかと思うので記録します。
データベースへ接続する際の認証手段は「ユーザ」と「パスワード」であると思い込みがちですが、最近の RDBMS では OS 側のユーザで認証するのがデフォルトになっています。
例えば Linux で foo というユーザでログインしていたとします。ユーザを指定せずに mysql や psql を起動すると、同じ foo というユーザでデータベースに接続します。これはこれで簡潔でわかりやすい仕組みなのですが、落とし穴があって Linux 側と RDBMS が同じユーザでないといけない制限が掛かっていたりします。
どういうことかというと、例えば PostgreSQL 9.6 を使っている環境で、ユーザ foo さんが psql をユーザ postgres で使いたいとき、次のようにユーザを指定して接続しても認証で弾かれます。パスワードが正しくても弾かれるので、ハマりやすいポイントです。
foo@host:~$ psql -U postgres
psql: FATAL: Peer authentication failed for user "postgres"
postgres で psql に入るには sudo su – postgres などとして、Linux 側でもユーザ postgres である必要があります。
foo@host:~$ sudo su - postgres
postgres@host:~$ psql
psql (11.1 (Debian 11.1-1.pgdg90+1), server 9.6.10)
Type "help" for help.
postgres=#
この認証の設定は /etc/postgresql/9.6/main/pg_hba.conf にあります。hba は Host Based Authentication の略のようです。
pg_hba.conf
pg_hba.conf を開くと、説明のコメントがほとんどを占めていますが、必要なのは次の部分です。
...(略)...
# Database administrative login by Unix domain socket
local all postgres peer
...(略)...
local all all peer
...(略)...
host all all 127.0.0.1/32 md5
...(略)...ここで “peer” となっているのが peer authentication です。これをパスワード認証にするには password または md5 とします。password は平文通信されるので、ハッシュ化される md5 を選びましょう。
各設定はそれぞれ次のような意味です。
local all postgres peer
ローカルシステム上のユーザが、ユーザ名 postgres を使った peer 認証で全てのデータベース(all)に接続できます。peer 認証なので、実質的にローカルシステムの postgres というユーザが、全てのデータベースに接続できるのと同じです。
local all all peer
ローカルシステム上のユーザは誰でも(2 つ目の all) peer 認証で全てのデータベース(1 つ目の all) に接続できます。
host all all 127.0.0.1/32 md5
127.0.0.1(localhost) からの接続はパスワード認証(md5)されて、全てのユーザ(2 つ目の all) が全てのデータベース(1 つ目の all) に接続できます。
“peer” を “md5″ に書換えて、ロールにパスワードを設定すれば完了です。”sudo system reload postgresql” などとして、設定を反映させるのを忘れないように。
ユーザ(ロール)のパスワード設定
postgres 等の権限があるユーザで psql に入り、ALTER ROLE または ALTER USER を使います。
postgres=# ALTER ROLE role_name ENCRYPTED PASSWORD 'your_password';
そのパスワード認証、必要ですか？
ここまで書いておいて今更ですが、peer 認証の仕組み自体は、分離している PostgreSQL と OS の認証をできるだけ簡素にする、とても良い仕組みです。MariaDB でも同じ手法を用いているため、これからは(あるいはもう既に)この手法が主流になっていくのでしょう。
これが問題になるのは外部、あるいはプログラムからのアクセスでしょう。それについては、デフォルトで 127.0.0.1(localhost) からのアクセスはパスワード認証(md5)に設定されているため、ロールにパスワードを設定するだけで事足ります。
今回は Python から psycopg2 で接続するのが最終目的だったため、ロールにパスワードを設定するだけで要件は満たしました。慣れた手法を使いたくなりますが、それによって時代の潮流から外れるのは、後々になって自分の首を絞めることになることはわかっているので、できる限り避けたいと思っています。