事象
ユーザーにテーブルに対する権限を付与しているのに "permission denied for schema ..." と怒られる。
- こんな感じでアクセス権を付与しているが、
% psql "host=redshift-cluster-poc.********.ap-northeast-1.redshift.amazonaws.com user=awsuser dbname=dev port=5439" create group adhoc_l1_group; create group adhoc_l2_group; grant all on all tables in schema public, awssampledb to group adhoc_l1_group; ★ テーブルに対して権限を付与しているが grant all on all tables in schema public, awssampledb to group adhoc_l2_group; create user adhoc_l1_user password 'Password1' in group adhoc_l1_group session timeout 36000; create user adhoc_l2_user password 'Password1' in group adhoc_l2_group session timeout 36000;
- "ERROR: permission denied for schema" と怒られる。
% psql "host=redshift-cluster-poc.*******.ap-northeast-1.redshift.amazonaws.com user=adhoc_l1_user dbname=dev port=5439" dev=> select count(*) from awssampledb.lineorder; ERROR: permission denied for schema awssampledb ★アクセス権がないと怒られる
原因
スキーマに対する usage 権限が付与されていないため。
% psql "host=redshift-cluster-poc.********.ap-northeast-1.redshift.amazonaws.com user=awsuser dbname=dev port=5439" dev=# select usename , schemaname , tablename , has_table_privilege(usename, schemaname || '.' || tablename, 'select') as select , has_table_privilege(usename, schemaname || '.' || tablename, 'insert') as insert , has_table_privilege(usename, schemaname || '.' || tablename, 'update') as update , has_table_privilege(usename, schemaname || '.' || tablename, 'delete') as delete , has_table_privilege(usename, schemaname || '.' || tablename, 'references') as references , has_schema_privilege(usename, schemaname, 'usage') as usage from pg_tables, pg_user where schemaname in ('awssampledb') -- 確認したいスキーマで絞り込む and usename in ('adhoc_l1_user','adhoc_l2_user') -- 確認したいユーザで絞り込む order by 1, 2, 3 ; usename | schemaname | tablename | select | insert | update | delete | references | usage ---------------+-------------+-----------+--------+--------+--------+--------+------------+------- adhoc_l1_user | awssampledb | customer | t | t | t | t | t | f adhoc_l1_user | awssampledb | dwdate | t | t | t | t | t | f adhoc_l1_user | awssampledb | lineorder | t | t | t | t | t | f adhoc_l1_user | awssampledb | part | t | t | t | t | t | f adhoc_l1_user | awssampledb | supplier | t | t | t | t | t | f adhoc_l2_user | awssampledb | customer | t | t | t | t | t | f adhoc_l2_user | awssampledb | dwdate | t | t | t | t | t | f adhoc_l2_user | awssampledb | lineorder | t | t | t | t | t | f adhoc_l2_user | awssampledb | part | t | t | t | t | t | f adhoc_l2_user | awssampledb | supplier | t | t | t | t | t | f (10 rows)
対処法
- スキーマに対する usage 権限を付与する
% psql "host=redshift-cluster-poc.********.ap-northeast-1.redshift.amazonaws.com user=awsuser dbname=dev port=5439" dev=# grant usage on schema awssampledb to adhoc_l1_user, adhoc_l2_user;
- 権限が付与されていることを確認する
dev=# select usename , schemaname , tablename , has_table_privilege(usename, schemaname || '.' || tablename, 'select') as select , has_table_privilege(usename, schemaname || '.' || tablename, 'insert') as insert , has_table_privilege(usename, schemaname || '.' || tablename, 'update') as update , has_table_privilege(usename, schemaname || '.' || tablename, 'delete') as delete , has_table_privilege(usename, schemaname || '.' || tablename, 'references') as references , has_schema_privilege(usename, schemaname, 'usage') as usage from pg_tables, pg_user where schemaname in ('awssampledb') -- 確認したいスキーマで絞り込む and usename in ('adhoc_l1_user','adhoc_l2_user') -- 確認したいユーザで絞り込む order by 1, 2, 3 ; usename | schemaname | tablename | select | insert | update | delete | references | usage ---------------+-------------+-----------+--------+--------+--------+--------+------------+------- adhoc_l1_user | awssampledb | customer | t | t | t | t | t | t adhoc_l1_user | awssampledb | dwdate | t | t | t | t | t | t adhoc_l1_user | awssampledb | lineorder | t | t | t | t | t | t adhoc_l1_user | awssampledb | part | t | t | t | t | t | t adhoc_l1_user | awssampledb | supplier | t | t | t | t | t | t adhoc_l2_user | awssampledb | customer | t | t | t | t | t | t adhoc_l2_user | awssampledb | dwdate | t | t | t | t | t | t adhoc_l2_user | awssampledb | lineorder | t | t | t | t | t | t adhoc_l2_user | awssampledb | part | t | t | t | t | t | t adhoc_l2_user | awssampledb | supplier | t | t | t | t | t | t (10 rows)
- アクセスできるようになっている
% psql "host=redshift-cluster-poc.********.ap-northeast-1.redshift.amazonaws.com user=adhoc_l1_user dbname=dev port=5439" dev=> select count(*) from awssampledb.lineorder; count ----------- 600037902 (1 row)
