| [ホーム] -> [Aache + PHP + PostgreSQL 実験室] |
PostgreSQL にアクセスするには、UNIX ドメインソケット経由と、TCP/IP ソケット経由の２種類があります。どちらを使っているかというと、特に設定していない場合は UNIX ドメインソケットのみ使っているかと思います。UNIX ドメインソケットを使っている場合は、たぶん /tmp/.s.PGSQL.5432 というファイルがあるはずです。
UNIX ドメインソケット経由の場合、PostgreSQL が動いているマシンにログインしなければならないので、比較的安全と言えます。この場合、アクセスの制御は、OS レベルでしてくれるからです。それに対して、TCP/IP ソケット経由の場合、他のマシンからでも PostgreSQL にアクセスできる反面、PostgreSQL 自身でアクセス制御を行う必要があります。
その、アクセス制御を設定するファイルが、pg_hda.confファイルです。これは、PostgreSQL の data ディレクトリに入っています。ファイルを開いてみてください。シャープ「#」で始まっている行はコメントです。デフォルトでは、次の２行が有効になっているのではないでしょうか。
local all trust host all 127.0.0.1 255.255.255.255 trust
一行めの local は、UNIX ドメインソケットの場合の設定で、２行めの host は、TCP/IP ソケットの場合の設定です。
UNIX ドメインソケットの場合、パラメータは三つあり、一つめが UNIX ドメインソケットを表す「local」です。２番目のパラメータの「all」は、どのデータベースに対するかの指定で、この場合は all になっているので、全てのデータベースが対象になっています。３番目のパラメータの trust は、「無条件に許可する」という意味です。したがって、「UNIX ドメインソケットの場合は、全てのデータベースに認証なしで接続できる」という意味になっています。
TCP/IP ソケットの場合は、パラメータが５つあります。２番目のパラメータは、UNIX ドメインソケットと同じで、対象となるデータベースです。３番目のパラメータは、アクセスを許可する IP アドレスで、マシンのアドレスかネットワークアドレスを指定できます。４番目は、３番目の IP アドレスに対するネットマスクです。デフォルトでは、IP アドレス 127.0.0.1、ネットマスク255.255.255.255 なので、同じマシンのループバックアドレス（127.0.0.1）からしか接続することはできません。
ちなみに 127.0.0.1 というアドレスは、マシン内部で利用する特別な IP アドレスで、自分自身を指しています。TCP/IP が利用できるマシンなら必ず自分自身に対して、この 127.0.0.1 というアドレスが振られています。試しに telnet 127.0.0.1 としてみれば、自分自身に接続されることが分かると思います。この IP アドレスのことをループバックアドレスと言います。
ところが実際は、同じマシンからでも、TCP/IP ソケット経由ではアクセスできないと思います。これは、PostgreSQL が TCP/IP ソケット経由のアクセスを待ち受けていないためです。この待ち受けを有効にするには、postgresql.conf ファイルを編集する必要があります。
#tcpip_socket = false tcpip_socket = true
tcpip_socket という項目に true をセットします。その後 PostgreSQL を再起動します。
> pg_ctl restart
これで、TCP/IP ソケット経由で待ち受けをするようになったはずです。試しに TCP/IP ソケット経由で接続してみましょう
これは UNIX ドメインソケット経由 > psql template1 これは TCP/IP ソケット経由 > psql -h 127.0.0.1 template1
接続できましたか？ これで、後は他のマシンからもログインできるようにするには、pg_hda.conf の設定をするだけです。ただし、容易に IP アドレスを変えたりしてはいけません。それだけだと、パスワードも聞いてこないので、だれでも（データベースに作成されてるユーザを騙れば）アクセスできてしまいます。そのため、５番目のパラメータも変更する必要があります。何種類かあるのですが、これらについては次で説明します。
PostgreSQL にパスワード認証させるには大きく二つの方法があります。データベース内にパスワードを持つ方法と、外部のファイルにパスワードを持つ方法です（それ以外では、Kerberos を使う方法もありますが、説明はしません）。
まずデータベース内にパスワードを持つ方法ですが、ここでは説明上、これをデータベース認証と呼ぶことにします。最初に、PostgreSQL にユーザを作成しましたが、この状態だと、そのユーザにはパスワードは設定されていません。しかし、alter user を使えば、パスワードを設定することができます。あるいは、ユーザ作成時に -W オプションを付けて、最小からパスワードを設定することも出来ます。
=> alter user jibun with password 'secret';
この様にすると、ユーザ jibun に、パスワード secret を設定できます。データベースのスーパーユーザ（ユーザを作成する権限のあるユーザ、psql のプロンプトが「=#」となっている）は誰のパスワードでも変更できますが、その他のユーザは、自分のパスワードだけを変更できます。
このタイプの認証のメリットは、ユーザが自分でパスワードを設定できる点です。デメリットは、データベース内にパスワードが平文で格納されるので、pg_shadow テーブルを見ることのできる PostgreSQL のスーパーユーザに、パスワードが読まれてしまう点です。
しかし、7.2 から、パスワードを暗号化して格納できるようになりました。alter user するときに、encrypted を指定するようにします。
=> alter user jibun with encrypted password 'secret';
これで、パスワードは md5 で暗号化された（正確には暗号化とは言わないか）のでかなり安心です。しかし、利用者全員に encrypted を付けてパスワードを変更してくれと言うのは大変です。そこで、デフォルトで暗号化して格納するように設定を行います。postgresql.conf に次の行を追加します。
#password_encryption = false password_encryption = true
これで encrypted を付けなくても暗号化されるようになりました。
そしたら、pg_hba.conf ファイルを開き、次のように修正します。
local all trust #host all 127.0.0.1 255.255.255.255 trust host all 127.0.0.1 255.255.255.255 md5
５番目のフィールドを trust から md5 に変更しました。こうすると、ループバックで接続するときにパスワードを聞いてくるようになります。pg_hba.conf ファイルを、変更した後は再起動が必要です（再起動しなくても大丈夫なようなことがマニュアルには書いてあるのですが・・・）。試してみましょう。
> pg_ctl restart > psql -h 127.0.0.1 template1
うまく接続できたでしょうか。ちなみに、データベースに md5 で暗号化して格納したから、pg_hba.conf も md5 と指定したわけではありません。実際、データベースに平文でパスワードを格納していても、この設定で接続できます。pg_hba.conf の md5 は、PostgreSQL クライアントとサーバがパスワードを交換するときに、md5 でやりとりするという設定なのです。従って、ネットワークを盗聴されていても平文が流れない分、安心ではあります。
この設定を行うと、パスワードを格納しているユーザ全員が接続することが可能になります（パスワード未設定のユーザは接続できません）。でも、ある特定のユーザだけに接続を許可したい場合は、許可したいユーザのリストを pg_hba.conf で一緒に指定します。
local all trust #host all 127.0.0.1 255.255.255.255 trust host all 127.0.0.1 255.255.255.255 md5 user.list
user.list（ファイル名は何でもいいのですが）内には、一行に一人ずつ接続可能なユーザを記述します。
この md5 を使った方法は非常にお勧めなのですが、一つだけ欠点があって、それは 7.2 からのみ使えるという点です。もし、7.2 より前のバージョンのクライアントから接続しようとした場合、接続できなくなってしまいます。この場合は crypt 認証を使います。
local all trust #host all 127.0.0.1 255.255.255.255 trust host all 127.0.0.1 255.255.255.255 crypt
一応パスワードは、ネットワーク上を簡単な暗号化を使って流れますが、あまり安全ではありません。また、データベースにパスワードを平文で格納しておく必要があります。md5 で格納されている場合は、unencrypted を付けて平文で格納し直してください。従ってこの場合、PostgreSQL のスーパーユーザはパスワードを見ることが可能となってしまいます。一応、md5 と同じように、接続できるユーザのリストを指定することは可能です。
もう一つ、外部のファイルにパスワードを持つ方法です。このパスワードファイルは、pg_passwd コマンドを使って作成します。
> pg_passwd $PGDATA/password.list File "/usr/local/pgsql/data/password.list" does not exist. Create? (y/n): y Username: jibun New password:pass phrase Re-enter new password:pass phrase
この様にすると、password.list ファイルに jibun というユーザと、それに対応するパスワードが追加されます。ファイルを使うメリットは、パスワードが crypt によって多少なり暗号化（符号化というのが正しいレベルだけど・・・）されるので、中を見られてもパスワードが推測しにくい点です。デメリットは、各ユーザがパスワードを設定できない点です（setuid ビットの立ったラッパーを作るって手もありますけどね）。まあ、逆の見方をすれば、ユーザがパスワードを勝手に変えることが出来ない、というメリットなのかもしれません。
pg_hba.conf ファイルは次のように設定します。
host all 127.0.0.1 255.255.255.255 password password.list
５番目のフィールドに password と指定することにより、パスワード認証になります。６番目のフィールドには、pg_passwd コマンドを使って作成したファイルを指定します。
パスワードファイルのパスワードではなく、データベース内のパスワードを利用することも出来ます。パスワードファイルの中身は、「ユーザ名:パスワード」という行です。この「パスワード」はパスワードを crypt した文字が入っていますが、この部分をプラス「+」一文字に書き換えます。
> cat password.list jibun:+ dareka:AaJEUQ6g1Wa3c > cat pg_hba.conf local all trust #host all 127.0.0.1 255.255.255.255 trust host all 127.0.0.1 255.255.255.255 password password.list
これで、ユーザ jibun は、データベース内のパスワードを利用し、dareka さんは、パスワードファイル内のパスワードで認証されます。データベース内のパスワードを使う場合は、平文で格納されていないといけません。
この様に、認証方法には数パターンありますが、一番いいのはやはり md5 を使う方法でしょう。どうしても古いバージョンを使っているマシンからアクセスを許可させたい場合は、その IP アドレスに対する設定を特別に用意するのがいいでしょう。一応こんな感じでしょうか。
> cat pg_hba.conf local all trust host all 127.0.0.1 255.255.255.255 trust host wwwdb 192.168.0.0 255.255.255.0 md5 wwwdb.user host pj1db 192.168.43.0 255.255.255.0 password pj1db.password host pj1db 192.168.43.21 255.255.255.255 crypt pj1db_md5.user host pj1db 192.168.43.16 255.255.255.240 md5 pj1db_md5.user > cat pj1db.password demo:AaJEUQ6g1Wa3c > cat pj1db_md5.user andy jack fujrock
データベース wwwdb に対しては wwwdb_md5.user にリストされているユーザで 192.168.0.0 - 192.168.0.255 からアクセスできます。このユーザは全員データベース認証です。
データベース pj1db に対しては、ユーザ demo がパスワードファイルによる認証で、192.168.43.0 - 192.168.43.255 からアクセスできます。このユーザはデモとして使わせるユーザなので、勝手にパスワードを変えられて欲しくないのです。また、192.168.43.21 のマシンには古いバージョンがインストールされているため、このマシンからアクセスする場合は crypt によるアクセスにします。それ以外の 192.168.43.16 - 192.168.43.31 からのアクセスは md5 による接続です。pj1db_md5.user にリスとされているユーザはデータベース認証ですが、192.168.43.21 のマシンを利用する人は平文でパスワードを格納しておく必要があります。ただし、その人は他のマシンからログインすることも可能です。
実はここまで来る途中で、すでに気付いているかもしれません。次のデフォルトの設定が危険だということを。
local all trust host all 127.0.0.1 255.255.255.255 trust
これは先ほども説明したとおり、同じマシンからなら、認証なしでデータベースに接続できるのです。問題は、同じマシンからなら、誰でも、好きなユーザとして接続できる 点です。psql コマンドのオプションに -U ユーザ名 と言うオプションがありますが、これは、そのユーザとして PostgreSQL に接続できることを意味します。
例えば arai さんが arai というデータベースを作成し、そこにテーブルを作って秘密のデータを保存しておいたとします。テーブルも自分以外読めないように設定してあります（デフォルトでそうなっている）。ところが、そのマシンにログインできる hizuya さんが、psql -U arai arai とすると、あたかも arai さんが接続しているかのように振る舞い、秘密のデータを見てしまうことができます。
これは、「データはみんなの共有財産」「他人にデータを消されても問題ない」と悟っている人以外はちょっとまずいことになります。なにせ、hizuya さんのくせに、arai さんのごとく振る舞えると言うことは、delete 文は言うに及ばず、drop database だって出来るのですから。
この場合、たとえ local に対しても trust ではなく md5 にしないといけません。
local all md5 host all 127.0.0.1 255.255.255.255 md5
これで万全でしょうか。そうでもないですね。確かにこれならパスワードを知らない限り他人になりすますことは出来なくなりました。しかし、データベースに接続できる人なら誰でも、好きなデータベースに接続できます。他人の作ったテーブルは基本的にその人以外操作できませんが、他人の作ったデータベースに勝手にテーブルを作ることは可能です。知らないうちに他人にテーブルを作られるのはちょっと気分が悪いでしょう。従って、データベース毎にユーザリストを用意してあげた方がいいですね。
local template0 md5 template.user local template1 md5 template.user local wwwdb md5 wwwdb.user host template0 127.0.0.1 255.255.255.255 md5 template.user host template1 127.0.0.1 255.255.255.255 md5 template.user host wwwdb 127.0.0.1 255.255.255.255 md5 wwwdb.user
少々神経質な気もしますが、セキュリティを考える場合は、思いつく限りの手を打っておくべきです。まあ、実際問題としては、データベースを追加するときに、pg_hba.conf を修正しないといけないので、PostgreSQL の再起動が必要になる点ですか・・・。ユーザリストにユーザを追加したりする場合は、何もしなくてもすぐに反映されるので、頻繁にデータベースを作ったりしなければ、このくらいやってもいいのかもしれません。
Linux の場合（Solaris もかな？）で PostgreSQL を --with-pam オプション付きでコンパイルした方は、md5 のところに、pam が指定できます。でも試した限り、pam 経由でパスワード認証することは出来ませんでした。どうも pam にパスワードが渡ってないみたいですが・・・。pam のパスワード認証以外の設定（例えばユーザの有効期限とか）は、きちんと動くみたいです。
別のマシンで稼働している PostgreSQL サーバに対して、TCP/IP を使って接続できることは説明しましたが、データはそのまま流れてしまうので、大事なデータを扱っているときは、ちょっと気になる人もいると思います。PostgreSQL では、このデータのやりとりを、SSL を使って暗号化することが出来ます。
SSL っていうと、Web サーバで SSL を使うのが有名ですよね。https:// で始まる URL にアクセスすると、その間はデータが暗号化されるので、ユーザ登録や、ネットバンキングなどで利用されているものと全く同じです。
SSL を使えるようにするには、SSL が利用できるようにコンパイルされていなといけません。コンパイルの configure を実行するときに、--with-ssl オプションを付ける必要があります。で、このときに、OpenSSL がインストールされている必要があります。OpenSSL は、RedHat から正規の RPM もありますし、インストールと作成方法を別途用意しているので、気力があったら試してみてください（個人的には、次の SSH による暗号化をお進めするので、これをやる必要はそんなにありません）。
作成したサーバ証明書と秘密鍵ファイルを、定位置にコピーします。それぞれを、PostgreSQL の data ディレクトリに入れて置く必要があります。ファイル名は、秘密鍵ファイルが server.key、サーバ証明書が server.crt でなければなりません。
> cp certnokey.pem $PGDATA/server.key > cp cert.crt $PGDATA/server.crt
その後、SSL を使うようにするために、postgresql.conf を修正します。
#ssl = false ssl = true
この後 PostgreSQL を再起動すると、SSL が有効になります。SSL は当然 TCP/IP を使ったときに利用されますが、このときのポートは、SSL を使わないときと同じポートになります。クライアントが接続してきたときに、SSL が使えれば SSL を使い、使えないようであれば使わないで今までと同じように接続します。SSL で接続したときは、psql コマンドの場合だと、次のように表示されます。
> psql -h 127.0.0.1 wwwdb
Password:pass phrase
Welcome to psql, the PostgreSQL interactive terminal.
Type: \copyright for distribution terms
\h for help with SQL commands
\? for help on internal slash commands
\g or terminate with semicolon to execute query
\q to quit
SSL connection (cipher: DES-CBC3-SHA, bits: 168)
wwwdb=>
この様に、SSL connection とメッセージが出ます。もしこの様にならないのであれば、サーバとクライアントのどちらかが SSL が使えないのかもしれません。両方とも --with-ssl オプション付きでコンパイルしてないといけません。
また、SSL を強制して、SSL しか受け付けないような設定も可能です。pg_hba.conf の該当する行の host を hostssl と変更します。
#host all 127.0.0.1 255.255.255.255 md5 hostssl all 127.0.0.1 255.255.255.255 md5
これで、ループバック（127.0.0.1）からの接続は必ず SSL を使うようになります。psql -h 127.0.0.1 template1 とかコマンドを打って確かめてみましょう。
この様に SSL を使うようにすれば、途中を流れるデータを覗き見される心配はなくなります。SSL のもう一つの目的に、「サーバが偽物でないか証明する」というのがあります（だから「サーバ証明書」を作ったのです）。先ほどの説明だと、サーバ証明書を自己署名してしまったので、本当にその証明書が正しいかは証明できていません。本当は「CA（認証局）」という機関に署名してもらわないといけないのですが、PostgreSQL だけだと、そこまで本格的にすることはないのでは、と思います。Apache で SSL を使う方法に関しては、次で説明します。
SSL 以外でも、SSH を利用しても、データのやりとりを暗号化することができます。SSH は主に、telnet の代わりになるプログラムで、ssh コマンドを使って、他のマシンにログインできます。一般的に使われているであろう OpenSSH のインストールと作成方法を別途用意しているので、目を通してみてください。
SSH でログインすると、ログイン元と、ログイン先でやりとりされるデータが暗号化されます。この状態で、ssh にあるポートフォワードという機能を使い、他の TCP/IP を利用するプログラムのデータも、一緒に暗号化できます。概念的にはこんな感じになります。
+-- server ---+ | +--------- server ----------+
| +-------+ | | | +-------+ +----------+ |
| | sshd | | | | | sshd <-p--> Postgres | |
| +-+ +-+ | | | +-+ ^ +-+ +----------+ |
+----| |----+ | +----| | |------------------+
| | | | p |
| | | | | |
| | | | p |
+----| |----+ | +----| | |------------------+
| +-+ +-+ | | | +-+ v +-+ +------+ |
| | ssh | | | | | ssh <-p-p--> psql | |
| +-------+ | | | +-------+ +------+ |
+--- client --+ | +--------- client ----------+
|
ssh connection | psql over ssh conection
sshd: SSH server, ssh: SSH client
Postgres: PostgreSQL server, psql: PostgreSQL client
普通は単純に PostgreSQL サーバに psql が接続します。しかし、SSH サーバ（sshd）に ssh が接続していると、psql は ssh に接続し、psql から送られたデータは、ssh が SSH サーバに転送します。SSH サーバは、ssh から送られてきた psql のデータを PostgreSQL サーバに送ります。上の図だと、PostgreSQL のデータの流れは、「p」で表しています（作図がんばったけど、この程度か・・・。分かってくれるかなぁ）。
psql コマンドは、ssh に対して接続し、PosrgreSQL サーバからすると、同じマシンで動いている SSH サーバから接続されたと認識します。
従って、ここですべき設定は、ssh が psql から待ち受けるための TCP/IP のポート番号と、そのデータを SSH サーバが PostgreSQL サーバに転送する方法です。ssh コマンドを実行するときに、次のようにします。
> ssh -L 25432:127.0.0.1:5432 dbuser@dbserver.foo.com
当然注意すべきは、-L オプションです。このフォーマットは、「ローカルで待ち受けるポート:PostgreSQL のサーバ名（IP アドレス）:PostgreSQL のポート番号」です。「ローカルで待ち受けるポート」はいくつでも構いません。空いている番号を使いましょう（もしすでに使われていたら、起動時に文句を言われます）。
次の PostgreSQL のサーバ名ですが、SSH サーバから見た PostgreSQL サーバ という点に注意してください。両方とも同じマシンで動いているはずなので、127.0.0.1（localhost）でいいのです。それから PostgreSQL のポート番号ですが、普通は 5432 を使っているのでそれを指定します。
psql コマンドは、次のように接続します。たぶん端末は ssh に占領されているはずなので、もう一つ端末（kterm とか TeraTerm とか）を起動する必要がありますね。
> psql -h 127.0.0.1 -p 25432 wwwdb
これで見事、dbserver.foo.com 上の wwwdb に、dbuser として接続できたでしょうか？ ちなみにここで指定している -h 127.0.0.1 -p 25432 って、ssh コマンドが待ち受けているのは、理解できているでしょうか。ちょっと考えればすぐに理解できると思うので、他の TCP/IP 接続をするソフトにも応用できますね（私は CVS とかこう使っています）。
SSH の使い方ですが、毎回 -L オプションを入力するのは面倒だという人は、~/.ssh/config というファイルに次のように書いておくと、必ずこのポートフォワードがされるようになります（このファイルがなければ作ってください）。
Host dbserver.foo.com LocalForward 25432 127.0.0.1:5432
また、ssh の接続が端末を占領してしまってジャマ、って方は、ssh を次のように実行すると、バックグラウンドに移行しますので、そのまま端末を使うことが出来ます（私の環境だと、Kill せずにログアウトしようとすると、たまにログアウト時に固まりますが・・・）。
> ssh -N -f dbuser@dbserver.foo.com
また、psql コマンドも、このサーバにメインで接続するようであれば、次のように設定しておくとデフォルトでそのサーバに（この場合は SSH 経由で）接続するようになります。
sh, bash の場合 > PGDATABASE=wwwdb; export PGDATABASE > PGHOST=127.0.0.1; export PGHOST > PGPORT=25432; export PGPORT > PGUSER=dbuser; export PGUSER > psql csh, tcsh の場合 > setenv PGDATABASE wwwdb > setenv PGHOST 127.0.0.1 > setenv PGPORT 25432 > setenv PGUSER dbuser > psql
まあ、すべて設定するのはちょっとやりすぎのような気もしますが・・・。これを bash なら ~/.bash_profile に、csh なら ~/.cshrc、tcsh なら ~/.tcshrc などに書いておけば、いいのですね。
違うサーバに接続するときは、同じように -h ホスト名 -p ポート番号 を指定すれば接続できます。ちなみに UNIX ドメインソケット経由で接続したい場合は、-h /tmp -p 5432 とすれば接続できます（もちろん、デフォルトの設定である /tmp/.s.PGSQL.5432 がある場合ですよ）。
ちなみに、この方法の良いところは、「PostgreSQL サーバは同じマシン上の SSH サーバから接続される」点です。これはつまり、PostgreSQL に、同じマシン上からのみ接続を許可するように設定できるという点です。これにより、見知らぬ誰かから接続を試みられる心配がなくなります。
この、同じマシン上からのみ接続を許可するようにするには、一つはすでに説明した pg_hba.conf で設定する方法です。
local wwwdb md5 wwwdb.user host wwwdb 127.0.0.1 255.255.255.255 md5 wwwdb.user
この様に 127.0.0.1 からのみ接続を許可すれば、他からは接続できなくなります。しかし、もっと安全な方法は、postgresql.conf を修正することです。
#virtual_host = '' virtual_host = '127.0.0.1'
この設定は、そのマシンが持っているどの IP アドレスに対する接続を受け入れるかを指定する設定です。ここで 127.0.0.1 を指定すれば、同じマシン上からのみ接続出来るようになります。そして、この IP アドレス以外は待ち受けをしないようになります。もし、他のマシンから接続しようとすると、他のマシンからは、PostgreSQL が動いていないように見えます。
接続を拒否されるのと、待ち受けをしないのは大きな違いです。接続を拒否されるというのは、一度は PostgreSQL サーバと接続し、クライアントの IP アドレスを確認し、許可されていないアドレスと判断して接続を閉じると言うことです。それに対し、待ち受けをしないと、そもそも PostgreSQL には絶対接続されません。
もし、PostgreSQL のクライアントの IP アドレスを判断する部分に何らかのバグがあったりしたら、「接続を拒否する」方法だと問題が起きてしまうかもしれません。また、同時にたくさんの接続を行って、サーバに負荷をかけてダウンさせるような攻撃をされるかもしれません。
もし、PostgreSQL の利用者が限定されるのでしたら、SSL を使う方法より、この SSH を使う方法をお勧めします。まあ、SSH を使うと言うことは、そのマシンに Shell アカウントを与えてしまうと言うことにもなりますが・・・。それで問題がなければいいでしょう。