前提
- Docker for Mac使用
- クライアントにpostgresqlを導入済み
- ホスト側からpsql等で接続するため
コンテナの起動
- イメージは
postgres:10
- コンテナ名は
dev-postgres
- パスワードは
postgres
- ポートは
15432
(コンテナの5432番にフォワードする)
$ docker run --name dev-postgres -e POSTGRES_PASSWORD=postgres -p 15432:5432 -d postgres:10
psqlでの接続
- postgresユーザで接続
$ psql -h localhost -p 15432 -U postgres
ユーザの作成
- postgresユーザで作成
- パスワードを設定する
- ユーザ名は
sample_owner
$ createuser sample_owner -h localhost -p 15432 -P -U postgres
アプリケーション用ユーザの作成
- postgresユーザで作成
- ユーザ名は
sample_app_user
$ createuser sample_app_user -h localhost -p 15432 -P -U postgres
データベースの作成
- postgresユーザで作成
- Ownerは
sample_owner
- データベース名は
sample
$ createdb -O sample_owner -h localhost -p 15432 -U postgres sample 'サンプルのデータベース'
下記のSQLを発行したのと同じ扱い
CREATE DATABASE sample OWNER sample_owner; COMMENT ON DATABASE sample IS 'サンプルのデータベース';
- psqlでの接続
$ psql -h localhost -p 15432 -U postgres sample
スキーマの作成
- スキーマはPublicを使うので作成しない
今日一日がんばって考えた結果、アプリケーション間での共有DBを憎み、ちいさなDBを前提にするなら、SchemaはPublicを使えばいいのでは、というお気持ちになりました。#PostgreSQL
— すーくん (@su_kun_1899) 2018年3月12日
権限の設定
PostgreSQLではユーザーを作成するとデータベースへの接続権限とPublicスキーマへの権限がデフォルトで付与されている。
なので、まず権限を剥がして、その後必要な権限を付与するという形にする。
- デフォルトの権限を剥がしておく
- publicスキーマの権限は、sampleデータベースに接続の上で剥がす
REVOKE CONNECT ON DATABASE postgres,template0,template1,sample FROM PUBLIC; REVOKE ALL ON SCHEMA public FROM PUBLIC;
- postgresユーザでデータベースとスキーマへの権限を付与する
-- データベースの操作権限 GRANT ALL ON DATABASE sample TO sample_owner; -- スキーマへの操作権限 GRANT ALL ON SCHEMA public TO sample_owner; GRANT USAGE ON SCHEMA public TO sample_app_user;
- sample_ownerユーザでテーブルへの権限を付与する
- sample_app_userにはCRUDのみ許可する
-- データベースへの接続権限 GRANT CONNECT ON DATABASE sample TO sample_app_user; -- テーブルへの操作権限 GRANT INSERT,SELECT,UPDATE,DELETE ON ALL TABLES IN SCHEMA public TO sample_app_user;
確認
- sample_ownerでログインできる
- sample_ownerはCREATE TABLEできる
- sample_userでログインできる
- sample_userでSELECTできる
- sample_userでCREATE TABLEできない
$ psql -h localhost -p 15432 -U sample_owner sample $ psql -h localhost -p 15432 -U sample_app_user sample
参考
PostgreSQL 10.0文書 kimulla.hatenablog.com qiita.com qiita.com