ablog

不器用で落着きのない技術者のメモ

Redshift でユーザーが使用可能なストレージ容量を制限する

容量制限つきのスキーマを作成して、ユーザーが利用できるようにする。

  • superuser でスキーマ作成権限を持つユーザーを作成する。
$ psql -a "host=redshift-cluster-poc-ads.********.ap-northeast-1.redshift.amazonaws.com user=awsuser dbname=sampledb port=5439"

psql (14.3, server 8.0.2)
SSL connection (protocol: TLSv1.2, cipher: ECDHE-RSA-AES256-GCM-SHA384, bits: 256, compression: off)
Type "help" for help.

-- 管理ユーザー用 Role を作成する
sampledb=# create role dm_admin_role;
CREATE ROLE
-- 管理ユーザー用 Role に権限を付与する
sampledb=# grant create schema to role dm_admin_role;
GRANT
sampledb=# grant create user to role dm_admin_role;
GRANT
-- 管理ユーザーを作成して、Role を付与する
sampledb=# create user dm_admin_user password 'Password1';
CREATE USER
sampledb=# grant role dm_admin_role to dm_admin_user;
GRANT
-- 管理ユーザーに sampledb へのアクセス権を付与する
sampledb=# grant all on database sampledb to dm_admin_user;
GRANT
  • 管理者ユーザーでユーザーを作成する。
$ psql -a "host=redshift-cluster-poc-ads.********.ap-northeast-1.redshift.amazonaws.com user=dm_admin_user dbname=sampledb port=5439"

Password for user dm_admin_user: 
psql (14.3, server 8.0.2)
SSL connection (protocol: TLSv1.2, cipher: ECDHE-RSA-AES256-GCM-SHA384, bits: 256, compression: off)
Type "help" for help.

-- データマート利用ユーザーを作成する
sampledb=> create user dm_user password 'Password1' ;
CREATE USER
--ストレージ容量の上限 2GB でスキーマを作成する。
sampledb=> create schema dm_schema authorization dm_user quota 2 gb;
CREATE SCHEMA
  • データマート利用ユーザーは2GBを超えて書込みができない。
$ psql -a "host=redshift-cluster-poc-ads.********.ap-northeast-1.redshift.amazonaws.com user=dm_user dbname=sampledb port=5439"

Password for user dm_user: 
psql (14.3, server 8.0.2)
SSL connection (protocol: TLSv1.2, cipher: ECDHE-RSA-AES256-GCM-SHA384, bits: 256, compression: off)
Type "help" for help.

sampledb=> CREATE TABLE dm_schema.lineorder 
(
  lo_orderkey          INTEGER NOT NULL,
  lo_linenumber        INTEGER NOT NULL,
  lo_custkey           INTEGER NOT NULL,
  lo_partkey           INTEGER NOT NULL,
  lo_suppkey           INTEGER NOT NULL,
  lo_orderdate         INTEGER NOT NULL,
  lo_orderpriority     VARCHAR(15) NOT NULL,
  lo_shippriority      VARCHAR(1) NOT NULL,
  lo_quantity          INTEGER NOT NULL,
  lo_extendedprice     INTEGER NOT NULL,
  lo_ordertotalprice   INTEGER NOT NULL,
  lo_discount          INTEGER NOT NULL,
  lo_revenue           INTEGER NOT NULL,
  lo_supplycost        INTEGER NOT NULL,
  lo_tax               INTEGER NOT NULL,
  lo_commitdate        INTEGER NOT NULL,
  lo_shipmode          VARCHAR(10) NOT NULL
);
CREATE TABLE
sampledb=> copy dm_schema.lineorder from 's3://awssampledb-yoheia/ssbgz/lineorder' 
iam_role 'arn:aws:iam::123456789012:role/redshift-spectrum-s3-fullaccess'
gzip compupdate off region 'ap-northeast-1';

INFO:  Load into table 'lineorder' completed, 600037902 record(s) loaded successfully.
ERROR:  Transaction 6931776 is aborted due to exceeding the disk space quota in schema(s): (Schema: dm_schema, Quota: 2048, Current Disk Usage: 25096). ★
Free up disk space or request increased quota for the schema(s). ★
  • スキーマのストレージ使用量制限を確認する
% psql -a "host=redshift-cluster-poc-ads.*******.ap-northeast-1.redshift.amazonaws.com user=awsuser dbname=sampledb port=5439"
Password for user awsuser: 
psql (14.3, server 8.0.2)
SSL connection (protocol: TLSv1.2, cipher: ECDHE-RSA-AES256-GCM-SHA384, bits: 256, compression: off)
Type "help" for help.


sampledb=# select * from SVV_SCHEMA_QUOTA_STATE
;
 schema_id |                                                           schema_name                                                            | schema_owner | quota | disk_usage | disk_usage_pct 
-----------+----------------------------------------------------------------------------------------------------------------------------------+--------------+-------+------------+----------------
    394209 | dm_schema                                                                                                                        |          218 |  2048 |          0 |              0
    

sampledb=# select * from STL_SCHEMA_QUOTA_VIOLATIONS;
 ownerid | userid | xid | pid | schema_id | schema_name | quota | disk_usage | disk_usage_pct | timestamp | disk_usage_diff 
---------+--------+-----+-----+-----------+-------------+-------+------------+----------------+-----------+-----------------
(0 rows)