Posted at

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

More than 1 year has passed since last update.


はじめに

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

RedshiftのSQLは基本的にはPostgreSQLをベースにしてるようですが、ただ完全な互換性があるわけではなく、ちょいちょいSQL構文が違ったり、SQL文は同じでも効果が違ったりするので油断ならないです。

都度Redshiftの公式ドキュメントを確認するのが確実です。

SQL リファレンス

特定のグループに所属するユーザにビューだけ見せたい場合の手順は若干煩雑なので、必要な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_ownermasked スキーマに作った場合に、 masked_viewers に自動でGRANTします。

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

最後に、一番大事なところですが masked_viewersunmasked スキーマの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 は末尾へのカラム追加であれば、エラーにならずに成功します。