PostgreSQLでpublic以外のスキーマをつくって使う

dev.classmethod.jp

という記事を見かけたのが発端。いままで特に深く考えずにpublicスキーマの世界で生きていたんだけど、ユーザごとにスキーマつくって権限あげるのがお行儀いいよな、と思ってやってみた。

-- init.sql

CREATE DATABASE mydb TEMPLATE='template0' ENCODING='UTF8' LC_CTYPE='C' LC_COLLATE='C';
\c mydb;

REVOKE CREATE ON SCHEMA public FROM PUBLIC;
REVOKE ALL ON DATABASE mydb FROM PUBLIC;

CREATE ROLE readwrite;
CREATE SCHEMA myschema;
GRANT CONNECT ON DATABASE mydb TO readwrite;
GRANT USAGE, CREATE ON SCHEMA myschema TO readwrite;
GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA myschema TO readwrite;
ALTER DEFAULT PRIVILEGES IN SCHEMA myschema GRANT SELECT, INSERT, UPDATE, DELETE ON TABLES TO readwrite;
GRANT USAGE ON ALL SEQUENCES IN SCHEMA myschema TO readwrite;
ALTER DEFAULT PRIVILEGES IN SCHEMA myschema GRANT USAGE ON SEQUENCES TO readwrite;

CREATE USER myuser WITH PASSWORD 'myuserpassword';
GRANT readwrite TO myuser;

これをやるとmydbのmyschemaで生きるmyuserさんができる。CREATE DATABASEはローカルと本番でできたりできなかったりすると思うのでその辺りはよしなに。開発環境のDockerイメージでは/docker-entrypoint-initdb.d以下に置いておくと初回に実行してくれる。volumeのある状態だと実行されないので、一度失敗したらdocker compose down --volumesなどしないといけないことに注意。

db:
  image: postgres:14
  environment:
    POSTGRES_PASSWORD: postgres
  ports:
    - 5432:5432
  volumes:
    - db-volume:/var/lib/postgresql/data
    - ./init.sql:/docker-entrypoint-initdb.d/init.sql # <- 追加

Ecto

ついでにEctoでどうやって繋ぎに行くか書いておきます。

# /config/dev.exs

config :my_app, MyApp.Repo,
  username: "myuser",
  password: "myuserpassword",
  hostname: "db",
  database: "mydb",
  stacktrace: true,
  show_sensitive_data_on_connection_error: true,
  pool_size: 10,
  migration_default_prefix: "myschema", # <- 1
  after_connect: {Postgrex, :query!, ["SET search_path TO myschema", []]} # <- 2

migration_default_prefixをつくったスキーマに指定
after_connectでsearch_pathをつくったスキーマに指定

の2つ。前者はマイグレーションのときの話で、後者はアプリケーションが起動してSELECTとかINSERTとかするときの話。マルチテナンシーとかやろうとすると細かく設定したいので、マイグレーション実行毎、マイグレーションファイル毎、Ecto.Schema毎、クエリ毎に指定することもできるけど、今回の話はpublicやめようねというだけなので割愛。

mix ecto.migrateの後にmyschemaにテーブルができていることが確認できたらOK。

$ psql -h localhost -Upostgres -d mydb
Password for user postgres:
psql (14.3, server 14.4 (Debian 14.4-1.pgdg110+1))
Type "help" for help.

mydb=# \dp myschema.*
                                      Access privileges
    Schema    |        Name        | Type  | Access privileges | Column privileges | Policies
--------------+--------------------+-------+-------------------+-------------------+----------
 myschema     | users              | table |                   |                   |
(1 rows)

mmag

ハマったことメモなど

PostgreSQLでpublic以外のスキーマをつくって使う

Ecto

参考