Help us understand the problem. What is going on with this article?

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 は末尾へのカラム追加であれば、エラーにならずに成功します。
crowdworks
21世紀の新しいワークスタイルを提供する日本最大級のクラウドソーシング「クラウドワークス」のエンジニアチームです!
https://crowdworks.co.jp/
Why not register and get more from Qiita?
  1. We will deliver articles that match you
    By following users and tags, you can catch up information on technical fields that you are interested in as a whole
  2. you can read useful information later efficiently
    By "stocking" the articles you like, you can search right away
Comments
No comments
Sign up for free and join this conversation.
If you already have a Qiita account
Why do not you register as a user and use Qiita more conveniently?
You need to log in to use this function. Qiita can be used more conveniently after logging in.
You seem to be reading articles frequently this month. Qiita can be used more conveniently after logging in.
  1. We will deliver articles that match you
    By following users and tags, you can catch up information on technical fields that you are interested in as a whole
  2. you can read useful information later efficiently
    By "stocking" the articles you like, you can search right away
ユーザーは見つかりませんでした