pgAdminⅢを使用して、ユーザー、データベース、テーブルを作成します。
pgAdminⅢを起動します。
サーバー > PostgreSQL 9.4にが付いているかと思います。それを右クリックして、「接続」をクリックします。そうするとが消えますね。これでデータベースに接続できました。
データベースの準備は以下の手順を踏みます。
- ロールグループの作成
- ログインロール(ユーザー)の作成
- データベースの作成
- テーブルの作成
特に、ユーザーの作成とテーブルの作成を逆にすると権限の設定が反映されないことがありますので、注意が必要です。
サイドバーのグループロールを右クリックして「新しいグループロール」メニューをクリックし、設定画面を表示します。
「ロール名」に「bookmin-user」と入力して「OK」ボタンをクリックします。
同様にログインロールを右クリックして、設定ウィンドウを開き、下記の手順で情報を入力します。
- プロパティタブのロール名に「bookmin-manager」と入力します。
- 定義タブでログインパスワードを入力します。
- ロールメンバータブで先ほどグループロールで追加した「bookmin-user」が「メンバーでない」側に表示されています。それをクリックし、ボタン「>>」をクリックし右側の「メンバー」側へ移動します。
メンバーが移動しました。
「ロール特権」タブを確認します。このままの設定で「OK」ボタンをクリックします。
サイドバーを確認します。
ログインロールに「bookmin-manager」が追加されています。
ちなみに、ユーザー[postgres]は管理者で、データベースのあらゆる操作をすることができる権限を持っています。
Webアプリはデータベースの機能のうち、検索や登録などの一部の操作できれば良いので、必要な機能のみを操作できるユーザーを作って、そのユーザーでWebアプリからは接続するようにします。なんでもできる権限を持ったユーザーでWebアプリから接続できるようにしておくと、Tableを削除されてしまったり、データベースのコンマンドを実行されてデータを抜き出したりされる危険性を残すことになってしまいます。
通常Webアプリでは、データの操作のみで、データベースやテーブルを直接変更できないユーザーを作成し、そのユーザーでデータベースにアクセスするようにします。また、検索のみが出来るユーザーと登録更新のみが出来るユーザーを用意し、場面場面で使い分けたりもします。
データベースを作成します。
データベース作成ウィンドウを開きプロパティタブで名前に「bookmin」と入力して「OK」ボタンをクリックしてデータベースを作成します。するとサイドメニューのデータベースにbookminが追加されています。
bookminをクリックして内容を展開します。ここから様々な設定を行うわけですが、今回はbookminのアクセスコントールとテーブルを２つ追加するまでとします。
bookminを右クリックしてコンテキストメニューを開きプロパティをクリックします。
初期特権タブをクリックします。
権限フィールドにあるロールから「group bookmin-user」を選択します。するとその下のチェックボックスが全て選択済状態になります。
そのうち、ALL、TRUNCATE、REFERENCESのチェックを外し、「追加/変更」ボタンをクリックします。
DELETEのチェックをも外しておき、Webアプリからはデータの削除ができないようにしておくことも有用です。Webアプリからデータを無効にする場合、対象のテーブルに削除区分列を設けておいて、その区分を無効に更新するまでとして、物理的な削除はバッチ処理や、運用で適宜管理者が削除するというルールにしておくこともよく行われます。
続いてテーブルを作成します。
テーブル作成SQL文を以下に示します。SQLに関してはまたの機会に解説します。
CREATE TABLE master_staff( staff_id character(13) NOT NULL DEFAULT '', staff_name character varying(64) NOT NULL DEFAULT '', login_password character(96) NOT NULL DEFAULT '', mail_address character varying(64) NOT NULL DEFAULT '', staff_type smallint NOT NULL DEFAULT 0, job_type smallint NOT NULL DEFAULT 0, is_valid boolean NOT NULL DEFAULT true, changed_password boolean NOT NULL DEFAULT false, enter_date date NOT NULL DEFAULT CURRENT_DATE, retire_date date, memo character varying(3000) NOT NULL DEFAULT '', update_program character varying(64) NOT NULL DEFAULT '', update_user character(13) NOT NULL DEFAULT '', update_time timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP, create_time timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP, CONSTRAINT pkey_master_staff PRIMARY KEY (staff_id) ); CREATE TABLE init_staff( init_key character(64) NOT NULL DEFAULT '', staff_id character(13) NOT NULL DEFAULT '', expiration_time timestamp NOT NULL DEFAULT CURRENT_DATE, init_time timestamp, mail_body character varying(400) NOT NULL DEFAULT '', update_program character varying(64) NOT NULL DEFAULT '', update_user character(13) NOT NULL DEFAULT '', update_time timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP, create_time timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP, CONSTRAINT pkey_init_staff PRIMARY KEY (init_key) ); CREATE INDEX idx_staff_id ON init_staff (staff_id);
pgAminのツールメニューのSQLをクリックし、SQLエディタを開きます。
開いたSQLエティタウィンドウのSQLエディタタブ内に先ほどのSQL文をコピーします。
この画面のツールメニューの右三角ボタンをクリックし、SQL文を実行します。
サイドメニューのコンテキストメニュー内にある「リフレッシュ」をクリックすると
テーブルメニューにテーブルが追加されていることが確認できます。
作成されたテーブルのうち一つをクリックしてみると、右側プロパティのACL(Access Control List)に先ほどデータベースで設定した権限が反映されていることが確認できます。
また、テーブルメニューを展開すると、列やインデックスが作成されていることが確認できます。
これで準備完了です。
pgAdminで登録されたデータを参照したり編集したりする場合は、テーブルのコンテキストメニュー・データビューから下図のウィンドウを開いて確認編集することができます。もちろん上で紹介したSQLエディタからも可能です。
プログラムで使用するSQL文は、PHPなどのプログラム上にいきなり直接書くのではなく、コマンドや、pgAdminのようなDBマネージャー上で、構文エラーをなくし正しく動作するうように調整してからプログラムソース上に記述するようにしましょう。
ここまで、DBマネージャー(pgAdminⅢ)を使用してテーブルの作成まで見てきました。DBマネージャーを使用せずにコマンドラインから操作する方法ももちろんあります。実際の運用や本番環境の構築ではコマンドラインから行うことの方が多いかもしれません。ですが初学者にとっては、DBマネージャーはとても有益だと思っています。どのような機能や設定、機能同士の関係などが俯瞰的に見ることができるからです。よくわからない項目ばかりだと思いますが、イメージとしてなんとなく記憶されるだけでも後の理解度に大きく影響します。