前回、PostgreSQLでいろいろと確認コマンドを打って分かった気になってみたわけだが、いよいよ実際にロールを作り、データベースを作り、スキーマを作ってPHPから接続してみようと思う。
1.ロールの作成
まずは、ロールを作る。ロールはPostgreSQLにおける役割を規定するものだが、PostgreSQLではユーザーにロールを割り当てて使うということではなく、ロール自体をユーザーとして使用する。もちろん、ロールをほかのロールに継承させるといった使い方もできる。このロールはOSのユーザーとは独立して存在するものだ。必ずしもロールと同名のOSユーザーが存在しなければならないというわけではないが、ロールのログインにPeer認証やident認証を用いる場合にはその必要がある。デフォルトではローカルからの接続にはPeer認証を行う設定になっている。しかしながら、ロールとOSユーザーが別物であるということを確認するため、今回はパスワード認証を行う設定に変更してみる。特に大きな意味はない。好奇心でそうするだけだ。
パスワード認証 では平文のパスワードとmd5による暗号化とが選択できる。しかしパスワード設定にmd5による暗号化を用いた場合、db_user_namespace機能が使えないという制限がつく。マニュアルでは、平文でパスワードを設定し、通信をSSLで保護することでセキュリティーを守ることを提案している。素直にPeer認証のままにしておけばこのような面倒はない。
ロールを作るには、CREATE ROLEコマンドを使用する。試しにtest01という名の管理ロールではない一般 ロールを作ってみる。
postgres=# CREATE ROLE test01 LOGIN PASSWORD 'パスワード';
CREATE ROLE
postgres=#
postgres=# \du
ロール一覧
ロール名 | 属性 | メンバー
----------+----------------------------------------------------------------------+----------
postgres | スーパーユーザ, ロールを作成できる, DBを作成できる, レプリケーション | {}
test01 | | {}
postgres=#
postgres=#
これで、test01という名前のログイン可能ユーザーができた。今回打ったオプションではLOGINしか権限を付与していないが、必要に応じてALTER ROLEコマンドで追加変更が可能だ。
このままではPeer認証となっており、OSユーザーにtest01と言いうユーザーが居ないのでログインできない。OSユーザーでtest01というユーザーを作るか、ほかの認証に切り替えるかどちらかの作業が必要となる。ここではパスワード認証に切り替えてみよう。どこからの接続にどの認証を使うかは、pg_hba.confという設定ファイルに記述されている。
postgres=# \q
-bash-4.1$ exit
#
# cd /var/lib/pgsql/9.2/data
# cp -p pg_hba.conf pg_hba.conf.`date +%Y%m%d`
# vi ./pg_hba.conf
#local all all peer
local all all password
#
# /etc/init.d/postgresql-9.2 restart
postgresql-9.2 サービスを停止中: [ OK ]
postgresql-9.2 サービスを開始中: [ OK ]
#
#
pg_hba.confの中のローカルからの接続は全てpeer認証とする、という行を”#”でコメントアウトし、password認証とする行を追加している。これでpostgreSQLを再起動して設定を反映させてやれよろしかろう。
2.データベースの作成
では次に、ロールtest01で接続するデータベースを作ってみる。
postgres=# CREATE DATABASE testdb OWNER test01 ENCODING 'UTF8' LC_COLLATE 'ja_JP.UTF-8' LC_CTYPE 'ja_JP.UTF-8';
CREATE DATABASE
postgres=#
postgres=# \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
testdb | test01 | UTF8 | ja_JP.UTF-8 | ja_JP.UTF-8 |
(4 行)
postgres=#
postgres=# \q
-bash-4.1$
-bash-4.1$
-bash-4.1$ psql testdb -U test01 -W
ユーザ test01 のパスワード:
psql (9.2.3)
"help" でヘルプを表示します.
testdb=>
testdb=>
testdb=>
でけた。エンコーディングをUTF8で、オーナーがtest01のデータベース作った。作ったと言っても、実のところ、CREATE DATABASEコマンドが行うのは、テンプレートデータベースのコピーである。デフォルトではtemplate1をコピーする。template1をカスタマイズしておけば、データベース作成を便利にすることができる。template1をカスタマイズしすぎてわけがわからなくなった場合は、template0をテンプレートに指定すれば、まっさらなデータベースを作ることができるようになっている。そのためtemplate0はカスタマイズ不可なのである。
今まで作った環境は全てUTF8に統一している。そうしておけば、外部接続を除いてはUTF8で作業する限り、日本語が文字化けすることはない。LC_COLLATEは文字列の並び換え順を指定している。ja_JPを指定しておけば日本語を辞書順に並べ替えてくれる。むろん、漢字の並べ替えはいまだ不完全である。日本語の悩ましいところでもある。LC_CTYPEは文字の種類を指定する。通常LC_COLLATEとLC_CTYPEに別のロケールを入れる必要はない。データベースも作成後に、ALTER?DATABASEコマンドで追加変更が可能だが、
LC_COLLATEとLC_CTYPE はデータベース作成時にしか設定できないので注意が必要だ。
データベースをオーナーにtest01を指定して作った状態で、ロールtest01を使用してtestdbに接続してみる。pspqコマンドで、データベース名、-Uオプションでロールを指定し、-Wでパスワード入力を即す。ログイン後は、プロンプトが”データベース名=>”となっている。”=>”となっているのは一般ロールであることを示しており、今まで使用していたpostgresの”=#”というプロンプトは管理ロールのプロンプトであることを示している。
ともあれ、やっとこさtest01ロールを使って、パスワード認証でtestdbに接続に成功した。テストデータを入れる中身を作っていこう。
3.スキーマの作成
いよいよスキーマの作成を行う。スキーマはデフォルトでpublicというスキーマが用意されており、サーチパスの一番先頭にpublicが設定されているので、何も指定しなければpublicスキーマ内に作成されたものを指すと暗黙的に理解する。例えば、testtbという名のテーブルにスキーマ名を指定せずにアクセスしているとすれば、それはpublic.testtbにアクセスしている。そのため、publicスキーマを使用する限りにおいては、スキーマを意識せずにMySQLと同じノリで操作することも可能だ。しかしせっかくなので、testscというスキーマを作ってみよう。
testdb=> CREATE SCHEMA testsc;
CREATE SCHEMA
testdb=>
testdb=> \dn
スキーマ一覧
名前 | 所有者
------------+----------
testsc | test01
public | postgres
(2 行)
testdb=>
testdb=> select current_schema();
current_schema
----------------
public
(1 行)
testdb=>
testdb=>
testdb=> SHOW search_path;
search_path
----------------
"$user",public
(1 行)
testdb=>
testdb=>
testdb=>
testdb=>
CREATE SCHEMAコマンドでスキーマを作る。所有者は指定しなければスキーマを作成したユーザーになる。”select current_schema()”関数を使って現在使用しているカレントスキーマを確認してみると、publicとなっている。 “SHOW search_path”を使ってスキーマのサーチパスを確認してみると、ユーザー名と同名のスキーマと、publicスキーマは指定しなくても検索されるようになっている。せっかくなのでここに、testscスキーマも追加してみる。
testdb=> SET search_path TO testsc,"$user",public;
SET
testdb=>
testdb=> SHOW search_path;
search_path
-----------------------------
testsc, "$user", public
(1 行)
testdb=>
testdb=> select current_schema();
current_schema
----------------
testsc
(1 行)
testdb=>
testdb=>
testdb=>
SETコマンドでsearch_pathにtestscを追加してみると、select current_schema()関数の結果がtestscスキーマに代わっている。これでtestscについても、スキーマの修飾子を省略することができるようになった。が、じつはSETコマンドで行われた設定はセッションが解放される毎に破棄される。この設定を恒久的にしたい場合は、postgresql.confファイルのsearch_pathの値を変更しPostgreSQLの再起動を行えばよい。
4.テーブルの作成とテストデータ挿入
CREATE TABLEコマンドを使ってtestscスキーマにtesttbテーブルを作成し、そこにテストデータをぶっ込んでみたい。
testdb=> CREATE TABLE testtb (
testdb(> ID integer,
testdb(> main_text text
testdb(> );
CREATE TABLE
testdb=>
testdb=> \d
リレーションの一覧
スキーマ | 名前 | 型 | 所有者
------------+--------+----------+----------
testsc | testtb | テーブル | test01
(1 行)
testdb=>
testdb=>
testdb=>
無事に、testscスキーマでtesttbテーブルができた。IDというカラムに通し番号を入れる想定でデータ型integer、カラム名main_textをデータ型text型で追加している。続いて、テストデータを入れてみる。
testdb=> INSERT INTO testtb ( ID, main_text ) VALUES ( '1' , '1行目だよ。一行目ですとも。' );
INSERT 0 1
testdb=> INSERT INTO testtb ( ID, main_text ) VALUES ( '2' , '2行目だよ。二行目ですとも。' );
INSERT 0 1
testdb=> INSERT INTO testtb ( ID, main_text ) VALUES ( '3' , '3行目だよ。三行目ですとも。' );
INSERT 0 1
testdb=> INSERT INTO testtb ( ID, main_text ) VALUES ( '4' , '4行目だよ。四行目ですとも。' );
INSERT 0 1
testdb=> INSERT INTO testtb ( ID, main_text ) VALUES ( '5' , '5行目だよ。五行目ですとも。' );
INSERT 0 1
testdb=> INSERT INTO testtb ( ID, main_text ) VALUES ( '6' , '6行目だよ。六行目ですとも。' );
INSERT 0 1
testdb=> INSERT INTO testtb ( ID, main_text ) VALUES ( '7' , '7行目だよ。七行目ですとも。' );
INSERT 0 1
testdb=> INSERT INTO testtb ( ID, main_text ) VALUES ( '8' , '8行目だよ。八行目ですとも。' );
INSERT 0 1
testdb=> INSERT INTO testtb ( ID, main_text ) VALUES ( '9' , '9行目だよ。九行目ですとも。' );
INSERT 0 1
testdb=> INSERT INTO testtb ( ID, main_text ) VALUES ( '10' , '10行目だよ。十行目ですとも。' );
INSERT 0 1
testdb=>
testdb=> SELECT * FROM testtb ORDER BY ID;
id | main_text
----+--------------------------------
1 | 1行目だよ。一行目ですとも。
2 | 2行目だよ。二行目ですとも。
3 | 3行目だよ。三行目ですとも。
4 | 4行目だよ。四行目ですとも。
5 | 5行目だよ。五行目ですとも。
6 | 6行目だよ。六行目ですとも。
7 | 7行目だよ。七行目ですとも。
8 | 8行目だよ。八行目ですとも。
9 | 9行目だよ。九行目ですとも。
10 | 10行目だよ。十行目ですとも。
(10 行)
testdb=>
testdb=>
testdb=>
とりあえずSQLのINSERTコマンドで10行入れてみた。同じくSQLでSELECTしてみると日本語も無事に格納されている。次はいよいよこのテストデータの入ったテストDB(データベースの略、ドラゴンボールではない)にPHPから接続してみるテストだが、その詳細は次回に譲る事としたい。