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 から存在します）

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

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

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