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

#Amazon Redshift

#AWS

しんや

2014.08.21

この記事は公開されてから1年以上経過しています。情報が古い可能性がありますので、ご注意ください。

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

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

テスト用グループ・ユーザーの作成
想定する構築状況
ユーザー作成の要素に対する権限付与設定
システム系のテーブル・ビューに対する権限付与設定
調査分析に使うコマンドの実行確認
付録：Amazon Redshift システム系テーブル・ビュー一覧
まとめ

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

先ず最初に、アクセス確認を行う為のユーザー及びユーザーが所属するグループを作成しておきます。スーパーユーザーで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

テーブル名	説明	参照可能ユーザー
STL_AGGR	集計を実行するステップのクエリについて分析	全てのユーザー
STL_ALERT_EVENT_LOG	パフォーマンスの問題を示している可能性のある条件がクエリオプティマイザによって特定された場合にアラートを記録	ユーザー
STL_BCAST	データをブロードキャストするクエリステップが実行されている間のネットワークアクティビティに関する情報を記録	ユーザー
STL_COMMIT_STATS	コミットのさまざまなステージのタイミングやコミットされるブロックの数など、コミットのパフォーマンスに関連するメトリックスを提供	スーパーユーザー
STL_CONNECTION_LOG	認証の試みと、接続および切断をログに記録	スーパーユーザー
STL_DDLTEXT	システムで実行された以下の DDL ステートメントを取得・CREATE SCHEMA、TABLE、VIEW ・DROP SCHEMA、TABLE、VIEW ・ALTER SCHEMA、TABLE	全てのユーザー
STL_DIST	データを配布するクエリステップが実行されている間のネットワークアクティビティに関する情報を記録	全てのユーザー
STL_DELETE	削除を実行するステップをクエリについて分析	全てのユーザー
STL_ERROR	クエリの実行中に発生するすべてのエラーを記録	全てのユーザー
STL_EXPLAIN	実行するために送信されたクエリの EXPLAIN プランを表示	全てのユーザー
STL_FILE_SCAN	COPY コマンドによってデータをロードするときに Amazon Redshift が読み込んだファイルを返す	全てのユーザー
STL_HASH	ハッシュを実行するステップをクエリについて分析	全てのユーザー
STL_HASHJOIN	ハッシュ結合を実行するステップをクエリについて分析	全てのユーザー
STL_INSERT	挿入を実行するステップをクエリについて分析	全てのユーザー
STL_LIMIT	SELECT クエリ内で LIMIT 句が使用されるときに発生する実行ステップを分析	全てのユーザー
STL_LOAD_COMMITS	データのロードを追跡またはトラブルシューティングするための情報を返す	全てのユーザー
STL_LOAD_ERRORS	すべての Amazon Redshift ロードエラーのレコードを表示	全てのユーザー
STL_LOADERROR_DETAIL	COPY コマンドを使用したテーブルのロード中に発生したデータ解析エラーのログを表示	全てのユーザー
STL_MERGE	マージを実行するステップをクエリについて分析	全てのユーザー
STL_MERGEJOIN	マージ結合を実行するステップをクエリについて分析	全てのユーザー
STL_NESTLOOP	ネステッドループ結合を実行するステップをクエリについて分析	全てのユーザー
STL_PARSE	文字列をロード用のバイナリ値に解析するクエリステップを分析	全てのユーザー
STL_PLAN_INFO	行のセットに関するクエリの EXPLAIN 出力を確認	全てのユーザー
STL_PROJECT	式を評価するために使用されるクエリステップの行が含まれる	全てのユーザー
STL_QUERY	データベースクエリに関する実行情報を返す	全てのユーザー
STL_QUERYTEXT	SQL コマンドのクエリテキストを取得	全てのユーザー
STL_REPLACEMENTS	無効な UTF-8 文字が COPY コマンドの ACCEPTINVCHARS オプションによって置き換えられたときのログを表示	全てのユーザー
STL_RETURN	クエリに含まれるリターンステップの詳細を格納	ユーザー
STL_SAVE	クエリに含まれる保存ステップの詳細を格納	ユーザー
STL_S3CLIENT	転送時間などのパフォーマンスメトリックスを記録	全てのユーザー
STL_S3CLIENT_ERROR	スライスで Amazon S3 からファイルをロードするときに発生したエラーを記録	全てのユーザー
STL_SCAN	テーブルをスキャンするステップをクエリについて分析	全てのユーザー
STL_SESSIONS	ユーザーセッション履歴に関する情報を返す	全てのユーザー
STL_SORT	ORDER BY 処理などを使用する、ソートを実行するステップのクエリを表示	全てのユーザー
STL_SSHCLIENT_ERROR	SSH クライアントに対して表示されるすべてのエラーを記録	全てのユーザー
STL_STREAM_SEGS	ストリームと並行セグメントの間の関係をリスト表示	全てのユーザー
STL_TR_CONFLICT	データベーステーブルのロック競合を認識し、解決するための情報を表示	全てのユーザー
STL_UNDONE	元に戻されたトランザクションに関する情報を表示	全てのユーザー
STL_UNIQUE	SELECT リストで DISTINCT 関数が使用されているとき、あるいは UNION または INTERSECT クエリから重複が除去されるときに発生する実行ステップを分析	全てのユーザー
STL_UNLOAD_LOG	アンロード処理の詳細を記録	全てのユーザー
STL_USERLOG	データベースユーザーに対する次の変更の詳細を記録・ユーザーの作成・ユーザーの削除・ユーザーの変更（名前の変更）・ユーザーの変更（プロパティの変更）	スーパーユーザー
STL_UTILITYTEXT	データベースに対して実行された SELECT 以外の SQL コマンドを取得／システムで実行された SQL ステートメントのうち、次のサブセットを取得可能・ABORT、BEGIN、COMMIT、END、ROLLBACK ・CANCEL ・COMMENT ・CREATE、ALTER、DROP DATABASE ・CREATE、ALTER、DROP USER ・EXPLAIN ・GRANT、REVOKE ・LOCK ・RESET ・SET ・SHOW ・TRUNCATE	全てのユーザー
STL_VACUUM	バキュームされたテーブルの行およびブロックに関する統計を表示	スーパーユーザー
STL_WARNING	Amazon Redshift での予期しない出来事のうち、エラーを引き起こすほど深刻ではないもののログを表示	全てのユーザー
STL_WINDOW	ウィンドウ関数を実行するクエリステップを分析	全てのユーザー
STL_WLM_ERROR	すべての WLM 関連のエラーを発生時に記録	全てのユーザー
STL_WLM_QUERY	WLM が扱うサービスクラス内で実行が試みられたクエリごとに、レコードが 1 件作成	全てのユーザー

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

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

テーブル名	説明	参照可	STV_ACTIVE_CURSORS	現在開いているカーソルの詳細を表示ユーザーから表示可能。ユーザーは、自身が開いたカーソルのみを表示可能。スーパーユーザーはすべてのカーソルを表示可能。	全てのユーザー	STV_BLOCKLIST	データベース内の各スライス、テーブル、または列で使用される 1 MB ディスクブロックの数が表示	スーパーユーザー	STV_CURSOR_CONFIGURATION	カーソル設定の制約を表示	スーパーユーザー	STV_EXEC_STATE	Amazon Redshift でアクティブに実行されているクエリおよびクエリ手順についての情報を取得	ユーザー	STV_INFLIGHT	データベースで現在どのクエリが実行されているかを調べる	ユーザー	STV_LOAD_STATE	実行中の COPY ステートメントの現在の状態についての情報を取得	ユーザー	STV_LOCKS	データベース内のテーブルに対する現時点での更新をすべて表示	スーパーユーザー	STV_PARTITIONS	Amazon Redshift のディスク速度の性能とディスクの利用状況を調べる	スーパーユーザー	STV_RECENTS	現在アクティブなクエリや、最近データベースに対して実行されたクエリに関する情報を取得	ユーザー	STV_SLICES	スライスからノードへの現在のマッピングを見る	ユーザー	STV_SESSIONS	Amazon Redshift のアクティブなユーザーセッションに関する情報を見る	ユーザー	STV_TBL_PERM	STV_TBL_PERM テーブルには、現在のセッション用にユーザーが作成した一時テーブルを含め、Amazon Redshift の永続テーブルに関する情報が表示	スーパーユーザー	STV_TBL_TRANS	現在メモリ内にある一時データベーステーブルに関する情報を取得	スーパーユーザー	STV_WLM_CLASSIFICATION_CONFIG	WLM の現在の分類ルール	スーパーユーザー	STV_WLM_QUERY_QUEUE_STATE	サービスクラスのクエリキューの現在の状態を記録	ユーザー	STV_WLM_QUERY_STATE	WLM に追跡されているクエリの現在の状態	ユーザー	STV_WLM_QUERY_TASK_STATE	サービスクラスクエリタスクの現在の状態を表示	ユーザー	STV_WLM_SERVICE_CLASS_CONFIG	WLM のサービスクラス設定を記録	スーパーユーザー	STV_WLM_SERVICE_CLASS_STATE	サービスクラスの現在の状態を表示	スーパーユーザー

システムビュー一覧

システムビュー - Amazon Redshift

テーブル名	説明	参照可	SVL_COMPILE	クエリの各クエリセグメントのコンパイル時間と位置を記録	全てのユーザー	SVV_DISKUSAGE	STV_TBL_PERM テーブルと STV_BLOCKLIST テーブルを結合して、SVV_DISKUSAGE システムビューを作成。データベースのテーブルに対するデータ割り当てに関する情報が含まれる	スーパーユーザー	SVL_QLOG	データベースに対して実行されたすべてのクエリのログが含まれる	ユーザー	SVV_QUERY_INFLIGHT	データベースで現在実行されているクエリを確認	ユーザー	SVL_QUERY_REPORT	Amazon Redshift の STL システムテーブルのいくつかを UNION（結合）したものから SVL_QUERY_REPORT ビューを作成し、実行されたクエリステップについての情報を提供	ユーザー	SVV_QUERY_STATE	現在実行されているクエリの実行についての情報を表示。STV_EXEC_STATE テーブルのデータのサブセットが含まれる	ユーザー	SVL_QUERY_SUMMARY	クエリの実行についての全般的な情報を確認。SVL_QUERY_REPORT ビューからのデータのサブセットが含まれる。SVL_QUERY_SUMMARY の情報はすべてのノードからの情報の集計であることに注意	ユーザー	SVL_STATEMENTTEXT	システムで実行されたすべての SQL コマンドの完全な記録を取得	ユーザー	SVV_VACUUM_PROGRESS	現在進行中のバキューム処理が終了するまでにかかる時間の予測を返す	スーパーユーザー	SVV_VACUUM_SUMMARY	STL_VACUUM テーブル、STL_QUERY テーブル、STV_TBL_PERM テーブルを結合し、システムがログ記録したバキューム処理についての情報を要約	スーパーユーザー	SVL_VACUUM_PERCENTAGE	バキューム処理の実行後にテーブルに割り当てられるデータブロックのパーセントを示す	スーパーユーザー

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

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

テーブル名	説明	参照可	PG_TABLE_DEF	テーブルに関してユーザーに表示される情報のみを返す	"Amazon Redshift ユーザーは、標準の PostgreSQL カタログテーブルにアクセスできます"→ユーザー？

まとめ

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