1. yteraoka

    Posted

    yteraoka
Changes in title
+PostgreSQL の ALTER DEFAULT PRIVILEGES
Changes in tags
Changes in body
Source | HTML | Preview
@@ -0,0 +1,110 @@
+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 に残るとなると組織の宣伝っぽいことは書きづらいですね。