2018/02/13(火)ゼロから始めるPHP…… (その2) PostgreSQL 入れる。
2018/02/13 06:38 アプリ側で、ハロワができたので、次は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
できてるっぽい。