PostgreSQL で Read-Only なユーザを作る

PostgreSQL で Read-Only なユーザを作るたびに「どうやって作るんだっけ？」となって調べているのでいい加減まとめておこうと思った次第だが、書いてみたらわざわざまとめるほどでもなかった...。

CREATE USER readonly PASSWORD 'password';
GRANT pg_read_all_data TO readonly;

昔はもっと面倒な手順があったが、PostgreSQL 14 以降では pg_read_all_data role が使えるようになってめちゃくちゃ簡単になった。¹ いい時代になった。

ちなみに「データの書き込みができる」ようにしたければ pg_write_all_data role も GRANT すればよい。

また「GRANT したあとで作成したテーブルやスキーマに対しては権限付くのだっけ？」と気になって試してみたが問題なさそうだった。

操作	pg_read_all_data	pg_write_all_data
USAGE	◯	◯
SELECT	◯	-
INSERT UPDATE DELETE	-	◯
TRUNCATE	-	-
CREATE ALTER DROP	-	-
GRANT REVOKE	-	-

補足: pg_write_all_data を付与できないケースもある

クラウドのマネージドデータベース (今回の場合は Azure Database for PostgreSQL) で「読み書きできるユーザ」を作成すべく pg_write_all_data ロールを付与しようとしたら、できなかった。

CREATE USER readwrite PASSWORD 'password';
GRANT pg_read_all_data TO readwrite;
GRANT pg_write_all_data TO readwrite;

ERROR:  permission denied to grant role "pg_write_all_data"
DETAIL:  Only roles with the ADMIN option on role "pg_write_all_data" may grant this role.

pg_write_all_data を割り当てるためには、GRANT を実行するユーザに ADMIN OPTION が付与されているか、superuser である必要があるとのこと。

ていうかデータベースインスタンス作成時に払い出された管理ユーザは superuser ではないのか？？？

SELECT usesuper FROM pg_user WHERE usename = CURRENT_USER;
-- → FALSE

superuser ではないっぽいねえ...。

じゃあ superuser としてクエリを実行するためにはどうすれば... (ググる)

サーバーを作成するときに、管理者ユーザーの資格情報を設定します。 管理者ユーザーは、サーバー上の最上位の特権ユーザーです。 これは、ロール azure_pg_admin に属します。 このロールには、完全なスーパーユーザーアクセス許可がありません。²

えっ

Azure Database for PostgreSQL フレキシブル サーバーでは、ユーザーに pg_write_all_data 属性を付与することはできません。 (中略) 回避策として、データベースとオブジェクトごとに、より限定的なレベルで同様のアクセス許可を付与することをお勧めします。³

そんな...

1. PostgreSQL: Documentation: 17: 21.5. Predefined Roles ↩

2. フレキシブル サーバーでのサーバーの概念 - Azure Database for PostgreSQL flexible server | Microsoft Learn ↩

3. セキュリティ - Azure Database for PostgreSQL flexible server | Microsoft Learn ↩