[解決済み】PostgreSQLで読み取り専用ユーザーを作成する方法は？

質問

PostgreSQLで、特定のデータベースからのSELECTのみを行えるユーザを作りたいのですが、どうすればいいですか？MySQLでは、コマンドは次のようになります。

GRANT SELECT ON mydb.* TO 'xxx'@'%' IDENTIFIED BY 'yyy';

PostgreSQL で同等のコマンドまたは一連のコマンドは何ですか？

試してみたが...

postgres=# CREATE ROLE xxx LOGIN PASSWORD 'yyy';
postgres=# GRANT SELECT ON DATABASE mydb TO xxx;

しかし、データベースに対して付与できるのは、CREATE、CONNECT、TEMPORARY、TEMPの4つのみであるようです。

どのように解決するのですか？

単一のテーブルに使用/選択権限を付与する

データベースに対してCONNECTのみを許可した場合、そのユーザーは接続することはできますが、他の特権を持つことはできません。このように、名前空間（スキーマ）にはUSAGEを、テーブルやビューにはSELECTを個別に付与する必要があります。

GRANT CONNECT ON DATABASE mydb TO xxx;
-- This assumes you're actually connected to mydb..
GRANT USAGE ON SCHEMA public TO xxx;
GRANT SELECT ON mytable TO xxx;

複数のテーブル/ビュー（PostgreSQL 9.0+)

PostgreSQLの最新バージョンでは、スキーマ内の全てのテーブル/ビュー/その他に対して、一つ一つ入力するのではなく、一つのコマンドで権限を付与することができます。

GRANT SELECT ON ALL TABLES IN SCHEMA public TO xxx;

これは、すでに作成されているテーブルにのみ影響します。より強力なのは、自動的に 新しいオブジェクトに割り当てられたデフォルトのロール を使用します。

ALTER DEFAULT PRIVILEGES IN SCHEMA public
   GRANT SELECT ON TABLES TO xxx;

デフォルトでは、このコマンドを発行したユーザーが作成したオブジェクト（テーブル）にのみ影響することに注意してください。ただし、発行したユーザーが所属するすべてのロールに設定することも可能です。しかし、新しいオブジェクトを作成する際に、自分が属しているすべてのロールのデフォルトの特権を取得するわけではありません...。データベースには所有ロールがあり、スキーマの変更はその所有ロールとして実行されるという考え方を採用するならば、デフォルトの特権はその所有ロールに割り当てるべきでしょう。IMHOでは、これは少し分かりにくいので、機能的なワークフローを考え出すために実験する必要があるかもしれません。

複数のテーブル/ビュー（PostgreSQL 9.0より前のバージョン）

長時間の複数テーブルの変更におけるエラーを回避するために、以下の「自動」プロセスで、必要な GRANT SELECT を各テーブル/ビューに追加します。

SELECT 'GRANT SELECT ON ' || relname || ' TO xxx;'
FROM pg_class JOIN pg_namespace ON pg_namespace.oid = pg_class.relnamespace
WHERE nspname = 'public' AND relkind IN ('r', 'v', 'S');

これは、コピー＆ペーストのために、すべてのテーブル、ビュー、およびシーケンスに対してGRANT SELECTするための関連するGRANTコマンドを公開で出力するはずです。当然ながら、これは既に作成されたテーブルにのみ適用されます。