PostgreSQL でパスワード認証を使う


PostgreSQL にパスワード認証を導入した際のメモです。 psql を使って検証していますが、他のクライアント・アプリケーションにも応用できると思います。各コマンドや設定ファイルには適宜マニュアルへのリンクを張っていますので、必要に応じて参照してください。

例示におけるプラットフォームには CentOS 6.3 を使っています。 他の OS やディストリビューションをお使いの方は、yumなど固有のコマンド名を適宜読み替えてください。

PostgreSQL サーバが動くホストを pghost01、psql を動かすホストを client02、接続時のデータベースユーザ名を pguser01 としています。コマンドラインのプロンプトは“ユーザ名@ホスト名:カレントディレクトリ”です。常に今自分がどのホスト上で誰の権限で操作しているのかを意識してください。

1.インストール

PostgreSQL が未導入の場合、まずは OS のパッケージ管理コマンドでパッケージの導入を行います。

root@pghost01:~# yum install postgresql-server

インストール自体はこれで終わりです。しかしこのままではデータベースクラスタ(データベース格納領域)がないのでサービスが正常に起動できません。

root@pghost01:~# service postgresql start
  
/var/lib/pgsql/data is missing. Use "service postgresql initdb" to initialize the cluster first.
                                                           [FAILED]

最低限、initdb を動かしてデータベースクラスタを作成しておきます。 データベースクラスタは(RHEL系のパッケージでは)/var/lib/pgsql/data 配下に作られます。

root@pghost01:~# service postgresql initdb
Initializing database:                                     [  OK  ]
root@pghost01:~# service postgresql start
Starting postgresql service:                               [  OK  ]
root@pghost01:~# service postgresql status
postmaster (pid  3837) を実行中...

2.まずは接続してみましょう

psql コマンドで接続時にユーザ名や接続先等を明示しない場合は、接続先ホストは localhost、データベースユーザ名は(Linux の)カレントユーザ名、データベース名はユーザ名と同じ名前のデータベースを指定したものと見なされます。 この例では接続先ホスト、ユーザ名、データベース名を明示しています。

user02@client02:~$ psql -h pghost01 -U pguser01 db01
psql: サーバに接続できませんでした: 接続を拒否されました。
サーバはホスト"pghost01"で稼動していますか?
また、ポート5432でTCP/IP接続を受け付けていますか?

このメッセージはサーバではなく psql 自身が出しており、クライアントホスト client02 からサーバホスト pghost01 (またはその上で動いているサーバープロセス)まで接続要求パケットが届いていないことを表しています。認証以前の問題です。

これ以降、いろいろな設定を追加しながら上記と同じコマンドを投入し、エラーメッセージがどう変わっていくかを検証します。

接続できない原因として、クライアントとサーバ間のファイアーウォール、もしくはサーバ上の iptables の設定に起因することも考えられます。これらに問題がないことを確認してから次に進んでください。

3.他のホストからの接続を許可する

PostgreSQL のデフォルトでは PostgreSQL サーバ自身 (localhost) からの接続のみしか許可していないので、他ホストからの接続を受け付けるように postgresql.conf の設定を変更します。

root@pghost01:~# netstat -tln|grep 5432
tcp        0      0 127.0.0.1:5432              0.0.0.0:*                   LISTEN
tcp        0      0 ::1:5432                    :::*                        LISTEN
(localhost(127.0.0.1 / ::1) からの接続のみを受け付ける(LISTEN)ようになっている)
root@pghost01:~# cd /var/lib/pgsql/data
root@pghost01:/var/lib/pgsql/data# grep -C 2 listen_addresses postgresql.conf
# - Connection Settings -

#listen_addresses = 'localhost'         # what IP address(es) to listen on;
                                        # comma-separated list of addresses;
                                        # defaults to 'localhost', '*' = all
root@pghost01:/var/lib/pgsql/data# vim postgresql.conf
(上記の行を修正し、行頭の # を外して有効にする)
root@pghost01:/var/lib/pgsql/data# grep listen_addresses postgresql.conf
listen_addresses = '*'          # what IP address(es) to listen on;
root@pghost01:/var/lib/pgsql/data~# service postgresql restart
root@pghost01:/var/lib/pgsql/data# netstat -tln|grep 5432
tcp        0      0 0.0.0.0:5432                0.0.0.0:*                   LISTEN
tcp        0      0 :::5432                     :::*                        LISTEN
(LISTEN アドレスが 0.0.0.0(ANY)になった)

4.特定のホスト/ユーザからの接続を許可する

user02@client02:~$ psql -h pghost01 -U pguser01 db01
psql: FATAL:  ホスト"10.30.8.11"、ユーザ"pguser01"、データベース"db01、SSL無効用のエントリがpg_hba.confにありません。

このメッセージは、pg_hba.conf(Host Based Authentication - ホストベースの認証設定ファイル)にマッチするエントリがないことを表しています。デフォルトでは有効な接続元は localhost からのみになっています:

root@pghost01:/var/lib/pgsql/data# grep ^[lh] pg_hba.conf
local   all         all                               ident
host    all         all         127.0.0.1/32          ident
host    all         all         ::1/128               ident

必要なエントリを追加します。以下の例ではデータベース、接続時ユーザ名、クライアントIPを個別に指定していますが、データベースおよびユーザにはそれぞれ ALL を指定することも可能です。ファイルを書き換えたら設定の再読み込みを行って変更を反映します。

root@pghost01:/var/lib/pgsql/data# vim pg_hba.conf
(末尾に1行追加。10.30.8.11はclient02のIPアドレス)
root@pghost01:/var/lib/pgsql/data# tail -1 pg_hba.conf
host    db01        pguser01    10.30.8.11/32         md5
root@pghost01:/var/lib/pgsql/data~# service postgresql reload

5.ユーザを追加し、パスワードを設定する

user02@client02:~$ psql -h pghost01 -U pguser01 db01
psql: FATAL:  ユーザ"pguser01"のパスワード認証に失敗しました。

少なくともpg_hba.conf のエントリのマッチまでは成功し、実際に認証を確認しようとして失敗しています。本当は pguser01 というユーザがないからエラーになっているのですが、表示上は「そのようなユーザはない」というエラーとはなりません。これはセキュリティの観点から、その名前のユーザが存在するかどうかも明示しないようにしているのだと考えられます。

次は実際に pguser01 というユーザを作って試してみます。パッケージで導入した場合はデフォルトでは postgres というデータベース管理ユーザが作られているので、そのユーザとして新たなデータベースユーザを作成します。あえてかなり低い権限にしています。

root@pghost01:# su - postgres
postgres@pghost01:~$ createuser -D -E -R -S -P pguser01
新しいロールのパスワード:********
もう一度入力してください:********

createuser の引数の意味は以下の通りです:

念のためパスワードを確認してみようとしましたが、暗号化されていて確認できませんでした:

postgres@pghost01:~$ psql postgres
psql (8.4.12)
"help" でヘルプを表示します.
postgres=# select usename,passwd from pg_user where usename='pguser01';
 usename  |  passwd
 ----------+----------
  pguser01 | ********
  (1 行)

6.使用するDBを用意する

user02@client02:~$ psql -h pghost01 -U pguser01 db01
ユーザ pguser01 のパスワード:********
psql: FATAL:  データベース"db01"は存在しません

やっとパスワード認証が通った模様です。しかし、まだ指定したデータベースを作成していないのでログインできません。インストール直後のデータベースは以下のようになっています:

root@pghost01:# su - postgres
postgres@pghost01:~$ psql -l
                                     データベース一覧
名前      |  所有者  |  エンコーディング   |  照合順序   | Ctype(変換演算子) |      アクセス権
-----------+----------+------------------+-------------+-------------------+-----------------------
postgres  | postgres | UTF8             | ja_JP.UTF-8 | ja_JP.UTF-8       |
template0 | postgres | UTF8             | ja_JP.UTF-8 | ja_JP.UTF-8       | =c/postgres
                                                                          : postgres=CTc/postgres
template1 | postgres | UTF8             | ja_JP.UTF-8 | ja_JP.UTF-8       | =c/postgres
                                                                          : postgres=CTc/postgres
(3 行)

スーパーユーザー(postgres)で、createdb コマンドを使ってデータベースを作成します。これでやっとログインまでたどり着きました。

postgres@pghost01:~$ createdb db01
user02@client02:~$ psql -h pghost01 -U pguser01 db01
ユーザ pguser01 のパスワード:********
psql (8.4.12)
"help" でヘルプを表示します.

db01=>

7.パスワードを自動入力する

最後にパスワード入力を省略する仕組みを入れてみましょう。ホームディレクトリの直下に.pgpassというファイルを置いておくと、psql などのクライアントプログラムはパスワードをこのファイルから読み取ってくれます。"#"で始まる行はコメントです。Windows の場合のパスワードファイルのパスは %APPDATA%\postgresql\pgpass.conf となります。

user02@client02:~$ vim ~/.pgpass
user02@client02:~$ cat ~/.pgpass
pghost01:5432:db01:pguser01:himitsu
# hostname:port:database:username:password
user02@client02:~$ chmod 600 ~/.pgpass
user02@client02:~$ psql -h pghost01 -U pguser01 db01
(パスワードを聞かれない)
psql (8.4.12)
"help" でヘルプを表示します.

db01=>

Go previous Go ahead Go up