はじめに
セキュリティ上の理由で、Redshift上の特定のテーブル/カラムを見せる人をコントロールしたいことがあります。
RedshiftのSQLは基本的にはPostgreSQLをベースにしてるようですが、ただ完全な互換性があるわけではなく、ちょいちょいSQL構文が違ったり、SQL文は同じでも効果が違ったりするので油断ならないです。
都度Redshiftの公式ドキュメントを確認するのが確実です。
特定のグループに所属するユーザにビューだけ見せたい場合の手順は若干煩雑なので、必要なSQLをまとめておきます。
Postgresの場合と比較して認識している差分は後述します。
設定方法
以降で、作業するユーザ名を明示していない箇所は基本的にスーパーユーザで作業して下さい。
特定のユーザで作業する必要がある場合はユーザ名を明示します。
グループの作成
まず、マスクされてるビューだけ見えるグループと、マスクされていないテーブルの生データが見れるグループを作成します。
CREATE GROUP unmasked_viewers;
CREATE GROUP masked_viewers;
ユーザの作成
テーブルのオーナー、ビューのオーナー、ビューを参照するユーザを作成します。
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句でオーナーを設定します。
CREATE SCHEMA unmasked AUTHORIZATION table_owner;
CREATE SCHEMA masked AUTHORIZATION view_owner;
権限の付与
unmaskedなスキーマのUSAGE権限をunmasked_viewersに付与します。
GRANT USAGE ON SCHEMA unmasked TO GROUP unmasked_viewers;
次にunmaskedなスキーマのすべてのテーブルをunmasked_viewersでSELECTできるようにします。
GRANT SELECT ON ALL TABLES IN SCHEMA unmasked TO GROUP unmasked_viewers;
しかしながら上記のGRANT文はすでに存在するテーブルにしか効果がありません。罠すぎる。
後から増えるテーブルについても自動でGRANTするためには、 DEFAULT PRIVILEGES の設定を行います。
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権限を与えます。
GRANT USAGE ON SCHEMA masked TO GROUP masked_viewers;
そして、maskedスキーマのすべてのテーブルへのSELECT権限を付与します。 GRANT SELECT ON ALL VIEWS という構文はありません。 GRANT SELECT ON ALL TABLES するとスキーマ内のすべてのビューもSELECTできるようになります。なのでスキーマをmaskedとunmaskedのスキーマを分けているのです。
GRANT SELECT ON ALL TABLES IN SCHEMA masked TO GROUP masked_viewers;
同様に、あとで作成したビューにも自動でGRANTされるように、 DEFAULT PRIVILEGES を設定します。
この場合は、view_owner が masked スキーマに作った場合に、 masked_viewers に自動でGRANTします。
ALTER DEFAULT PRIVILEGES FOR USER view_owner IN SCHEMA masked GRANT SELECT ON TABLES TO GROUP masked_viewers;
最後に、一番大事なところですが masked_viewers に unmasked スキーマのUSAGE権限だけ必要なのがポイントです。
SELECTは不要です。というかSELECTは与えるとビューを作った意味がないです。
GRANT USAGE ON SCHEMA unmasked TO GROUP masked_viewers;
これがPostgres9系で試してたときと挙動が違って悩んだ。
テーブルの作成
テスト用のテーブルを作成します。table_ownerユーザで作業して下さい。
CREATE TABLE unmasked.users(id int8, username varchar(16), email varchar(16));
ダミーのレコードを挿入します。
INSERT INTO unmasked.users(id, username, email) VALUES(1, 'hoge', 'hoge@example.com');
確認してコミットします。
SELECT * FROM unmasked.users;
id | username | email
----+----------+------------------
1 | hoge | hoge@example.com
(1 row)
COMMIT;
ビューの作成
ビューを作成します。emailカラムを落として、見せないようにします。view_ownerユーザで作業して下さい。
CREATE VIEW masked.users AS SELECT id, username FROM unmasked.users;
ビューの参照
ビューを参照してみます。viewerユーザで作業して下さい。
SELECT * FROM masked.users;
id | username
----+---------
1 | hoge
(1 row)
emailカラムが見えなくなっていれば成功です。
おまけ
search_pathの設定
unmaskedとmaskedのスキーマを省略してアクセスしたい場合は、search_pathを調整すれば可能です。
デフォルトのsearch_pathは "$user",public です。
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は末尾へのカラム追加であれば、エラーにならずに成功します。