Redshift ユーザーによって COPY/UNLOAD でアクセスできる S3 のフォルダ（プレフィックス）を分ける方法をメモ。
実現すること
- Redshift ユーザー "rs_user_a" は S3 パス "s3://s3-for-redshift/rs_user_a/" 以下のみに COPY/UNLOAD でアクセスできる。
- Redshift ユーザー "rs_user_b" は S3 パス "s3://s3-for-redshift/rs_user_b/" 以下のみに COPY/UNLOAD でアクセスできる。
検証結果
Redshift ユーザー "rs_user_a" は S3 パス "s3:///rs_user_a/" 以下のみに COPY/UNLOAD でアクセスできる。
- rs_user_a ユーザーで Redshift にログインする。
$ psql "host=redshift-cluster-poc-central.********.ap-northeast-1.redshift.amazonaws.com user=rs_user_a dbname=dev port=5439"
- 許可している S3 パスには COPY/UNLOAD できる。
dev=> unload ('select * from public.customer') dev-> to 's3://s3-for-redshift/rs_user_a/customer' dev-> iam_role 'arn:aws:iam::123456789012:role/iam-role_for_rs_role_a' dev-> header dev-> format csv dev-> gzip dev-> parallel off dev-> allowoverwrite; INFO: UNLOAD completed, 10125500 record(s) unloaded successfully. UNLOAD dev=> copy public.customer from 's3://s3-for-redshift/rs_user_a/customer000.gz' dev-> iam_role 'arn:aws:iam::123456789012:role/iam-role_for_rs_role_a' dev-> format csv dev-> gzip compupdate off region 'ap-northeast-1' dev-> ignoreheader 1; INFO: Load into table 'customer' completed, 10125500 record(s) loaded successfully. COPY
- 許可していない S3 パスには COPY/UNLOAD できない。
dev=> unload ('select * from public.customer') dev-> to 's3://s3-for-redshift/rs_user_b/customer' dev-> iam_role 'arn:aws:iam::123456789012:role/iam-role_for_rs_role_a' dev-> header dev-> format csv dev-> gzip dev-> parallel off dev-> allowoverwrite; ERROR: S3ServiceException:User: arn:aws:sts::123456789012:assumed-role/iam-role_for_rs_role_a/RedshiftIamRoleSession is not authorized to perform: s3:PutObject on resource: "arn:aws:s3:::s3-for-redshift/rs_user_b/customer000.gz" because no identity-based polic DETAIL: ----------------------------------------------- error: S3ServiceException:User: arn:aws:sts::123456789012:assumed-role/iam-role_for_rs_role_a/RedshiftIamRoleSession is not authorized to perform: s3:PutObject on resource: "arn:aws:s3:::s3-for-redshift/rs_user_b/customer000.gz" because no identity-based polic code: 8001 context: Failed to initialize S3 output stream. S3 path: s3://s3-for-redshift/rs_user_b/customer000.gz query: 35041819 location: s3_text_unloader.cpp:369 process: padbmaster [pid=1073750164] ----------------------------------------------- dev=> copy public.customer from 's3://s3-for-redshift/rs_user_b/customer000.gz' dev-> iam_role 'arn:aws:iam::123456789012:role/iam-role_for_rs_role_a' dev-> format csv dev-> gzip compupdate off region 'ap-northeast-1' dev-> ignoreheader 1; ERROR: S3ServiceException:User: arn:aws:sts::123456789012:assumed-role/iam-role_for_rs_role_a/RedshiftIamRoleSession is not authorized to perform: s3:GetObject on resource: "arn:aws:s3:::s3-for-redshift/rs_user_b/customer000.gz" because no identity-based polic DETAIL: ----------------------------------------------- error: S3ServiceException:User: arn:aws:sts::123456789012:assumed-role/iam-role_for_rs_role_a/RedshiftIamRoleSession is not authorized to perform: s3:GetObject on resource: "arn:aws:s3:::s3-for-redshift/rs_user_b/customer000.gz" because no identity-based polic code: 8001 context: S3 key being read : s3://s3-for-redshift/rs_user_b/customer000.gz query: 35482475 location: copy_s3_scanner.cpp:344 process: query1_252_35482475 [pid=15960] -----------------------------------------------
Redshift ユーザー "rs_user_b" は S3 パス "s3:///rs_user_b/" 以下のみに COPY/UNLOAD でアクセスできる。
- rs_user_b ユーザーで Redshift にログインする。
$ psql "host=redshift-cluster-poc-central.********.ap-northeast-1.redshift.amazonaws.com user=rs_user_b dbname=dev port=5439"
- 許可している S3 パスには COPY/UNLOAD できる。
dev=> unload ('select * from public.customer') dev-> to 's3://s3-for-redshift/rs_user_b/customer' dev-> iam_role 'arn:aws:iam::123456789012:role/iam-role_for_rs_role_b' dev-> header dev-> format csv dev-> gzip dev-> parallel off dev-> allowoverwrite; INFO: UNLOAD completed, 20251000 record(s) unloaded successfully. UNLOAD dev=> copy public.customer from 's3://s3-for-redshift/rs_user_b/customer000.gz' dev-> iam_role 'arn:aws:iam::123456789012:role/iam-role_for_rs_role_b' dev-> format csv dev-> gzip compupdate off region 'ap-northeast-1' dev-> ignoreheader 1; INFO: Load into table 'customer' completed, 20251000 record(s) loaded successfully. COPY
- 許可していない S3 パスには COPY/UNLOAD できない。
dev=> unload ('select * from public.customer') dev-> to 's3://s3-for-redshift/rs_user_a/customer' dev-> iam_role 'arn:aws:iam::123456789012:role/iam-role_for_rs_role_b' dev-> header dev-> format csv dev-> gzip dev-> parallel off dev-> allowoverwrite; ERROR: S3ServiceException:User: arn:aws:sts::123456789012:assumed-role/iam-role_for_rs_role_b/RedshiftIamRoleSession is not authorized to perform: s3:PutObject on resource: "arn:aws:s3:::s3-for-redshift/rs_user_a/customer000.gz" because no identity-based polic DETAIL: ----------------------------------------------- error: S3ServiceException:User: arn:aws:sts::123456789012:assumed-role/iam-role_for_rs_role_b/RedshiftIamRoleSession is not authorized to perform: s3:PutObject on resource: "arn:aws:s3:::s3-for-redshift/rs_user_a/customer000.gz" because no identity-based polic code: 8001 context: Failed to initialize S3 output stream. S3 path: s3://s3-for-redshift/rs_user_a/customer000.gz query: 35483157 location: s3_text_unloader.cpp:369 process: padbmaster [pid=1073988131] ----------------------------------------------- dev=> copy public.customer from 's3://s3-for-redshift/rs_user_a/customer000.gz' dev-> iam_role 'arn:aws:iam::123456789012:role/iam-role_for_rs_role_b' dev-> format csv dev-> gzip compupdate off region 'ap-northeast-1' dev-> ignoreheader 1; ERROR: S3ServiceException:User: arn:aws:sts::123456789012:assumed-role/iam-role_for_rs_role_b/RedshiftIamRoleSession is not authorized to perform: s3:GetObject on resource: "arn:aws:s3:::s3-for-redshift/rs_user_a/customer000.gz" because no identity-based polic DETAIL: ----------------------------------------------- error: S3ServiceException:User: arn:aws:sts::123456789012:assumed-role/iam-role_for_rs_role_b/RedshiftIamRoleSession is not authorized to perform: s3:GetObject on resource: "arn:aws:s3:::s3-for-redshift/rs_user_a/customer000.gz" because no identity-based polic code: 8001 context: S3 key being read : s3://s3-for-redshift/rs_user_a/customer000.gz query: 35483161 location: copy_s3_scanner.cpp:344 process: query1_252_35483161 [pid=15960] -----------------------------------------------
GRANT されていない IAM ロールには ASSUMEROLE できない
- rs_user_a
$ psql "host=redshift-cluster-poc-central.ceyg6jv96hfq.ap-northeast-1.redshift.amazonaws.com user=rs_user_a dbname=dev port=5439" dev=> copy public.customer from 's3://s3-for-redshift/rs_user_b/customer000.gz' dev-> iam_role 'arn:aws:iam::123456789012:role/iam-role_for_rs_role_b' dev-> format csv dev-> gzip compupdate off region 'ap-northeast-1' dev-> ignoreheader 1; ERROR: User rs_user_a does not have ASSUMEROLE permission on IAM role "arn:aws:iam::123456789012:role/iam-role_for_rs_role_b" for COPY
- rs_user_b
$ psql "host=redshift-cluster-poc-central.ceyg6jv96hfq.ap-northeast-1.redshift.amazonaws.com user=rs_user_b dbname=dev port=5439" dev=> copy public.customer from 's3://s3-for-redshift/rs_user_a/customer000.gz' dev-> iam_role 'arn:aws:iam::123456789012:role/iam-role_for_rs_role_a' dev-> format csv dev-> gzip compupdate off region 'ap-northeast-1' dev-> ignoreheader 1; ERROR: User rs_user_b does not have ASSUMEROLE permission on IAM role "arn:aws:iam::123456789012:role/iam-role_for_rs_role_a" for COPY
設定手順
- 対象の Redshift クラスターで以下を1度実行する。
- これを実行しないと grant されてない IAM ロールに AssumeRole できてしまう。
revoke assumerole on all from public for all;
ASSUMEROLE のための IAMロール "iam-role_for_rs_role_a" を作成する。
- iam-policy_for_rs_role_a
{ "Version": "2012-10-17", "Statement": [ { "Effect": "Allow", "Action": [ "s3:ListBucket" ], "Resource": [ "arn:aws:s3:::*" ] }, { "Effect": "Allow", "Action": [ "s3:GetObject", "s3:PutObject" ], "Resource": [ "arn:aws:s3:::s3-for-redshift/rs_user_a/", "arn:aws:s3:::s3-for-redshift/rs_user_a/*" ] } ] }
- 信頼関係
{ "Version": "2012-10-17", "Statement": [ { "Effect": "Allow", "Principal": { "Service": "redshift.amazonaws.com" }, "Action": "sts:AssumeRole" } ] }
ASSUMEROLE のための IAMロール "iam-role_for_rs_role_b" を作成する。
- iam-policy_for_rs_role_b
{ "Version": "2012-10-17", "Statement": [ { "Effect": "Allow", "Action": [ "s3:ListBucket" ], "Resource": [ "arn:aws:s3:::*" ] }, { "Effect": "Allow", "Action": [ "s3:GetObject", "s3:PutObject" ], "Resource": [ "arn:aws:s3:::s3-for-redshift/rs_user_b/", "arn:aws:s3:::s3-for-redshift/rs_user_b/*" ] } ] }
- 信頼関係
{ "Version": "2012-10-17", "Statement": [ { "Effect": "Allow", "Principal": { "Service": "redshift.amazonaws.com" }, "Action": "sts:AssumeRole" } ] }
- 作成した IAM ロールを Redshift にアタッチする。
- Redshift ユーザーを作成し、PUBLIC スキーマのテーブルへのアクセス権を付与する。
CREATE USER rs_user_a PASSWORD 'Password123!'; CREATE USER rs_user_b PASSWORD 'Password123!'; grant usage on schema public to rs_user_a, rs_user_b; grant all on all tables in schema public to rs_user_a, rs_user_b;
- Redshift ユーザーが ASSUMEROLE できるよう権限を GRANT する。
GRANT ASSUMEROLE ON 'arn:aws:iam::123456789012:role/iam-role_for_rs_role_a' TO rs_user_a FOR COPY, UNLOAD; GRANT ASSUMEROLE ON 'arn:aws:iam::123456789012:role/iam-role_for_rs_role_b' TO rs_user_b FOR COPY, UNLOAD;
設定したロールを確認する
- rs_user_a
dev=# select pg_get_iam_role_by_user('rs_user_a'); pg_get_iam_role_by_user ------------------------------------------------------------------------------- (rs_user_a,all,None) (rs_user_a,arn:aws:iam::123456789012:role/iam-role_for_rs_role_a,COPY|UNLOAD) (2 rows)
- rs_user_b
dev=# select pg_get_iam_role_by_user('rs_user_b'); pg_get_iam_role_by_user ------------------------------------------------------------------------------- (rs_user_b,all,None) (rs_user_b,arn:aws:iam::123456789012:role/iam-role_for_rs_role_b,COPY|UNLOAD) (2 rows)
補足
- スーパーユーザーで以下を実行すると "revoke assumerole on all from public for all" を元に戻せる。また、"revoke assumerole on all from public for all" を手順の最後に実行するとそれまでは grant していない IAM ロールにも AssumeRole できる。
grant assumerole on all to public for all;
参考
ASSUMEROLE アクセス許可の付与
指定されたロールを持つユーザーおよびグループに付与される ASSUMEROLE アクセス許可の構文を次に示します。ASSUMEROLE 権限の使用を開始する際は、「ASSUMEROLE アクセス許可を付与するための使用上の注意事項」を参照してください。GRANT ASSUMEROLE ON { 'iam_role' [, ...] | default | ALL } TO { username | ROLE role_name | GROUP group_name | PUBLIC } [, ...] FOR { ALL | COPY | UNLOAD | EXTERNAL FUNCTION | CREATE MODEL } [, ...]GRANT - Amazon Redshift
