Redshiftでテーブルの実体を見せずにビューだけ見せてアクセス制限したい

# はじめに
セキュリティ上の理由で、Redshift上の特定のテーブル/カラムを見せる人をコントロールしたいことがあります。

RedshiftのSQLは基本的にはPostgreSQLをベースにしてるようですが、ただ完全な互換性があるわけではなく、ちょいちょいSQL構文が違ったり、SQL文は同じでも効果が違ったりするので油断ならないです。
都度Redshiftの公式ドキュメントを確認するのが確実です。

[SQL リファレンス](http://docs.aws.amazon.com/ja_jp/redshift/latest/dg/cm_chap_SQLCommandRef.html)

特定のグループに所属するユーザにビューだけ見せたい場合の手順は若干煩雑なので、必要なSQLをまとめておきます。
Postgresの場合と比較して認識している差分は後述します。

# 設定方法
以降で、作業するユーザ名を明示していない箇所は基本的にスーパーユーザで作業して下さい。
特定のユーザで作業する必要がある場合はユーザ名を明示します。

# グループの作成
まず、マスクされてるビューだけ見えるグループと、マスクされていないテーブルの生データが見れるグループを作成します。

```sql
CREATE GROUP unmasked_viewers;
CREATE GROUP masked_viewers;
```

# ユーザの作成
テーブルのオーナー、ビューのオーナー、ビューを参照するユーザを作成します。

```sql
CREATE USER table_owner PASSWORD 'xxxx';
CREATE USER view_owner PASSWORD 'xxxx' IN GROUP unmasked_viewers;
CREATE USER viewer PASSWORD 'xxxx' IN GROUP masked_viewers;
```

## スキーマの作成
テーブルを格納するスキーマと、ビューを格納するスキーマを分けます。AUTHORIZATION句でオーナーを設定します。

```sql
CREATE SCHEMA unmasked AUTHORIZATION table_owner;
CREATE SCHEMA masked AUTHORIZATION view_owner;
```

## 権限の付与

unmaskedなスキーマのUSAGE権限をunmasked_viewersに付与します。

```sql
GRANT USAGE ON SCHEMA unmasked TO GROUP unmasked_viewers;
```

次にunmaskedなスキーマのすべてのテーブルをunmasked_viewersでSELECTできるようにします。

```sql
GRANT SELECT ON ALL TABLES IN SCHEMA unmasked TO GROUP unmasked_viewers;
```

しかしながら上記のGRANT文はすでに存在するテーブルにしか効果がありません。罠すぎる。
後から増えるテーブルについても自動でGRANTするためには、 `DEFAULT PRIVILEGES` の設定を行います。

```sql
ALTER DEFAULT PRIVILEGES FOR USER table_owner IN SCHEMA unmasked GRANT SELECT ON TABLES TO GROUP unmasked_viewers;
```

この設定は `table_owner` ユーザが `unmasked` スキーマに新しいテーブルを作成した場合に、自動的にSELECT権限を `unmasked_viewers` に付与するという意味になります。
条件に誰が作ったのかというユーザがトリガになるので注意が必要です。基本的にはスキーマのオーナーが作る運用ルールにしておくのが管理しやすいと思います。

同じ要領で、maskedスキーマにmasked_viewersのUSAGE権限を与えます。

```sql
GRANT USAGE ON SCHEMA masked TO GROUP masked_viewers;
```

そして、maskedスキーマのすべてのテーブルへのSELECT権限を付与します。 `GRANT SELECT ON ALL VIEWS` という構文はありません。 `GRANT SELECT ON ALL TABLES` するとスキーマ内のすべてのビューもSELECTできるようになります。なのでスキーマをmaskedとunmaskedのスキーマを分けているのです。

```sql
GRANT SELECT ON ALL TABLES IN SCHEMA masked TO GROUP masked_viewers;
```

同様に、あとで作成したビューにも自動でGRANTされるように、 `DEFAULT PRIVILEGES` を設定します。
この場合は、`view_owner` が `masked` スキーマに作った場合に、 `masked_viewers` に自動でGRANTします。

```sql
ALTER DEFAULT PRIVILEGES FOR USER view_owner IN SCHEMA masked GRANT SELECT ON TABLES TO GROUP masked_viewers;
```

最後に、一番大事なところですが `masked_viewers` に `unmasked` スキーマのUSAGE権限だけ必要なのがポイントです。
SELECTは不要です。というかSELECTは与えるとビューを作った意味がないです。

```sql
GRANT USAGE ON SCHEMA unmasked TO GROUP masked_viewers;
```

これがPostgres9系で試してたときと挙動が違って悩んだ。


## テーブルの作成

テスト用のテーブルを作成します。table_ownerユーザで作業して下さい。

```sql
CREATE TABLE unmasked.users(id int8, username varchar(16), email varchar(16));
```

ダミーのレコードを挿入します。

```sql
INSERT INTO unmasked.users(id, username, email) VALUES(1, 'hoge', 'hoge@example.com');
```

確認してコミットします。

```sql
SELECT * FROM unmasked.users;
 id | username |      email
----+----------+------------------
  1 | hoge     | hoge@example.com
(1 row)

COMMIT;
```


## ビューの作成

ビューを作成します。emailカラムを落として、見せないようにします。view_ownerユーザで作業して下さい。

```sql
CREATE VIEW masked.users AS SELECT id, username FROM unmasked.users;
```

## ビューの参照
ビューを参照してみます。viewerユーザで作業して下さい。

```sql
SELECT * FROM masked.users;
 id | username 
----+--------- 
  1 | hoge     
(1 row)
```

emailカラムが見えなくなっていれば成功です。

# おまけ
## search_pathの設定
unmaskedとmaskedのスキーマを省略してアクセスしたい場合は、search_pathを調整すれば可能です。

デフォルトのsearch_pathは `"$user",public` です。

```sql
ALTER USER view_owner SET search_path = "$user",unmasked;
ALTER USER viewer SET search_path = "$user",masked;
```

search_pathに入っているスキーマ名は修飾しなくてもアクセス可能です。これをうまく使うとユーザによって同じSQL文で透過的に見せるカラムを落としたりできます。
ちなみにこの設定はクラスタ全体 or ユーザ個別に設定できますが、グループ単位では設定できなくて、若干めんどいです。

## ビューのカラムを増やしたい場合
基本的にはビューをDROP/CREATEして再作成しないと反映されません。
`CREATE OR REPLACE VIEW` 文というのがありますが、試してみたところ、カラムが完全に一致する場合はエラーを出さずに正常終了しますが、カラムが増えたりするとエラーになります。

## PostgreSQLとの違い
上記のテーブルの実体を見せずにビューだけ見せてアクセス制限する方法で、認識しているPostgresとの違いは以下のとおりです。
ぐぐって出てくるPostgresの情報は概ね参考になりつつも、若干注意が必要です。

* Redshiftの場合は、masked_viewersは実テーブルの存在するunmaskedスキーマへのUSAGEが必要です。Postgresの場合は不要のようです。
* Redshiftの場合は、 `GRANT 〜 TO GROUP グループ名` の部分はGROUPキーワードを指定せずに単に `GRANT 〜 TO グループ名` とするとユーザ名と解釈されてそんなユーザいないエラーになります。Postgresは9系でUSERとGROUPの概念が統合されてROLEになったので、GROUPの指定が不要ですが、Redshiftの場合は明示が必要です。
* Redshiftの場合は、`CREATE OR REPLACE VIEW` はカラムが一致していないとエラーになります。 Postgresだと `CREATE OR REPLACE VIEW` は末尾へのカラム追加であれば、エラーにならずに成功します。