PostgreSQL には ALTER DEFAULT PRIVILEGES といいう新規作成した TABLE などにデフォルトで設定される権限を変更する機能があります（標準SQLにはありません）。
- http://www.postgresql.org/docs/9.4/static/sql-alterdefaultprivileges.html
- http://www.postgresql.jp/document/9.4/html/sql-alterdefaultprivileges.html (日本語)
（上記ドキュメントは 9.4 ですがこの機能は 9.0 から存在します）
ここではこれの使い方を見てみたいと思います。
シンタックスはドキュメントにある通りですが、次のようになっており
ALTER DEFAULT PRIVILEGES
[ FOR { ROLE | USER } target_role [, ...] ]
[ IN SCHEMA schema_name [, ...] ]
abbreviated_grant_or_revoke
-
FORで誰が作成した場合に有効かを指定（デフォルトはこのコマンドの実行ユーザー） - スキーマを限定する場合は
IN SCHEMAで指定 -
abbreviated_grant_or_revokeで誰にどんな権限を与える（もしくは剥奪する）か
どのユーザーでログインしているかわかりやすいように psql のプロンプトをカスタマイズしておきます
$ cat > ~/.psqlrc <<_EOD_
\set PROMPT1 '%n@%/%R%#%x '
_EOD_
アプリから接続するための appuser と、レポーティング用の report ユーザーが存在する環境を作ります
CREATE USER appuser;
CREATE USER report;
CREATE DATABASE testdb01 OWNER appuser ENCODING 'utf8';
この状態で appuser が普通にテーブルを作成すると
appuser@testdb01=> CREATE TABLE table01 (id int, name text);
CREATE TABLE
appuser@testdb01=> \d
List of relations
Schema | Name | Type | Owner
--------+---------+-------+---------
public | table01 | table | appuser
(1 row)
appuser@testdb01=> \z
Access privileges
Schema | Name | Type | Access privileges | Column access privileges
--------+---------+-------+-------------------+--------------------------
public | table01 | table | |
(1 row)
appuser@testdb01=>
このように appuser がオーナーで他にアクセス権限はついていません。
もしも、毎度毎度 TABLE 作成ごとに report ユーザーに SELECT 権限をつける必要があるとしたらここで ALTER DEFAULT PRIVILEGES が使えます。
ALTER DEFAULT PRIVILEGES GRANT SELECT ON TABLES TO report;
appuser@testdb01=> ALTER DEFAULT PRIVILEGES GRANT SELECT ON TABLES TO report;
ALTER DEFAULT PRIVILEGES
appuser@testdb01=> CREATE TABLE table02 (id int, name text);
CREATE TABLE
appuser@testdb01=> \d
List of relations
Schema | Name | Type | Owner
--------+---------+-------+---------
public | table01 | table | appuser
public | table02 | table | appuser
(2 rows)
appuser@testdb01=> \z
Access privileges
Schema | Name | Type | Access privileges | Column access privileges
--------+---------+-------+-------------------------+--------------------------
public | table01 | table | |
public | table02 | table | appuser=arwdDxt/appuser+|
| | | report=r/appuser |
(2 rows)
appuser@testdb01=>
自動的に report ユーザーに SELECT 権限が付与されました。
ここでは report というユーザーに付与されるように設定しましたが、例えば nantoka_ro という Read-Only 用 role を作成し、これに SELECT 権限がつくようにしておいて、必要なユーザーにその role を付与する方が使い勝手は良いかもしれません。（PostgreSQL の user はログイン権限のついた role だからほぼ同じですけれども）
TABLE オーナー (appuser) と同じ権限で良いのであれば DEFAULT PRIVILEGES を使うまでもなく
GRANT appuser TO report;
で appuser の権限がまるっと report ユーザーに付与されます。複数ユーザーなら role でまとめられます。
ログインIDは人ごとに変えたいけれども DB やテーブルは共有するという場合は全員に共通の role を割り当て、各自はログイン後に set role ロール名 すると TABLE を作ったりした場合の OWNER がその role になります。
グループで共有するならグループ所有にするという考え方ですね。
ALTER DEFAULT PRIVILEGES の FOR で role を指定するとその role を付与されたユーザーなら誰が作っても GRANT されるのかと思ったらこれは効きませんでした。残念。
組織 Advent Calendar 初挑戦ですが、Qiita に残るとなると組織の宣伝っぽいことは書きづらいですね。