Linux Install Memo

サーバー管理者によるLinux関連ソフトのインストールメモ

Home » □PostgreSQL をサーバー/クライアントとして使う(いざ自宅DB)

□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を実際に操作してみましょう。
基本的な操作として、下記の4つの処理を簡単にやってみます。

1.自宅DBに接続
2.テーブル作成
3.データの書き込み
4.データの読み込み

基本的にはデータベースに接続ができればなんでも処理できるはずですので
とにかく1をクリアしましょう。(^^;

1.自宅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 へ接続成功です!!

となるはずです。

2.テーブル作成

続いてテーブルを作成する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 は既に存在しています!?

というようにエラー表示が出てしまいます。

3.データの書き込み
4.データの読み込み

書き込みと読み込みは一度にやってしまいましょう。

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 でアクセス元を制限しているのでこれだけでも自宅DBなら
問題ないと思いますが、さらにセキュリティを気にする場合は、テーブル
へのアクセス権などを設定してください。

5.おまけ

さて、外部からのアクセスをパスワード付きとしただけならまだしも自鯖
ないからのアクセスもパスワードを必須とした場合、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

のようにすることで、パスワード入力を省くことができる。

Name of author

Name: admin

コメントを残す

メールアドレスが公開されることはありません。 * が付いている欄は必須項目です