Posted at

PostgreSQL の ALTER DEFAULT PRIVILEGES

More than 3 years have passed since last update.

PostgreSQL には ALTER DEFAULT PRIVILEGES といいう新規作成した TABLE などにデフォルトで設定される権限を変更する機能があります(標準SQLにはありません)。

(上記ドキュメントは 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 PRIVILEGESFORrole を指定するとその role を付与されたユーザーなら誰が作っても GRANT されるのかと思ったらこれは効きませんでした。残念。

組織 Advent Calendar 初挑戦ですが、Qiita に残るとなると組織の宣伝っぽいことは書きづらいですね。