Amazon Redshift グループとユーザー、アクセス権の管理について

Amazon Redshiftで良く使いそうな便利系SQL

#Amazon Redshift

#AWS

しんや

2014.05.22

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

しんやです。今回は小ネタ＆備忘録エントリ。

さて今回はAmaaon Redshiftに於けるグループとユーザー管理のコマンドについて。PostgreSQLのインタフェース同様のコマンドが各々用意されていますが、Amazon Redshiftでもやはり、このスキーマやテーブルの内容はこのユーザーやグループには見せる／見せない...と言った制御を行いたい場面が出てきます。

代表的なところで言えば、『この(スキーマ及び)テーブルの内容については、所定ユーザーのアクセスに対しては参照権限(SELECT)のみ許可する』辺りでしょうか。BIツールからAmazon Redshiftへのアクセスを行う上で、分析ツール側からデータを改変されては困ります。(そもそもツールから操作が出来るかというのもあるけれど)

以下はそんなSQLコマンド達の実行サンプルです。

グループの作成
ユーザーの作成
ユーザーのグループへの追加
グループからのユーザーの削除
グループに所属するユーザーの一覧を確認する
任意のスキーマに対し、任意のグループからの参照(SELECT)権限のみ許可する
任意のスキーマに対し、任意のユーザーからの参照(SELECT)権限のみ許可する
まとめ＆あわせて使いたい

グループの作成

ユーザーが所属するグループの作成を行います。グループ作成時にユーザーを指定するオプションもありますが、ここではシンプルにグループ作成のみの例を。

CREATE GROUP - Amazon Redshift

cmtestdb=# CREATE GROUP cmawsgroup;
CREATE GROUP

ユーザーの作成

対してこちらはユーザーの作成。パスワードと合わせて設定を行います。スーパーユーザーを作成する場合であれば、このタイミングでcreateuserオプションを追加します。

CREATE USER - Amazon Redshift

cmtestdb=# CREATE USER testuser1 WITH PASSWORD 'testPASS111';
CREATE USER
cmtestdb=# CREATE USER testuser2 WITH PASSWORD 'testPASS222';
CREATE USER

ユーザーのグループへの追加

所定のグループへユーザーを追加する際はALTER GROUPコマンドを使います。

ALTER GROUP - Amazon Redshift

cmtestdb=# ALTER GROUP cmawsgroup ADD USER testuser1;
ALTER GROUP
cmtestdb=# ALTER GROUP cmawsgroup ADD USER testuser2;
ALTER GROUP

グループからのユーザーの削除

ユーザーの追加同様、削除もALTER GROUPコマンドを使います。

ALTER GROUP - Amazon Redshift

cmtestdb=# CREATE USER testuser3 WITH PASSWORD 'testPASS333';
CREATE USER
cmtestdb=# ALTER GROUP cmawsgroup DROP USER testuser3;
ALTER GROUP

グループに所属するユーザーの一覧を確認する

グループの情報はpg_groupにて確認可能です。

cmtestdb=# SELECT * FROM pg_group;
 groname    | grosysid |  grolist  
------------+----------+-----------
 cmawsgroup |      100 | {118,119}
(1 行)

また、ユーザーの情報はpg_userにて確認可能です。

cmtestdb=# SELECT * FROM pg_user;
   usename   | usesysid | usecreatedb | usesuper | usecatupd |  passwd  | valuntil | useconfig 
-------------+----------+-------------+----------+-----------+----------+----------+-----------
 rdsdb       |        1 | t           | t        | t         | ******** |          | 
 :
 testuser1   |      118 | f           | f        | f         | ******** |          | 
 testuser2   |      119 | f           | f        | f         | ******** |          | 
 testuser3   |      120 | f           | f        | f         | ******** |          | 
(xx 行)

グループとユーザーの関連はpg_group.grolistの内容とpg_user.usesysidの内容で紐付いているようです。という訳でこの2つを結合した情報を取得して一発で任意のグループのユーザー一覧ゲットだぜ！

...と思っていたのですが、ここまで来てusesysidとgrolistの値を比較しようとして断念。そもそもusesysid in grolistとかは対応して無さそうだし、配列を行に変えてワンクッション置いて...というようにunnestを試みてみたものの関数としてはRedshiftでは用意されていなさそう。おのれ配列め...w

一行入魂　Postgresqlで行と配列と文字列の相互変換

cmtestdb=# SELECT pg_user.usename, pg_user.usesysid, pg_group.groname, pg_group.grolist FROM pg_user,pg_group;
   usename   | usesysid | groname    |  grolist  
-------------+----------+------------+-----------
 rdsdb       |        1 | cmawsgroup | {118,119}
 :
 testuser1   |      118 | cmawsgroup | {118,119}
 testuser2   |      119 | cmawsgroup | {118,119}
 testuser3   |      120 | cmawsgroup | {118,119}
(xx 行)

任意のスキーマに対し、任意のグループからの参照(SELECT)権限のみ許可する

ユーザーがグループに多数所属している＆そのグループ単位でアクセス制御を行わせたいというのであれば、以下のように『グループ単位で権限付与を行う』手段が効率が良いかもしれません。

GRANT - Amazon Redshift

cmtestdb=# GRANT SELECT ON public.sample_table TO GROUP cmawsgroup;

任意のスキーマに対し、任意のユーザーからの参照(SELECT)権限のみ許可する例

スキーマ作成後に任意のグループを追加した際は以下の手順でひと通り設定を確認しつつ動作確認まで行います。

/** スキーマ作成 */
# CREATE SCHEMA new_schema;                
/** テーブル作成 */
# CREATE TABLE new_schema.new_table (...); 
:
:
/** グループ作成 */
# CREATE GROUP new_group;
/** ユーザー作成 */
# CREATE USER new_user WITH PASSWORD 'XXXXXXXXXXXX';    
/** ユーザーをグループに追加 */
# ALTER GROUP new_group ADD USER new_user;              
/** スキーマに対する指定グループからのアクセスを許可 */
# GRANT USAGE ON SCHEMA new_schema TO GROUP new_group;  
/** 指定テーブルに対するアクセス権限設定 */
# GRANT SELECT ON new_schema.new_table TO GROUP new_group;  

/** 作成ユーザーでログイン */
$ psql -h ....  

/** SELECT:実行可能 */
$ SELECT * FROM new_schema.new_table;    

/** INSERT:権限不足によるエラー */
$ INSERT INTO new_schema.new_table VALUES(...,);    
ERROR:  permission denied for relation new_table

/** UPDATE:権限不足によるエラー */
$ INSERT UPDATE new_schema.new_table SET ... WHERE ...;    
ERROR:  permission denied for relation new_table

/** DELETE:権限不足によるエラー */
$ DELETE FROM new_schema.new_table WHERE...;    
ERROR:  permission denied for relation new_table