スキーマに新規作成したテーブルへのアクセス権をデフォルトで付与する

Amazon Redshift で以下の権限を付与しておけば、新規作成したビューも参照できることと確認した（以下の例では DROP/CREATE している）。

ALTER DEFAULT PRIVILEGES FOR USER ... IN SCHEMA ...  GRANT SELECT ON TABLES TO ...;
GRANT SELECT ON ALL TABLES IN SCHEMA ... TO ...;

手順

ロールを作成する

$ psql -A -h rs-ra3-4xl-4n.********.us-east-1.redshift.amazonaws.com -p 5439 -d tpch_3tb -U awsuser
CREATE ROLE admin_role;
GRANT CREATE SCHEMA TO ROLE admin_role;

CREATE ROLE obj_admin_role;
GRANT CREATE TABLE TO ROLE obj_admin_role;

CREATE ROLE bi_role;

ユーザーを作成する

CREATE USER schema_owner PASSWORD 'Password123!';
GRANT ROLE admin_role TO schema_owner;

CREATE USER obj_admin PASSWORD 'Password123!';
GRANT ROLE obj_admin_role TO obj_admin;

CREATE USER bi_user PASSWORD 'Password123!';
GRANT ROLE bi_role TO bi_user;

スキーマを作成する（schema_owner）

$ psql -A -h rs-ra3-4xl-4n.********.us-east-1.redshift.amazonaws.com -p 5439 -d dev -U schema_owner
CREATE SCHEMA test_schema;

スキーマへのアクセス権を付与する（superuser）

GRANT USAGE ON SCHEMA test_schema to ROLE obj_admin_role;
GRANT ALL ON SCHEMA test_schema to ROLE obj_admin_role;
GRANT USAGE ON SCHEMA test_schema to ROLE bi_role;

テーブルとビューを作成する（obj_admin）

$ psql -A -h rs-ra3-4xl-4n.********.us-east-1.redshift.amazonaws.com -p 5439 -d dev -U obj_admin
CREATE TABLE test_schema.test_table1 (col1 varchar);
CREATE VIEW test_schema.test_view1 as select * from test_schema.test_table1;

参照ユーザーにビューへの参照権限を付与する（superuser）

ALTER DEFAULT PRIVILEGES FOR USER obj_admin IN SCHEMA test_schema  GRANT SELECT ON TABLES TO bi_user;
GRANT SELECT ON ALL TABLES IN SCHEMA test_schema TO bi_user;

既存 View を SELECT できるか確認する（bi_user1）

$ psql -A -h rs-ra3-4xl-4n.********.us-east-1.redshift.amazonaws.com -p 5439 -d dev -U bi_user
SELECT * from test_schema.test_view1;

新規 View を再作成する（obj_admin）

$ psql -A -h rs-ra3-4xl-4n.********.us-east-1.redshift.amazonaws.com -p 5439 -d dev -U obj_admin
DROP VIEW test_schema.test_view1;
CREATE VIEW test_schema.test_view1 as select * from test_schema.test_table1;

再作成した View を SELECT できるか確認する（bi_user1）

$ psql -A -h rs-ra3-4xl-4n.********.us-east-1.redshift.amazonaws.com -p 5439 -d dev -U bi_user
SELECT * from test_schema.test_view1;

初期化手順

DROP VIEW test_schema.test_view1;
DROP TABLE test_schema.test_table11;
DROP SCHEMA test_schema CASCADE;

DROP USER bi_user;
DROP USER obj_admin;
DROP USER schema_owner;

DROP ROLE admin_role FORCE;
DROP ROLE obj_admin_role FORCE;
DROP ROLE bi_role FORCE;