Amazon Redshift Serverlessで権限を分割しながらユーザとテーブルを作成

adminにsuperuser権限付与

★IAMユーザで実行★
select current_user;
-->IAM:awsuser

alter user admin WITH CREATEUSER;
select * from pg_user;

スキーマ作成

CREATE SCHEMA <schema_name> AUTHORIZATION admin;

select n.nspname as "schema",pg_catalog.pg_get_userbyid(n.nspowner) as "owner",　n.nspacl
from pg_catalog.pg_namespace n
where n.nspname !~ '^pg_' and n.nspname <> 'information_schema' order by 1;

ユーザ作成

CREATE USER user_etl WITH PASSWORD 'P@ssword12345';
CREATE USER user_ro_test1 password disable;
CREATE USER user_ro_test2 password 'P@ssword12345';

ロール作成・権限付与

★adminユーザで実行★　※恐らくIAMユーザでもいける気がするが念の為
select current_user;
--> admin

create role dwh_role_etl;
GRANT USAGE ON SCHEMA <schema_name> TO role dwh_role_etl;
GRANT CREATE ON SCHEMA <schema_name> TO role dwh_role_etl;`
ALTER DEFAULT PRIVILEGES IN SCHEMA <schema_name> GRANT ALL ON TABLES TO role dwh_role_etl;

create role dwh_role_ro;
GRANT USAGE ON SCHEMA <schema_name> TO role dwh_role_ro;
ALTER DEFAULT PRIVILEGES IN SCHEMA <schema_name> GRANT SELECT ON TABLES TO role dwh_role_ro;

select * from SVV_ROLES;

ユーザにロール付与

grant role dwh_role_etl to user_etl;
grant role dwh_role_ro to user_ro_test1;
grant role dwh_role_ro to user_ro_test2;

ETL用ロールにsuperuser権限を付与

TRUNCATE コマンドを実行するには、テーブルの所有者またはスーパーユーザーである必要があります。
https://docs.aws.amazon.com/ja_jp/redshift/latest/dg/r_TRUNCATE.html

alter user user_etl WITH CREATEUSER;
select * from pg_user;

テーブル作成

CREATE TABLE <schema_name>.test_table (
    colint1 int,
    日本語カラム１ varchar(200)
) DISTSTYLE AUTO;

insert into <schema_name>.test_table values (1,'テスト１');
select * from <schema_name>.test_table;

動作確認

★user_etlユーザで実行★
select current_user;
--> user_etl

select * from <schema_name>.test_table;
truncate table <schema_name>.test_table;