~これは、雑魚技術者の勉強記録～

f:id:g071067:20200613191805p:plain

おつかれさまです。3日坊主・・・いや、第3回を迎えております。いやいや、今回まで懲りずにご覧いただけている方のほうが、、感謝です。

　前回、テーブル操作が可能なところまで到達し、データベースっぽい動きができるようになりました。ただ、残課題も多く出まして、権限を中心とした説明を残して終了となりました。

そこで、今回は、ユーザー・権限について説明する回にしたいと思います。

シリーズ

tecsak.hatenablog.com

<第３回の概要>

シリーズ
<第３回の概要>
3-1．ユーザーと属性
- ▼属性
3-2．ユーザーとロール
3-3．データベースオブジェクトと権限
3-4．実践！「"グループ"という概念」としてのロールのイメージ確認

~　前編　~

3-1．ユーザーと属性
　ユーザーと、ユーザーに関わる設定について見ていきます。

3-2．ユーザーとロール
　PostgreSQL独特のロール文化に注目！！

3-3．データベースオブジェクトと権限
　権限について見ていきます。

3-4．実践！「"グループ"という概念」としてのロールのイメージ確認
　今回は、座学が複雑でして、説明を先行せざるを得ませんでした。後半に実践が溜まっています。
ロールの概念を絡めた、権限の検証を行います。

~　後編　~
3-5．実践！publicスキーマにみるPUBLICキーワード
　前回の残課題でもある、publicの取り扱いに迫ります。

3-6．実践！スキーマを介した権限と動作検証
　第2回の復習にもなりますが、データベース・スキーマという枠組みと権限も絡めた総ざらいを行います。

3-1．ユーザーと属性

　PostgreSQLにもユーザーの概念があります(当たり前でしょうか)。ユーザーに権限を付与することで、使用者である人間達が共同で環境を使用するにあたっての、住み分けが出来るようになります。

　"住み分け"という言葉は、これまでの回で何度も登場してきました。エンコーディングなどといった設定類や、名前空間としての名称類が、1つの環境のなかで使い分け出来るよう、 "データベース"、"スキーマ"という仕切りが利用できるということでした。

これに権限という概念が加わることで、いよいよシステムらしくなるかと思います。 "データベース"、"スキーマ"も、権限の住み分けとしても利用することが可能であり、第2回の延長のような形でお読みいただけたらと思います。

　ユーザーはデータベースクラスタのレベルで定義され、クラスタ内のすべてのデータベースで有効となります。

f:id:g071067:20200626214444p:plain

　はじめに、ユーザーを中心に話を進め、ユーザー作成時に考慮が必要な設定を紹介したいと思います。その後、権限について紹介していきます。

▼属性

　PostgreSQLのユーザーには、属性という概念があります。いわゆる、他DBでいうところのシステム権限ととらえて問題ないと思います。これに関して、マニュアルでも明確な呼称が定義されていません。単に"権限"と表記されるなど、読み手も困ってしまいます(PostgreSQLではこのような状況によく出くわします・・・)。ここでは、マニュアル(の一部)より引用して、"属性"と呼ぶことにします。

　別途、とあるテーブルを参照できる、などのオブジェクト権限とは別の概念となります。ざっと書き走りますが、設定実施に関しても、属性はALTER ROLE(USER)を、オブジェクト権限はGRANTを使用します。

　主な属性を紹介いたします。

属性名
スーパーユーザー権限
データベースの作成権限\|
ユーザーの作成権限
ログイン権限
レプリケーション権限
継承

名称だけで、およそ属性の意味が伝わると思いまして、全ての説明は省きますが、一部ご説明します。

・「スーパーユーザー権限」属性
　なんでもアリのユーザーです。逆の表現で、マニュアルからの引用となりますが、ログインを除き、スーパーユーザーに対する権限に関するチェックは行われません。　initdb コマンドでそのデータベースクラスタを作成したユーザが、自動的にスーパーユーザーとして登録されます。使用は必要最低限に留めるべきです。

・「ユーザーの作成権限」属性
　ユーザーの作成ができます。　名称そのままですが、念のため、ユーザーを作成できるということは当ユーザー自身の権限が弱小であったとしても、強力なユーザーを作成する力を持ちますので、取り扱いには注意となります。

・「継承」属性
　そのロールが属するロールの権限を"継承"するかどうかを決定します。デフォルトは継承されます。 f:id:g071067:20200626214459p:plain

※「レプリケーション権限」に関しては、レプリケーション環境を紹介する回で説明させていただきます。

3-2．ユーザーとロール

　ロールについてご説明します。PostgreSQLにおけるロールは、ひと癖ありますので、よく理解する必要があります。実践とあわせて、詳しく見ていきたいと思います。

▼ロールと経緯

　はじめに、世間体でのイメージとして、"ロール"、"ロール"とは？と考えた場合、「権限の集合」を思い浮かべられるのではないかと思います。例えば、複数の権限であるものの、いつも同じ組み合わせで、この権限付与が定型業務かのようにパターン化されている場合、これらの権限をグルーピングする概念があれば、付与・はく奪、そして組み合わせの変更・・・と運用面で効果的に活用できます。

ユーザーと権限のn:nの関係を解消しています。

　さて、PostgreSQLにおけるロールについて、一旦マニュアル(日本語)抜粋そのままを読んでみたいと思います。

<マニュアル抜粋>

ロールの概念には、"ユーザ"という概念と"グループ"という概念が含まれます。 PostgreSQLバージョン8.1より前まででは、ユーザとグループは異なる種類の実体として扱われていました。 しかし、現在ではロールしか存在しません。 すべてのロールは、ユーザとして、グループとして、またはその両方として動作することができます。

　なかなか、頭に浸透されないのは、私だけでしょうか。まずはじめに、PostgreSQLのロールとは、「ユーザーの集合」である点を認識する必要があります。これを、マニュアルでは「"グループ"という概念」という用語で表現していますが、権限ではなく、ユーザーのほうをグルーピングしました、ということです。

　次いで、「"ユーザ"という概念」が、同一の実体として使用できるということです(8.1以降)。何を言っているのか・・・。　　例えば、AユーザーとBユーザーを設けたとします。本来、語弊なく表現するのであれば「AロールとBロールを設けたとします」と書くべきところです。ここで、BユーザーにAユーザーが加入できるわけです。このとき、Bとは「ユーザとして、グループとして、またはその両方として動作」する実体であります。

　グループXを作り、XにAとBを加入させたい～(Xは、要は箱だけ)　という感覚をお持ちの方もいらっしゃるかと思いますが、PostgreSQLでは上記のような仕様となります。　今後の本稿での表現について (前回以前でも該当があるかもしれませんが。。) 、"ユーザー"という表現を用います("ロール"ではないということ)(マニュアルが"ユーザ"表記でして、気になる方、すみません)。

f:id:g071067:20200626214507p:plain f:id:g071067:20200626214524p:plain

▼PUBLICキーワード

　ここで、前談のロールにおける「"グループ"という概念」を踏まえ、PUBLICキーワードについてご説明いたします。 PUBLICキーワードとはPUBLICロールのことでして、全てのロールがPUBLICロールに含まれる形となります。

　また、PUBLICキーワードには、下記のような権限が付与されています。つまり、全ユーザーがデフォルトで付与される権限です。

対象	権限名	説明
データベース	CONNECT	データベースへの接続権限
データベース	CREATE TEMP TABLE	一時テーブルの作成権限

言葉で説明すると、あっさり終了するのですが、取り扱いには注意です。実践でさらに見ていきます。

<再掲> f:id:g071067:20200626214444p:plain

▼ユーザー作成

　作成は、SQLコマンド「CREATE ROLE」(CRETE USER)で行います。OSコマンド「createuser」での方法も用意されています。　SQLによる方法では、"CREATE xxxx"で以て、ROLEとUSERの2種類あります。違いは些細でして、「ログイン権限」属性のデフォルト(オプション指定しなかった場合にどうなるのか)が異なるのみです。ROLEは権限無し(ログイン不可)がデフォルトで、USERは権限有がデフォルトになります。　このあたりも、旧ロール仕様の名残りです。

3-3．データベースオブジェクトと権限

　前述のとおり、属性とは別物で、その名の通り、"権限"です。ポイントを纏めましたので、これに従ってご説明します。

▼データベースオブジェクトと権限

　ユーザーのテーブルなどのオブジェクトに対して行使可能となる権限の付与が可能です。厳密には、オブジェクトとは、テーブル・列・ビュー・データベース・関数・スキーマ・テーブル空間など、多岐にわたりますが、本稿では一部にフォーカスして紹介させてください。

　基本的には、予想通りの権限設定が可能と思って間違いありません。詳細な説明は割愛しますが、SELECT、INSERT、UPDATEなどの可否は指定可能ですまた、WITH GRANT OPTIONといった、権限の付与権限も使用可能です。

▼データベース・スキーマと権限

　データベース・スキーマに対して付与可能な権限を紹介します。これらはオブジェクトの集合であり、データベースクラスタを仕切る枠組みでしたが、枠単位で制御可能な権限にはどのようなものがあるか、見てみましょう。

対象	権限名	説明
データベース	CREATE	スキーマの作成権限
スキーマ	CREATE	オブジェクトの作成権限
スキーマ	USAGE	オブジェクトへのアクセス権限

CREATE・・・次いで小さい単位のオブジェクトの作成権限があります。加えて、スキーマの場合はUSAGE・・・参照や更新が可能となる権限があります。スキーマ内のオブジェクト全体に対して有効となります。

▼オーナーと権限

所有者は、所有している(オーナーである)オブジェクトに対し、デフォルト状態では全権を持ちます。別途、自身の権限をはく奪可能で、読み取り専用のイメージで設定を施すことはできます。また、削除およびテーブル変更はオーナーのみが実施可能です。

ここで、オーナーとはユーザーであり、ユーザーはロールですのでとある1つの固有名詞のユーザーのみがオーナーと見なすことができない可能性もあるということです。

▼構築時使用のスーパーユーザーのパスワード変更

　構築時に使用したスーパーユーザーは、自動作成されるということでしたが、パスワード設定が行われません。パスワードは"属性"に該当しまして、これの変更には前述のとおりSQLの「ALTER ROLE」にて行います。　引き続き、OS認証のみで使用の場合は実施不要です。

-bash-4.2$ psql
psql (9.6.3)
"help" でヘルプを表示します.

postgres=# alter role postgres password 'password';
ALTER ROLE
postgres=#

3-4．実践！「"グループ"という概念」としてのロールのイメージ確認

　「"グループ"という概念」としての、ロールの動作を検証してみたいと思います。

　まずはじめに、ユーザーを複数作成してみます(user_x、user_y、user_a、user_b、user_i)。この時点では、「"ユーザ"という概念」として(未だ「"グループ"という概念」としての用途には至っていないですね)存在させます。

　その後、とあるオブジェクトに対する権限がuser_x、user_yユーザーにある状態で、他ユーザー(user_a、user_b、user_i)の権限的振る舞いがどのようになるか見てみたいと思います。　当然、user_aユーザーに直接的に権限付与を図っては題意としての意味がありません。

　user_xユーザーやuseryユーザーへ加入させ、これらが「"グループ"という概念」も含んだ状態での、権限的振る舞いを観察してまいります。　また、"権限がuser_x、user_yユーザーにある状態"について、 user_xユーザーはオブジェクトのオーナーとして、user_yユーザーは(オーナーより)権限付与された形にて実現してみることにします。

f:id:g071067:20200626214535p:plain

f:id:g071067:20200626214544p:plain

▼シナリオ

流れ	詳細/項目建て
登場人物を全て作成してまいります
作成全般	①ユーザー作成
	②権限の確認(初期状態)
	③テーブル作成(user_x、user_aユーザーにて)
	④テーブルの確認
確認	⑤権限の確認
確認	⑥テーブルの参照を試み
ロール加入させます(オーナー側)
加入(継承あり)	⑦user_aおよびuser_bユーザーをuser_xユーザーに加入、
加入(継承あり)	user_iユーザーをuser_aユーザーに加入
確認	⑧権限の確認
確認	⑨テーブルの参照を試み
継承なし	⑩継承属性のはく奪
確認	⑪テーブルの参照を試み
(継承属性を一旦戻します)	⑫継承属性の付与(一旦戻し)
ロール加入させます(権限所有ユーザー側)
加入(継承あり)	⑬user_aおよびuser_bユーザーをuser_yユーザーに加入とuser_xユーザーからの脱退
加入(継承あり)
確認	⑭テーブルの参照を試み
継承なし	⑮「継承」属性のはく奪
確認	⑯テーブルの参照を試み

▼登場人物を全て作成してまいります

①ユーザー作成

ユーザーを作成します。　いっぱい作るゾー！参照程度の検証につき、属性としましては「ログイン権限」属性のみ付与といたしましょうか。 sqlにて作成してみます。

ここでは、"postgres"ユーザー(スーパーユーザー)で作成を行ってしまいます。 (接続データベースは何でもかまいません)

adb=# select current_user;
 current_user
--------------
 postgres
(1 行)

adb=#
adb=# create role user_x with LOGIN PASSWORD 'password';
CREATE ROLE
adb=# create role user_y with LOGIN PASSWORD 'password';
CREATE ROLE
adb=# create role user_a with LOGIN PASSWORD 'password';
CREATE ROLE
adb=# create role user_b PASSWORD 'password';
CREATE ROLE
adb=# create role user_i with LOGIN PASSWORD 'password';
CREATE ROLE
adb=#
adb=# \du
                                                      ロール一覧
 ロール名 |                                              属性                                              | メンバー
----------+------------------------------------------------------------------------------------------------+----------
 postgres | スーパーユーザ, ロールを作成できる, DBを作成できる, レプリケーション, 行単位セキュリティを無視 | {}
 user_a   |                                                                                                | {}
 user_b   | ログインできない                                                                               | {}
 user_i   |                                                                                                | {}
 user_x   |                                                                                                | {}
 user_y   |                                                                                                | {}

adb=#

※行単位セキュリティを無視：9.5より搭載された、行単位セキュリティ(RLS)を適用するかどうかの属性となります。　特定のレコードのみ見せることが可能な機能で、デフォルトはOFF(無視・適用しない)です。

「ログイン権限」属性を、with LOGIN句で指定しています。また、パスワードをPASSWORD句によります。 \duでユーザー一覧を表示。全ユーザーとも、作成された様子ですね。

ここで、あえてuser_bユーザーに対してのみ、「ログイン権限」属性の指定を行いませんでした。見えたものは、\du結果の第2カラムの表示で、「ログイン権限」属性については属性無しの場合に表示が行われるということです。 (属性ありの場合に表示が行われるものもあり、ややこしい)

user_bユーザーにも「ログイン権限」属性付与して、等しくしておきます。

adb=# alter role user_b with LOGIN;
ALTER ROLE
adb=#
adb=# \du
                                                      ロール一覧
 ロール名 |                                              属性                                              | メンバー
----------+------------------------------------------------------------------------------------------------+----------
 postgres | スーパーユーザ, ロールを作成できる, DBを作成できる, レプリケーション, 行単位セキュリティを無視 | {}
 user_a   |                                                                                                | {}
 user_b   |                                                                                                | {}
 user_i   |                                                                                                | {}
 user_x   |                                                                                                | {}
 user_y   |                                                                                                | {}

adb=#

②権限の確認(初期状態)

　現状の権限(オブジェクト権限)を一旦見てみます。追って、テスト用のオブジェクトを作成してまいります。

adb=# \z
                       アクセス権
 スキーマ | 名前 | 型 | アクセス権 | 列の権限 | ポリシー
----------+------+----+------------+----------+----------
(0 行)

adb=#

空っぽからのスタートです。

③テーブル作成(user_x、user_aユーザーにて)

user_x、user_aユーザーにてテーブルを作成したいと思います。即ち、テーブルのオーナーはそれぞれuser_x、user_aとなる、ということです (ここでは、スキーマはこだわりません)。

adb=# \c - user_x
select current_user;
データベース "adb" にユーザ"user_x"として接続しました。
adb=> select current_user;
 current_user
--------------
 user_x
(1 行)

adb=>
adb=> create table roletest_x(
adb(> id integer,
adb(> value varchar(20)
adb(> );
CREATE TABLE
adb=>
adb=> insert into roletest_x values(1,'roletest data');
INSERT 0 1
adb=>
adb=> select * from roletest_x;
 id |     value
----+---------------
  1 | roletest data
(1 行)

adb=>
adb=> \c - user_a
データベース "adb" にユーザ"user_a"として接続しました。
adb=> select current_user;
 current_user
--------------
 user_a
(1 行)

adb=>
adb=> create table roletest_a(
adb(> id integer,
adb(> value varchar(20)
adb(> );
CREATE TABLE
adb=>
adb=> insert into roletest_a values(2,'roletest data');
INSERT 0 1
adb=>
adb=> select * from roletest_a;
 id |     value
----+---------------
  2 | roletest data
(1 行)

adb=>

はい。「\c - <ユーザー名>」で、使用するユーザーを変更しています。接続するDBの変更は「\c <DB名>」でしたね。なんだか、ややこしい。。　ハイフンが間に入るかどうかということです。

その後、current_user関数にて、為念での使用ユーザー変更確認を行っています。 ※以降、重要な場合を除き、使用ユーザー確認を省略する場合があります。

④テーブルの確認

　⑤にて実施します。

⑤権限の確認

　見てみます。

adb=> \dt
            リレーションの一覧
 スキーマ |    名前    |    型    | 所有者
----------+------------+----------+--------
 public   | roletest_a | テーブル | user_a
 public   | roletest_x | テーブル | user_x
(2 行)

adb=>
adb=> \z
                             アクセス権
 スキーマ |    名前    |    型    | アクセス権 | 列の権限 | ポリシー
----------+------------+----------+------------+----------+----------
 public   | roletest_a | テーブル |            |          |
 public   | roletest_x | テーブル |            |          |
(2 行)

adb=>

　オーナーについて(\dt結果の"所有者"欄)、前述どおりの表示を確認できました。権限について、テーブルの存在はあるものの、どうやら右3カラムは空欄のようです。誰もアクセスできないのでしょうか(スーパーユーザーは無条件でできますが)？

　先ほど、既にuser_x、user_a自身での挿入・参照が可能でした。即ち、\z表記にオーナー分はあえて含まれないようです。

⑥テーブルの参照を試み

　逆に、オーナー以外で参照不可であることを念のため見ておきましょうか。未だロールに加入していません。単純な検証です。

adb=> \c - user_x
select * from roletest_x;
データベース "adb" にユーザ"user_x"として接続しました。
adb=> select * from roletest_x;
 id |     value
----+---------------
  1 | roletest data
(1 行)

adb=> \c - user_y
データベース "adb" にユーザ"user_y"として接続しました。
adb=> select * from roletest_x;
ERROR:  permission denied for relation roletest_x
adb=> \c - user_a
データベース "adb" にユーザ"user_a"として接続しました。
adb=> select * from roletest_x;
ERROR:  permission denied for relation roletest_x
adb=> \c - user_b
データベース "adb" にユーザ"user_b"として接続しました。
adb=> select * from roletest_x;
ERROR:  permission denied for relation roletest_x
adb=> \c - user_i
データベース "adb" にユーザ"user_i"として接続しました。
adb=> select * from roletest_x;
ERROR:  permission denied for relation roletest_x
adb=>
adb=> \c - user_x
データベース "adb" にユーザ"user_x"として接続しました。
adb=> select * from roletest_a;
ERROR:  permission denied for relation roletest_a
adb=> \c - user_y
データベース "adb" にユーザ"user_y"として接続しました。
adb=> select * from roletest_a;
ERROR:  permission denied for relation roletest_a
adb=> \c - user_a
データベース "adb" にユーザ"user_a"として接続しました。
adb=> select * from roletest_a;
 id |     value
----+---------------
  2 | roletest data
(1 行)

adb=> \c - user_b
データベース "adb" にユーザ"user_b"として接続しました。
adb=> select * from roletest_a;
ERROR:  permission denied for relation roletest_a
adb=> \c - user_i
データベース "adb" にユーザ"user_i"として接続しました。
adb=> select * from roletest_a;
ERROR:  permission denied for relation roletest_a
adb=>

OWNER自身以外は全てエラーとなりました。エラーメッセージも、権限エラーである旨が伺える内容となっています。

さあ、ここからが本題です！！

▼ロール加入させます(オーナー側)

⑦user_aおよびuser_bユーザーをuser_xユーザーに加入、user_iユーザーをuser_aユーザーに加入

前述の図のような入れ子構造にしてまいります。意図としては、オーナーに対して加入して、参照の振る舞いがどうなるのか？ということでしたね。

adb=> \c - user_x
データベース "adb" にユーザ"user_x"として接続しました。
adb=> grant user_x to user_a;
GRANT ROLE
adb=> grant user_x to user_b;
GRANT ROLE
adb=>
adb=> \c - user_a
データベース "adb" にユーザ"user_a"として接続しました。
adb=> grant user_a to user_i;
GRANT ROLE
adb=>
adb=> \du
                                                      ロール一覧
 ロール名 |                                              属性                                              | メンバー
----------+------------------------------------------------------------------------------------------------+----------
 postgres | スーパーユーザ, ロールを作成できる, DBを作成できる, レプリケーション, 行単位セキュリティを無視 | {}
 user_a   |                                                                                                | {user_x}
 user_b   |                                                                                                | {user_x}
 user_i   |                                                                                                | {user_a}
 user_x   |                                                                                                | {}
 user_y   |                                                                                                | {}

adb=>

"メンバー"欄に、加入しているロールの名称が表示されています。

加入もgrant文で行われます。

　grant  <加入先>  to  <対象ユーザー>

⑧権限の確認

　加入が完了したところで、ロールが絡んだ権限の表示仕様も見てみましょうか。

adb=> \z
                             アクセス権
 スキーマ |    名前    |    型    | アクセス権 | 列の権限 | ポリシー
----------+------------+----------+------------+----------+----------
 public   | roletest_a | テーブル |            |          |
 public   | roletest_x | テーブル |            |          |
(2 行)

adb=>

先ほどと変わりありませんね。オーナーとしての権限の、ロール経由での付与分に関しても、表示されないようです。

⑨テーブルの参照を試み

　ロール加入状態で、あらためて。いざ本題です。

adb=> \c - user_x
データベース "adb" にユーザ"user_x"として接続しました。
adb=> select * from roletest_x;
 id |     value
----+---------------
  1 | roletest data
(1 行)

adb=> \c - user_y
データベース "adb" にユーザ"user_y"として接続しました。
adb=> select * from roletest_x;
ERROR:  permission denied for relation roletest_x
adb=> \c - user_a
データベース "adb" にユーザ"user_a"として接続しました。
adb=> select * from roletest_x;
 id |     value
----+---------------
  1 | roletest data
(1 行)

adb=> \c - user_b
データベース "adb" にユーザ"user_b"として接続しました。
adb=> select * from roletest_x;
 id |     value
----+---------------
  1 | roletest data
(1 行)

adb=> \c - user_i
select * from roletest_x;
データベース "adb" にユーザ"user_i"として接続しました。
adb=> select * from roletest_x;
 id |     value
----+---------------
  1 | roletest data
(1 行)

adb=>
adb=> \c - user_x
データベース "adb" にユーザ"user_x"として接続しました。
adb=> select * from roletest_a;
ERROR:  permission denied for relation roletest_a
adb=> \c - user_y
データベース "adb" にユーザ"user_y"として接続しました。
adb=> select * from roletest_a;
ERROR:  permission denied for relation roletest_a
adb=> \c - user_a
データベース "adb" にユーザ"user_a"として接続しました。
adb=> select * from roletest_a;
 id |     value
----+---------------
  2 | roletest data
(1 行)

adb=> \c - user_b
データベース "adb" にユーザ"user_b"として接続しました。
adb=> select * from roletest_a;
ERROR:  permission denied for relation roletest_a
adb=> \c - user_i
データベース "adb" にユーザ"user_i"として接続しました。
adb=> select * from roletest_a;
 id |     value
----+---------------
  2 | roletest data
(1 行)

adb=>

はい。グループに所属のユーザーは参照可能となりました。「継承」属性が効いた、ということですね。

⑩継承属性のはく奪

ここで、継承属性をはく奪してみます。

adb=> \c - postgres
データベース "adb" にユーザ"postgres"として接続しました。
adb=#
adb=# alter role user_x NOINHERIT;
ALTER ROLE
adb=# alter role user_y NOINHERIT;
ALTER ROLE
adb=# alter role user_a NOINHERIT;
ALTER ROLE
adb=# alter role user_b NOINHERIT;
ALTER ROLE
adb=# alter role user_i NOINHERIT;
ALTER ROLE
adb=#
adb=# \du
                                                      ロール一覧
 ロール名 |                                              属性                                              | メンバー
----------+------------------------------------------------------------------------------------------------+----------
 postgres | スーパーユーザ, ロールを作成できる, DBを作成できる, レプリケーション, 行単位セキュリティを無視 | {}
 user_a   | 継承なし                                                                                       | {user_x}
 user_b   | 継承なし                                                                                       | {user_x}
 user_i   | 継承なし                                                                                       | {user_a}
 user_x   | 継承なし                                                                                       | {}
 user_y   | 継承なし                                                                                       | {}

adb=#

継承属性について、"継承なし"で表示されています。こちらも、属性付与無き場合に表示される仕様のようです。

文法的には、 revokeというよりは、"INHERIT|NOINHERIT "を設定・適用する、という書き方となり、今回の目的としては、後者NOINHERITの適用となります。

⑪テーブルの参照を試み

　継承属性のはく奪状態で、あらためて参照の振る舞いを見てみましょう。

adb=# \c - user_x
データベース "adb" にユーザ"user_x"として接続しました。
adb=> select * from roletest_x;
 id |     value
----+---------------
  1 | roletest data
(1 行)

adb=> \c - user_y
データベース "adb" にユーザ"user_y"として接続しました。
adb=> select * from roletest_x;
ERROR:  permission denied for relation roletest_x
adb=> \c - user_a
データベース "adb" にユーザ"user_a"として接続しました。
adb=> select * from roletest_x;
ERROR:  permission denied for relation roletest_x
adb=> \c - user_b
データベース "adb" にユーザ"user_b"として接続しました。
adb=> select * from roletest_x;
ERROR:  permission denied for relation roletest_x
adb=> \c - user_i
データベース "adb" にユーザ"user_i"として接続しました。
adb=> select * from roletest_x;
ERROR:  permission denied for relation roletest_x
adb=>
adb=> \c - user_x
データベース "adb" にユーザ"user_x"として接続しました。
adb=> select * from roletest_a;
ERROR:  permission denied for relation roletest_a
adb=> \c - user_y
データベース "adb" にユーザ"user_y"として接続しました。
adb=> select * from roletest_a;
ERROR:  permission denied for relation roletest_a
adb=> \c - user_a
データベース "adb" にユーザ"user_a"として接続しました。
adb=> select * from roletest_a;
 id |     value
----+---------------
  2 | roletest data
(1 行)

adb=> \c - user_b
データベース "adb" にユーザ"user_b"として接続しました。
adb=> select * from roletest_a;
ERROR:  permission denied for relation roletest_a
adb=> \c - user_i
データベース "adb" にユーザ"user_i"として接続しました。
adb=> select * from roletest_a;
ERROR:  permission denied for relation roletest_a
adb=>

結果として、オーナーのみが参照可能となりました。「"グループ"という概念」は、権限の振る舞いの面からは、実質見られません。先ほどの、ロール加入前と同じ結果になった、という表現もできますね。

▼(継承属性を一旦戻します)

⑫継承属性の付与(一旦戻し)

　継承属性について、一旦、もどに戻させていただきます。

adb=> \c - user_x
データベース "adb" にユーザ"user_x"として接続しました。
adb=> alter role user_x INHERIT;
ERROR:  permission denied
adb=> alter role user_y INHERIT;
ERROR:  permission denied
adb=> alter role user_a INHERIT;
ERROR:  permission denied
adb=> alter role user_b INHERIT;
ERROR:  permission denied
adb=> alter role user_i INHERIT;
ERROR:  permission denied
adb=>
adb=> \c - user_a
データベース "adb" にユーザ"user_a"として接続しました。
adb=> alter role user_x INHERIT;
ERROR:  permission denied
adb=> alter role user_y INHERIT;
ERROR:  permission denied
adb=> alter role user_a INHERIT;
ERROR:  permission denied
adb=> alter role user_b INHERIT;
ERROR:  permission denied
adb=> alter role user_i INHERIT;
ERROR:  permission denied
adb=>
adb=> \c - postgres
データベース "adb" にユーザ"postgres"として接続しました。
adb=# alter role user_x INHERIT;
ALTER ROLE
adb=# alter role user_y INHERIT;
ALTER ROLE
adb=# alter role user_a INHERIT;
ALTER ROLE
adb=# alter role user_b INHERIT;
ALTER ROLE
adb=# alter role user_i INHERIT;
ALTER ROLE
adb=#
adb=# \du
                                                      ロール一覧
 ロール名 |                                              属性                                              | メンバー
----------+------------------------------------------------------------------------------------------------+----------
 postgres | スーパーユーザ, ロールを作成できる, DBを作成できる, レプリケーション, 行単位セキュリティを無視 | {}
 user_a   |                                                                                                | {user_x}
 user_b   |                                                                                                | {user_x}
 user_i   |                                                                                                | {user_a}
 user_x   |                                                                                                | {}
 user_y   |                                                                                                | {}

adb=#

あえて、ユーザー自身での、継承属性付与を一度試しており、失敗に至っています。「ユーザーの作成権限」属性さえ与えなければ、乱用(自己パワーアップ)されることはありません。

(ロールへの加入については、ユーザー自身で可能でした。従って、ユーザー自身で「継承」属性を行使しての権限付与が行われる可能性があるということです。　ここで、DBAとしては「継承」属性を与えないことでコントロールすることになります)

▼ロール加入させます(権限所有ユーザー側)

⑬user_aおよびuser_bユーザーをuser_yユーザーに加入とuser_xユーザーからの脱退

　次に、オブジェクト権限を付与し、オーナーでなく権限付与されたロールへの加入でどうなるか見てみましょう。

ということで、はじめにuser_yユーザーに対して参照権限を付与します。その後、加入ロールをuser_xユーザーからuser_yユーザーへ変更します。

adb=# \c - user_x
データベース "adb" にユーザ"user_x"として接続しました。
adb=> grant select on roletest_x to user_y;
GRANT
adb=>
adb=> revoke user_x from user_a;
REVOKE ROLE
adb=> revoke user_x from user_b;
REVOKE ROLE
adb=>
adb=> \c - user_y
データベース "adb" にユーザ"user_y"として接続しました。
adb=> grant user_y to user_a;
GRANT ROLE
adb=> grant user_y to user_b;
GRANT ROLE
adb=>
adb=> \du

                                                      ロール一覧
 ロール名 |                                              属性                                              | メンバー
----------+------------------------------------------------------------------------------------------------+----------
 postgres | スーパーユーザ, ロールを作成できる, DBを作成できる, レプリケーション, 行単位セキュリティを無視 | {}
 user_a   |                                                                                                | {user_y}
 user_b   |                                                                                                | {user_y}
 user_i   |                                                                                                | {user_a}
 user_x   |                                                                                                | {}
 user_y   |                                                                                                | {}

adb=>
adb=> \z
                                   アクセス権
 スキーマ |    名前    |    型    |      アクセス権       | 列の権限 | ポリシー
----------+------------+----------+-----------------------+----------+----------
 public   | roletest_a | テーブル |                       |          |
 public   | roletest_x | テーブル | user_x=arwdDxt/user_x+|          |
          |            |          | user_y=r/user_x       |          |
(2 行)

adb=>

権限付与のために、テーブルのオーナーであるuser_xユーザーにスイッチして、grant文を発行しました。

権限の状態を表示する\zの結果がようやく出るようになりました。簡単ですが、"アクセス権"欄のの読み方を説明しましょう。

<ロール名> = <権限名> / <この権限を付与したロール>

<権限名>について、記号の一部の意味を記載します。およそ頭文字で想像しやすいものです。
r -- SELECT (読み取り(read))
w -- UPDATE (書き込み(write))
a -- INSERT (追加(append))

「arwdDxt」は、テーブルとしては、全権を意味します。

grant selectにより、user_yユーザーに対してrが付いています。・・・あれれ。 user_xに関する権限も表示されていますね。全権所持ということですが、オーナーであるためと考えられます。 →ここで言いたいのは、先ほどまでオーナー分は表示されなかったのに、この段になってなぜオーナー分まで表示されるようになった？ということです。 (PostgreSQLでは、こんなこと、よくあります)

⑭テーブルの参照を試み

　権限付与後の現状で、参照の振る舞いを確認します。

adb=> \c - user_x
データベース "adb" にユーザ"user_x"として接続しました。
adb=> select * from roletest_x;
\c - user_y
 id |     value
----+---------------
  1 | roletest data
(1 行)

adb=> \c - user_y
select * from roletest_x;
データベース "adb" にユーザ"user_y"として接続しました。
adb=> select * from roletest_x;
\c - user_a
 id |     value
----+---------------
  1 | roletest data
(1 行)

adb=> \c - user_a
select * from roletest_x;
データベース "adb" にユーザ"user_a"として接続しました。
adb=> select * from roletest_x;
\c - user_b
 id |     value
----+---------------
  1 | roletest data
(1 行)

adb=> \c - user_b
データベース "adb" にユーザ"user_b"として接続しました。
adb=> select * from roletest_x;
 id |     value
----+---------------
  1 | roletest data
(1 行)

adb=>
adb=> \c - user_i
データベース "adb" にユーザ"user_i"として接続しました。
adb=> select * from roletest_x;
 id |     value
----+---------------
  1 | roletest data
(1 行)

adb=>
adb=> \c - user_x
データベース "adb" にユーザ"user_x"として接続しました。
adb=> select * from roletest_a;
ERROR:  permission denied for relation roletest_a
adb=> \c - user_y
データベース "adb" にユーザ"user_y"として接続しました。
adb=> select * from roletest_a;
ERROR:  permission denied for relation roletest_a
adb=> \c - user_a
データベース "adb" にユーザ"user_a"として接続しました。
adb=> select * from roletest_a;
 id |     value
----+---------------
  2 | roletest data
(1 行)

adb=> \c - user_b
データベース "adb" にユーザ"user_b"として接続しました。
adb=> select * from roletest_a;
ERROR:  permission denied for relation roletest_a
adb=> \c - user_i
データベース "adb" にユーザ"user_i"として接続しました。
adb=> select * from roletest_a;
 id |     value
----+---------------
  2 | roletest data
(1 行)

adb=>

user_yユーザーはもちろんのこと、加入ユーザーも参照可能となっています。

⑮「継承」属性のはく奪

　念のため、「継承」属性に関しても、同様の検証を行っておきましょうか。

adb=> \c - postgres
データベース "adb" にユーザ"postgres"として接続しました。
adb=# alter role user_x NOINHERIT;
ALTER ROLE
adb=# alter role user_y NOINHERIT;
ALTER ROLE
adb=# alter role user_a NOINHERIT;
ALTER ROLE
adb=# alter role user_b NOINHERIT;
ALTER ROLE
adb=# alter role user_i NOINHERIT;
ALTER ROLE
adb=#
adb=# \du
                                                      ロール一覧
 ロール名 |                                              属性                                              | メンバー
----------+------------------------------------------------------------------------------------------------+----------
 postgres | スーパーユーザ, ロールを作成できる, DBを作成できる, レプリケーション, 行単位セキュリティを無視 | {}
 user_a   | 継承なし                                                                                       | {user_y}
 user_b   | 継承なし                                                                                       | {user_y}
 user_i   | 継承なし                                                                                       | {user_a}
 user_x   | 継承なし                                                                                       | {}
 user_y   | 継承なし                                                                                       | {}

adb=#

⑯テーブルの参照を試み

　引き続き、同様の確認をします。

adb=# \c - user_x
データベース "adb" にユーザ"user_x"として接続しました。
adb=> select * from roletest_x;
 id |     value
----+---------------
  1 | roletest data
(1 行)

adb=> \c - user_y
データベース "adb" にユーザ"user_y"として接続しました。
adb=> select * from roletest_x;
 id |     value
----+---------------
  1 | roletest data
(1 行)

adb=> \c - user_a
データベース "adb" にユーザ"user_a"として接続しました。
adb=> select * from roletest_x;
ERROR:  permission denied for relation roletest_x
adb=> \c - user_b
データベース "adb" にユーザ"user_b"として接続しました。
adb=> select * from roletest_x;
ERROR:  permission denied for relation roletest_x
adb=> \c - user_i
データベース "adb" にユーザ"user_i"として接続しました。
adb=> select * from roletest_x;
ERROR:  permission denied for relation roletest_x
adb=>
adb=> \c - user_x
データベース "adb" にユーザ"user_x"として接続しました。
adb=> select * from roletest_a;
ERROR:  permission denied for relation roletest_a
adb=> \c - user_y
データベース "adb" にユーザ"user_y"として接続しました。
adb=> select * from roletest_a;
ERROR:  permission denied for relation roletest_a
adb=> \c - user_a
データベース "adb" にユーザ"user_a"として接続しました。
adb=> select * from roletest_a;
 id |     value
----+---------------
  2 | roletest data
(1 行)

adb=> \c - user_b
データベース "adb" にユーザ"user_b"として接続しました。
adb=> select * from roletest_a;
ERROR:  permission denied for relation roletest_a
adb=> \c - user_i
データベース "adb" にユーザ"user_i"として接続しました。
adb=> select * from roletest_a;
ERROR:  permission denied for relation roletest_a
adb=>

結果も同様で、継承の跡は見られません。

ポイントを振り返りますと、とあるロールへの加入を、そのロールにて実施可能な点ではないでしょうか。

これは、およそ "権限の付与権限"に近い手段であり、セキュリティ面で考慮が必要と思われます。調べましたが、該当ユーザーそのものに対するgrantを防ぐ手立ては見当たりませんでした。 (他ロールへの加入、はく奪は制御可能。CREATE ROLE権限によります)

従って、ユーザーが複数必要な場合は、安易に継承属性を付与すべきではないと考えられます。

　ここで、前編が終了となり、ひと区切りさせていただきます。

　おつかれさまでした。ありがとうございました。

tecsak’s diary

【PostgreSQL】第3回　ユーザーを作成し権限管理をしてみよう(前編)