□PostgreSQL をサーバー/クライアントとして使う(いざ自宅DB)
ダイナミックDNSサービス：MyDNS.JP(http://www.MyDNS.JP/)を使用して、
自宅に PostgreSQL のデータベースサーバー(自宅DB)を設置してみます。
目的としては、データは自宅でしっかり管理して、PHP が入っている外部の
WEBサーバー(例えば BiG-NET の)からアクセスができるようにします。
自宅サーバーについては、このメモでガリガリインストしている方には説明
は不要でしょうからはしょって、具体的な PostgreSQL の設定などに限定を
します。
まずは外部からアクセスするユーザーを PostgreSQL に設定します。この時
一緒にパスワードも設定しましょう。DBユーザーを作れるユーザー(笑)に
なって、「test1」というユーザーを追加します。
samba:~$ /usr/local/pgsql/bin/createuser -A -D -P -E test1
Enter password for new user:
Enter it again:
CREATE USER
パスワードを求められるので適当に入れます。createuser のオプションは
–help でみてください。ここではDBの新規作成不可、ユーザー追加不可で
パスワードを暗号化した「test1」というユーザーを追加しました。
つづいて、/usr/local/pgsql/data/pg_hba.conf を編集して、外部の特定の
IPアドレス(普通はPHPがOKなWEBサーバー？)からの接続のみできるように
許可します。ここで特定のデータベースに対して接続できるユーザーを制限
できます。
> # TYPE? DATABASE??? USER??????? IP-ADDRESS??????? IP-MASK?????????? METHOD
>
> local?? all???????? all???????????????????????????????????????????? trust
> # IPv4-style local connections:
> host??? all???????? all???????? 127.0.0.1???????? 255.255.255.255?? trust
> host??? test1?????? test1?????? 192.168.192.0???? 255.255.255.0???? md5
ここでは、「192.168.0.0/24」からなら「db_test1」というデータベース
に対して「test1」というユーザーだけが「md5」による認証で接続が可能
という設定にしました。「md5」ではなくて「crypt」に、という本がほと
んどかと思いますが、それは PostgreSQL の 7.1 までの古いバージョン
向けです。何はともあれ、これで外からアクセスできる準備ができました。
PostgreSQL を普通に起動している場合には、5432 番ポートで受け付ける
筈ですが、最近のバージョンでは普通にインストールしたままだと、外部
からの接続は受け付けないかもしれません。
そんなときには /usr/local/pgsql/data/postgresql.conf を確認して、
ポートの指定などがコメントアウトしていないかどうかを見ます。(★)
：
：
> listen_addresses = ‘*’ （古いVerなら tcpip_socket = on）
> port = 5432
> max_connections = 1000
> superuser_reserved_connections = 20
：
：
また自宅サーバーを iptables 等でフィルタリングしている場合は、外部
からこのポートにアクセスができるようにしないといけません。
/etc/services に 5432番を postgres と書き足してもいいですし、直接
指定でもいいでしょう。
で、実際にアクセスをしてみると…
> hogehoge:~# /usr/local/pgsql/bin/psql -d db_test1 -h 192.168.192.30 -U test1
> Password:
> Welcome to psql 7.3.2, 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
>
> db_test1=>
と入れました。んー、何かネットワークしていますねー。
では、PHPがOKなWEBから自宅DBを実際に操作してみましょう。
基本的な操作として、下記の４つの処理を簡単にやってみます。
１．自宅DBに接続
２．テーブル作成
３．データの書き込み
４．データの読み込み
基本的にはデータベースに接続ができればなんでも処理できるはずですので
とにかく１をクリアしましょう。(^^;
１．自宅DBに接続
test_db1.php
−−−−−−−−−−
<HTML>
<BODY>
<?
// データベースへの接続
$DB = pg_connect(“host=192.168.192.30 port=5432 dbname=db_test1 user=test1 password=testtest”);
if (!$DB)
{
??? print “自宅DBへの接続が失敗しました!?<BR>\n”;
??? print “</BODT></HTML>\n”;
??? exit;
}
$dbname = pg_dbname($DB);
print “自宅DB:$dbname へ接続成功です!!<BR>\n”;
?>
</BODY>
</HTML>
−−−−−−−−−−
こんなHTML(PHPスクリプト)を用意しました。
これは「database.mydns.jp」の「5432」番ポートに「test1」で「pass1」
というユーザー名＆パスワードで接続し、「db_test1」というデータベース
にアクセスするものです。
このHTMLにWEBからアクセスをしてうまくいけば
> 自宅DB:db_test1 へ接続成功です!!
となるはずです。
２．テーブル作成
続いてテーブルを作成するSQL文を追加してみます。
test_db2.php
−−−−−−−−−−
：
：
print “自宅DB:$dbname へ接続中です!!<BR>\n”;
// テーブルの生成
$SQL = “SELECT relname from pg_class where relname = ‘test1_tbl’;”;
$RESULT = pg_query($DB, $SQL);
$ROW = pg_num_rows($RESULT);
if ($ROW)
{
??? print “test1_tbl は既に存在しています!?<BR>\n”;
}
else
{
??? $SQL = “CREATE TABLE test1_tbl (
??? name TEXT,
??? age INTEGER);
??? “;
??? $RESULT = pg_query($DB, $SQL);
??? print “test1_tbl はありませんので作りました!!<BR>\n”;
}
?>
</BODY>
</HTML>
−−−−−−−−−−
こんなふうに追加しました。
これは「test1_tbl」というテーブルを追加しろという命令ですね。
別途コンソールから psql でログインしておいてテーブルの有無を
確認すると
> db_test1=> \d
> No relations found.
というようにない状態で、このPHPスクリプトにアクセスをして
うまくいくと
> 自宅DB:db_test1 へ接続中です!!
> test1_tbl はありませんので作りました!!
という表示になります。psql で確認しても
> db_test1=> \d
>????????? List of relations
>? Schema |?? Name??? | Type? | Owner
> ——–+———–+——-+——-
>? public | test1_tbl | table | test1
> (1 row)
>
> db_test1=>
とできていました。
ちなみに、もし同名のテーブルが存在していると
> 自宅DB:db_test1 へ接続中です!!
> test1_tbl は既に存在しています!?
というようにエラー表示が出てしまいます。
３．データの書き込み
４．データの読み込み
書き込みと読み込みは一度にやってしまいましょう。
test_db3.php
−−−−−−−−−−
：
：
print “test1_tbl はありませんので作りました!!<BR>\n”;
}
// データの書き込み
$SQL = “INSERT INTO test1_tbl VALUES (
???????? ‘日本太郎’, 25);\n
?????? “;
$SQL .= “INSERT INTO test1_tbl VALUES (
???????? ‘日本花子’, 22);
?????? “;
$RESULT = pg_query($DB, $SQL);
// データの読み出し
$SQL = “SELECT * FROM test1_tbl;
?????? “;
$RESULT = pg_query($DB, $SQL);
$ROW = pg_num_rows($RESULT);
if ($ROW)
{
??? print “$ROW 件のデータがありました!!<BR>\n”;
??? for ($r = 0; $r < $ROW; $r ++)
??? {
??????? $ARRAY = pg_fetch_array($RESULT,$r);
??????? print “\t$ARRAY[name]\t$ARRAY[age]<BR>\n”;
??? }
}
else
{
??? print “データがありませんでした!?<BR>\n”;
}
?>
</BODY>
</HTML>
−−−−−−−−−−
こんなふうに追加しました。
うまくいくと
> 自宅DB:db_test1 へ接続中です!!
> test1_tbl は既に存在しています!?
> 2 件のデータがありました!!
> 日本太郎 25
> 日本花子 22
というようになります。
リロードするとどんどん追加していくので件数も増えていきます。(^^;
自宅サーバーの方のDBで一応、直接確認しておくと
> db_test1=> select * from test1_tbl;
>????? name | age
> ———-+—–
> 日本太郎? | 25
> 日本花子? | 22
> (2 rows)
>
> db_test1=>
というわけで、自宅DBがしっかり動いていることが確認できました。
pg_hba.conf でアクセス元を制限しているのでこれだけでも自宅ＤＢなら
問題ないと思いますが、さらにセキュリティを気にする場合は、テーブル
へのアクセス権などを設定してください。
５．おまけ
さて、外部からのアクセスをパスワード付きとしただけならまだしも自鯖
ないからのアクセスもパスワードを必須とした場合、pg_dump でのバック
アップの仕方に困ってしまうかと思います。(なぜなら pg_dump は通常の
psql でDBにアクセスするのと同じ事になるので、当然パスワードを求め
られる)
そんな時は、ちと危なっかしいですがIDとパスワードを書いたファイル
(仮にIDを hogehoge、パスワードを areare とする)を次のようにどこか
において
> hogehoge:~# cat ~/postgresql.auth
> hogehoge
> areare
/usr/local/pgsql/bin/pg_dump -u db_test1 < /root/postgresql.auth > db_test1.back
のようにすることで、パスワード入力を省くことができる。
