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

  • 0
    いいね
  • 0
    コメント

    はじめに

    セキュリティ上の理由で、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 は末尾へのカラム追加であれば、エラーにならずに成功します。