PostgreSQL

Red Hat Linuxのページトップへ

PostgreSQLの公式サイト
日本PostgreSQLユーザ会
phpPgAdmin
PostgreSQLはフリーなデータベースソフト
SQL(Structured Query Language)

DBD::Pg
DBI
Ruby用モジュールはコチラ

公式サイトの上の[download]から
必要なもの
postgresql-7.4.5.tar.gz

インストール方法
(1)コンパイル 
(2)インストール 
(3)データベースの初期化 
(4)デーモン(postmaster)の立ち上げ 

PostgreSQL用のグループとユーザーアカウントを作成します。今回はどちらも「postgres」という名前にします。 
# mkdir /home/postgres
# groupadd postgres
(# groupadd -g 26 postgres )
# useradd postgres -g postgres -d /home/postgres
(# useradd -u 26 -g 26 -c 'PostgreSQL Server' -d /var/lib/pgsql -s /bin/bash postgres )
(# usermod -d /home/postgres postgres )
# chown postgres:postgres /home/postgres
# passwd postgres
  PostgreSQLの実行ファイルを置くディレクトリを作成します。 

# mkdir /usr/local/pgsql
# chown postgres.postgres /usr/local/pgsql

ダウンロードしたPostgreSQLのソースファイルを/usr/local/srcディレクトリに展開します。
ソースファイルは、FTPで適当なディレクトリにあらかじめアップしておきます。 
# mkdir /usr/local/src
# cp postgresql-7.4.5.tar.gz /usr/local/src/
# cd /usr/local/src
# tar zxvf postgresql-7.4.5.tar.gz

展開されたソースのオーナーをpostgresに変更し、suコマンドでpostgresになります。 
# chown -R postgres:postgres postgresql-7.4.5
# su postgres

展開したPostgreSQLのディレクトリに移動します。 
$ cd /usr/local/src/postgresql-7.4.5

($ cd postgresql-7.4.5/src)

環境に合わせた設定を行う為、configureスクリプトを使います。 
configure時にエラーが出る場合は、PostgreSQLのコンパイルに必要なファイルが揃っていない可能性があります。
(syslog出力オプションを付与しておくことをお勧めします。)

$ ./configure --enable-multibyte=EUC_JP --enable-syslog 
--with-java JDBCドライバ
--with-tcl PGAccess(GUI管理)Tcl/Tk
--with-tclconfig=/usr/local/lib --with-tkconfig=/usr/local/lib

ソースをコンパイルし、インストールします。 
コンパイルできなかった場合、コンパイラが入っていない可能性があります。 
$ make clean
$ make all 
コンパイルされたバイナリを /usr/local/pgsql/ 以下にインストールする
/usr/local/pgsql/ 以外にインストールしたい場合はこちら
$ make install  

ドキュメント(HTML形式)のインストールはdocディレクトリで行う
$ cd ../doc
$ make install

/usr/local/src/postgresql-7.3.5/contrib/start-scripts/以下にlinuxという postgres起動用スクリプトファイルがあります。
これを/etc/init.d/以下にpostgresとしてコピーし、適切なランレベルへ設定してください。
# cp /usr/local/src/postgresql-7.3.5/contrib/start-scripts/linux /etc/rc.d/init.d/postgres
# ln -s /etc/rc.d/init.d/postgres /etc/rc.d/rc3.d/S98postgres
# ln -s /etc/rc.d/init.d/postgres /etc/rc.d/rc3.d/S98postgres

PostgreSQLを動作させるにはpostgresユーザの環境変数を設定する必要がある
ホームディレクトリで.bash_profileを開き、以下を追加します。 
csh/tcshの場合はこちら
export PATH="$PATH":/usr/local/pgsql/bin
export POSTGRES_HOME=/usr/local/pgsql
export PGLIB=$POSTGRES_HOME/lib
export PGDATA=$POSTGRES_HOME/data
export MANPATH="$MANPATH":$POSTGRES_HOME/man
export LD_LIBRARY_PATH="$LD_LIBRARY_PATH":"$PGLIB"

コマンドサーチパス
PostgeSQLのコマンドはすべて/usr/local/pgsql/bin下にあるので
これを実行できるようにコマンドサーチパスに追加する

環境変数
PGLIB /usr/local/pgsql/lib
PGDATA データベース領域
MANPATH PostgreSQLのオンラインマニュアル
LD_LIBRARY_PATH PostgreSQLのクライアント用sharedライブラリ
sourceコマンドで.bash_profileを再読み込みするか、ログインしなおす $ source .bash_profile データベースを初期化する (一度だけ実行すればいい) $ initdb ($ /usr/local/pgsql/bin/initdb --pgdata=/usr/local/pgsql/data ) ($ /usr/bin/initdb --pgdata=/var/lib/pgsql/data ) これで /usr/local/pgsql/data/以下にデータベース領域が初期設定される (設定ファイル[pg_hba.conf・pg_ident.conf・postgresql.conf]も作成される) hba:host-based authenticationホストベースの認証の略 postgresql.confの設定を行います。 $ vi /usr/local/pgsql/data/postgresql.conf 以下のように修正する silent_mode = false ↓ silent_mode = on tcpip_socket = false ↓ tcpip_socket = on PostgreSQLのデーモンプロセスpostmasterを起動する 以下のいずれかのコマンドを実行 $ /usr/local/pgsql/bin/pg_ctl -w start $ /usr/local/pgsql/bin/pg_ctl -D /usr/local/pgsql/data -l logfile start $ /usr/local/pgsql/bin/postmaster -D /usr/local/pgsql/data Apacheの実行ユーザであるnobodyを、PostgreSQLユーザとして登録します。 「新しいユーザにデータベース作成を許可しますか?」 「新しいユーザにユーザ作成を許可しますか?」 という質問がされますが、どちらもNo(n)と答えます。 $ /usr/local/pgsql/bin/createuser nobody $ /usr/local/pgsql/bin/createdb $ /usr/local/pgsql/bin/dropdb postgres 標準設定ではlocalhostからの接続のみ許可されている 外部からもアクセスできるようにしたい場合は/usr/local/pgsql/data/pg_hba.confの #host all 0.0.0.0 0.0.0.0 trust のコメントアウトを外す 『trust』のところを『passwd』または『crypt』にするとパスワード認証ができる passwd: 普通のファイルにユーザ名とパスワードを 書いておく方法 crypt: PostgreSQL のデータベースでパスワードを管理する方法 テスト postmasterが起動していない場合は立ち上げておく $ pg_ctl -w start regression用のディレクトリに移ります。 $ cd /usr/local/src/postgresql-7.0.2/src/test/regress regression testを実行する $ make all runtest テスト項目毎に[ok/fail]が出力される 『fail』と出ても、必ずしも問題がある訳ではない DBD::Pg # cd /usr/local/src/ # tar xfz /ソースファイルのディレクトリ/DBD-Pg-1.22.tar.gz # cd /usr/local/src/DBD-Pg-1.22/ 以下は必ずroot権限で実行すること # export PGLIB=/usr/local/pgsql/lib (必要であれば) # export PGUSER=postgres (必要であれば) # perl Makefile.PL # make # make test # make install ※PostgreSQL を起動していないとエラーになります。 fedora cp /usr/share/pgsql/postgresql.conf.sample /var/lib/pgsql/data/postgresql.conf データベースサーバをスタートさせたい
pg_ctl start
データベースサーバを終了させたい
pg_ctl stop
別ホストからTCP/IP経由でアクセス出来るように設定したい
pg_ctl stop
vi /usr/local/pgsql/data/postgresql.conf
(vi /var/lib/pgsql/data/postgresql.conf)
以下のように変更
tcpip_socket = true
変更の保存後、起動
pg_ctl start
同時接続数を【128】に設定して起動したい
pg_ctl stop
vi /usr/local/pgsql/data/postgresql.conf
以下のように変更
max_connections = 128
shared_buffers = 4096   # max_connections * 2 以上。メモリがある場合、
                        # 多い方が高パフォーマンスを得やすい。
変更の保存後、起動
pg_ctl start
ログをファイル【/tmp/pgsql.log】に残すようにして起動したい
pg_ctl -l /tmp/pgsql.log start
実行された SQL を含むログをファイル【/tmp/pgsql.log】に残すようにして起動したい
pg_ctl stop
vi /usr/local/pgsql/data/postgresql.conf
以下のように変更
log_statement = true        # 7.3 以上の場合
debug_print_query = true    # 7.3 未満の場合
変更の保存後、起動
pg_ctl -l /tmp/pgsql.log start
デフォルトエンコーディングを EUC_JP(EUC-JP)にしてデータベースを初期化したい
initdb --encoding=EUC_JP
デフォルトエンコーディングをUnicode(UTF-8)にしてデータベースを初期化したい
initdb --encoding=UNICODE
データベース【mydatabase】を作成したい(postgresユーザで)
createdb mydatabase
エンコーディングを EUC(EUC-JP) に指定してデータベース【mydatabase】を作成したい(postgresユーザで)
createdb --encoding=EUC_JP mydatabase
エンコーディングを Unicode(UTF-8)に指定してデータベース【mydatabase】を作成したい(postgresユーザで)
createdb --encoding=UNICODE mydatabase
データベース【mydatabase】を作成したい(postgres以外のユーザで)
su - postgres -c 'createdb mydatabase'
データベース【mydatabase】を削除したい(postgresユーザで)
dropdb mydatabase
データベース【mydatabase】を削除したい(postgres以外のユーザで)
su - postgres -c 'dropdb mydatabase'
ファイル【myfile】に書かれたSQLをデータベース【mydatabase】に一括実行したい
psql -d mydatabase < myfile
シェルからデータベース【mydatabase】で SQL を実行したい
psql -d mydatabase -c 'select * from mytable order by id;'
シェルからデータベース【mydatabase】でバックスラッシュコマンドを実行したい
psql -d mydatabase -c '\d'
シェルからデータベース【mydatabase】でバックスラッシュコマンドと SQL を実行したい
echo '\d \\ select * from mytable;' | psql -d mydatabase
シェルからデータベース【mydatabase】でユーザ名を指定して SQL を実行したい
psql -d mydatabase -U postgres -c 'select * from mytable order by id;'
データベース【mydatabase】のテーブル【mytable】にtext型のフィールド【myfield】を追加したい
psql -d mydatabase -c 'alter table mytable add column myfield text;'
データベース【mydatabase】のテーブル【mytable】のフィールド【myfield】を削除したい(PostgreSQL7.3以降)
psql -d mydatabase -c 'alter table mytable drop column myfield;'
データベース【mydatabase】のテーブル【mytable】の名前を【mytable2】に変更したい
psql -d mydatabase -c 'alter table mytable rename to mytable2;'
データベース【mydatabase】のテーブル【mytable】のフィールド【myfield】の名前を【myfield2】に変更したい
psql -d mydatabase -c 'alter table mytable rename column myfield to myfield2;'
ラージオブジェクトなしでデータベース【mydatabase】をファイル【myfile】にバックアップしたい
pg_dump -d mydatabase > myfile
ラージオブジェクトなしでバックアップされたファイル【myfile】からデータベース【mydatabase】にリストアしたい
psql -f myfile -d mydatabase
ラージオブジェクトありでデータベース【mydatabase】をファイル【myfile】にバックアップしたい
pg_dump -b -Fc -d mydatabase > myfile
ラージオブジェクトなしでバックアップされたファイル【myfile】からデータベース【mydatabase】にリストアしたい
pg_restore -d mydatabase myfile
ラージオブジェクトありでデータベース【mydatabase】をtar形式でファイル【myfile】にバックアップしたい
pg_dump -b -Ft -d mydatabase > myfile
ラージオブジェクトありでバックアップされたファイル【myfile】(tar形式)からリストアしたい
pg_restore -d -d mydatabase myfile
ラージオブジェクトありでデータベース【mydatabase】をバックアップし、gzip 圧縮してファイル【myfile.gz】に保存したい
pg_dump -b -Fc -d mydatabase | gzip > myfile.gz
ラージオブジェクトありでバックアップ&gzip 圧縮されたファイルファイル【myfile.gz】からデータベース【mydatabase】にリストアしたい
gzip -dc myfile.gz | pg_restore -d mydatabase
別ホスト【anotherhost】のデータベース【mydatabase】をラージオブジェクトなしでファイル【myfile】にバックアップしたい
pg_dump -h anotherhost -U postgres -d mydatabase > myfile
別ホストのデータベース【mydatabase】をラージオブジェクトありでファイル【myfile.gz】にバックアップ&gzip圧縮したい
pg_dump -b -Fc -h hostname -U postgres -d mydatabase | gzip > myfile.gz
データベース【mydatabase】のテーブルスキーマのみ(入っているデータは含まない)をSQLとしてファイル【myfile】に保存したい
pg_dump -s -d mydatabase > myfile
データベース【mydatabase】のデータのみ(テーブルスキーマは含まない)のみをSQLとしてファイル【myfile】に保存したい
pg_dump -a -d mydatabase > myfile
全てのデータベースをラージオブジェクトなしでファイル【myfile】にバックアップしたい(ラージオブジェクトありはpg_dumpallではバックアップ不可)
pg_dumpall > myfile
データベース【mydatabase】のテーブル【mytable】のデータを、タブ区切りのファイル【myfile】として全て取り出したい
psql -d mydatabase -c 'copy mytable to stdout;' > myfile
データベース【mydatabase】のテーブル【mytable】のデータの一部(SQLで選択)を、タブ区切りのファイル【myfile】として全て取り出したい
psql -d mydatabase --tuples-only --pset format=unaligned --field-separator="タブ文字" \ -c "select * from mytable where id<10;" > myfile
bash の場合、--field-separator中のタブ文字は、C-v TAB で入力します。 タブ区切りのファイル【myfile】をデータベース【mydatabase】のテーブル【mytable】に挿入したい
cat myfile | psql -d mydatabase -c 'copy mytable from stdin;'
タブ区切りのファイル【myfile】をEUCに変換してからデータベース【mydatabase】のテーブル【mytable】に挿入したい
cat myfile | nkf -e | psql -d mydatabase -c 'copy mytable from stdin;'
データベース【mydatabase】のテーブル【mytable】のデータを、コンマ区切りのファイル【myfile】として全て取り出したい
psql -d mydatabase -c "copy mytable to stdout delimiter ',';" > myfile
データベース【mydatabase】のテーブル【mytable】のデータの一部(SQLで選択)を、コンマ区切りのファイル【myfile】として取り出したい
psql -d mydatabase --tuples-only --pset format=unaligned --field-separator="," \ -c "select * from mytable where id<10;" > myfile
コンマ区切りのファイル【myfile】をEUCに変換してからデータベース【mydatabase】のテーブル【mytable】に挿入したい
cat myfile | nkf -e | psql -d mydatabase -c "copy mytable from stdin delimiter ',';"
ファイル【myfile】に保存されたタブ区切りのデータの間に一つデータを追加して、データベース【mydatabase】のテーブル【mytable】に挿入したい
cat myfile | perl -F"\n|\t" -nae 'print "$F[0]\t$F[1]\tmydata\t$F[2]\n";' \ | psql -d mydatabase -c 'copy mytable from stdin;'
例の場合は、3つのフィールドがあって、二つ目と三つ目のフィールドの間に「mydata」という値を追加しています。 ファイル【myfile】に保存されたタブ区切りのデータの間のフィールドを一つ削除して、データベース【mydatabase】のテーブル【mytable】に挿入したい
cat myfile | perl -F"\n|\t" -nae 'print "$F[0]\t$F[2]\n";' \ | psql -d mydatabase -c 'copy mytable from stdin;'
例の場合は、3つのフィールドがあって、真ん中のフィールドを取り除いています。 データベース【mydatabase】に、192.168.0.10 と 192.168.0.12 からのみ接続出来るようにしたい
pg_ctl stop
vi /usr/local/pgsql/data/pg_hba.conf
下記の2行を追加
host mydatabase all 192.168.0.10 255.255.255.255 trust
host mydatabase all 192.168.0.12 255.255.255.255 trust
設定の保存後、起動
pg_ctl start
データベース【mydatabase】に、192.168.0.* (*は任意の番号)からのみ接続出来るようにしたい
pg_ctl stop
vi /usr/local/pgsql/data/pg_hba.conf
下記の1行を追加
host mydatabase all 192.168.0.0 255.255.255.0 trust
設定の保存後、起動
pg_ctl start
データベース【mydatabase】に、192.168.0.* (*は任意の番号)からユーザ【myuser】がパスワード【yourpassword】で接続出来るようにしたい
psql -d mydatabase -c "create user myuser with password 'yourpassword';"
vi /usr/local/pgsql/data/pg_hba.conf
下記の1行を上のほう(上のほうの設定が優先されます)に追加
host mydatabase myuser 192.168.0.0 255.255.255.0 md5
データベース【mydatabase】ユーザ【myuser】のパスワードを【newpassword】に変更したい
psql -d mydatabase -c "alter user myuser password 'newpassword';"
データベース【mydatabase】のテーブル【mytable】に、ユーザ【myuser】で全ての操作ができるようにしたい
psql -d mydatabase -c 'grant all on mytable to myuser;'
データベースの一覧を表示したい
psql -l
データベース【mydatabase】のテーブル一覧を表示したい
psql -d mydatabase -c '\dt'
データベース【mydatabase】のテーブル【mytable】の定義を確認したい
psql -d mydatabase -c '\d mytable'
使用しているPostgreSQLサーバープロセスのバージョンを知りたい
psql -d template1 -c 'select version();'
PostgreSQL のセキュリティ PostgreSQL にアクセスするには、UNIX ドメインソケット経由と、TCP/IP ソケット経由の2種類がある。 PostgreSQL が動いているマシンに直接ログインする場合がUNIX ドメインソケット経由、他のマシンからでも PostgreSQL にアクセスできる場合がTCP/IP ソケット経由。 UNIX ドメインソケット経由だとアクセスの制御は、OS のユーザーログインで行われるので比較的安全。 それに対して、TCP/IP ソケット経由の場合、PostgreSQL 自身でアクセス制御を行う必要がある。 そのアクセス制御をpg_hda.confファイルで設定する。 これは、initdbを実行してデータベースを初期化した際に data ディレクトリ内に作成される。 pg_hda.confの標準設定
local  all                              trust
host   all  127.0.0.1  255.255.255.255  trust
1行目の local は、UNIX ドメインソケットの場合の設定で、2行目の host は、TCP/IP ソケットの場合の設定です。
パラメータ接続方法アクセスするデータベースIPネットマスク認証
UNIX ドメインソケットの場合localall  trust
TCP/IP ソケットの場合localall127.0.0.1255.255.255.255trust
UNIX ドメインソケットの場合は、『全てのデータベースに認証なしで接続できる』という状態になっている。 TCP/IP ソケットの場合は、『自マシンからの接続に対してのみ、全てのデータベースに認証なしで接続できる』という状態になっている。 しかし標準設定では同じマシンからでも、TCP/IP ソケット経由ではアクセスできない。 これは、PostgreSQL が TCP/IP ソケット経由のアクセスを待ち受けていないためです。 この待ち受けを有効にするには、postgresql.conf ファイルを編集する必要がある。 postgresql.conf ファイルの編集
#tcpip_socket = false
tcpip_socket = true
編集後 PostgreSQL を再起動する。 # pg_ctl restart 接続例 UNIX ドメインソケット経由 # psql template1 TCP/IP ソケット経由 # psql -h 127.0.0.1 template1 他のマシンからもログインできるようにするには、pg_hda.conf を設定をするだけ。 ただしIPアドレスを変更するだけだとパスワードが必要ないので、だれでもアクセスできてしまう。 そのため5番目の『認証』パラメータも変更する必要がある。 ユーザ認証を行う 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 に次の行を追加します。 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 5番目のフィールドを 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 5番目のフィールドに password と指定することにより、パスワード認証になります。6番目のフィールドには、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 のパスワード認証以外の設定(例えばユーザの有効期限とか)は、きちんと動くみたいです。 SSL による暗号化 別のマシンで稼働している PostgreSQL サーバに対して、TCP/IP を使って接続できることは説明しましたが、データはそのまま流れてしまうので、大事なデータを扱っているときは、ちょっと気になる人もいると思います。PostgreSQL では、このデータのやりとりを、SSL を使って暗号化することが出来ます。 SSL っていうと、Web サーバで SSL を使うのが有名ですよね。https:// で始まる URL にアクセスすると、その間はデータが暗号化されるので、ユーザ登録や、ネットバンキングなどで利用されているものと全く同じです。 SSL を使えるようにするには、SSL が利用できるようにコンパイルされていなといけません。コンパイルの configure を実行するときに、--with-ssl オプションを付ける必要があります。で、このときに、OpenSSL がインストールされている必要があります。OpenSSL は、RedHat から正規の RPM もありますし、インストールと作成方法を別途用意しているので、気力があったら試してみてください(個人的には、次の SSH による暗号化をお進めするので、これをやる必要はそんなにありません)。 OpenSSL のインストール 作成したサーバ証明書と秘密鍵ファイルを、定位置にコピーします。それぞれを、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 を使う方法に関しては、次で説明します。 SSH による暗号化 SSL 以外でも、SSH を利用しても、データのやりとりを暗号化することができます。SSH は主に、telnet の代わりになるプログラムで、ssh コマンドを使って、他のマシンにログインできます。一般的に使われているであろう OpenSSH のインストールと作成方法を別途用意しているので、目を通してみてください。 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 アカウントを与えてしまうと言うことにもなりますが・・・。それで問題がなければいいでしょう。

ぺんたん.info
あかいひ.com
IPくん.com
8枠.com - 競馬情報
galhime.com

PC Q&A||古い記事|雑学|ラーメン