Amazon Redshift: クラスタ環境の分析・確認用に権限制御したユーザーを準備する

Amazon Redshiftに於いてクエリパフォーマンスのチューニングを行う場合、アクセス対象のテーブルに対する権限を持ったユーザーで無いと当然の事ながらDBに接続し、中身を調べる事は出来ません。スーパーユーザー権限を持つユーザーであれば、基本的に全てのスキーマ・テーブル・ビューにアクセスする事が出来るのですが、当然ながら『何でも出来てしまう』ため、(意図しない形であっても、故意にであっても)Redshiftクラスタ内の要素に対して更新や削除が出来てしまえます。管理者的な立場にある人ならいざ知らず、他者(他社)に作業を依頼する場合であれば必要最小限のユーザー権限のみ保有するユーザーを作成しておき、余計な事はさせたくない、と思うはずです。(調査を受ける方としても同じ思いでしょう)

そこで当エントリでは、そのような状況になった場合、どういった形でその『調査用』ユーザーを用意するのが良いのかという観点で関連しそうなポイントを調べてみました。

目次

• テスト用グループ・ユーザーの作成
• 想定する構築状況
• ユーザー作成の要素に対する権限付与設定
• システム系のテーブル・ビューに対する権限付与設定
  • アクセス制限：『ユーザー』に指定されているシステム・ビューへの確認
  • アクセス制限：『全てのユーザー』に指定されているシステム・ビューへの確認
  • アクセス制限：『スーパーユーザー』に指定されているシステム・ビューへの確認
• 調査分析に使うコマンドの実行確認
• 付録：Amazon Redshift システム系テーブル・ビュー一覧
  • ログ記録のためのSTLテーブル一覧
  • スナップショットデータの STV テーブル一覧
  • システムビュー一覧
  • システムカタログテーブル一覧
• まとめ

テスト用グループ・ユーザーの作成

先ず最初に、アクセス確認を行う為のユーザー及びユーザーが所属するグループを作成しておきます。スーパーユーザーでDBにアクセスし、以下コマンドで要素を作成します。

/** グループの作成. */
# CREATE GROUP cm_cluster_research;
CREATE GROUP

/** ユーザーの作成及びグループへのユーザーの追加. */
# CREATE USER research_user WITH PASSWORD 'abc123DEF';
CREATE USER
# ALTER GROUP cm_cluster_research ADD USER research_user;
ALTER GROUP

想定する構築状況

今回状況として構築したいのは、以下のようなものを想定しています。以降の手順で、この形に近づけていく作業を行っていきます。

• 対象となるスキーマ内のテーブルに於いては、参照権限(SELECT)のみ許可する必要最低限の形としたい。
• また、各種情報・状況調査の為にシステム系テーブルも見たいものについては見られるようにしておく必要がある。

ユーザー作成の要素に対する権限付与設定

分析や参照に利用する為のテーブルを任意のスキーマに作成します。スキーマ名：cm_research、テーブル名：redshift_testで要素を作成しました。

/** スキーマの作成. */
# CREATE SCHEMA cm_research;
CREATE SCHEMA

/** テーブルの作成. */
# CREATE TABLE cm_research.redshift_test (
#    id CHAR(5) NOT NULL encode lzo,
#    name VARCHAR(30) NOT NULL encode lzo,
#    quantity INT NOT NULL encode mostly16,
#    date DATE NOT NULL encode delta,
#    PRIMARY KEY(id))
#  distkey(id)
#  sortkey(date,id);
CREATE TABLE
#

この時点ではグループ及びユーザーの作成、またスキーマ及びテーブルの作成は済んでいますが、両者間の紐付けは特に行っていません。作成したユーザーでログイン後、テーブルにアクセスを試みてみます。全て『権限が無い為』に弾かれています。

/** SELECT文実行. */
=> SELECT * FROM cm_research.redshift_test;
ERROR:  permission denied for schema cm_research

/** INSERT文実行. */
=> INSERT INTO cm_research.redshift_test VALUES('0001','テスト１',100,'2014/08/20');
ERROR:  permission denied for schema cm_research

/** UPDATE文実行. */
=> UPDATE cm_research.redshift_test SET quantity = 999 WHERE id = '0001';
ERROR:  permission denied for schema cm_research

/** DELETE文実行. */
=> DELETE FROM cm_research.redshift_test;
ERROR:  permission denied for schema cm_research
=>

スーパーユーザーで入り直し、所定のユーザー・もしくはユーザーが所属するグループに対し、必要なテーブルに必要な権限を付与します。このエントリでは、テーブルへのアクセス許可をグループに付与する形で進めますので、権限付与もグループに対して行います。

/** 対象グループに対し利用権限を付与する. */
# GRANT USAGE ON SCHEMA cm_research TO GROUP cm_cluster_research;
GRANT
# 
/** 更に、対象テーブルに対して参照権限『のみ』与える. */
# GRANT SELECT ON cm_research.redshift_test TO GROUP cm_cluster_research;
GRANT

再度作成したユーザーで入り直し、処理を試みてみます。権限付与したSELECT文だけ実行出来るようになりました。

/** SELECT文実行. */
=> SELECT * FROM cm_research.redshift_test;
 id | name | quantity | date 
----+------+----------+------
(0 rows)

/** INSERT文実行. */
=> INSERT INTO cm_research.redshift_test VALUES('0001','テスト１',100,'2014/08/20');
ERROR:  permission denied for relation redshift_test

/** UPDATE文実行. */
=> UPDATE cm_research.redshift_test SET quantity = 999 WHERE id = '0001';
ERROR:  permission denied for relation redshift_test

/** DELETE文実行. */
=> DELETE FROM cm_research.redshift_test;
ERROR:  permission denied for relation redshift_test
=>

対象テーブルの定義の確認も行えました。

cmawsteamdb=> \d cm_research.redshift_test;
      Table "cm_research.redshift_test"
  Column  |         Type          | Modifiers 
----------+-----------------------+-----------
 id       | character(5)          | not null
 name     | character varying(30) | not null
 quantity | integer               | not null
 date     | date                  | not null
Indexes:
    "redshift_test_pkey" PRIMARY KEY, btree (id)

システム系のテーブル・ビューに対する権限付与設定

システム系のテーブル・ビューは以下の様な用途・種別で分類されています。

• ログ記録のための STL テーブル
• スナップショットデータの STV テーブル
• システムビュー
• システムカタログテーブル

そして、システムテーブルには『可視性』というものが設定されており、閲覧者ユーザーがどの権限なのかでテーブルの閲覧内容に制限がある様です。

• システムテーブルとビューのデータの可視性 - Amazon Redshift

システムテーブルとビューには、データについて 2 つのクラスの可視性
（ユーザーが表示可能、スーパーユーザーが表示可能）があります。

スーパーユーザー特権を持っているユーザーのみが、
スーパーユーザーが表示可能なカテゴリのテーブルのデータを表示できます。

通常のユーザーは、ユーザーが表示可能なテーブルのデータを表示できますが、
表示できるのは自分のアクティビティによって生成された行のみです。
別のユーザーによって生成された行は、通常のユーザーには表示されません。

スーパーユーザーは両方のカテゴリのテーブルのすべての行を表示できます。

それぞれ内容を確認してみます。

アクセス制限：『ユーザー』に指定されているシステム・ビューへの確認

システムカタログテーブル：PG_TABLE_DEFが見えるかどうか確認してみます。上記でスキーマを新規作成してるので一度パラメータグループに値を追加し、再起動する必要があります。

再起動後作成したユーザーで内容を確認してみたところ、内容は取得出来ました。特に権限付与設定をせずとも問題無さそうです。

=> SELECT DISTINCT schemaname, tablename FROM pg_table_def WHERE schemaname = 'cm_research';
 schemaname  |     tablename      
-------------+--------------------
 cm_research | redshift_test
 cm_research | redshift_test_pkey
(2 rows)

御覧の様にテーブル構造も情報を取得出来ています。

=> SELECT * FROM pg_table_def WHERE schemaname = 'cm_research';
 schemaname  |     tablename      |  column  |         type          | encoding | distkey | sortkey | notnull 
-------------+--------------------+----------+-----------------------+----------+---------+---------+---------
 cm_research | redshift_test      | id       | character(5)          | lzo      | t       |       2 | t
 cm_research | redshift_test      | name     | character varying(30) | lzo      | f       |       0 | t
 cm_research | redshift_test      | quantity | integer               | mostly16 | f       |       0 | t
 cm_research | redshift_test      | date     | date                  | delta    | f       |       1 | t
 cm_research | redshift_test_pkey | id       | character(5)          | lzo      | t       |       2 | f
(5 rows)

その他システム系のテーブル・ビューについても内容は取得出来ました。

=> SELECT COUNT(*) FROM STL_SAVE;
 count 
-------
   258
(1 row)

=> SELECT COUNT(*) FROM SVL_QLOG;
 count 
-------
    13
(1 row)

アクセス制限：『全てのユーザー』に指定されているシステム・ビューへの確認

ドキュメント上、『すべてのユーザー』と記載のあるものについても内容は確認出来る模様。『ユーザー』『全てのユーザー』については、"閲覧可能だが自らのユーザーに該当するもののみ"という仕様があるので、ここで引っ掛かって居るのはその条件を満たした内容のようですね。

=> SELECT COUNT(*) FROM STL_DDLTEXT;
 count 
-------
     0
(1 row)

=> SELECT COUNT(*) FROM SVL_COMPILE;
 count 
-------
   155
(1 row)

アクセス制限：『スーパーユーザー』に指定されているシステム・ビューへの確認

まずは現時点でそのままアクセスしてみます。『権限が無い』と怒られますね。

=> SELECT COUNT(*) FROM STL_CONNECTION_LOG;
ERROR:  permission denied for relation stl_connection_log
=> SELECT COUNT(*) FROM SVV_DISKUSAGE;
ERROR:  permission denied for relation svv_diskusage
=>

スーパーユーザーでログインし直し、作成ユーザー(のグループ)に対して権限付与設定を試みて見ます...出来ました！

# GRANT SELECT ON pg_catalog.stl_connection_log TO GROUP cm_cluster_research; 
GRANT
# GRANT SELECT ON pg_catalog.svv_diskusage TO GROUP cm_cluster_research; 
GRANT

作成ユーザーでログインし直してみると、これらのテーブルでも権限を付与すればアクセス出来る事が分かりました。

=> SELECT COUNT(*) FROM STL_CONNECTION_LOG;
 count 
-------
   945
(1 row)

=> SELECT COUNT(*) FROM SVV_DISKUSAGE;
 count 
-------
 27595
(1 row)

調査分析に使うコマンドの実行確認

クエリの実行計画を表示するEXPLAINコマンドはどうでしょうか。上記で作成したテーブルは何もデータを入れてないので結果らしい結果は出ませんが、実行は可能でした。参照権限があれば対象テーブル(が入っている)クエリの実行計画の取得は可能そうです。

• EXPLAIN - Amazon Redshift

=> EXPLAIN SELECT * FROM cm_research.redshift_test;
                                  QUERY PLAN                                  
------------------------------------------------------------------------------
 XN Seq Scan on redshift_test  (cost=0.00..5.60 rows=560 width=90)
 ----- Tables missing statistics: redshift_test -----
 ----- Update statistics by running the ANALYZE command on these tables -----
(3 rows)

若干ズレますが、VACUUMやANALYZEはどうでしょうか。これはその要素のオーナー(作成者)で無ければ駄目な様です。

=> VACUUM cm_research.redshift_test;
ERROR:  skipping "redshift_test" --- only table or database owner can vacuum it
=> ANALYZE cm_research.redshift_test;
ERROR:  skipping "redshift_test" --- only table or database owner can analyze it

付録：Amazon Redshift システム系テーブル・ビュー一覧

以下に、Amazon Redshiftで提供されているシステム系のテーブル・ビューに関する情報をまとめます。アクセス権限情報の一覧付きです。

ログ記録のためのSTLテーブル一覧

• ログ記録のための STL テーブル - Amazon Redshift

スナップショットデータの STV テーブル一覧

• スナップショットデータの STV テーブル - Amazon Redshift

システムビュー一覧

• システムビュー - Amazon Redshift

システムカタログテーブル一覧

• システムカタログテーブル - Amazon Redshift

まとめ

以上、調査用に用意しておくAmazon Redshiftユーザーの環境の整え方に関するまとめでした。これらの内容を整理すると、以下のポイントに基づいて権限が限定されたユーザーを用意する(または用意して頂く)事で想定する情報は用意出来そうです。自らの管理する環境でもそうですし、お客様環境を調査する際に御用意頂くユーザーについてもこのラインに基づいたユーザーを作成頂く事で安全な調査が行えそうですね。

• 1.調査対象となるテーブルを予め特定しておく必要がある(必要最低限のテーブルに対するアクセス権限を付与させるため)
• 2.とりあえずはテーブルに対する参照権限(SELECT)だけあれば何とかなりそう(データベースやテーブルに余計な影響を与えないため)
• 3.システム系のテーブルやビューに対しても、アクセス範囲は絞られるが閲覧は可能(1,2の設定後に所定ユーザーでアクセスした内容であれば確認出来そう)
• 4.クエリの実行計画(EXPLAIN)についても、関連するテーブルへの(参照)権限があれば取得は可能
• 5.スーパーユーザーが対象となっているテーブルやビューはデフォルトでは参照不可。しかし参照が必要であればGRANT文で通常運用テーブルと同様に権限付与は可能

こちらからは以上です。