Amazon Redshiftで行レベルのアクセス制御を試してみた

nagamasa

2022.07.31

この記事は公開されてから1年以上経過しています。情報が古い可能性がありますので、ご注意ください。

先日、Amazon Redshift(以降、Redshift)において「行レベルのアクセス制御」がサポートされていました。

本記事では、この機能についての基本的な操作を確認していきたいと思います。

行レベルのセキュリティ(Row Level Security)

行レベルのセキュリティ/Row Level Security (以降、RLS) のポリシーを作成し、作成したポリシーをユーザーやロールにアタッチすることで、特定の行(レコード)へアクセスできるように制御する機能です。

簡単な図で書くと、下記のような制御が可能です。

  • gunma ユーザー、tochigi ユーザーは、region の値が '関東' である行のみアクセスが可能
  • kagoshima ユーザーでは、region の値が '九州' である行のみアクセスが可能

今後は、元々サポートされていた列レベルのアクセス制御と併用して、より細かいアクセス制御ができるようになります。

事前情報

バージョン

Redshiftのクラスターバージョンは、1.0.40182で試しました。

$ select version();
                                                          version
---------------------------------------------------------------------------------------------------------------------------
 PostgreSQL 8.0.2 on i686-pc-linux-gnu, compiled by GCC gcc (GCC) 3.4.2 20041017 (Red Hat 3.4.2-6.fc3), Redshift 1.0.40182
(1 row)

サンプルデータ

任意のユーザー情報をテーブルに格納していきます。

sample=# CREATE SCHEMA rls;
CREATE SCHEMA
sample=#
sample=#
sample=# CREATE TABLE rls.users (
user_id    VARCHAR(100),
user_name  VARCHAR(20),
gender       VARCHAR(20),
prefecture   VARCHAR(20),
region       VARCHAR(100)
);
CREATE TABLE
sample=#
sample=# INSERT INTO rls.users
(user_id, user_name, gender, prefecture,region)
VALUES
('101', 'kagoshima','man', '鹿児島県','九州'),
('102', 'tottori','man', '鳥取県','中国'),
('103', 'fukui','man', '福井県','中部'),
('104', 'gunma','woman', '群馬県','関東'),
('105', 'tochigi','man', '栃木県','関東'),
('106', 'wakayama','woman', '和歌山県','近畿'),
('107', 'hiroshima','man', '広島県','中国'),
('108', 'tottori','woman', '鳥取県','中国'),
('109', 'miyagi','man', '宮城県','東北'),
('110', 'akita','man', '秋田県','東北');
INSERT 0 10
sample=#
sample=#
sample=# select * from rls.users;
 user_id | user_name | gender | prefecture | region
---------+-----------+--------+------------+--------
 101     | kagoshima | man    | 鹿児島県   | 九州
 102     | tottori   | man    | 鳥取県     | 中国
 103     | fukui     | man    | 福井県     | 中部
 104     | gunma     | woman  | 群馬県     | 関東
 105     | tochigi   | man    | 栃木県     | 関東
 106     | wakayama  | woman  | 和歌山県   | 近畿
 107     | hiroshima | man    | 広島県     | 中国
 108     | tottori   | woman  | 鳥取県     | 中国
 109     | miyagi    | man    | 宮城県     | 東北
 110     | akita     | man    | 秋田県     | 東北
(10 rows)

sample=#

制御のフロー

基本的に下記のフローに沿って制御することが可能です。

※ AWS公式ブログ 引用

主に下記の構文を用いて、行レベルのアクセス制御を適用していきます。

CREATE RLS POLICY

RLS POLICY を作成する構文です。 WITH句で制御するカラムを定義をして、USING句で制御する条件や構文を定義します。

CREATE RLS POLICY policy_name
[ WITH (column_name data_type [, ...]) [ [AS] relation_alias ] ]
USING ( using_predicate_exp )

ATTACH RLS POLICY

対象テーブルや対象ロールなどを指定して、RLS POLICY を適用する構文です。

ATTACH RLS POLICY policy_name ON [TABLE] table_name [, ...]
TO { user_name | ROLE role_name | PUBLIC } [, ...]

RLS 有効化

対象テーブルを指定して、RLSを有効化する構文です。

ALTER TABLE table_name ROW LEVEL SECURITY ON;

やってみた

ユーザーに直接RLS Policyを適用した場合の挙動確認

regionの値が九州のレコードのみ参照可能なkagoshimaユーザーを作成します。

※以降、一時的な検証目的のためユーザーパスワードは無効で作成しています。

sample=# CREATE USER kagoshima PASSWORD DISABLE;
CREATE USER

スキーマ、テーブルに対して、全件参照できるようにしておきます。

sample=# GRANT USAGE ON SCHEMA rls TO kagoshima;
GRANT
sample=# GRANT SELECT ON rls.users TO kagoshima;
GRANT

sample=# SET SESSION AUTHORIZATION 'kagoshima';
SET
sample=>
sample=> SELECT CURRENT_USER;
 current_user
--------------
 kagoshima
(1 row)

sample=> SELECT * FROM rls.users;
user_id | user_name | gender | prefecture | region
---------+-----------+--------+------------+--------
 101     | kagoshima | man    | 鹿児島県   | 九州
 102     | tottori   | man    | 鳥取県     | 中国
 103     | fukui     | man    | 福井県     | 中部
 104     | gunma     | woman  | 群馬県     | 関東
 105     | tochigi   | man    | 栃木県     | 関東
 106     | wakayama  | woman  | 和歌山県   | 近畿
 107     | hiroshima | man    | 広島県     | 中国
 108     | tottori   | woman  | 鳥取県     | 中国
 109     | miyagi    | man    | 宮城県     | 東北
 110     | akita     | man    | 秋田県     | 東北
(10 rows)

sample=>

regionの値が九州のレコードのみ参照可能なkyusyu_policyを作成します。

sample=# CREATE RLS POLICY kyusyu_policy WITH (region VARCHAR(100)) USING ( region = '九州');
CREATE RLS POLICY

kagoshimaユーザーに対して、rls.usersテーブルでkyusyu_policyを適用するようにします。

sample=# ATTACH RLS POLICY kyusyu_policy ON rls.users TO kagoshima;
ATTACH RLS POLICY
sample=#

rls.users テーブルに対して、行レベルのセキュリティ(RLS)を有効化します。

sample=# ALTER TABLE rls.users ROW LEVEL SECURITY on;
ALTER TABLE

kagoshima ユーザーに切り替えて、全件参照してみます。

sample=# SET SESSION AUTHORIZATION 'kagoshima';
SET
sample=> SELECT * FROM rls.users;
 user_id | user_name | gender | prefecture | region
---------+-----------+--------+------------+--------
 101     | kagoshima | man    | 鹿児島県   | 九州
(1 row)

想定通り、regionの値が九州のレコードのみが参照できるようになっています！

続いて、RLS POLICYの無効化 → RLS POLICYのDETACH → RLS POLICYのDROP を試してみます。

システムビューSVV_RLS_POLICYで、定義したRLS Policyの詳細を確認できます。

sample=# \x
Expanded display is on.
sample=#
sample=# SELECT * FROM SVV_RLS_POLICY;
-[ RECORD 1 ]---+-------------------------------------------------------
poldb           | sample
polname         | kyusyu_policy
polalias        | rls_table
polatts         | [{"colname":"region","type":"character varying(100)"}]
polqual         | "rls_table"."region" = CAST('九州' AS TEXT)
polenabled      | t
polmodifiedby   | cm-nagamasa
polmodifiedtime | 2022-07-31 08:46:05

sample=#

また定義したRLS POLICYの適用状況がシステムビューSVV_RLS_ATTACHED_POLICYで確認できます。

sample=#
sample=# SELECT * FROM SVV_RLS_ATTACHED_POLICY;
-[ RECORD 1 ]--------------
relschema   | rls
relname     | users
relkind     | table
polname     | kyusyu_policy
grantor     | cm-nagamasa
grantee     | kagoshima
granteekind | user
is_pol_on   | t
is_rls_on   | t

まずは、対象テーブルのRLSを無効化してみます。

sample=# ALTER TABLE rls.users ROW LEVEL SECURITY off;
ALTER TABLE

is_rls_onが f(false) に更新されました。

sample=# SELECT * FROM SVV_RLS_ATTACHED_POLICY;
-[ RECORD 1 ]--------------
relschema   | rls
relname     | users
relkind     | table
polname     | kyusyu_policy
grantor     | cm-nagamasa
grantee     | kagoshima
granteekind | user
is_pol_on   | t
is_rls_on   | f

この状態で、kagoshimaユーザーに切り替えて全件参照してみても、
RLSが無効化されているため、テーブルに存在している本来のレコードが全件参照できる状態です。

sample=# SET SESSION AUTHORIZATION 'kagoshima';
SET
sample=>
sample=> SELECT current_user;
 current_user
--------------
 kagoshima
(1 row)
sample=> SELECT * FROM rls.users;
 user_id | user_name | gender | prefecture | region
---------+-----------+--------+------------+--------
 101     | kagoshima | man    | 鹿児島県   | 九州
 102     | tottori   | man    | 鳥取県     | 中国
 103     | fukui     | man    | 福井県     | 中部
 104     | gunma     | woman  | 群馬県     | 関東
 105     | tochigi   | man    | 栃木県     | 関東
 106     | wakayama  | woman  | 和歌山県   | 近畿
 107     | hiroshima | man    | 広島県     | 中国
 108     | tottori   | woman  | 鳥取県     | 中国
 109     | miyagi    | man    | 宮城県     | 東北
 110     | akita     | man    | 秋田県     | 東北
(10 rows)

sample=>

続いて、RLS POLICYをDETACHしてみます。

sample=# DETACH RLS POLICY kyusyu_policy ON rls.users FROM kagoshima;
DETACH RLS POLICY
sample=#

SVV_RLS_ATTACHED_POLICYを参照してみます。

sample=# \x
Expanded display is on.
sample=# SELECT * FROM SVV_RLS_ATTACHED_POLICY;
(0 rows)

レコードがありませんでした。

SVV_RLS_ATTACHED_POLICYに関しては、
レコードの有無でRLS POLICYがATTACHされているかどうか確認できそうですね。

SVV_RLS_POLICYについては、まだRLS POLICY自体を削除してないので、レコードが残っている状態です。

sample=# SELECT * FROM SVV_RLS_POLICY;
-[ RECORD 1 ]---+-------------------------------------------------------
poldb           | sample
polname         | kyusyu_policy
polalias        | rls_table
polatts         | [{"colname":"region","type":"character varying(100)"}]
polqual         | "rls_table"."region" = CAST('九州' AS TEXT)
polenabled      | t
polmodifiedby   | cm-nagamasa
polmodifiedtime | 2022-07-31 08:46:05

このRLS POLICY自体を削除するには、DROP RLS POLICY 構文で削除することが可能です。

sample=# DROP RLS POLICY kyusyu_policy;
DROP RLS POLICY

RLS POLICY自体が削除されていることが確認できました。

sample=# SELECT * FROM svv_rls_policy;
(0 rows)

ロールにRLS POLICYを適用して確認

regionの値が関東のレコードのみアクセスを想定したgunmaユーザー、tochigiユーザーを作成します。

sample=# CREATE USER gunma PASSWORD DISABLE;
CREATE USER

sample=# CREATE USER tochigi PASSWORD DISABLE;
CREATE USER

kanto_roleを作成します。

sample=# CREATE ROLE kanto_role;
CREATE ROLE

sample=# GRANT ROLE kanto_role TO gunma, tochigi;
GRANT
sample=#

regionの値が関東のレコードのみアクセス可能にする kanto_policy を作成します。

sample=# CREATE RLS POLICY kanto_policy WITH (region VARCHAR(100)) USING ( region = '関東');
CREATE RLS POLICY
sample=#

kanto_roleに対して、rls.usersテーブルでkanto_policyを適用します。

sample=# ATTACH RLS POLICY kanto_policy ON rls.users TO ROLE kanto_role;
ATTACH RLS POLICY

gunma ユーザーに切り替えて、全件参照してみます。

sample=# SET SESSION AUTHORIZATION 'gunma';
SET
sample=>
sample=> SELECT * FROM rls.users;
 user_id | user_name | gender | prefecture | region
---------+-----------+--------+------------+--------
 104     | gunma     | woman  | 群馬県     | 関東
 105     | tochigi   | man    | 栃木県     | 関東
(2 rows)

一応tochigiユーザーにも切り替えて、全件参照してみます。

sample=> SET SESSION AUTHORIZATION 'tochigi';
SET
sample=>
sample=> SELECT CURRENT_USER;
 current_user
--------------
 tochigi
(1 row)

sample=>
sample=> SELECT * FROM rls.users;
 user_id | user_name | gender | prefecture | region
---------+-----------+--------+------------+--------
 104     | gunma     | woman  | 群馬県     | 関東
 105     | tochigi   | man    | 栃木県     | 関東
(2 rows)

sample=>

想定通りの行(レコード)のみ、参照できることを確認できました！

current_userごとにアクセスできる行を制御する場合

下記のように user_name の値が current_user の戻り値と一致した行のみアクセスできようなRLS POLICYを定義します。

sample=# CREATE RLS POLICY see_only_own_policy WITH (user_name VARCHAR(100)) USING (user_name = current_user);
CREATE RLS POLICY
sample=#

wakayamaユーザー、fukuiユーザーを作成します。

sample=# CREATE USER wakayama PASSWORD DISABLE;
CREATE USER
sample=#
sample=# CREATE USER fukui PASSWORD DISABLE;
CREATE USER

users_roleを作成し、rls.usersテーブルに対してのアクセス権限を付与します。

sample=# CREATE ROLE users_role;
CREATE ROLE
sample=# GRANT USAGE ON SCHEMA rls TO ROLE users_role;
GRANT
sample=# GRANT SELECT ON rls.users TO ROLE users_role;
GRANT
sample=# GRANT ROLE users_role TO wakayama, fukui;
GRANT

see_only_own_policy を users_role に適用したのですが、エラーとなりました。

sample=# ATTACH RLS POLICY see_only_own_policy ON rls.users TO ROLE users_role;
ERROR:  column "user_name" with type "character varying(20)" in relation "users" is incompatible type "character varying(100)" in policy
sample=#

原因は、RLS POLICY 定義上のカラム定義と対象テーブル上のカラム定義が不一致のため、エラーとなっていました。

再度、RLS POLICYを作り直して、RLS POLICYをATTACHします。

sample=# DROP RLS POLICY see_only_own_policy;
DROP RLS POLICY
sample=#
sample=# CREATE RLS POLICY see_only_own_policy WITH (user_name VARCHAR(20)) USING (user_name = current_user);
CREATE RLS POLICY
sample=#
sample=#
sample=# ATTACH RLS POLICY see_only_own_policy ON rls.users TO ROLE users_role;
ATTACH RLS POLICY
sample=#

対象テーブルがのRLSが有効化されてない場合は、有効化します。

sample=# ALTER TABLE rls.users ROW LEVEL SECURITY on;
ALTER TABLE
sample=#

wakayamaユーザーに切り替えて確認してみました。

sample=# SET SESSION AUTHORIZATION 'wakayama';
SET
sample=>
sample=> select current_user;
 current_user
--------------
 wakayama
(1 row)
sample=> select * from rls.users;
 user_id | user_name | gender | prefecture | region
---------+-----------+--------+------------+--------
 106     | wakayama  | woman  | 和歌山県   | 近畿
(1 row)

想定通りの行(レコード)のみとなっています。

sample=# SET SESSION AUTHORIZATION 'fukui';
SET
sample=>
sample=>
sample=> select current_user;
 current_user
--------------
 fukui
(1 row)

sample=> select * from rls.users;
 user_id | user_name | gender | prefecture | region
---------+-----------+--------+------------+--------
 103     | fukui     | man    | 福井県     | 中部
(1 row)

fukuiユーザーに切り替わっても、current_userの値が正しく評価されて想定通りの行(レコード)のみ参照できました。

列レベルのアクセス制御と併用する場合

一旦rls.usersテーブルに対してSELECT権限をREVOKEした後に、参照可能なカラムを指定してSELECT権限を付与します。

sample=# REVOKE SELECT ON rls.users FROM ROLE users_role;
REVOKE
sample=# GRANT SELECT (user_id, user_name, prefecture, region) ON rls.users TO ROLE users_role;
GRANT
sample=#

fukuiユーザーに切り替えて確認してみます。

sample=> SET SESSION AUTHORIZATION 'fukui';
SET
sample=>
sample=> select * from rls.users;
ERROR:  permission denied for relation users
sample=>

列レベルのアクセス制御が効いているため、全カラム指定だとエラーになることが確認できました。

sample=> select user_id, user_name, prefecture, region from rls.users;
 user_id | user_name | prefecture | region
---------+-----------+------------+--------
 103     | fukui     | 福井県     | 中部
(1 row)

sample=>

参照可能なカラムのみ指定した場合は、正しく参照できます。

簡単ではございますが、列レベル・行レベルのアクセス制御を併用したパターンも確認できました。

その他

パフォーマンス面での注意点や使用上の制約

下記の公式ドキュメントに細かく記載されていたので、実際に本番で使用する場合などは下記のドキュメントを参照しながら事前検証などを実施した方が良さそうです。

おわりに

今回は行レベルのアクセス制御に関して、基本的な操作を試してみました。

パフォーマンス面での注意点や使用上の制約など検証できてない部分があるので、今後もう少し細かく検証できればと思います。

以上、DA(データアナリィクス)事業本部のナガマサでした。

参考記事

スキルアップ|初心者向け|書評|デベキャン|AWS認定|スキルアップ|デベキャンだより|勉強会|コミュニティ

関連記事

Amazon DataZone の新しい生成 AIベースの新機能「AIリコメンデーション」を試してみる！

石川覚

2024.04.10

Amazon DataZone 初期導入から Amazon Redshift 統合の新機能まで解説する！

石川覚

2024.04.09

クラスメソッド データアナリティクス通信(AWSデータ分析編) – 2024年4月号

石川覚

2024.04.01

[Redshift] 伸縮自在なサイズ変更機能を利用して移行可能なクラスター構成を確認する方法

村上紗敏

2024.03.26