Amazon Redshiftで行レベルのアクセス制御を試してみた
先日、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(データアナリィクス)事業本部のナガマサでした。
![[アップデート] Amazon Redshift Query Editor V2、結果セットと出力サイズの上限を100MBに拡大したので検証しました](https://images.ctfassets.net/ct0aopd36mqt/wp-thumbnail-63f1274931b942e9a92e601c1127ad73/cfe87ec6d62fa2fc3c474ed4cb2f6c2e/amazon-redshift)
