PostgreSQLのDocker公式イメージを使って開発用DBを構築する #PostgreSQL

前提

  • 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

createuser

アプリケーション用ユーザの作成

  • 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 -h localhost -p 15432 -U postgres sample

createdb

スキーマの作成

5.8. スキーマ

権限の設定

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