Redshift で列レベルのアクセス制御を検証してみた。
- Redshift に接続する
psql "host=redshift-cluster-4.********.ap-northeast-1.redshift.amazonaws.com user=awsuser dbname=dev port=5439"
- テーブルを作成する
CREATE TABLE customer ( c_custkey INTEGER NOT NULL, c_name VARCHAR(25) NOT NULL, c_address VARCHAR(25) NOT NULL, c_city VARCHAR(10) NOT NULL, c_nation VARCHAR(15) NOT NULL, c_region VARCHAR(12) NOT NULL, c_phone VARCHAR(15) NOT NULL, c_mktsegment VARCHAR(10) NOT NULL );
- データを自アカウントの S3 にアップロードする
- S3 to S3 copy で "An error occurred (AccessDenied) whencalling the GetObjectTagging operation: Access Denied" エラーになったため、一旦 EC2 にダウンロードしている
mkdir ssbgz aws s3 mb awssampledb-123456789012 aws s3 cp --recursive s3://awssampledbuswest2/ssbgz/ ./ssbgz/ aws s3 cp --recursive ./ssbgz/ s3://awssampledb-awssampledb-123456789012/ssbgz/
- データをロードする
copy customer from 's3://awssampledb-123456789012/ssbgz/customer' iam_role 'arn:aws:iam::123456789012:role/RedshiftS3ReadOnly' gzip compupdate off region 'ap-northeast-1';
- ユーザーを作成する
create user test_user1 password 'Password1';
- 特定カラムだけアクセス権を付与する
GRANT SELECT(c_custkey, c_name, c_mktsegment) ON customer TO test_user1;
- スーパーユーザーでは全カラムにアクセスできる
dev=# select * from customer limit 10; c_custkey | c_name | c_address | c_city | c_nation | c_region | c_phone | c_mktsegment -----------+--------------------+--------------------------+------------+------------+-------------+-----------------+-------------- 1401 | Customer#000001401 | C4vlB8ENikVmaMiz | INDONESIA9 | INDONESIA | ASIA | 19-339-404-7859 | BUILDING 1402 | Customer#000001402 | F7 m0JwiCABmb | FRANCE 9 | FRANCE | EUROPE | 16-713-144-2780 | AUTOMOBILE 1403 | Customer#000001403 | ,ql804gtMc3uxTf | JAPAN 4 | JAPAN | ASIA | 22-458-624-2509 | HOUSEHOLD 1404 | Customer#000001404 | pIO5i3yjeODChGMH | JAPAN 2 | JAPAN | ASIA | 22-320-701-5582 | MACHINERY 1405 | Customer#000001405 | i9khsGcg17kWI4q5LKTc | MOZAMBIQU4 | MOZAMBIQUE | AFRICA | 26-285-488-6682 | BUILDING 1406 | Customer#000001406 | g1xS4snd0fzl4R, | ETHIOPIA 4 | ETHIOPIA | AFRICA | 15-767-155-6419 | HOUSEHOLD 1407 | Customer#000001407 | zZsTZ3nI | KENYA 8 | KENYA | AFRICA | 24-529-300-1554 | BUILDING 1408 | Customer#000001408 | NMIb3p1DyU,Z | IRAQ 1 | IRAQ | MIDDLE EAST | 21-901-381-6344 | HOUSEHOLD 1409 | Customer#000001409 | jzfaCks | IRAQ 3 | IRAQ | MIDDLE EAST | 21-667-401-3780 | MACHINERY 1410 | Customer#000001410 | yEUlreh6mkGmg8SIwKZooUOJ | RUSSIA 7 | RUSSIA | EUROPE | 32-358-270-1819 | HOUSEHOLD
- ユーザーを test_user1 に切り替える
dev=# set session authorization test_user1; SET
- アクセス権を付与したカラムにはアクセスできる
dev=> select c_custkey, c_name, c_mktsegment from customer limit 10; c_custkey | c_name | c_mktsegment -----------+--------------------+-------------- 1301 | Customer#000001301 | MACHINERY 1302 | Customer#000001302 | MACHINERY 1303 | Customer#000001303 | MACHINERY 1304 | Customer#000001304 | AUTOMOBILE 1305 | Customer#000001305 | MACHINERY 1306 | Customer#000001306 | MACHINERY 1307 | Customer#000001307 | MACHINERY 1308 | Customer#000001308 | MACHINERY 1309 | Customer#000001309 | AUTOMOBILE 1310 | Customer#000001310 | HOUSEHOLD (10 行)
- アクセス権を付与してないカラムにはアクセスできない。 select * from もエラーになる。テーブル定義は確認できる。
dev=> select * from customer limit 10; ERROR: permission denied for relation customer dev=> select c_address, c_city from customer; ERROR: permission denied for relation customer dev=> \d customer; テーブル"public.customer" 列 | タイプ | 照合順序 | Null 値を許容 | デフォルト --------------+-----------------------+----------+---------------+------------ c_custkey | integer | | not null | c_name | character varying(25) | | not null | c_address | character varying(25) | | not null | c_city | character varying(10) | | not null | c_nation | character varying(15) | | not null | c_region | character varying(12) | | not null | c_phone | character varying(15) | | not null | c_mktsegment | character varying(10) | | not null |
