2018/02/13(Tue)ゼロから始めるPHP…… (その2) PostgreSQL 入れる。

はてブ数 2018/02/13 6:38 つーさ
自分用メモ ゼロから始めるPHP…… (その1) Hello world の続き

アプリ側で、ハロワができたので、次はDBを用意してつないでみたいと思う。
とりあえず、DBを用意するところまでやる。


データベースを準備しよう

PostgreSQL のインストール

ソースをとってきてコンパイルしてもいいのかもしれないが、Cygwinに用意されてるので使う。
Cygwinのインストールと同時に済んでるはず。
Cygserverを起動しておく必要もある。

データディレクトリの準備

適当にデータ用のディレクトリを掘ってinitdbすることで、データベース用のディレクトリが作れる。

-U でスーパユーザの名前を指定できる。(指定しないと現在のユーザ名になる?)
-W をつけるとスーパユーザのパスワード作成を同時に行える。
$ mkdir psqldata
$ /usr/sbin/initdb -D psqldata -U postgres -W

デーモン起動

pg_ctlでデーモンを起動すればいいっぽい。今後、PC再起動時はこのコマンドを打つ。
$ /usr/sbin/pg_ctl -D $HOME/psqldata -l $HOME/psqldata/postgres.log start

スーパーユーザで接続。

$ psql -U postgres postgres
psql (9.6.6)
"help" でヘルプを表示します.
おや、パスワード聞かれなかったぞ……?

initdb のときに -A md5 を付けてなかったな。

ローカル接続でもパスワードでユーザ認証するように設定する。

パスワード設定しても聞かれないの変だなーと思っていた。
なるほど、unixソケットやループバック接続のときはすべて信じる設定になっている。

psqldata/pg_hba.conf をテキストエディタで開いて認証メソッドを trust から md5 にしておく。
  # TYPE  DATABASE        USER            ADDRESS                 METHOD
- local   all             all                                     trust
+ local   all             all                                     md5
- host    all             all             127.0.0.1/32            trust
+ host    all             all             127.0.0.1/32            md5
- host    all             all             ::1/128                 trust
+ host    all             all             ::1/128                 md5
再起動して、接続してみる。
$ /usr/sbin/pg_ctl -D $HOME/psqldata -l $HOME/psqldata/postgres.log restart
サーバ停止処理の完了を待っています....完了
サーバは停止しました
サーバは起動中です。

$ psql -U postgres postgres;
ユーザ postgres のパスワード:
psql (9.6.6)
"help" でヘルプを表示します.
よさそう。

デフォルト付与される権限の削除

デフォルトでは「誰でも postgres DB に接続できて、postgres DB の public 領域に書ける」らしい。
postgres=# REVOKE ALL ON DATABASE postgres FROM PUBLIC; -- PUBLICロールから postgres DBへのアクセス権を削除。
postgres=# DROP SCHEMA public; -- postgres DB内のpublicスキーマを削除。

ユーザとDB作成。

ロールとスキーマと私と
PostgreSQLのDBサーバには複数のDBがあって、
DBには複数のスキーマ(名前空間みたいなもの。ネストはできない)があって、
スキーマの中には複数のテーブル(等)がある。

/[データベース]/[スキーマ]/[テーブル等]

スキーマ? 何それ知らない……。今までDB触ってて作った覚えもない。
https://www.postgresql.jp/document/9.6/html/ddl-schemas.html

平たく言うと、
DBを作るとデフォルトでpublicスキーマが作られてて、
publicスキーマは基本的にどのロールでも読み書きができる。
ユーザ名と同じスキーマが存在しない場合は、このpublicスキーマ内への操作と見なされる。

アクセス権はロール単位で管理される。
特にログインできるロールをユーザと呼び、ログインできないロールをグループと呼ぶことがある。
どのロールにどのオブジェクト(データベースとかスキーマとかテーブルとか)のどんな操作を許すかを設定する。
改めてユーザとDB作成。
試しに、 testapp ユーザ と testapp ユーザだけがアクセスできる testapp DB を用意してみる。
DB内にユーザ名と同じスキーマ名前のスキーマが存在していれば、デフォルトでそれが選ばれるので、
DB内にユーザ名と同じ名前のスキーマを1つだけ作っておけば、スキーマというものは基本的に意識しなくてよい
ということらしい。
-- ロール(ユーザ) testapp をパスワード testapp で作る。
postgres=# CREATE USER testapp WITH PASSWORD 'testapp';

-- データベース testapp を UTF8 で作る。
postgres=# CREATE DATABASE testapp ENCODING 'UTF8';

-- いったん誰もアクセスできなくして、ロール testapp にだけDBへの接続許可をあげる。
postgres=# REVOKE ALL ON DATABASE testapp FROM PUBLIC;
postgres=# GRANT CONNECT ON DATABASE testapp TO testapp;

-- testapp DBに移動して
postgres=# \\c testapp

-- publicスキーマの削除と testappユーザ向けのスキーマを作成。
testapp=# DROP SCHEMA public;
testapp=# CREATE SCHEMA AUTHORIZATION testapp;
うーん? でもこの方法だと、
後で、読み取り専用ユーザとかを後で追加したときに、
スキーマを指定してテーブルアクセスしないといけなくなるんじゃないの?
testapp=# ALTER DATABASE testapp SET search_path='testapp';
できました。

アクセスできるユーザを増やすには、DBへのCONNECTと、スキーマのUSAGEが最低限必要で、
それに加えて、必要な操作をGRANTしてあげればいいみたいだ。
testapp=# CREATE USER testapp2 WITH PASSWORD 'testapp';
testapp=# GRANT CONNECT ON DATABASE testapp TO testapp2;
testapp=# GRANT USAGE ON SCHEMA testapp TO testapp2;
今後作るテーブル含めて)全部SELECTしていいよ的な設定をする方法はあるのかな?
調べたら、 ROLE testapp が テーブルを作った時に、自動的に GRANT する みたいなことができるみたい。
testapp=# ALTER DEFAULT PRIVILEGES FOR ROLE testapp IN SCHEMA testapp GRANT SELECT ON TABLES TO testapp2;
||< 
テーブル以外のオブジェクトも、ALTER DEFAULT PRIVILEGES で、できる。
すでに作っちゃってるテーブルに対してはたぶん、適用されないので、先にテーブル作っちゃった場合は、
>||
testapp=# GRANT SELECT ON ALL TABLES IN SCHEMA testapp TO testapp2;
||< 

個別のテーブルに対して、grant したい場合は、普通に、
>||
testapp=# GRANT SELECT ON TABLE test TO testapp2;

今作ったユーザで今作ったDBに接続してみる

$ psql -U testapp testapp
TCP接続
$ psql -h localhost -U testapp testapp

テスト用ダミーテーブル作成とインサート

リレーショナルデータベースをリレーショナルデータベースとして使う気がないテーブル設計(笑)
testapp=> CREATE TABLE test (
testapp(>   key TEXT NOT NULL PRIMARY KEY,
testapp(>   value TEXT DEFAULT NULL
testapp(> );
CREATE TABLE
testapp=>INSERT INTO test VALUES ('test-key', 'test-value');
INSERT 0 1
できてるっぽい。