【PostgreSQL】第3回 ユーザーを作成し権限管理をしてみよう(後編)
~これは、雑魚技術者の勉強記録～
おつかれさまです。後編をスタートさせていただきます。
シリーズ
<第３回の概要>
~ 後編 ~
3-5．実践！publicスキーマにみるPUBLICキーワード
前回の残課題でもある、publicの取り扱いに迫ります。
3-6．実践！スキーマを介した権限と動作検証
第2回の復習にもなりますが、データベース・スキーマという枠組みと
権限も絡めた総ざらいを行います。
3-5．実践！publicスキーマにみるPUBLICキーワード
PUBLICキーワードを使用する例を紹介してみたいと思います。
前編3-4では、スキーマを意識せずにテーブルを作成しました。
結果としては、復習にもなりますが、ユーザー名と同名のスキーマが存在しない限りは "public"スキーマにて取り扱いされますので(構築時デフォルト設定)、今、即席で作成したばかりのユーザーでの作業につき(たまたま、同名のスキーマが存在したら別ですが)、やはり"public"スキーマに作成された結果となっておりました。
<再掲>
adb=> \dt
リレーションの一覧
スキーマ | 名前 | 型 | 所有者
----------+------------+----------+--------
public | roletest_a | テーブル | user_a
public | roletest_x | テーブル | user_x
(2 行)
adb=>
ここで、
加えて、publicスキーマを使用不可もしくは削除することをお忘れなく。
第2回の残課題でした、publicスキーマの使用不可に挑みたいと思います。
方法としては、オブジェクトの作成ができなければ問題なしとして、publicスキーマに対するcreate権限のはく奪を以て実現としてみたいと思います。
▼権限確認
スキーマに対する権限を確認してみます。publicスキーマに作成可能な状況でしたが・・・
見てみましょう。
adb=> \dn
スキーマ一覧
名前 | 所有者
----------+----------
postgres | postgres
public | postgres
xwork | postgres
(3 行)
adb=>
adb=> \dn+
スキーマ一覧
名前 | 所有者 | アクセス権 | 説明
----------+----------+----------------------+------------------------
postgres | postgres | |
public | postgres | postgres=UC/postgres+| standard public schema
| | =UC/postgres |
xwork | postgres | |
(3 行)
adb=>
第2回までは出てきませんでしたが、"+"を付与して、表示内容が拡大できる場合があります。 "\dn"→"\dn+"として、表示が充実しました。
アクセス権の読み方について、対スキーマバージョンですが、前編で見たオブジェクト権限の表示とほぼ変わりません。
スキーマに対して付与できる権限は2つある、ということでした。
スーパーユーザー"postgresql"に対して付与されている、"U"、"C"がそれぞれ何を表すか
もう分かりますね！
ここで、左辺が空欄のものについて、PUBLICキーワード分を意味します。
publicスキーマに対する、PUBLICキーワードの・・・ ややこしい。
また、前編ではuser_x、user_aユーザーでpublicスキーマにテーブルをCREATE出来ていたわけですが、
user_x、user_aユーザーの権限表記が見当たりませんね。
adb=> \dt
リレーションの一覧
スキーマ | 名前 | 型 | 所有者
----------+------------+----------+--------
public | roletest_a | テーブル | user_a
public | roletest_x | テーブル | user_x
(2 行)
adb=>
確かに3-4実施直後のままでして、テーブルのCREATE済にてpublicスキーマに存在するのですがね。 一旦、先に進めます。
▼user_xユーザーより、publicスキーマに対するcreate権限をはく奪
作成出来たのですから、当然、権限が付与されており、これをはく奪してしまおう、の試みです。 user_xユーザーを対象に行ってみます。
adb=> \c - postgres
データベース "adb" にユーザ"postgres"として接続しました。
adb=#
adb=# revoke create on schema public from user_x;
REVOKE
adb=#
adb=# \dn+
スキーマ一覧
名前 | 所有者 | アクセス権 | 説明
----------+----------+----------------------+------------------------
postgres | postgres | |
public | postgres | postgres=UC/postgres+| standard public schema
| | =UC/postgres |
xwork | postgres | |
(3 行)
adb=#
見た目、もともとuser_x分の表示が無かったわけですが、何も変わりませんね。
▼テーブル作成
手ごたえありませんでしたので、尚更検証してみたいものです。 適当なテーブルの作成を試みてみます。
adb=# \c - user_x データベース "adb" にユーザ"user_x"として接続しました。 adb=> adb=> create table public.publictest_x( adb(> id integer, adb(> value varchar(20) adb(> ); CREATE TABLE adb=> adb=> \dt リレーションの一覧 スキーマ | 名前 | 型 | 所有者 ----------+--------------+----------+-------- public | publictest_x | テーブル | user_x public | roletest_a | テーブル | user_a public | roletest_x | テーブル | user_x (3 行) adb=>
出来ちゃった～ 何故？ と、もうネタが出ちゃっているかもしれませんが、次に進みます。
▼PUBLICキーワードより、publicスキーマに対するcreate権限をはく奪
ユーザー直で権限が無いのに作成可能ということでしたら、ロール関係を疑うことになります。 user_xは特にロールに加入していませんが・・・ いえ、PUBLICだけは強制加入ということでした。
adb=> \du
ロール一覧
ロール名 | 属性 | メンバー
----------+------------------------------------------------------------------------------------------------+----------
postgres | スーパーユーザ, ロールを作成できる, DBを作成できる, レプリケーション, 行単位セキュリティを無視 | {}
user_a | 継承なし | {user_y}
user_b | 継承なし | {user_y}
user_i | 継承なし | {user_a}
user_x | 継承なし | {}
user_y | 継承なし | {}
adb=>
確かに、user_xの"メンバー"欄は空欄ですが、PUBLICキーワードだけは別物、表示されないようですね。 さて、もはやPUBLICキーワードからのはく奪以外に疑いようがありません。 そういえば、スキーマ一覧にCが表示されていました。。 実施してみます。
adb=> \c - postgres
データベース "adb" にユーザ"postgres"として接続しました。
adb=#
adb=# revoke create on schema public from PUBLIC;
REVOKE
adb=#
adb=# \dn+
スキーマ一覧
名前 | 所有者 | アクセス権 | 説明
----------+----------+----------------------+------------------------
postgres | postgres | |
public | postgres | postgres=UC/postgres+| standard public schema
| | =U/postgres | ・・・★
xwork | postgres | |
(3 行)
adb=#
publicスキーマに対する権限表示より、Cが消えた！(★印箇所)
▼テーブル作成
再度、実証いたします。
adb=# \c - user_x データベース "adb" にユーザ"user_x"として接続しました。 adb=> adb=> create table public.publictest_x_v2( adb(> id integer, adb(> value varchar(20) adb(> ); ERROR: permission denied for schema public 行 1: create table public.publictest_x_v2( ^ adb=>
CREATEの阻止成功です。 結果的には出来たものの、何か心に引っかかるものがあります・・・
・・・・・
振り返りますと、
スキーマとオブジェクトによる、権限の2段構えは、権限の仕組み・構造を捉えにくい形にしています。
特に、PUBLICキーワードに関しては、ロール一覧にも表示されず、忘れてしまいそうです。 全ユーザーが加入するロールということで、影響力は明らかに大きいわけですが、にもかかわらず、頭の中で認識を保つのは気が引けます。
スキーマ一覧へは、左辺空欄で以て、U・Cの表示が出ましたが、publicスキーマに対する、PUBLICキーワードへのUSAGE、CREATE権限を有した状態がデフォルトでした。 “権限あり”がデフォルトというのも、うーん、危機感を覚えますね。この手の管理は、引き算ではなく、足し算で進めていきたいもののです。
・・・・・
当方の業務では、publicスキーマを用いず、ユーザー名=スキーマ名にて運用を行っていることもあり、PUBLICより全権はく奪してPUBLICキーワードの存在を意識する必要が無い状態にして運用しています。
3-6．実践！スキーマを介した権限と動作検証
最後に、スキーマを介した検証をしたいと思います。
3-6では、ユーザーと同名のスキーマを設けるモデルとしたいと思います。複数ユーザーを作成。自他スキーマ(自=ユーザーと同名のスキーマのこと)に対して、代表アクションとしてCREATEとSELECTが可能か見ていきます。
基本的には、各ユーザーが各専用のスキーマ・オブジェクトのみを自由に使用出来、逆に、他ユーザーからの介入(CREATE、自作したオブジェクトに対するSELECT)は不可能であるべき、とします。その上で、権限を付与可能であるべきユーザーによって許可されたユーザー・対象オブジェクトの関係上でのみ操作可能であるべき、とします。
また、スーパーユーザーは使用すべきでない点を尊重したいと思います。
従って、"専用のユーザー"を別途作成。3-6検証専用のユーザー・データベース・スキーマの作成からシナリオを組みます。"専用のユーザー"とは、ユーザー・データベース・スキーマの作成が可能なユーザーをはじめに作成するということです。
そして、OWNERの概念を含ませた検証にしたいと思います。データベースに対しては、"専用のユーザー"がOWNERとなりますが、スキーマに対して、"専用のユーザー"による設定の余地("専用のユーザー"、"スキーマ名と同名のユーザー" の2種)と動きの違いを観察します。
▼シナリオ
①環境作成用のユーザー作成
スーパーユーザーにて環境作成用のユーザー(test_x)を作成。環境作成に必要な属性を付与します。
②環境作成
ユーザー、データベース、スキーマを作成します。スキーマのオーナーについて、"専用のユーザー"、
"スキーマ名と同名のユーザー"の2種を試すため、ユーザーとスキーマを同名にて2通り作成します。
・データベース名=kengen
・test_aユーザー - test_aスキーマ(オーナー=test_x)
・test_bユーザー - test_bスキーマ(オーナー=test_b)
③テーブルのCREATE
test_x含む3ユーザーにて、2スキーマへのテーブルCREATEを試みます。
・test_aユーザー、test_bユーザー：meiboテーブル
・test_xユーザー：kibanテーブル
④テーブルのSELECT
③で作成したオブジェクトへの参照を試みます。
⑤テーブルの共有
④を踏まえ、参照不可であったユーザーへの共有がどの程度可能か検証します。
全ユーザーによる、全オブジェクトへのSELECT権限付与を試みます。
①環境作成用のユーザー作成
まずはじめに、環境作成用のtest_xユーザーを作成します。 これは、スーパーユーザーで実施することとなります。 データベース、ユーザーの作成を実施するユーザーにつき、「データベースの作成権限」属性、「ユーザーの作成権限」属性を付与します。今回は、OSコマンドで実施してみます。
-bash-4.2$ id uid=26(postgres) gid=26(postgres) groups=26(postgres) context=unconfined_u:unconfined_r:unconfined_t:s0-s0:c0.c1023 -bash-4.2$ -bash-4.2$ createuser -d -r test_x -bash-4.2$ -bash-4.2$ psql -c '\du' ロール一覧 ロール名 | 属性 | メンバー ----------+------------------------------------------------------------------------------------------------+---------- postgres | スーパーユーザ, ロールを作成できる, DBを作成できる, レプリケーション, 行単位セキュリティを無視 | {} test_x | ロールを作成できる, DBを作成できる | {} user_a | 継承なし | {user_y} user_b | 継承なし | {user_y} user_i | 継承なし | {user_a} user_x | 継承なし | {} user_y | 継承なし | {} -bash-4.2$ -bash-4.2$ psql -c '\du test*' ロール一覧 ロール名 | 属性 | メンバー ----------+------------------------------------+---------- test_x | ロールを作成できる, DBを作成できる | {} -bash-4.2$
createuserコマンドのオプションにて、各属性の付与 -d(データベースの作成権限属性) -r(ユーザーの作成権限属性) を指定。 最後にユーザー名を指定しました。 その後、スーパーユーザーでデータベースにとりあえずの接続を行いませんでした。psqlの-cオプションにてコマンドを渡し、ユーザー一覧を表示させました。 これまでの実験結果が残っており、表示行が多くなってまいりましたので、正規表現にてあらためて絞りました。
②環境作成
それでは、作成したtest_xユーザーによる操作のパートとなりますので、じっくりとログインして、作業開始しましょうか。
-bash-4.2$ psql -U test_x postgres psql (9.6.3) "help" でヘルプを表示します. postgres=> select current_database(), current_user; current_database | current_user ------------------+-------------- postgres | test_x (1 行) postgres=>
psqlで、-Uオプションにてログインユーザーの指定を、最後にデータベース名を指定しました。作業用のデータベースが出来ていないため、仮でpostgresデータベースへ一旦接続しています。
それでは、kengenデータベースを作成します。
postgres=> create database kengen;
CREATE DATABASE
postgres=> \l
データベース一覧
名前 | 所有者 | エンコーディング | 照合順序 | Ctype(変換演算子) | アクセス権
-----------+----------+------------------+----------+-------------------+-----------------------
adb | postgres | UTF8 | C | C |
kengen | test_x | UTF8 | C | C |
postgres | postgres | UTF8 | C | C |
template0 | postgres | UTF8 | C | C | =c/postgres +
| | | | | postgres=CTc/postgres
template1 | postgres | UTF8 | C | C | =c/postgres +
| | | | | postgres=CTc/postgres
(5 行)
postgres=>
今回も、第2回同様、単純に作成できています。
では、作成したばかりのデータベースに接続を移動しましょう。
postgres=> \c kengen データベース "kengen" にユーザ"test_x"として接続しました。 kengen=> select current_database(), current_user; current_database | current_user ------------------+-------------- kengen | test_x (1 行) kengen=>
順調です。続いて、test_a、test_bユーザーの作成を行います。
kengen=> create role test_a with LOGIN PASSWORD 'password';
CREATE ROLE
kengen=> create role test_b with LOGIN PASSWORD 'password';
CREATE ROLE
kengen=>
kengen=> \du test_*
ロール一覧
ロール名 | 属性 | メンバー
----------+------------------------------------+----------
test_a | | {}
test_b | | {}
test_x | ロールを作成できる, DBを作成できる | {}
kengen=>
作成できました。
データベース、ユーザーときましたら、次はスキーマです。名称はユーザー名と同じ。
オーナーだけ注意します。test_aスキーマ(オーナー=test_xユーザー)、test_bスキーマ(オーナー= test_bユーザー)を作成！！
kengen=> create schema test_a authorization test_x; CREATE SCHEMA kengen=> kengen=> create schema authorization test_b; ERROR: must be member of role "test_b" kengen=>
スキーマ名を省略すると、所有者と同名がデフォルトとして採用されます。
ということで、オーナーとスキーマ名が同じtest_bスキーマの作成に関しては、あえて省略してみました。
ですが、別な要因でtest_bスキーマの作成に失敗しています。
失敗の意味としましては、作成を試みたtest_xユーザーと、オーナーとなる予定のtest_bユーザーがロール関係に無く、またtest_xユーザーはスーパーユーザーではないため、関わりの無いロールをオーナーとしたスキーマの作成が権限上、不可能ということです。
test_b本人によるスキーマ作成は、(kengenデータベースに対する)スキーマの作成権限を付与していないため不可能です。
また、test_xユーザーとtest_bユーザーをロール関係にするのは、余計な権限の譲渡につながり、そもそもユーザーを分けている意味が無いでしょう。題意に反します。
従って、この時点で当シナリオにおいては、"ユーザー"＝"スキーマのオーナー"とはなり得ません。
その上で、不自然ですが、ここだけ。スーパーユーザーで強引に実現してしまいます。
kengen=> \c - postgres
データベース "kengen" にユーザ"postgres"として接続しました。
kengen=# select current_database(), current_user;
current_database | current_user
------------------+--------------
kengen | postgres
(1 行)
kengen=#
kengen=# create schema authorization test_b;
CREATE SCHEMA
kengen=#
kengen=> \dn+
スキーマ一覧
名前 | 所有者 | アクセス権 | 説明
--------+----------+----------------------+------------------------
public | postgres | postgres=UC/postgres+| standard public schema
| | =UC/postgres |
test_a | test_x | |
test_b | test_b | |
(3 行)
kengen=>
スーパーユーザーへ安易にスイッチ変更できているように見えるかもしれませんが、OS認証で通るためです。
強引にtest_bスキーマの所有者をtest_bユーザーに揃えました。
スキーマまで出来ますと、舞台は仕切りまで整いました。オブジェクト操作に移りましょう。
③テーブルのCREATE
はじめに、テーブルのCREATEです。
test_aユーザーにて、テーブルの作成を試みます。両スキーマに対して行います。
kengen=# \c - test_a
データベース "kengen" にユーザ"test_a"として接続しました。
kengen=> select current_database(), current_user;
current_database | current_user
------------------+--------------
kengen | test_a
(1 行)
kengen=>
kengen=> create table test_a.meibo(id integer,namae varchar(20));
ERROR: permission denied for schema test_a
行 1: create table test_a.meibo(id integer,namae varchar(20));
^
kengen=> create table test_b.meibo(id integer,namae varchar(20));
ERROR: permission denied for schema test_b
行 1: create table test_b.meibo(id integer,namae varchar(20));
^
kengen=>
ぼろぼろに怒られました～ 全滅！！
とはいえ、後者に関しては、他ユーザー用のスキーマですので、これでOKですね。
ですが、前者、自スキーマとして作成したものまで、作成できないとあっては・・・ 理由は何でしょう？
そうです、スキーマに対するCREATE権限が無いのです。
もしくは、オーナーであれば問題ありませんが、オーナー(test_x)≠ユーザー(test_a)につき無条件での操作は不可能。
従って、権限を介しての手続きとなります。
ということで、test_xユーザーよりCREATE権限付与の手続きを行ってもらいたいと思います。
kengen=> \c - test_x
データベース "kengen" にユーザ"test_x"として接続しました。
kengen=> select current_database(), current_user;
current_database | current_user
------------------+--------------
kengen | test_x
(1 行)
kengen=>
kengen=> grant create on schema test_a to test_a;
GRANT
kengen=>
kengen=> \dn+
スキーマ一覧
名前 | 所有者 | アクセス権 | 説明
--------+----------+----------------------+------------------------
public | postgres | postgres=UC/postgres+| standard public schema
| | =UC/postgres |
test_a | test_x | test_x=UC/test_x +|
| | test_a=C/test_x |
test_b | test_b | |
(3 行)
kengen=>
それでは、再度CREATEにチャレンジします。
kengen=> \c - test_a
データベース "kengen" にユーザ"test_a"として接続しました。
kengen=> select current_database(), current_user;
current_database | current_user
------------------+--------------
kengen | test_a
(1 行)
kengen=>
kengen=> create table test_a.meibo(id integer,namae varchar(20));
CREATE TABLE
kengen=> create table test_b.meibo(id integer,namae varchar(20));
ERROR: permission denied for schema test_b
行 1: create table test_b.meibo(id integer,namae varchar(20));
^
kengen=>
kengen=> \dt test_*.*
リレーションの一覧
スキーマ | 名前 | 型 | 所有者
----------+-------+----------+--------
test_a | meibo | テーブル | test_a
(1 行)
kengen=>
はい、できました～
テーブルの所有者については、CREATEを行ったtest_aユーザー(自身)となっています。
参照の動作について、後ほど検証を行いますが、ざっと適当にデータ挿入、参照してみます。
引き続き、test_aユーザー(のみ)です。
kengen=> insert into test_a.meibo values(1,'test_a write');
ERROR: permission denied for schema test_a
行 1: insert into test_a.meibo values(1,'test_a write');
^
kengen=>
また怒られた～
先ほどと同じ概念で、今度はUSAGE権限が無いということです。
USAGEは、その名の通り、"使う"ことができません。CREATEだけ付与されている状況は、業務ではほぼ無いかもしれません。
対応も先ほどと同じ。test_xユーザーより権限付与いただきましょう。
kengen=> \c - test_x
データベース "kengen" にユーザ"test_x"として接続しました。
kengen=> select current_database(), current_user;
current_database | current_user
------------------+--------------
kengen | test_x
(1 行)
kengen=>
kengen=> grant usage on schema test_a to test_a;
GRANT
kengen=>
kengen=> \dn+
スキーマ一覧
名前 | 所有者 | アクセス権 | 説明
--------+----------+----------------------+------------------------
public | postgres | postgres=UC/postgres+| standard public schema
| | =UC/postgres |
test_a | test_x | test_x=UC/test_x +|
| | test_a=UC/test_x |
test_b | test_b | |
(3 行)
kengen=>
これで、アクセス権に、UとCが入りました！
それでは、再チャレンジします。
kengen=> \c - test_a データベース "kengen" にユーザ"test_a"として接続しました。 kengen=> select current_database(), current_user; current_database | current_user ------------------+-------------- kengen | test_a (1 行) kengen=> kengen=> insert into test_a.meibo values(1,'test_a write'); INSERT 0 1 kengen=> select * from test_a.meibo; id | namae ----+-------------- 1 | test_a write (1 行) kengen=>
使えるようになりました。OKですね。
同様にCREATEおよび簡単な挿入・参照動作を、test_bユーザーでも行います。
振り返りですが、今度はスキーマのオーナー(test_b)=ユーザー(test_b)であるということです。
kengen=> \c - test_b
データベース "kengen" にユーザ"test_b"として接続しました。
kengen=> select current_database(), current_user;
current_database | current_user
------------------+--------------
kengen | test_b
(1 行)
kengen=>
kengen=> create table test_a.meibo(id integer,namae varchar(20));
ERROR: permission denied for schema test_a
行 1: create table test_a.meibo(id integer,namae varchar(20));
^
kengen=> create table test_b.meibo(id integer,namae varchar(20));
CREATE TABLE
kengen=>
kengen=> insert into test_b.meibo values(1,'test_b write');
INSERT 0 1
kengen=> select * from test_b.meibo;
id | namae
----+--------------
1 | test_b write
(1 行)
kengen=>
kengen=> \dt test_*.*
リレーションの一覧
スキーマ | 名前 | 型 | 所有者
----------+-------+----------+--------
test_a | meibo | テーブル | test_a
test_b | meibo | テーブル | test_b
(2 行)
kengen=>
すぱっと決まりました。オーナーにつき、全権があります。
また、他スキーマであるtest_aユーザー分に関しては、期待どおり作成不可であります。
test_xユーザーでも、再度同様を試します。
kengen=> \c - test_x
データベース "kengen" にユーザ"test_x"として接続しました。
kengen=> select current_database(), current_user;
current_database | current_user
------------------+--------------
kengen | test_x
(1 行)
kengen=>
kengen=> create table test_a.kiban(id integer,namae varchar(20));
CREATE TABLE
kengen=> create table test_b.kiban(id integer,namae varchar(20));
ERROR: permission denied for schema test_b
行 1: create table test_b.kiban(id integer,namae varchar(20));
^
kengen=>
kengen=> insert into test_a.kiban values(1,'test_x write');
INSERT 0 1
kengen=> select * from test_a.kiban;
id | namae
----+--------------
1 | test_x write
(1 行)
kengen=>
kengen=> \dt test_*.*
リレーションの一覧
スキーマ | 名前 | 型 | 所有者
----------+-------+----------+--------
test_a | kiban | テーブル | test_x
test_a | meibo | テーブル | test_a
test_b | meibo | テーブル | test_b
(3 行)
kengen=>
test_bユーザーのときと同じ、オーナーであれば・・・の結果です。
test_aのスキーマに関しては、スキーマのオーナーにあたりますので、CREATE・USAGEは既に許可されており、スパッと作業完了となります。
CREATEについて、検証終了です。
結果としては、スキーマを作成したユーザー(オーナー)によるCREATE権限の付与に依存しています(test_bユーザー分については、スーパーユーザーを用いて強引に操作しましたので、現実味はありません)。
軽いテストがてら実施した参照動作・・・USAGEについても、自作のテーブルについてのみ先に見てしまいましたが、USAGEに関しても、CREATEと同じでした。
ただ、USAGE(対スキーマ)＝参照(SELECT 対オブジェクト)ではありませんが、似たところもあり
自作外の全テーブルに対する全ユーザーの参照の振る舞いを、次に確認したいと思います。
④テーブルのSELECT
一旦、現状で、ざっと参照をかけてみます。
test_aユーザーにて
kengen=> \c - test_a
データベース "kengen" にユーザ"test_a"として接続しました。
kengen=> select * from test_a.meibo;
id | namae
----+--------------
1 | test_a write
(1 行)
kengen=> select * from test_a.kiban;
ERROR: permission denied for relation kiban
kengen=> select * from test_b.meibo;
ERROR: permission denied for schema test_b
行 1: select * from test_b.meibo;
^
kengen=>
test_bユーザーにて
kengen=> \c - test_b
データベース "kengen" にユーザ"test_b"として接続しました。
kengen=> select * from test_a.meibo;
ERROR: permission denied for schema test_a
行 1: select * from test_a.meibo;
^
kengen=> select * from test_a.kiban;
ERROR: permission denied for schema test_a
行 1: select * from test_a.kiban;
^
kengen=> select * from test_b.meibo;
id | namae
----+--------------
1 | test_b write
(1 行)
kengen=>
test_xユーザーにて
kengen=> \c - test_x
データベース "kengen" にユーザ"test_x"として接続しました。
kengen=> select * from test_a.meibo;
ERROR: permission denied for relation meibo
kengen=> select * from test_a.kiban;
id | namae
----+--------------
1 | test_x write
(1 行)
kengen=> select * from test_b.meibo;
ERROR: permission denied for schema test_b
行 1: select * from test_b.meibo;
^
kengen=>
各々自作のテーブルしか参照できない・・・ばっちりではないでしょうか。
基本的には、自作のテーブルに対してオーナーは自身であり、これに対する権限としては、オーナーが行使できる分のみ、という状態です。
⑤テーブルの共有
さて、それでは逆に共有は可能なのかということで、権限の付与が可能か試したいと思います。
とあるテーブルに対し、他ユーザーにも公開し、情報共有したい～ の場合です。
また一方、誰が権限付与可能なのかを確認しなければ、前述の参照是非の確認のみでは検証不十分でしょう。権限付与によって、結果的に参照可能となってしまっては同じです。
まず気になりますのが、test_xユーザーです。環境作成を担ったユーザーではありますが、保身のためにも、全てのデータが参照可能(権限付与によって、結果的に参照可能)で良いとは限らないはずです。
test_a、test_bユーザーのオブジェクトに対するgrantが可能なのか見てみます。
test_xユーザーにて
kengen=> \c - test_x データベース "kengen" にユーザ"test_x"として接続しました。 kengen=> select current_database(), current_user; current_database | current_user ------------------+-------------- kengen | test_x (1 行) kengen=> kengen=> grant select on test_a.meibo to test_b; ERROR: permission denied for relation meibo kengen=> grant select on test_b.meibo to test_a; ERROR: permission denied for schema test_b kengen=>
スキーマのオーナーであっても、オブジェクト権限の付与へは関与できない様子です。いいですね。
また、test_xユーザー自作のテーブル(test_a.kiban)に対しての操作は、自由あるいは自爆というものでしょう。割愛します。
続いて、test_aユーザーにて
kengen=> \c - test_a
データベース "kengen" にユーザ"test_a"として接続しました。
kengen=> select current_database(), current_user;
current_database | current_user
------------------+--------------
kengen | test_a
(1 行)
kengen=>
kengen=> grant select on test_a.meibo to test_b;
GRANT
kengen=> grant select on test_b.meibo to test_a;
ERROR: permission denied for schema test_b
kengen=>
kengen=> \z
アクセス権
スキーマ | 名前 | 型 | アクセス権 | 列の権限 | ポリシー
----------+-------+----------+-----------------------+----------+----------
test_a | kiban | テーブル | | |
test_a | meibo | テーブル | test_a=arwdDxt/test_a+| |
| | | test_b=r/test_a | |
(2 行)
kengen=>
やはり、自作のものに限るようです。
test_bユーザーに対して、SELECT(r)が付与されました。
test_bユーザーでも、同様のことを行います。 結果は見えたも同然ですが。
kengen=> \c - test_b
データベース "kengen" にユーザ"test_b"として接続しました。
kengen=> select current_database(), current_user;
current_database | current_user
------------------+--------------
kengen | test_b
(1 行)
kengen=>
kengen=> grant select on test_a.meibo to test_b;
ERROR: permission denied for schema test_a
kengen=> grant select on test_b.meibo to test_a;
GRANT
kengen=>
kengen=> \z
アクセス権
スキーマ | 名前 | 型 | アクセス権 | 列の権限 | ポリシー
----------+-------+----------+-----------------------+----------+----------
test_b | meibo | テーブル | test_b=arwdDxt/test_b+| |
| | | test_a=r/test_b | |
(1 行)
kengen=>
権限の付与動作としては、思うとおりに出来た感がありますね。
・・・とは別に、実証確認しないと、なんだか恐ろしいので、、
再び、ざっと参照処理を通します。
test_aユーザーにて
kengen=> \c - test_a
データベース "kengen" にユーザ"test_a"として接続しました。
kengen=> select * from test_a.meibo;
id | namae
----+--------------
1 | test_a write
(1 行)
kengen=> select * from test_a.kiban;
ERROR: permission denied for relation kiban
kengen=> select * from test_b.meibo;
ERROR: permission denied for schema test_b
行 1: select * from test_b.meibo;
^
kengen=>
test_bユーザーにて
kengen=> \c - test_b
データベース "kengen" にユーザ"test_b"として接続しました。
kengen=> select * from test_a.meibo;
ERROR: permission denied for schema test_a
行 1: select * from test_a.meibo;
^
kengen=> select * from test_a.kiban;
ERROR: permission denied for schema test_a
行 1: select * from test_a.kiban;
^
kengen=> select * from test_b.meibo;
id | namae
----+--------------
1 | test_b write
(1 行)
kengen=>
test_xユーザーにて
kengen=> \c - test_x
データベース "kengen" にユーザ"test_x"として接続しました。
kengen=> select * from test_a.meibo;
ERROR: permission denied for relation meibo
kengen=> select * from test_a.kiban;
id | namae
----+--------------
1 | test_x write
(1 行)
kengen=> select * from test_b.meibo;
ERROR: permission denied for schema test_b
行 1: select * from test_b.meibo;
^
kengen=>
さっきと変わってな～い！！ 自作のものしか見れませんね。
test_a、test_bユーザー間のみでの、テーブル見せ合いっこが出来ませんでした。
何か忘れているような・・・
スキーマに対するUSAGEを忘れていますね。他スキーマへの参照ですので、基本はUSAGE無し、かつ、スキーマオーナーであるはずも無いでしょう。付与が必要です。
付与します。
(先ほどは、test_aスキーマに対するUSAGE権限をtest_aユーザーに付与。これをtest_xユーザーにて行いました)
test_aユーザーにて付与を試みます。
kengen=> \c - test_a
データベース "kengen" にユーザ"test_a"として接続しました。
kengen=> select current_database(), current_user;
current_database | current_user
------------------+--------------
kengen | test_a
(1 行)
kengen=>
kengen=> grant usage on schema test_a to test_b;
WARNING: no privileges were granted for "test_a"
GRANT
kengen=> grant usage on schema test_b to test_a;
ERROR: permission denied for schema test_b
kengen=>
kengen=> \dn+
スキーマ一覧
名前 | 所有者 | アクセス権 | 説明
--------+----------+----------------------+------------------------
public | postgres | postgres=UC/postgres+| standard public schema
| | =UC/postgres |
test_a | test_x | test_x=UC/test_x +|
| | test_a=UC/test_x |
test_b | test_b | |
(3 行)
kengen=>
はい、test_aユーザーでは、そもそもスキーマのオーナーではありませんので付与できません。
まして、test_bスキーマ分など、出来るはずもなく期待値どおりです。
test_bユーザーにて
kengen=> \c - test_b
データベース "kengen" にユーザ"test_b"として接続しました。
kengen=> select current_database(), current_user;
current_database | current_user
------------------+--------------
kengen | test_b
(1 行)
kengen=>
kengen=> grant usage on schema test_a to test_b;
ERROR: permission denied for schema test_a
kengen=> grant usage on schema test_b to test_a;
GRANT
kengen=>
kengen=> \dn+
スキーマ一覧
名前 | 所有者 | アクセス権 | 説明
--------+----------+----------------------+------------------------
public | postgres | postgres=UC/postgres+| standard public schema
| | =UC/postgres |
test_a | test_x | test_x=UC/test_x +|
| | test_a=UC/test_x |
test_b | test_b | test_b=UC/test_b +|
| | test_a=U/test_b |
(3 行)
kengen=>
test_bユーザーに関しては、オーナーですので、付与可能です。
まあ、スーパーユーザーで強引にオーナーとしましたので、現実には起こりえない筋ですが。
test_xユーザーにて
kengen=> \c - test_x
データベース "kengen" にユーザ"test_x"として接続しました。
kengen=> select current_database(), current_user;
current_database | current_user
------------------+--------------
kengen | test_x
(1 行)
kengen=>
kengen=> grant usage on schema test_a to test_b;
GRANT
kengen=> grant usage on schema test_b to test_a;
ERROR: permission denied for schema test_b
kengen=>
kengen=> \dn+
スキーマ一覧
名前 | 所有者 | アクセス権 | 説明
--------+----------+----------------------+------------------------
public | postgres | postgres=UC/postgres+| standard public schema
| | =UC/postgres |
test_a | test_x | test_x=UC/test_x +|
| | test_a=UC/test_x +|
| | test_b=U/test_x |
test_b | test_b | test_b=UC/test_b +|
| | test_a=U/test_b |
(3 行)
kengen=>
先ほど同様、オーナー分に関しては、付与可能です。
これで最後です。参照の実証確認をします。
test_aユーザーにて
kengen=> \c - test_a データベース "kengen" にユーザ"test_a"として接続しました。 kengen=> select * from test_a.meibo; id | namae ----+-------------- 1 | test_a write (1 行) kengen=> select * from test_a.kiban; ERROR: permission denied for relation kiban kengen=> select * from test_b.meibo; id | namae ----+-------------- 1 | test_b write (1 行) kengen=>
test_bユーザーにて
kengen=> \c - test_b データベース "kengen" にユーザ"test_b"として接続しました。 kengen=> select * from test_a.meibo; id | namae ----+-------------- 1 | test_a write (1 行) kengen=> select * from test_a.kiban; ERROR: permission denied for relation kiban kengen=> select * from test_b.meibo; id | namae ----+-------------- 1 | test_b write (1 行) kengen=>
test_xユーザーにて
kengen=> \c - test_x
データベース "kengen" にユーザ"test_x"として接続しました。
kengen=> select * from test_a.meibo;
ERROR: permission denied for relation meibo
kengen=> select * from test_a.kiban;
id | namae
----+--------------
1 | test_x write
(1 行)
kengen=> select * from test_b.meibo;
ERROR: permission denied for schema test_b
行 1: select * from test_b.meibo;
^
kengen=>
はい。
はずはじめに、test_aユーザーがtest_bユーザーのテーブルを、test_bユーザーがtest_aユーザーのテーブルを参照可能となりました。期待値通りです。
次に、test_xユーザーについて、変わりなく、自作のテーブルのみ参照可能でした。こちらも期待値通りですね。
テーブルオーナーによるSELECT権限の付与、および、スキーマオーナーによるUSAGE権限の付与が双方揃わなければ、他ユーザーに対するオブジェクト公開が不可能とのことで、それがスキーマのオーナーであっても、ユーザーの作成者であっても、テーブルのオーナーであっても、単独にて破られることは無いということです。
例えば、業務の担当割として
ここまでを、基盤・DBA担当が、ここからは、業務・アプリ担当が、などと役割の境界が
発生することがあるかと思います。
本題を振り返って、test_xユーザーを基盤・DBA担当、test_aユーザーを業務・アプリ担当、と重ねたとき
両担当からの設定を以て権限の是非をコントロールできるということになります。
権限管理を、業務・アプリ側に概ね任せつつも、スキーマ単位では基盤・DBAが介入して
共有を全く不可能にすることもできます。
また、基盤・DBA自らの権限のみで、業務データの参照が可能とならないため、保身にもなります。
第3回を終了させていただきます。 権限等セキュリティ面が絡みますと、やはり気を抜くわけにはいきませんね。十分な検討とテストが必要です。 特に、public/PUBLICの概念はPostgreSQL独特のものでしたが、私見で恐縮ですが、便利の裏返しでややこしさが目立った感があります。ロール(ユーザー)に関しても、グループ/ユーザーの概念が同居ということで、慣れが必要なようです。
今回までで、データベースクラスタからユーザー、テーブル等オブジェクトまで、作る系がおよそイメージ出来るようになったかと思います。 あ、表領域の概念が気になる方がいらっしゃるかもしれませんね。セキュリティという単語が出てしまいますと、監査なども気になり出し、キリがなく。。。
作り物として、データベースクラスタにはじまり、ユーザー・テーブルまで作成したところで、そろそろ、PostgreSQLが業務システムに登場し出したキッカケの1つとも言える、レプリケーション機能の紹介・検証に迫りたいと思います。当機能の紹介を通じて、他の概念についても触れる機会が出来ますので、いつもどり？ の寄り道続きとなりますが、各種拾いながら進んでいきたいと思います。
<第2回より抜粋、データベースの説明にて>
接続と申しましても、詳細は別途説明しなくてはなりませんが、 (中略) クライアント認証を通過したセッションが、さらにどのデータベースと関わるのかを 制御しているのは、実質、権限でしかありません。
折角、オブジェクトと権限(データベース内部といいますか)についてある程度の整理・検証まで行いましたので、対データベース・入口部分にも少し触れようと思います。
おつかれさまでした。ありがとうございました。
