PostgreSQL で「permission denied for table」が出てテーブルを操作できない

要約

ERROR: permission denied for table users は、接続そのものは成功していて、そのロールに当該テーブルの権限が無いという意味である。
接続エラーではないので、接続文字列やパスワードをいくら見直しても直らない。

つまずきやすいのは、PostgreSQL の権限が テーブル単位 と スキーマ単位 の二段構えになっている点である。
テーブルに SELECT を付けても、そのテーブルが属するスキーマに USAGE が無ければ到達できない。
逆にスキーマの USAGE だけでもテーブルは読めない。

myapp=> SELECT * FROM users;
ERROR:  permission denied for table users

よくある原因

1. テーブル権限が無い: テーブルは所有者以外には自動で権限が付かない。
   マイグレーションを管理者ロールで流し、アプリは別ロールで接続している構成でよく起きる。
2. スキーマの USAGE が無い: GRANT でテーブル権限だけ付けて満足しているケース。GRANT の公式ドキュメント（新しいタブで開く）↗ のとおり、スキーマに対しては CREATE と USAGE を別に指定する。
3. 所有者とアプリのロールが別: 誰がテーブルを作ったかで既定の権限が決まる。
   所有者を確認せずに権限を追いかけると原因を見失う。
4. 新規テーブルだけ漏れる: GRANT ... ON ALL TABLES IN SCHEMA は実行時点で存在するテーブルにしか作用しない。
   翌週のマイグレーションで増えたテーブルは、また権限が無い状態から始まる。
5. シーケンスの権限漏れ: SERIAL 列を持つテーブルへ INSERT すると裏でシーケンスを進める。
   テーブル権限だけでは permission denied for sequence users_id_seq になる。

解決策

1. 現状の権限を確認する

\dp users

Access privileges 列が空なら所有者以外は何もできない。
ロール名と付いている権限記号（r = SELECT、a = INSERT など）を先に把握する。

2. スキーマとテーブルの両方に付与する

GRANT USAGE ON SCHEMA public TO app_user;
GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA public TO app_user;
GRANT USAGE ON ALL SEQUENCES IN SCHEMA public TO app_user;

3 行をセットで実行する。
シーケンスを忘れると読めるのに書けない状態になる。

3. これから作られるテーブルにも効かせる

ALTER DEFAULT PRIVILEGES FOR ROLE migrator IN SCHEMA public
  GRANT SELECT, INSERT, UPDATE, DELETE ON TABLES TO app_user;
ALTER DEFAULT PRIVILEGES FOR ROLE migrator IN SCHEMA public
  GRANT USAGE ON SEQUENCES TO app_user;

FOR ROLE には テーブルを作るロール を指定する。
ここを実行者自身のロールと取り違えると、マイグレーションで増えたテーブルには反映されない。

4. 権限設計を単純にする

読み取り専用ロールと書き込みロールを分け、アプリは必要な方だけを使う。
ロールを増やすより、既存ロールに GRANT app_readonly TO app_user; のように束ねる方が管理しやすい。