PostgreSQL の ALTER DEFAULT PRIVILEGES

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](http://www.postgresql.org/docs/9.0/static/sql-alterdefaultprivileges.html) から存在します）

ここではこれの使い方を見てみたいと思います。

シンタックスはドキュメントにある通りですが、次のようになっており

```
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` ユーザーが存在する環境を作ります

```sql
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` が使えます。

```sql
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 を使うまでもなく

```sql
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 に残るとなると組織の宣伝っぽいことは書きづらいですね。